Moving tables across PostgreSQL instances
6 months ago
- #Database Migration
- #Logical Replication
- #PostgreSQL
- Moving specific tables between PostgreSQL instances requires logical replication, not Google's DMS which migrates entire databases.
- Grant replication access to user accounts on both source and destination PostgreSQL instances.
- Copy table schema using pg_dump, initially without constraints and indexes for efficiency.
- Logical replication operates in two modes: initial data dump and CDC (Change Data Capture) for real-time updates.
- Handle constraints and indexes separately; rebuild them after the initial data dump is complete.
- Create a publication on the source instance and a corresponding subscription on the destination to start replication.
- Monitor replication progress and lag using PostgreSQL system tables like pg_replication_slots and pg_stat_subscription.
- Manually sync sequences between source and destination instances before switching traffic.
- Use PgBouncer for near-zero downtime during the switchover by pausing, reloading, and resuming connections.
- Clean up by dropping the subscription and publication once the migration is confirmed successful.