Migrating a production database is often a high-stakes operation. Traditional "dump and restore" methods require significant maintenance windows, leading to service downtime. To solve this, PostgreSQL offers Logical Replication, a native mechanism that allows data to stream from a source to a destination in real-time.
How Logical Replication Works
At its core, logical replication leverages the Write-Ahead Log (WAL). While physical replication copies disk blocks, logical replication decodes the WAL into row-level changes (INSERTs, UPDATEs, and DELETEs).
The Publisher: On the source database, you define a Publication. This serves as a set of change sets for specific tables.
The Subscriber: On the destination database, you create a Subscription. The subscriber connects to the publisher, pulls an initial snapshot of the data, and then continuously "replays" ongoing changes.
The Replication Slot: The publisher uses a replication slot to ensure that WAL files are not deleted until the subscriber has confirmed receipt. This prevents data loss during transient network failures.
Step 1: Configuring the Source (Publisher)
Before starting, ensure your wal_level is set to logical in your postgresql.conf. For managed db this can be done by setting some flag from your cloud provider database management page.
To begin, connect to your source database and create a publication. You can choose to replicate the entire database or specific tables:
CREATE PUBLICATION <publication_name> FOR ALL TABLE;
CREATE PUBLICATION <publication_name> FOR TABLE tableA, tableB, tableC;
SELECT aiven_extras.pg_create_publication_for_all_tables('<publication_name>', 'INSERT,UPDATE,DELETE,TRUNCATE')
SELECT * FROM pg_publication;
Step 2: Preparing the Destination (Subscriber)
Important: Logical replication does not replicate schema definitions (DDL). You must ensure the table structures exist on the destination database before starting the subscription.
Once the schema is ready, connect to the destination and initiate the subscription:
CREATE SUBSCRIPTION <subscription_name_here>
CONNECTION 'dbname=<database_name_here> host=localhost port=5432 sslmode=disabled user=postgres password=<password_here>'
PUBLICATION <publication_name> WITH (slot_name = '<slot_name_here>', create_slot = true, copy_data = true);
Managed Service Nuances
If you are using managed PostgreSQL services, the standard CREATE SUBSCRIPTION command may be restricted due to permissions:
SELECT * FROM aiven_extras.pg_create_subscription(
'<subscription_name_here>',
'dbname=<database_name_here> host=localhost port=5432 sslmode=disabled user=postgres password=<password_here>',
'<publication_name>',
'<slot_name_here>',
TRUE, TRUE
);
- Google Cloud SQL: Ensure the
cloudsql.logical_decoding flag is set to on.
Step 3: Monitoring the Migration
The migration happens in two phases: the initial sync (copying existing data) and streaming (applying delta changes). You can monitor the progress with these queries:
SELECT
n.nspname || '.' || c.relname AS table_name,
sr.srsubstate AS state,
CASE sr.srsubstate
WHEN 'i' THEN 'initialize'
WHEN 'd' THEN 'data copying'
WHEN 's' THEN 'synchronized'
WHEN 'f' THEN 'finishedcopy'
WHEN 'r' THEN 'ready'
END AS state_description
FROM pg_subscription_rel sr
JOIN pg_class c ON c.oid = sr.srrelid
JOIN pg_namespace n ON n.oid = c.relnamespace;
SELECT * FROM pg_stat_activity;
Monitor Replication Lag
To ensure the destination is caught up before you perform the final cutover, check the lag:
SELECT
slot_name,
plugin,
slot_type,
active,
confirmed_flush_lsn,
pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS slot_lag_bytes
FROM
pg_replication_slots
WHERE
slot_type = 'logical';
Step 4: Final Cutover and Cleanup
Once the lag is near zero, point your application services to the new destination database. After the migration is confirmed successful, clean up the replication resources to free up system overhead.
Drop the Subscription:
DROP SUBSCRIPTION <subscription_name>;
SELECT * FROM aiven_extras.pg_drop_subscription('<subscription_name>');
ALTER SUBSCRIPTION mysub DISABLE;
ALTER SUBSCRIPTION mysub SET (slot_name = NONE);
Drop the Publication (on Source):
DROP publication <publication_name>;
Remove the Slot (if it persists):
SELECT pg_drop_replication_slot('<slot_name>');
Sequences are Not Replicated!
Logical replication only copies data changes. It does not keep sequences (used for auto-incrementing IDs) in sync. You can use the following query to get the current sequence values
SELECT t.sequence_name, nextval(t.sequence_name::text) - 1 FROM (SELECT sequence_schema, sequence_name
FROM information_schema.sequences) AS t;
SELECT setval('<sequence_name>', <value_here>);
Conclusion
Logical replication is an elegant solution for moving data with minimal impact on users. By following these steps—Publication, Subscription, and Monitoring—you can migrate gigabytes or terabytes of data while keeping your application online.
