Speeding up logical replication setup
summary
pg_createsubscriber for Postgres 17 converts a prepared physical replica into a logical one to avoid slow per-table COPY-based initial sync, creating publication/subscription pairs and replication slots for every database and setting the recoveryTargetLSN to the latest replication slot LSN so the replica has all data up to a single cut point. It runs on the target server, drops preexisting subscriptions, removes unused physical slots, runs pg_resetwal to change the system identifier, and turns subscriptions on; for multi‑TB clusters this cuts setup from days to hours but breaks cascading physical stands, requires matching major versions and careful rollback planning.
AI-written summary · about
chapters (13)
- 0:00Introduction to talk
- 1:57Motivation for faster setup
- 3:04How initial sync works
- 7:08Introducing pg_create_subscriber
- 12:14Tool design requirements
- 15:37Behind the scenes concept
- 20:26Key steps in conversion
- 26:19Cleanup and final steps
- 32:14Tool interface and options
- 38:42Example execution
- 42:00Warnings and limitations
- 47:02When to avoid this approach
- 48:59Q&A session
related talks
36mHow to Upgrade Replication Clusters without Downtime?
59mLogical Replication...LIVE!
01h 41mPostgreSQL Upgrades with Logical Replication & Minimal Downtime
53mThe journey towards active replication in PostgreSQL
41mOnline Upgrade of replication clusters without downtime
44mThe journey towards active-active replication in PostgreSQL
31mPast, Present, and Future: Logical Decoding and Replication in PostgreSQL
56mLogical Replication of DDLs