A PostgreSQL migration can approach zero user-visible downtime when the old and new systems run in parallel, changes are continuously replicated, the application is made compatible with both states, data is reconciled, and traffic is switched through a rehearsed cutover. The database copy is only one workstream. The harder work is schema compatibility, sequences, large objects, extensions, writes during cutover, application connections and rollback.
Zero downtime is a business objective, not a literal promise. Define an allowable interruption and recovery point, then design for it.
Choose the migration method
Dump and restore is simple and dependable when downtime is acceptable. pg_upgrade is efficient for compatible in-place major upgrades but requires a controlled outage. Physical replication is useful for matching versions and infrastructure transitions. Logical replication supports selective replication and cross-major-version migration, allowing old and new clusters to run together.
Choose from data volume, version path, platform constraints, extension compatibility, write rate, downtime tolerance and rollback needs. Do not select logical replication merely because zero downtime sounds desirable; it adds operational complexity.
Define success and failure before building
Set the maximum user-visible interruption, recovery-time objective, recovery-point objective, performance target, reconciliation tolerance and rollback deadline. Name the business workflows that must remain available. Define who can stop the cutover and which signals trigger rollback.
A cutover without pre-agreed thresholds becomes an executive debate during an incident.
Phase one: discovery
Inventory PostgreSQL versions, databases, schemas, tables, row counts, growth, write hotspots, sequences, extensions, functions, triggers, collations, roles, privileges, jobs, backups, replicas, connection pools and downstream consumers. Map every application and reporting dependency.
Profile long-running transactions, replication capacity, WAL generation, network throughput and storage. Identify tables without primary keys because logical replication needs stable row identity for efficient updates and deletes.
Phase two: compatibility assessment
Compare supported extensions, data types, reserved words, planner behaviour, authentication, collation and configuration. Test application drivers and ORMs against the target version. Review release notes for behaviour changes.
Use production-like data to run query regression and performance tests. A syntactically successful migration can still change query plans and latency.
Phase three: target preparation
Provision the target with encryption, networking, monitoring, backups, high availability, maintenance settings and access controls. Apply schema through version-controlled migrations. Configure roles and privileges deliberately rather than copying historical excess.
Size replication slots and WAL retention. Monitor disk pressure because a stalled subscriber can retain WAL on the publisher.
Phase four: initial load and continuous replication
Load the bulk data, then establish logical replication for ongoing changes. For very large systems, partition the migration or use parallel copy carefully. Track lag in bytes and time, apply errors and table synchronisation.
Logical replication does not automatically solve every object. Plan sequences, large objects, materialised views, DDL, generated data and jobs. Freeze or coordinate schema changes while replication is active.
Application compatibility patterns
Use expand-and-contract changes. Add new columns or structures first, deploy code that can tolerate both versions, backfill, switch reads and writes, then remove the old path later. Avoid destructive schema changes before all application instances are compatible.
Control connections through a stable endpoint or pooler. Shorten connection lifetime before cutover so clients move quickly. Test failover behaviour for workers, scheduled jobs and long-lived sessions.
Validation that goes beyond row counts
Compare table counts, checksums or sampled hashes, aggregates, null patterns, sequence values and domain-specific balances. Re-run critical business workflows against the target. Compare query latency, locks, CPU, memory, storage and error rates.
For financial or inventory systems, use business reconciliation: totals by day, account or order state matter more than equal row counts alone.
Rehearse the cutover
Run at least one full rehearsal with representative data and traffic. Time every step. Validate permissions, DNS or endpoint changes, pool reload, job control, sequence synchronisation, monitoring and rollback.
Produce a minute-by-minute runbook with command owner, expected output, decision point and evidence location. Rehearsal should expose ambiguous responsibilities.
The cutover sequence
Announce the change window and freeze unrelated releases. Confirm backups and target health. Stop or drain background writers that cannot be safely replicated. Reduce replication lag to the agreed threshold. Quiesce writes briefly if required. Synchronise sequences and residual objects. Switch the connection endpoint. Run smoke and business tests. Re-enable jobs in a controlled order. Observe before declaring success.
Keep the old environment isolated but recoverable until the rollback window closes.
Rollback design
Rollback is simple only before new writes occur on the target. After cutover, returning to the old primary requires reverse replication, replay or acceptance of data loss. Decide the strategy in advance.
Use feature flags and routing controls for application rollback, but ensure the data path is compatible. A code rollback that points to a stale database is not recovery.
Operational risks
Replication lag can grow under burst traffic. A slot can fill disk. Missing keys can make updates expensive. An extension may not exist on the target. Long transactions can delay synchronisation. Dual-running jobs can process work twice. Connection pools can keep old sessions alive.
Every risk needs monitoring, an owner and a tested response.
After cutover
Increase monitoring during the stabilisation period. Rebuild statistics, review query plans, verify backups and restore, rotate credentials, update documentation and remove temporary access. Decommission only after audit, retention and rollback obligations are satisfied.
Capture lessons and convert manual checks into repeatable automation.
Migration runbook checklist
The runbook should list exact source and target identities, owners, communication channels, backups, replication status, sequence values, jobs, endpoint changes, validation queries and rollback commands. Every command should have expected output and an escalation condition.
Use a separate observer to record timestamps and decisions. Prevent ad hoc command changes during cutover unless the incident lead approves them.
Schema-change strategy during replication
Freeze unnecessary DDL. For required changes, use backward-compatible migrations and apply them in the correct order on publisher and subscriber. Add nullable columns before code relies on them, backfill separately, then enforce constraints. Avoid renames and type changes that old code cannot tolerate.
Track schema version on both clusters. A data pipeline that appears healthy can still apply into a structurally incompatible target.
Sequences, identities and generated values
Logical replication does not keep ordinary sequences synchronised automatically in the same way as table data. Before cutover, calculate safe next values and prevent writes from creating collisions. Review identity columns, custom allocators and external identifiers.
Test inserts after the switch, not only reads. Sequence mistakes often appear only when the first new transaction arrives.
Performance validation plan
Capture baseline query latency, throughput, locks, buffer behaviour, I/O and connection usage. Replay representative workloads on the target. Examine changed plans and statistics. Test peak write rates while replication runs, because bulk synchronisation and WAL retention can alter capacity.
After cutover, watch percentile latency and business transaction time. A healthy database dashboard can hide a slower customer workflow.
Incident scenarios to rehearse
Rehearse replication-slot growth, subscriber failure, network interruption, a long transaction, target performance regression, authentication failure, stale pool connections and an incomplete sequence update. Practice abort before switch, rollback immediately after switch and recovery after new target writes.
The last scenario is the hardest and must have an explicit data decision. Do not describe rollback as one button unless reverse data flow has been proven.
Detailed execution checklist
Replication readiness
Confirm wal_level, slots, publications, subscriptions, network throughput and storage headroom. Monitor retained WAL and subscriber errors continuously. A stalled slot can become an availability incident on the source.
Table readiness
Identify missing primary keys, replica identity, partitioning, large tables and high-write objects. Test copy duration and update behaviour. Exclude or handle unsupported objects explicitly.
Object migration
Version-control roles, grants, extensions, functions, triggers, views, jobs and configuration. Logical replication handles table changes, not the complete operational database.
Write control
List every writer including APIs, workers, ETL, scripts and administrators. Decide how each is paused, drained or made dual-compatible. One forgotten batch job can invalidate reconciliation.
Connection switching
Use a stable endpoint and test pool refresh, DNS caching and long sessions. Verify application retry behaviour. Switching the database is ineffective if clients continue using old connections.
Data reconciliation
Predefine counts, hashes, aggregates and business balances. Automate comparisons and investigate differences before cutover. Produce a signed evidence pack for critical systems.
Backup and restore
Take protected backups of source and target and prove restore procedures. Replication is not a backup because errors and malicious changes can replicate.
Security parity
Validate encryption, authentication, roles, network controls, logging and secrets. Migration must not create a temporarily over-privileged target.
Change freeze
Control application, schema, infrastructure and configuration changes around rehearsals and cutover. Route emergency changes through the migration lead and replay them on both environments.
Communication
Define stakeholder notices, customer impact language, incident channel, go or no-go authority and status cadence. Operational clarity reduces pressure-driven mistakes.
Stabalisation
Maintain heightened monitoring, on-call coverage and reconciliation after cutover. Defer unrelated optimisation until correctness and durability are proven.
Retirement
Close replication safely, archive evidence, revoke old access and remove infrastructure after approval. Retaining an unmanaged old primary creates security and cost risk.
Practitioner planning workbook
Source health
For source health, measure load, WAL, locks, long transactions, storage and replication capacity. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Target parity
The target parity workstream should verify configuration, extensions, roles, network, encryption, backup and monitoring. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Schema inventory
Treat schema inventory as an operating control: capture tables, partitions, keys, sequences, functions, triggers, views and jobs. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
Application drivers
For application drivers, test ORM, driver, prepared statements, connection pooling and retry behaviour. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Initial copy
The initial copy workstream should select parallelism and throttling that protect production workload. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Ongoing sync
Treat ongoing sync as an operating control: monitor table state, apply error, lag, retained WAL and subscriber capacity. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
DDL governance
For ddl governance, coordinate backward-compatible schema change across source and target. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Sequence safety
The sequence safety workstream should set next values and test inserts immediately before and after cutover. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Background writers
Treat background writers as an operating control: identify workers, ETL, scripts, reports and administrator activity. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
Reconciliation
For reconciliation, automate counts, hashes, aggregates and domain balances with tolerances. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Performance
The performance workstream should replay peak queries and writes; compare plans and percentile latency. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Cutover endpoint
Treat cutover endpoint as an operating control: test proxy, DNS, pools, credentials, firewall and client refresh. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
Go/no-go evidence
For go/no-go evidence, present lag, reconciliation, target health, backup and team readiness. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Rollback boundary
The rollback boundary workstream should define response before and after new target writes occur. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Business smoke tests
Treat business smoke tests as an operating control: execute revenue, identity, reporting and operational workflows. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
Stabilisation
For stabilisation, increase monitoring, support and reconciliation through the agreed window. Record the current state, target state, accountable owner, dependencies and evidence required for approval. Test the assumption using production-like information, and define the failure signal that would stop or redirect the work.
Audit evidence
The audit evidence workstream should retain approvals, commands, results, timestamps and exceptions. Convert the decision into measurable acceptance criteria, an owner and a review date. Include normal operation, edge conditions and recovery, because a design that works only in the happy path is not production-ready.
Decommission
Treat decommission as an operating control: close slots, revoke access, archive data and remove source infrastructure safely. Preserve the supporting artefacts, unresolved questions and agreed exceptions. Recheck the control after launch so temporary migration choices do not become permanent unmanaged risk.
