Last active
October 17, 2025 19:59
-
-
Save andrepiske/c0421f279210db1452cae2af327751bd to your computer and use it in GitHub Desktop.
pg logical replication
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --------------------------------------------- | |
| -- Create logical replication: | |
| -- | |
| -- on source: | |
| CREATE PUBLICATION db_copy FOR ALL TABLES; | |
| -- optional: | |
| SELECT pg_create_logical_replication_slot('db_copier', 'pgoutput'); | |
| -- then use it in subscription with: | |
| -- WITH (create_slot=false, slot_name='db_copier'); | |
| -- on destination: | |
| CREATE SUBSCRIPTION db_inbox CONNECTION 'host=<host> port=5432 dbname=<dbname> user=<user> password=<password>' PUBLICATION db_copy; | |
| -- other useful commands: | |
| -- ALTER SUBSCRIPTION db_inbox DISABLE; | |
| -- ALTER SUBSCRIPTION db_inbox ENABLE; | |
| -- ALTER SUBSCRIPTION db_inbox REFRESH; | |
| --------------------------------------------- | |
| -- Is replication finished yet? | |
| -- | |
| -- | |
| -- https://www.postgresql.org/docs/current/catalog-pg-subscription-rel.html | |
| -- https://www.postgresql.org/docs/current/catalog-pg-class.html | |
| select cls.relname, sr.srsubstate, sr.srsublsn from pg_subscription_rel sr join pg_class cls on (cls.oid = sr.srrelid) where sr.srsubstate<>'r' order by sr.srsubstate asc; | |
| -- Long COPY operations: | |
| SELECT st.*, rel_cl.relname FROM pg_stat_progress_copy st LEFT JOIN pg_class rel_cl ON (rel_cl.oid = st.relid); | |
| -- LSN of logical replication slots: | |
| SELECT slot_name, active, confirmed_flush_lsn, (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance FROM pg_replication_slots WHERE slot_type='logical'; | |
| SELECT pg_current_wal_lsn(); | |
| --------------------------------------------- | |
| -- Migrate sequences: | |
| SELECT sequencename, last_value, increment_by FROM pg_sequences WHERE schemaname='public' ORDER BY sequencename ASC, last_value ASC; | |
| -- $ irb -rpg | |
| db = PG.connect(ENV['CONNURL']) | |
| data = db.query("SELECT sequencename, last_value, increment_by FROM pg_sequences WHERE schemaname='public' AND last_value IS NOT NULL;").to_a.map { |x| [x['sequencename'], Integer(x['last_value']), Integer(x['increment_by'])] } | |
| puts data.map{ |seq, last, _| "SELECT setval('#{seq}', #{last}, true);" } | |
| -- alternatively: puts data.map{ |seq, last, incr| "ALTER SEQUENCE #{seq} RESTART WITH #{last + incr};" } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment