Migrating PostgreSQL to a New Environment with Logical Replication (Primary/Replica to Primary/Replica)

When you need to migrate PostgreSQL to a new environment and a newer major version, physical replication alone is not enough. Physical replication requires you to promote the replica to primary and upgrade it in-place during the switchover, resulting in more downtime and a higher likelihood of failure.

Logical replication solves that problem. You can replicate data from the old primary to a new primary running a newer version, validate everything while both systems are live, then cut over with a controlled maintenance window. Plus, the new primary will already have its replica in place, which has been streaming from it the entire time.

This guide walks through a pattern that works well in production:

  • Old pair: primary + replica (existing environment)
  • New pair: primary + replica (new environment)
  • Migration method: logical replication from old primary to new primary
  • Steady-state HA on new pair: streaming replication (optionally with WAL archive shipping as fallback)

Why This Pattern Works

Logical replication gives you a rolling migration path across major versions without requiring a long outage. You can:

  • Build the new cluster in parallel.
  • Continuously sync data while users stay on the old cluster.
  • Cut over after replication lag reaches zero.

Then, once the new primary is live, you keep your normal HA model with streaming replication to the new replica.

Prerequisites

  • PostgreSQL superuser access on old and new clusters
  • Firewall rules for PostgreSQL traffic
  • A dedicated replication user
  • Maintenance window for final cutover
  • Verified backup and rollback plan

Phase 1: Replicate Old Primary to New Primary with Logical Replication

1. Prepare connectivity and replication settings on the old primary

Allow the new primary to connect in pg_hba.conf:

host    replication     <replication_user>    <new_primary_ip>/32    md5
host    all             all           <new_primary_ip>/32    md5

Set logical replication-related parameters in postgresql.conf:

wal_level = logical
max_wal_senders = 12
max_replication_slots = 10

max_wal_senders and max_replication_slots are workload-dependent. Size them for your number of replicas, subscriptions, and expected concurrency rather than copying these values as-is.

This will require you to restart PostgreSQL after making the changes, which can be disruptive. I recommend doing the restart during a maintenance window to avoid interrupting your users. 

systemctl restart postgresql

2. Grant permissions required by logical replication

On the old primary, grant read access for the replication user:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <replication_user>;

For tables without a primary key, configure replica identity so UPDATE and DELETE can be replicated:

ALTER TABLE some_table_without_pk REPLICA IDENTITY FULL;

3. Prepare roles and schema on the new primary

Dump roles and schema from old primary:

pg_dumpall -r -f roles.sql
pg_dump -Fc -f schema.dmp -s appdb

Restore both to new primary:

psql -f roles.sql
pg_restore -d postgres -C schema.dmp

4. Optional: make safe schema improvements before cutover

This is one of the biggest advantages of this migration pattern, but timing matters: do these schema changes before you create the subscription in Step 5. The new primary will be a schema only database, a clean slate where you can make changes without being hindered by bloat.

Good candidates:

  • Widening key columns (integer to bigint) when growth is a concern
  • Adding missing indexes that would be too disruptive to build with existing data
  • Rebuilding views or functions that need version-specific cleanup

Keep changes "within reason":

  • Avoid risky app-level behavior changes during migration week.
  • Avoid table/column renames during the replication window.
  • Validate foreign keys, sequences, and dependent objects.

In practice, do the schema prep first, then start logical replication. After replication is active, freeze schema changes until cutover is complete.

5. Create publication on old primary and subscription on new primary

Create the publication on the old primary (publisher):

CREATE PUBLICATION app_upgrade FOR ALL TABLES;

Then connect to the new primary and run the subscription command there. This is the step that tells the new primary to subscribe to the old primary.

On new primary (subscriber):

CREATE SUBSCRIPTION app_upgrade
CONNECTION 'dbname=appdb host=<old_primary_ip> user=<replication_user> password=<password>'
PUBLICATION app_upgrade;

As soon as this command succeeds, PostgreSQL starts the initial table copy and then continues with change replication.

Make sure that the new primary has a clear path over the network to the old primary. This may seem obvious, but can often be overlooked, especially in firewalled environments.

6. Schema freeze while logical replication is active

Do not run schema changes on either side while the subscription is active unless you are executing a coordinated, tested change plan.

Why this matters:

  • PostgreSQL logical replication does not replicate DDL.
  • Publisher/subscriber schema drift can cause replication apply errors and stop replication.
  • Additive changes can sometimes be staged safely, but they still require careful ordering and validation.

Best practice for migrations like this is simple: freeze DDL after CREATE SUBSCRIPTION, finish data sync and cutover, then resume normal schema migrations.

7. Verify logical replication progress

Useful checks:

SELECT *, srrelid::regclass FROM pg_subscription_rel;

This shows per-table subscription state on the subscriber. The srrelid::regclass cast translates internal table IDs into readable table names so you can quickly spot anything not fully synchronized yet.

SELECT received_lsn, latest_end_lsn FROM pg_catalog.pg_stat_subscription;

