How to migrate a PostgreSQL database using logical replication

What and why

Logical replication streams row changes between PostgreSQL databases at the table level, even across major versions. This makes it an excellent tool for migrating to a new server or version with only a brief cutover. The source publishes changes; the target subscribes and applies them. This tutorial migrates a database with minimal downtime.

Prerequisites

• Two PostgreSQL servers (10 or newer), reachable from each other.
• Admin access to both and matching table definitions.
• A maintenance moment for the short final cutover.

Steps

1. Prepare both servers

On the source, set wal_level = logical and restart. Ensure max_replication_slots and max_wal_senders allow at least one slot. Configure pg_hba.conf and firewall to allow the target to connect.

2. Copy the schema

Logical replication does not copy DDL. Dump and restore the schema first:

pg_dump --schema-only -d source_db | psql -d target_db

3. Create a publication

On the source, publish the tables to migrate:

CREATE PUBLICATION migrate_pub FOR ALL TABLES;

4. Create a subscription

On the target, subscribe; by default it copies existing data, then streams changes:

CREATE SUBSCRIPTION migrate_sub
  CONNECTION 'host=source dbname=source_db user=repl password=secret'
  PUBLICATION migrate_pub;

5. Monitor replication lag

Watch progress until the initial copy finishes and the target keeps up:

SELECT * FROM pg_stat_subscription;

On the source, check the replication slot's lag with pg_replication_slots.

6. Cut over traffic

When lag is near zero, stop writes to the source, let the last changes apply, then point the application at the target. Reset sequences on the target with setval because logical replication does not advance them. Drop the subscription afterward.

Verification

Before cutover, compare row counts on key tables between source and target; they should match once caught up. After cutover, confirm the application writes succeed on the target and that sequences produce non-colliding ids.

Next Steps

Use FOR TABLE publications to migrate selectively, add tables incrementally with ALTER SUBSCRIPTION ... REFRESH PUBLICATION, and keep the old server briefly as a fallback before decommissioning.