Test PostgreSQL backups: from a dump to a working system

Define what needs to work again

Choose an existing backup and a specific application. Record the backup time, database name, PostgreSQL version, dump format and tools used. Include the application version, extensions and files stored outside the database, such as attachments. A database dump does not automatically include those external files.

Agree with the business team which tasks will demonstrate acceptance. For a hypothetical order system, these might be signing in, reading a known order and saving a clearly labelled test record. This is a test example, not a customer case. Define the expected result before starting.

Separate the destination from production

Use a separate test instance with its own database and application credentials. Verify the host and destination before each import. Prevent customer emails, payments, webhooks and background jobs with production side effects. A different database name alone does not prevent the application from contacting external systems.

A restore test may involve real business data. Limit access and retention accordingly, and protect backup files and credentials. Use dumps from a trusted source: recovery executes database statements contained in them. PostgreSQL's documentation explicitly describes this trust boundary.

Check roles and extensions before importing

pg_dump covers one database. Global objects such as roles and tablespaces are outside that scope; pg_dumpall can capture them separately. Determine which roles the destination needs and how ownership and permissions will be restored. An option such as --no-owner changes ownership behaviour and does not replace testing with the eventual application account.

Also check required extensions and compatibility between the database and tool versions. For the first rehearsal, use an environment that matches the installation being recovered where practical. Treat a version upgrade as an additional change requiring its own checks rather than combining it silently with the recovery test.

Use the right tool and inspect errors

A plain SQL dump is read with psql; pg_dump archive formats use pg_restore. A filename extension alone does not reliably identify the format. Record the backup method actually used and select the import procedure accordingly.

Do not let errors disappear in a long output log. psql can stop at the first SQL error with ON_ERROR_STOP=on; pg_restore provides --exit-on-error. Stopping does not automatically undo steps already completed. A single transaction is a separate option that must suit the workload and procedure. Record the exit status and error messages before starting the application.

Test using the application account

Check the recovered data against known records. Then start the matching application version and exercise the agreed workflows with the intended permissions. Successful access as a database administrator says little about whether the restricted application account can read and write. Also check that related attachments exist and match the recovered data.

Measure the complete process: obtaining the backup, preparing the destination, importing data and performing business checks. Separately record the age of the recovered data. Recovery time and the possible loss of newer changes are different results. Both need to meet the business requirements.

• Document the backup, formats, versions and options used.
• Check import status, errors, permissions and external files.
• Verify sign-in, reading and writing with the application account.
• Record duration, data age, unresolved issues and their owners.
• Remove test data under the agreed procedure and repeat after significant changes.

Make the test part of operations

A successful first test provides a baseline. Repeat it after changes to the backup procedure, extensions or application, and at intervals suited to the risk. When discussing servers or operations with NUXOA, documented versions, data volume and measured times help scope the enquiry. Agree explicitly who backs up the database, performs recovery tests and resolves errors.