This guide describes a practical approach for migrating a PostgreSQL database from an older major version to a newer one using logical replication and pgcopydb, while introducing a proxy layer (pgcat) that facilitates zero-downtime cutover and enables future read/write splitting without modifying application code.
This approach is suitable for very large PostgreSQL installations (multi-terabyte datasets and very large table counts).
Achieve zero-downtime migration from PostgreSQL 11 to PostgreSQL 14.
Use pgcopydb to orchestrate full copy + continuous logical replication.
Introduce pgcat as a stable proxy endpoint for applications.
Allow read/write splitting in the future without changing application logic.
Ensure predictable, reversible migration steps.
Applications → Pgcat → PostgreSQL 11 (source)
Pgcat simply forwards all connections to the existing database.
Applications → Pgcat → PostgreSQL 11 (source)
pgcopydb performs:
1. Full copy into PostgreSQL 14 (target)
2. Continuous logical replication (changes from PG11 → PG14)
Applications → Pgcat → PostgreSQL 14 (target, primary)
Optional: Pgcat may later route reads to replica instances.
Connection pooling
SQL-aware routing
Read/write split support
Mirroring (for future migrations)
Dynamic configuration reload
Acts as a stable entrypoint for applications
Orchestrates full data copy from source to target
Sets up logical replication automatically
Handles syncing and validation steps
Used by pgcopydb for continuous synchronization until switchover
Configure Pgcat to forward all traffic to PostgreSQL 11.
Update application DSNs to point to Pgcat instead of the old database.
No behavioral change for applications.
This stabilizes the entrypoint so that future cutover does not require application-level configuration updates.
Deploy a clean PostgreSQL 14 instance (e.g., AWS RDS).
Ensure required extensions exist on the target.
Ensure network access between migration host and both databases.
pgcopydb handles:
Full schema copy
Parallel table copy
Setting up publications and subscriptions
Continuous logical replication
Let pgcopydb run until the target database is fully synchronized except for live writes.
When ready for migration:
Place the application into brief write-freeze mode (read-only or temporarily stop writes).
Allow logical replication to catch up until lag is zero.
Validate key metrics and connection health.
This ensures a consistent cutover.
Update pgcat configuration to point the primary cluster to PostgreSQL 14.
Reload pgcat configuration.
Resume application writes.
Because applications connect through Pgcat, no DSN changes are required.
Downtime is limited to the brief freeze window.
Validate query responses, performance, and replication completeness.
Optionally keep the old PostgreSQL 11 instance in read-only mode for rollback.
Once satisfied, decommission the old cluster.
After migration, Pgcat becomes a persistent routing layer.
Pgcat can:
Route write queries to the primary
Route read queries to one or more replicas
Perform pool-level isolation
This allows you to introduce replicas for scaling without changing application code.
Zero downtime: achieved through logical replication + proxy-based cutover.
Scalable: works with very large datasets.
Application transparency: applications only connect to Pgcat.
Future flexibility: read/write split, mirroring, and sharding become possible without rework.
Safe rollback path: old cluster can be preserved until confident.
This workflow combines pgcopydb's powerful migration orchestration with pgcat's flexible routing layer to create a zero-downtime migration pipeline that remains beneficial after the migration is complete.
Applications see a consistent endpoint throughout, while the infrastructure gains the ability to scale horizontally and adopt more advanced database topologies without additional application changes.