PostgreSQL backups and restore: prove recovery, not just a file

Who is this for?

Teams operating PostgreSQL and mobile backend infrastructure.

Before you start

A separate test database, protected backup storage and an authorized operator account.

1. Set recovery objectives

RPO describes acceptable data loss; RTO describes the recovery-time target. A daily copy cannot establish a much shorter RPO. Recovery includes transfer, database restore, indexes, verification and application startup. Tie objectives to important business operations.

2. Inventory the full scope

SQL dumps do not automatically include user-uploaded objects, deployment settings or encryption keys. Losing a key can make encrypted backed-up values unusable. Protect secrets through a separate controlled recovery method.

3. Use a bounded logical-backup example

Adapt these commands only to your own isolated test environment. Keep passwords out of command-line arguments and use a protected credential method. Check version and extension compatibility. Logical dumps do not satisfy every recovery objective; continuous archiving and point-in-time recovery require another operating plan.

umask 077
pg_dump -h 127.0.0.1 -U app_backup -d app_test -Fc -f app_test.dump
createdb -h 127.0.0.1 -U app_operator app_restore_test
pg_restore -h 127.0.0.1 -U app_operator -d app_restore_test --no-owner app_test.dump
# Never restore over production for this exercise.

4. Verify a separate restore

Check critical records, relationships, login, writes and file access, not just the process exit code. Counts are only an initial check. Prevent the test environment from sending real mail, push or payments. Recovery tests must not trigger production user workflows.

5. Separate failure domains

A dump on the same disk is lost with that disk. Plan storage separation, encryption, retention and deletion permissions. Test the failure alert destination. A provider’s free-backup label is not proof of application recovery.

6. Keep a recovery record

Record the copy used, restoration duration, verified flows and missing items. Make the procedure usable by a replacement operator. Choose repeat frequency around data change and business risk. Uygulama Cloud does not currently provide customer backup/restore services.

• Verify backup failure alerts.
• Restore away from production.
• Check file and SQL consistency.
• Compare measured results with objectives.