PostgreSQL Migration Checklist: Cutover, Validation, and Rollback

A PostgreSQL migration can look successful right up to the first customer write. The application connects, dashboards are green, and the copied tables have the expected row counts. Then an insert fails, a background worker updates the old database, or a rollback leaves newly created orders behind.

The central question is which database owns writes at each stage, and how you prove the next database is ready to take over.

This guide is for teams moving a PostgreSQL application between hosting providers or into their own cloud account. It assumes a single writable source and a separate target, with either a maintenance window or one-way replication during preparation. It is a planning checklist, not an executable runbook: commands, privileges, and replication capabilities depend on your PostgreSQL versions and providers.

If you are still deciding whether to move, start with our PaaS alternatives guide. Once the move makes sense, use the gates below to decide when it is safe to proceed.

1. Define the interruption you can actually support

“Zero downtime” is too vague to serve as an acceptance criterion. A site that still serves cached pages while rejecting purchases is available for browsing, but unavailable for its most important transaction.

Write down three separate requirements:

Requirement	Question to settle before choosing tooling
Write interruption	How long may customers be unable to create or change data?
Recovery time objective (RTO)	How quickly must service return if the migration fails?
Recovery point objective (RPO)	How much committed data, measured in time, may be lost during recovery?

For example, a team might accept a five-minute planned write pause, require recovery within fifteen minutes, and allow no loss of acknowledged orders. These are illustrative business requirements, not achievable-by-default targets.

That combination immediately rules out “restore yesterday’s backup” as the complete rollback plan. It also means any queued requests need durable storage and clear retry behavior before the application tells a customer their action succeeded.

Choose the simplest approach that meets the measured requirements:

• Dump and restore during a maintenance window: appropriate when a rehearsal shows that export, transfer, restore, validation, and reconnection fit the allowed interruption.
• Initial copy plus change data capture (CDC): appropriate when copying the database would take too long during the write pause. Replicate changes while production runs, then pause writers for final synchronization and cutover.

CDC reduces the work remaining at cutover. It does not, by itself, establish a safe rollback path or guarantee uninterrupted writes.

2. Inventory every writer and every migration exception

Start with the application topology. The web service is rarely the only writer.

List queue consumers, scheduled jobs, webhooks, administrative tools, reporting jobs that write results, and deployment scripts that run schema migrations. For each, record its database credential, owner, pause procedure, and restart procedure.

Then inspect the database and target environment:

• PostgreSQL versions, extensions, encoding, collation requirements, and provider restrictions.
• Tables, keys, partitions, sequences, large objects, views, functions, and triggers.
• Roles, grants, row-level security policies, and application connection settings.
• Connection limits, pooler behavior, network access, TLS verification, and representative query performance.

When using native PostgreSQL logical replication, schema changes and sequence state are not replicated automatically. Large objects are also excluded. Copying rows is therefore only part of preparing a writable replacement. Review the PostgreSQL 18 logical replication restrictions and the documentation for your exact version and migration tool.

For native logical replication, confirm the source permits wal_level = logical and sufficient replication slots and WAL senders. Subscriber worker capacity also matters. Managed providers may control these settings or require a restart; check before scheduling the migration. See PostgreSQL replication configuration.

Exit gate: every writer has an owner, and every object outside the replication mechanism has a tested transfer or rebuild procedure.

3. Rehearse the full cutover, including failure

A useful rehearsal starts with representative data and ends with either a working application on the target or a demonstrated return to the source.

Measure the initial copy time, replication catch-up under representative write volume, final validation time, and the time to refresh application connections. Exercise the actual credentials and network path the application will use.

Include failure cases: the target becomes unreachable, replication stops, a validation check fails, or a worker does not pause. The runbook should tell the operator what to do without improvising database ownership during the incident.

Keep unrelated application releases and destructive schema changes out of the cutover. Fewer simultaneous changes make failures easier to diagnose and recovery easier to test.

Assign a cutover lead who can call an abort, a database owner who verifies synchronization, and an application owner who verifies user workflows. One person may hold multiple roles, but each decision needs a named owner.

Exit gate: the measured interruption and recovery times fit the agreed objectives, with room for investigation rather than a schedule that works only when everything is perfect.

4. Validate data and application behavior separately

Matching row counts are a useful first check. They cannot detect a changed amount, a missing row balanced by an extra row, or an incorrect permission that prevents the application from reading either table.

Use several layers of evidence:

Layer	What to verify
Coverage	Every intended table is included; exclusions are documented and handled separately.
Data	Row comparisons or deterministic checksums across bounded key ranges, plus counts.
Business rules	Domain-specific invariants, such as order totals reconciling with their line items.
Database readiness	Required indexes, constraints, privileges, extensions, and sequence state are ready.
Application behavior	Critical reads and writes work through the real application identity and connection path.

Compare equivalent data states. Checksums taken independently while the source is changing can produce mismatches that reflect timing rather than corruption. Design the comparison around a consistent snapshot or the final period when writes are paused and replication has caught up.

