6 min read
|
Saved February 14, 2026
|
Copied!
Do you care about this?
This article details the process of moving specific tables from one PostgreSQL instance to another using logical replication. It covers granting access, copying schema, setting up publications and subscriptions, and handling sequences and indexes to minimize downtime.
If you do, here's more
Moving tables between PostgreSQL instances requires careful planning, especially when specific tables need to be migrated rather than entire databases. The author opted for native logical replication to allow for the transfer of selected tables. The process starts with granting replication access to user accounts on both source and destination databases, followed by copying the table schema using `pg_dump`. It's essential that the schemas are identical, and constraints should be set aside during the initial data dump to avoid errors.
Logical replication operates in two phases: an initial dump of data followed by change data capture (CDC) for real-time updates. During the initial dump, specific constraints cannot be enforced, which necessitates creating tables without constraints and indexes first. After the structure is replicated, the author emphasizes the importance of creating a publication on the source instance and a subscription on the destination. This allows for data copying and real-time updates, with monitoring options available through PostgreSQL’s system tables.
Once the data is synchronized, the author discusses adding indexes and foreign keys, which should be done after the CDC state is established. The article also highlights the often-overlooked `ANALYZE` command, crucial for maintaining efficient query performance on the new instance. The final steps involve handling sequences manually, ensuring they are aligned between the two instances. Properly managing write operations and utilizing PgBouncer can help minimize downtime during the switchover, facilitating a smoother transition.
Questions about this article
No questions yet.