This shows where the subscriber is in the WAL stream. In plain terms: how much data has been received and how far apply/ack progress has moved. During healthy catch-up, these values should stay close.

On publisher:

SELECT pg_current_wal_lsn();

This is the publisher's current WAL position right now. Compare it conceptually with subscriber progress (latest_end_lsn) to judge lag. If the publisher keeps running away from subscriber LSNs, you are not caught up yet.

The goal before cutover is straightforward: table sync complete and lag at/near zero.

Phase 2: Build New Replica from New Primary (Streaming Replication)

Once the new primary is receiving logical changes, build the new replica from it.

1. Configure replication access and baseline config

  • Add replication access for new replica in new primary pg_hba.conf
  • Ensure replica has matching core config where appropriate
  • Reload PostgreSQL after pg_hba.conf changes

2. Seed replica with pg_basebackup

On new replica:

systemctl stop postgresql
rm -rf /var/lib/postgresql/<version>/main/*
pg_basebackup -h <new_primary_ip> -p 5432 -U <replication_user> -D /var/lib/postgresql/<version>/main/ -Fp -Xs -R -C -S app_replica_slot
systemctl start postgresql

3. Optional but useful: keep WAL archive shipping enabled

Even with streaming replication and slots, keeping WAL archive shipping as fallback can improve recovery options if a replica falls behind or is rebuilt.

Personally, I prefer to implement WAL archive shipping. If a replica falls behind, it can catch up. Otherwise, I would have to rebuild the replica after a sustained network outage.

Example parameters:

archive_mode = on
archive_command = 'cp %p /path/to/wal_archive/%f~ && mv /path/to/wal_archive/%f~ /path/to/wal_archive/%f'

On replica, define a corresponding restore command if you use archive-based catch-up.

Example from this migration pattern:

restore_command = 'cp -i /postgresql_wal_backup/wal/16/%f %p'

4. Validate streaming health

On primary:

SELECT * FROM pg_stat_replication;
SELECT * FROM pg_replication_slots;

On replica:

SELECT * FROM pg_stat_wal_receiver;
SELECT pg_is_in_recovery();
SELECT
    CASE
        WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn()
        THEN 0
        ELSE EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())
    END AS log_delay;

I like this log_delay check because it gives you an easy seconds-behind estimate. Use it on the replica while it is in recovery (pg_is_in_recovery() = true). Like any timestamp-based lag metric, treat it as an operational estimate rather than an absolute.

Phase 3: Cutover (Maintenance Window)

1. Put applications in maintenance mode

  • Stop scheduled jobs that write to the database
  • Drain active DB sessions
  • Confirm no active write traffic remains

Useful check to see if there are any active queries:

SELECT * FROM pg_stat_activity WHERE state <> 'idle';

2. Confirm replication is fully caught up

Validate logical subscriber and streaming replica lag before final shutdown.

3. Stop old primary cleanly

systemctl stop postgresql

Wait for logs confirming clean shutdown. This is an important safety point: stopping the old primary cleanly ensures all remaining logical changes are delivered before you move write traffic.

4. Advance sequences on new primary (required)

Logical replication does not replicate sequence state. If you skip this step, newly inserted rows absolutely will collide with existing IDs after cutover.

Example query to list sequences:

SELECT relname FROM pg_class WHERE relkind = 'S';

Example query to advance one sequence to match table data:

SELECT setval('orders_id_seq', COALESCE((SELECT MAX(id) FROM orders), 1), true);

This sets orders_id_seq to the current max orders.id (or 1 if the table is empty) so the next insert does not reuse an existing ID.

I recommend creating a SQL file with all of the updates in it, then run your sequence update SQL on the new primary (example):

psql -h localhost -U appdb_user appdb < update-all-sequences.sql

Use whatever script/process you already trust for sequence alignment, but do not reopen writes until this is complete.

5. Finalize new primary

Typical post-cutover tasks:

  • Rebuild dependent views/functions changed during prep
  • Drop logical subscription to old primary
DROP SUBSCRIPTION app_upgrade;

6. Point applications to new primary

Update connection targets (DNS, host mappings, config, secrets, or service discovery) and validate application behavior.

Phase 4: Post-Cutover Validation and Cleanup

Validate

  • Application reads/writes succeed
  • Replication to new replica remains healthy
  • WAL archiving pipeline is healthy (if used)

Clean up

  • Disable old replication/backup jobs tied to retired cluster
  • Decommission old database hosts only after backup verification confirms new path is good

Common Pitfalls to Avoid

  • Forgetting REPLICA IDENTITY on tables without primary keys
  • Skipping sequence alignment before reopening writes
  • Making too many schema changes at once during migration
  • Declaring success before backup verification passes on the new cluster

Final Notes

Logical replication is not just an upgrade bridge. It is also a controlled transition window where you can perform overdue, low-risk schema maintenance while moving to new infrastructure.

If you keep the scope disciplined, verify lag continuously, and treat cutover as an operational event (not just a SQL event), this pattern gives you a clean migration path from old primary/replica to new primary/replica with minimal downtime and lower stress.