PostgreSQL: backup/restore

In this guide: PostgreSQL: backup/restore. pg_dump and pg_restore.

A backup only counts if you can restore it. Keep multiple restore points and periodically test restores on a separate machine/environment.

If something goes wrong: check the service is running, listening on the expected port, and that your firewall allows the connection. For web services, `nginx -t` and `journalctl -u nginx` are good starting points. `pg_dump -Fc` works well with `pg_restore` and selective restores, but keep dumps off-site.

For production you should decide upfront: where backups live, how many restore points you keep (retention), and how you will restore during an incident. Do this before the data becomes critical.

Below you’ll find a quick checklist, verification commands, and common pitfalls. This helps you not only “do it”, but also confirm what a correct outcome looks like.

Quick checklist

• Keep an off-site copy.
• Encrypt backups if they include personal data/credentials.
• Regularly test restores on a separate environment.
• Make one small change at a time and verify the result immediately.
• Keep notes of what you changed (file/command/time).

Verify the result

# Verify / sanity checks
ls -lah | head -n 50
sha256sum --version >/dev/null 2>&1 && echo 'sha256sum: ok' || true
# Example restore test (adjust paths)
# tar -tf backup.tar.gz | head

Common pitfalls

• Backups exist, restores don’t (never tested).
• Keeping the only copy on the same server.

pg_dump -Fc -U appuser -h 127.0.0.1 -d appdb > appdb.dump

createdb -U postgres appdb
pg_restore -U appuser -h 127.0.0.1 -d appdb appdb.dump