Test a PostgreSQL restore before migration

This is a rehearsal, not a production restore runbook. Confirm the database engine/version, data owner, retention, encryption, and available disk before taking a copy. Never test by overwriting production.

Create and identify the backup

• Use the project’s documented backup method and a consistent snapshot. Record source database, version, timestamp/timezone, size, encryption key owner, and checksum.
• Store the encrypted copy separately from the production host. Keep secrets out of shell history, tickets, and screenshots; verify that authorized staff can retrieve the key.

Restore into an isolated target

• Create a new empty database with the intended major version and compatible roles/extensions. Restore there using the matching PostgreSQL restore tool; never point the command at production.
• Check expected tables, row counts for critical records, constraints, application startup, and a read-only sample workflow. Record restore duration and mismatches before approving a migration.

Example and expected result

• Choose the restore tool by format: plain SQL uses psql; custom/tar archives use pg_restore (inspect with pg_restore --list backup.dump). Restore into a disposable isolated database, with workers and outbound integrations off and test secrets only. Never use production for this drill.
• Record exit code, elapsed time, expected table counts and known aggregate checks. If restore fails or counts differ, stop and inspect the error, extensions and roles on the test host before migration. pg_dump covers one database; global roles require a separate backup.

Primary sources

• PostgreSQL documentation · pg_dump and restore formats