Restore a database and configuration

A database dump without its roles, extensions, version requirements, and application configuration may not produce a working service.

The dump file is the easy part. What breaks restores is everything around it: the role that owns the tables, the extension the schema depends on, the engine version the dump format expects, and the connection string the application reads from a config file you forgot to back up.

Restore in a controlled order

Create the target with a compatible engine version first. Check what the backup came from and what you’re restoring onto:

psql --version
# psql (PostgreSQL) 16.4
pg_restore --list shopdb-2026-08-03.dump | head -3
# ; Archive created at 2026-08-03 02:00:11
# ;     dbname: shopdb

Restoring a dump from a newer major version into an older server is not supported. The reverse direction — old dump, newer server — is the normal upgrade path.

Restore roles and schema as required, then load data:

sudo -u postgres psql -f pg-globals-2026-08-03.sql          # roles first
sudo -u postgres createdb shopdb
sudo -u postgres pg_restore --dbname=shopdb shopdb-2026-08-03.dump

Roles come first for a reason. Load data before the owning role exists and the restore floods you with role "shopapp" does not exist errors, leaving objects owned by the wrong user.

Then apply only documented recovery steps — the runbook, not improvisation — and connect the application with isolated credentials: the application’s own database user, not postgres. If the app only works when connected as a superuser, you’ve hidden a permissions problem you’ll rediscover in production.

Validate before cutover

A restore that completes is not a restore that worked. Run validation queries with expected answers:

sudo -u postgres psql shopdb -c "SELECT count(*) FROM orders;"
#  count
# -------
#  48213

Compare against the count recorded when the backup ran. Check the newest row’s timestamp against your recovery point. Then start the service against the restored database and run one real user-visible action before you send traffic at it. Only after that evidence do you make the cutover decision.

Write the exact restore order for one database-backed application. Include version checks, credentials, migrations, validation queries, and the cutover decision.