Postgres backups that actually restore: a practical setup for small teams

Almost every team says they have backups. Far fewer have restored one recently. The uncomfortable truth is that a backup you have never restored is a hope, not a backup. Files can be empty, truncated, missing a database, encrypted with a key nobody kept, or stored on the same disk that just failed.

This is the backup setup I use for PostgreSQL on small and medium products running on Linux servers, often in Docker, and how I make sure it actually works.

Start with two numbers

Before choosing tools, agree on two things with whoever owns the business:

• How much data can we afford to lose? If the database died now, is losing the last 24 hours acceptable? The last hour? The last five minutes? This is the recovery point objective.
• How long can we be down while restoring? An hour? A day? This is the recovery time objective.

A booking or field-service system usually cannot lose a day of jobs, because people already acted on them. A marketing site's form submissions might tolerate it. The answers decide whether nightly dumps are enough or you need continuous archiving.

Option 1: Logical backups with pg_dump

pg_dump exports a database as SQL or in Postgres's custom format. It is simple, portable across Postgres versions and lets you restore a single table if needed.

A typical nightly job, run from the host against a container:

docker exec db pg_dump -U app -Fc appdb > /backups/appdb-$(date +%F).dump

Use the custom format (-Fc), which is compressed and works with pg_restore, including selective and parallel restores. Do not forget globals such as roles, which pg_dump does not include; pg_dumpall --globals-only captures them.

Limits: a dump is a snapshot at one moment. Anything written after it is lost if you restore it. Dumps of large databases also take time and load the server, so schedule them at quiet hours.

For many small products, nightly dumps plus off-site copies are a reasonable start.

Option 2: Physical backups with point-in-time recovery

For data you cannot afford to lose by the day, use physical base backups plus continuous archiving of the write-ahead log (WAL). Postgres records every change in WAL files. If you keep a base backup and every WAL file since, you can restore the database to any moment, for example to one minute before someone ran a bad DELETE.

Tools like pgBackRest and WAL-G handle this well: scheduled base backups, WAL archiving to object storage, compression, encryption, retention and restore. Managed Postgres services offer point-in-time recovery built in, which is one of the strongest arguments for using them.

This setup is more complex, but it changes the answer to "how much can we lose" from "since last night" to "a few minutes at most".

Get backups off the server

Backups stored on the database server protect you from a bad query, but not from a dead disk, a deleted server, a provider problem or a compromise. Copy every backup to separate storage:

• Object storage with a different provider, or at least a different account with separate credentials.
• Credentials on the server that can write new backups but cannot delete old ones, where the storage supports it. If an attacker gets into the server, they should not be able to erase your history.
• Versioning or object lock on the bucket where available.

Encrypt them

Backups contain everything: customer data, password hashes, business records. Encrypt them before they leave the server, and store the encryption key somewhere separate from the backups, with more than one person able to reach it. A backup encrypted with a key that only lived on the dead server is useless.

Keep a retention policy

A common pattern:

• Daily backups kept for a couple of weeks.
• Weekly backups kept for a few months.
• Monthly backups kept for longer, depending on business and legal needs.

Retention matters because some problems are noticed late. If data was silently corrupted ten days ago and you only keep seven days, every backup you have contains the corruption.

Restore drills: the part everyone skips

Schedule a restore test, at least monthly, and after any change to the backup setup:

1. Spin up a fresh Postgres container of the same major version.
2. Download the latest backup from off-site storage, not from the server.
3. Decrypt and restore it.
4. Run a few checks: row counts of key tables, the latest record timestamps, a login to the app pointed at the restored database.
5. Write down how long it took.

That time is your real recovery time. If it is much longer than the business expects, you have found out on a calm afternoon instead of during an outage.

You can automate most of this: a scheduled job that restores the latest backup into a throwaway container, runs sanity queries and reports the result. Then a broken backup becomes an alert instead of a surprise.

Monitor the backups themselves

Alert when:

• A backup job fails or does not run.
• A backup file is suspiciously small compared to previous ones.
• No new backup has arrived in off-site storage within the expected window.

A silent backup job that stopped working three months ago is one of the most common disaster stories. I describe how I wire alerts like these in monitoring and alerts for a small product.

Docker-specific notes

• Never treat a Docker volume as a backup. Volumes live on the same disk.
• Dump through pg_dump, or use a tool designed for physical backups. Copying the data directory of a running Postgres is not a consistent backup.
• Pin the Postgres major version in your compose file and plan upgrades deliberately. Restoring a dump into a newer major version works; physical backups need the same major version.

For the rest of a single-server Docker setup, see running Docker Compose in production.

Do not forget the files

Databases are often only half the data. Uploaded photos, documents and generated files need their own backup, ideally in object storage with versioning from the start. A restored database pointing at missing images is only a partial recovery.

Write it down

Keep a short restore runbook with exact commands, where backups live, where the key is and who can access it. During an incident, nobody wants to work out pg_restore flags from memory.

A minimal plan to start this week

1. Nightly pg_dump -Fc plus globals, encrypted, copied off-site.
2. Retention of daily and weekly copies.
3. An alert if no new backup arrives.
4. One full restore test, timed, written down.
5. Decide whether your data needs point-in-time recovery, and plan it if so.

If you want help reviewing your backups or setting up a restore drill, see my work and get in touch.