Moving a PostgreSQL database to a new server: dump, roles, restore and the cutover freeze

What a dump contains

pg_dump exports a single database. PostgreSQL documents that the export is consistent even while the database is in use and does not block other users. It does not include roles or tablespaces, which belong to the cluster, so those come from pg_dumpall, which has an option for global objects only. It also leaves out optimiser statistics by default, so a restored database can run queries differently until statistics are rebuilt. Write down, before you start, what the app needs outside the database itself: extensions, roles and their privileges, scheduled jobs and settings. A dump of the database alone is not a copy of the whole service.

• One database per dump.
• Roles and tablespaces come from the cluster-wide dump.
• Extensions must exist on the target.

Choose a format you can restore selectively

The plain text format restores through the SQL client. The custom and directory formats are restored with pg_restore, are compressed by default and allow selecting and reordering items, and the directory format also allows parallel dumping and restoring. For a move, they are the practical choice because you can list the contents, restore into an empty database, retry a failed object and speed the restore with several jobs. The all-or-nothing single-transaction option cannot be combined with parallel jobs, and a restore that continues past errors reports a count at the end, so decide whether you want it to stop at the first error.

• Prefer custom or directory format for a rehearsal.
• Use exit-on-error when you want to see the first failure.
• Parallel jobs speed up a large restore.

Roles, ownership and versions

Restores fail in boring ways. The restore tool issues ownership changes by default, and these fail unless the connecting user is a superuser or owns everything, so either create the roles first or choose to skip ownership and set it deliberately. Dumped roles can carry password hashes, so protect that file or omit passwords and set them on the target yourself. Versions matter too: a dump can be loaded into the same or a newer major version, and loading into an older one is not guaranteed. Always create the target database empty, from the clean template, to avoid duplicate-object errors.

• Recreate roles before restoring.
• The target major version must be the same or newer.
• Treat a role dump as sensitive.

Prove the restore before cutover

Restore a recent dump into an empty database on the target and compare it with the source. Compare row counts for every table, checksums for the important ones and the results of the queries your application depends on. Have the application write and read a test record on the target. Run the statistics update afterwards. Record the time the dump and the restore took; the sum is your minimum write freeze, and the cutover will take longer than the rehearsal if the data has grown. A rehearsal that has never been compared is not a rehearsal.

• Counts, checksums and key queries side by side.
• A written test record read back on the target.
• Timings for the freeze estimate.

Cutover: stop writes first

A dump is a snapshot; anything written after it is not in it. On the day, stop writes on the old database, by stopping the application or revoking write access, take the final dump, restore, repeat the comparison, switch the connection setting and watch the application. Leave the old database untouched so you can go back, and agree the point after which going back would lose writes made on the new one. The fixed host-move job covers one database up to 50 GB by this method and does not offer zero-downtime replication.