patternsqlpostgresqlMajor
Logical replication for zero-downtime migrations and selective replication
Viewed 0 times
logical replicationPUBLICATIONSUBSCRIPTIONWALzero-downtime migrationreplication slotwal_level
Error Messages
ERROR: logical replication slot requires wal_level >= logicalERROR: table "orders" cannot be replicated because it does not have a primary key or REPLICA IDENTITYProblem
Major version upgrades, schema restructuring, and cross-region replication require either long downtime (pg_dump/restore) or complex tooling, with no native way to replicate a subset of tables.
Solution
Use logical replication to stream changes table-by-table:
-- On the source (primary):
ALTER SYSTEM SET wal_level = logical;
-- Restart required
CREATE PUBLICATION my_pub
FOR TABLE orders, products, customers;
-- On the destination (replica):
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=source_host dbname=mydb user=replicator'
PUBLICATION my_pub;
-- Monitor replication lag:
SELECT slot_name, confirmed_flush_lsn,
pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots;
-- On the source (primary):
ALTER SYSTEM SET wal_level = logical;
-- Restart required
CREATE PUBLICATION my_pub
FOR TABLE orders, products, customers;
-- On the destination (replica):
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=source_host dbname=mydb user=replicator'
PUBLICATION my_pub;
-- Monitor replication lag:
SELECT slot_name, confirmed_flush_lsn,
pg_current_wal_lsn() - confirmed_flush_lsn AS lag_bytes
FROM pg_replication_slots;
Why
Logical replication decodes WAL entries into row-level operations (INSERT/UPDATE/DELETE) that can be filtered by table and replayed on a different schema or PostgreSQL version. This enables gradual cutover without locking.
Gotchas
- wal_level = logical requires a server restart and increases WAL volume
- Tables must have a PRIMARY KEY or REPLICA IDENTITY FULL for UPDATE/DELETE replication
- DDL changes (ALTER TABLE) are not replicated; apply them manually on both sides
- Replication slots retain WAL until consumed; an abandoned slot causes disk fill
Code Snippets
Monitor logical replication lag
-- Check replication lag per slot:
SELECT slot_name,
pg_size_pretty(pg_current_wal_lsn() - confirmed_flush_lsn) AS lag
FROM pg_replication_slots
WHERE slot_type = 'logical';Context
Major version upgrades, cross-region replication, or selective table streaming
Revisions (0)
No revisions yet.