AWS DMS can compare source and target rows and report mismatches. Its validation also adds database and network load, so include it in capacity planning. Review pending, suspended, and failed validation states; an absence of reported failures does not mean all rows were checked. See AWS DMS data validation.

Run write-path tests in the rehearsal environment. During production cutover, any synthetic target writes must be explicitly accounted for: tests that send emails, charge cards, or enqueue work can create real external effects.

Exit gate: required comparisons pass, unexplained differences are resolved, and critical application workflows pass in rehearsal.

5. Cut over through explicit gates

Use the following sequence as a structure for your environment-specific runbook. Record the evidence and decision at each gate.

Gate A: Stop source writes

Put write endpoints into maintenance mode or use the durable queuing behavior tested in rehearsal. Pause workers, schedulers, and other writers. Drain in-flight transactions and apply a tested database-level restriction to application write access where possible, while preserving migration access.

Disabling a button in the frontend is insufficient. Verify that old application processes and background jobs cannot continue writing to the source.

Gate B: Prove final synchronization

After all source writers are stopped and outstanding transactions have completed, record a final replication position or equivalent completion boundary supported by your tooling. Verify that the target has applied through that boundary across every replication stream and that initial table synchronization is complete.

A low lag metric is supporting evidence, not the entire gate. AWS DMS exposes separate source and target latency metrics; target latency includes the source capture delay. Check task health, table status, errors, and final apply progress as well. See AWS DMS latency guidance.

If your tooling cannot demonstrate final synchronization, resolve that in rehearsal. Do not substitute a convenient dashboard reading during the live cutover.

Gate C: Make the target ready for writes

Run the final data checks. Complete sequence handling and any remaining object or permission work. For native logical replication, sequence state needs separate attention before inserts begin; replicated ID values do not advance the target sequence automatically. Follow a sequence procedure tested against your schema and sequence configuration, rather than applying a blanket MAX(id) + 1 fix. See the PostgreSQL sequence replication limitation.

Keep source application writes blocked. Follow the migration tool’s tested procedure for ending or disabling forward replication once the final boundary is applied, before independent target writes begin.

Gate D: Switch connections, then admit writes

Update application configuration and refresh long-lived connections and pools. Verify which database each service actually reaches; changing DNS or a secret does not prove existing processes have reconnected.

Perform read-only smoke checks before opening writes. Then enable the target as the sole production writer and resume traffic and workers in the order tested during rehearsal. Watch transaction errors, latency, connection saturation, queue age, and a business signal such as successful order creation.

The first independent target write changes your rollback options. Record that transition explicitly.

6. Choose rollback based on where new data exists

Consider an illustrative failure: the source and target are synchronized at 10:00. The target accepts new orders at 10:01. At 10:03, an application error triggers a rollback.

Reconnecting to the source now makes those new orders disappear from the application’s view. The old database is intact, but incomplete.

State when the problem appears	Recovery approach
Source is still the only writer	Abort preparation; continue serving from the source.
Source writes are paused; target has no independent writes	Keep target writes blocked, reconnect all services to the source, then reopen source writes.
Target has accepted new writes	Preserve target changes and use the tested reconciliation or reverse replication procedure before reopening source writes, or repair service on the target.
Both databases accepted independent writes	Stop further divergence and reconcile conflicts before selecting a single writer.

Reverse replication requires advance design: supported versions, compatible schemas, conflict handling, and a verified way to capture target changes. It is not a switch you can assume will work after a failure.

Database recovery must also account for external effects. If an order triggered a payment or an email, replaying its work can duplicate the effect unless the workflow has suitable idempotency controls.

Before cutover, agree on separate responses for failure before and after target writes begin. If the second response requires repair on the target, document that and demonstrate it against the recovery objective.

7. Retire the source after an observation period

Keep source application writes blocked while observing the target through representative traffic and scheduled jobs. Choose the observation period around the workload: a successful afternoon does not exercise an overnight settlement job.

Verify target backups, perform a restore test, confirm alert delivery and ownership, and update the operational runbooks. Retain the old database according to the agreed recovery and retention plan; it is a historical recovery asset, not a current replica unless you deliberately maintain it as one.

At closure, remove migration credentials and temporary connectivity, and clean up replication resources using the relevant tool’s procedure. Record what was migrated, what was excluded, the validation results, and who accepted the outcome.

The checklist to take into your migration review

• Write interruption, recovery time, and acceptable data loss are agreed.
• Every writer can be paused and redirected, including workers and scheduled jobs.
• Replication prerequisites and unsupported objects have been checked.
• A rehearsal has demonstrated cutover and recovery within the agreed limits.
• Validation checks compare equivalent data states and cover business invariants.
• Final synchronization has a verifiable completion boundary.
• Target sequences, permissions, connections, and write paths are ready.
• Recovery procedures cover both sides of the first target write.
• Target backup restoration, monitoring, and operational ownership are verified.

A migration plan is ready when each box points to evidence: a measurement, a query result, a rehearsed procedure, or a named owner.

Agrohi’s data migration service covers migration planning, data validation, and rollback design. For an architecture review, bring your database versions, size, write rate, dependency list, and interruption limits. Those inputs let us discuss a concrete migration path and the work needed to make it recoverable.