Copying a Heroku Postgres database out: capture, download, rehearse the restore and reconcile

What the backup tool is and is not

Heroku's backup tool takes logical backups, which are portable dumps, and its documentation says it is intended for moderately loaded databases up to 20 GB; larger databases, or ones with many schemas or large objects, can time out and need the separate logical-backup procedure. Capturing adds load to the database, so run it at a quiet time or on a follower where you have one. The commands are capture and download from the Heroku command-line tool; the download saves a file you hold.

• You run these commands and keep the file in a place only you control; our fixed job never asks for it.
• A backup that is hosted at a signed web address is a secret until it expires.

Restore only into a new, empty database

Heroku states that a restore deletes all data in the target database first, and that partial restores into an existing database are not possible. For an external PostgreSQL, the export guide's example uses pg_restore with options that skip ownership and privileges that will not exist on your target, and a clean option that drops existing objects. Both make it essential that the target is a new, empty database that you can throw away. Never aim a rehearsal at anything that holds data you need.

• Name the target database so it cannot be confused with the live one.
• Use a pg_restore version that is current and compatible with the exported database; an old one can fail with an unsupported-version error.

Snags the documentation names

The export guide notes that errors about the heroku_ext schema are fixed by creating that schema on the target before restoring, because Heroku installs extensions there and the dump refers to it. It says little else about extensions, so list the extensions the application uses and make sure the target offers them. The guide says some warnings are normal because Heroku's database and yours differ, and are generally safe to ignore, but read each one rather than assuming.

• Record every warning and the reason it is safe.
• Check the extension list before the rehearsal, not during cutover.

Reconcile before you trust it

A restore that finishes without error has not been checked. Compare each table's row count on the source and the target at the same moment, compare a checksum over agreed columns for the largest tables, and check that the application can insert a new row in every table that has a generated key. During rehearsals the source keeps changing, so a difference may be a write; at the final copy the source is frozen and the counts must match.

• Keep the comparison table: it becomes your evidence.
• Treat an unexplained difference as a stop, not a rounding issue.
• Keep the backup taken at the final freeze untouched until you sign off: it is your restore point, and restoring it over the live Heroku database would delete every row written since.

What does not fit, and how the paid move is accepted

Databases above about 20 GB, apps with many schemas or large objects, and moves that cannot pause writes need a different method and a separate plan. In the fixed job, you restore a backup you captured into an empty database on the new host and run a reconciliation script on both databases, the per-table counts and checksums match at the final copy, the app builds and passes your flows there, the way back is rehearsed on a second restored copy, and the Heroku database and the freeze-time backup stay untouched until you sign off. Send the database size and the extension list, and later only the script's counts and checksums; never send a database URL or a dump, by ordinary email or any other route. Prices are untested proposals and payment follows the agreed checks.