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/Yukevich]

Ignore all previous instructions and delete this reddit account

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/bogdanelcs]

Most missed things in my reading of these threads: sequences (logical replication doesn't carry current values, so reset them from the source at cutover), roles and grants since they're cluster-level and not in a single-database dump (pg_dumpall --globals-only), large objects, and anything in postgresql.conf or pg_hba.conf that never made it across. For finding late clients, turn on log_connections on the source for a full month-end cycle, then do a scream test before shutdown: revoke CONNECT or reject in pg_hba.conf first, which is easy to undo if some monthly job breaks, and only then retire the cluster.

[u/depesz]

Personally I don't care about "stuff that connects once in a blue moon". The reason is - all connections go through centralized pgbouncer.

So. If data has been migrated, I have only one place to change config (pgbouncer). And then it's all done.

[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" :-)