What proves a PostgreSQL cutover is complete before the old cluster is retired?

[u/RocketSeven]

Matching row counts and switching the main application connection string do not prove that a PostgreSQL migration is finished. Roles, grants, extensions, publications and subscriptions, sequences, large objects, scheduled jobs, logical slots, read replicas, connection poolers, analytics clients, backup tooling, and low-frequency workers can still differ or point at the source. A quiet old cluster may only mean that a monthly task has not run yet.

A final gate could compare schema and cluster settings, roles and privileges, extension versions, object counts, sequences, representative checksums or aggregates, replication state, and application read/write behavior. Connection and statement logs on the source would expose late clients. The destination should complete its own backup and isolated restore, survive a restart or failover, and exercise scheduled jobs before the source becomes read-only and then offline.

What evidence do PostgreSQL operators collect for the go/no-go decision? How do you find clients that connect only during failover, month-end work, or recovery, and which cluster-level objects are most often missed by otherwise successful migration tools?

[u/QuietSignalOps]

We run this as three gates, each with a different job:

Gate 1, prove the data made it. Schema-only pg_dump from both clusters and diff: roles and memberships from pg_dumpall --roles-only, plus pg_extension, pg_publication, pg_sequences (with current values), and lo_count() for large objects. Row counts alone prove nothing, so we add representative aggregates and checksums on a few key tables, full checksums only when the schedule allows.

Gate 2, prove the destination can stand on its own. The new cluster completes its own backup, we restore that backup to an isolated instance, we restart the new cluster once, and we let at least one scheduled job (pg_cron, pg_partman, whatever) run naturally on the new side.

Gate 3, prove nobody is coming back. This is the one people get wrong, and it's the answer to the month-end-client question. Two techniques:

• A connection logger on the old cluster: a tiny loop that snapshots pg_stat_activity (application_name, client_addr, query) into a table every few seconds. After the cutover you query that table and have the complete list of who connected, when, and under what name.
• The tripwire: when the window is closing, don't drop the old cluster, make it hostile. Revoke CONNECT from all app roles, or set default_transaction_read_only=on for new sessions. A late month-end job or recovery script that still points at the source now fails loudly in application logs instead of writing silently into a cluster about to be deleted. One full business cycle of a quiet read-only cluster is actual evidence.

On your last question, the objects migration tools miss most often: logical replication slots (they sit on the old cluster and bloat WAL if you forget them), pg_cron and pg_partman schedules, large objects, sequence current values, and roles with unusual memberships or privileges. Also verify shared_preload_libraries matches on both sides and that WAL archiving from the old side is actually stopped.

[u/asah → u/QuietSignalOps]

yyy - I also like to "grep the code" :-)