Back up PostgreSQL to S3: from pg_dump to a verified restore

Most PostgreSQL backup guides stop at the pg_dump command line. That is the easy part. What decides whether you get your data back is everything around it: what the dump leaves out, which format you chose, whether the script really fails when the dump fails, and whether anyone has ever replayed the file.

This guide covers the whole chain: what a PostgreSQL backup has to contain besides the database itself, how to pick between plain SQL, custom and directory, a backup script that cannot report success on an empty file, a manual restore in a throwaway container, and four SQL queries that tell you the restored database holds your data. It needs only pg_dump, docker and the AWS CLI.

What a PostgreSQL backup has to contain

pg_dump backs up one database: its tables, its data, its views, its functions. It does not back up what lives beside it, at server level:

• roles and their passwords;
• the settings applied to the server (postgresql.conf, pg_hba.conf);
• the other databases on the same server.

Roles are retrieved with pg_dumpall --globals-only, which produces a small separate SQL file. Without it, the restore succeeds, but the application accounts no longer exist and nobody can connect.

Extensions, on the other hand, are in the dump, as a CREATE EXTENSION. They must therefore be installed on the server where you restore, otherwise the dump stops on that line. That is the classic trap with postgis and pgcrypto.

Choosing the dump format

For a database of a few gigabytes, compressed plain SQL is enough and stays the easiest to inspect. Beyond that, directory with -j divides dump and restore time by the number of cores you give it.

Do not compress a -Fc or -Fd dump: it already is, and gzipping it again only burns CPU for a few percent.

The backup script

#!/bin/bash
set -euo pipefail

ts=$(date +%Y%m%d_%H%M%S)
dest=s3://backups-billing/postgres

pg_dump -h db.internal -U backup_user -d billing --no-owner --no-acl \
  | gzip > "/backups/billing_${ts}.sql.gz"

pg_dumpall -h db.internal -U backup_user --globals-only \
  | gzip > "/backups/globals_${ts}.sql.gz"

aws s3 cp "/backups/billing_${ts}.sql.gz" "${dest}/"
aws s3 cp "/backups/globals_${ts}.sql.gz" "${dest}/"

--no-owner --no-acl strips the original owners and privileges from the dump: without them, the restore asks for roles that only exist on the production server.

Without pipefail, a failed dump comes out as a success

In pg_dump | gzip, the shell only looks at the exit code of the last link. gzip succeeds at compressing an empty stream, so the script returns 0 and cron is happy. set -o pipefail, included in the set -euo pipefail above, makes the whole line fail as soon as pg_dump fails. It is the number one cause of empty backups that stay green for months.

Keep the timestamp in the file name: a file always overwritten under the same name leaves no chance of going back to yesterday. For retention, a lifecycle rule on the bucket does the job with no script to maintain: objects older than N days are deleted by the storage itself.

That leaves running it every night. A cron entry is enough, on two conditions: its error output goes somewhere someone reads, and a run that drags on does not overlap with the next one. flock -n on a lock file settles the second point in one line.

Restoring once, by hand

A backup is only proven once restored. Do it once, in full, on a throwaway machine. It is also the procedure you will follow the day it matters.

docker run -d --name pg-test -e POSTGRES_PASSWORD=test postgres:17-alpine
until docker exec pg-test pg_isready -q; do sleep 1; done

docker exec pg-test createdb -U postgres billing
gunzip -c /backups/globals_20260918_010000.sql.gz \
  | docker exec -i pg-test psql -U postgres -v ON_ERROR_STOP=1 -d postgres
gunzip -c /backups/billing_20260918_010000.sql.gz \
  | docker exec -i pg-test psql -U postgres -v ON_ERROR_STOP=1 -d billing

ON_ERROR_STOP=1 is essential: without it, psql carries on after an error and you end up with a half-restored database that looks like it works.

Match the server's major version

postgres:17-alpine must match the major version of your server. A dump taken on a newer server does not replay on an older one.

Note how long it took. That is your real restore duration, the only one worth comparing to the delay you promised.

What to check in a restored database

A dump that replayed without an error does not mean the data is there. Four questions, in this order:

select count(*) from information_schema.tables where table_schema = 'public';
select count(*) from invoices;
select max(created_at) from invoices;
select last_value from invoices_id_seq;

1. The tables are there. Zero tables means an empty dump that replayed perfectly.
2. The rows are there. Compare against the order of magnitude in production, not an exact number: a database that grows is normal.
3. The data is recent. The most recent date should be yesterday's, not last month's. That is what catches a backup job that stopped running.
4. The sequences kept up. A sequence left at 1 causes key collisions on the first write after the restore.

Then destroy everything: docker rm -f pg-test.

What this chain does not prove

It proves that a recent backup restores into a fresh PostgreSQL and that it contains the expected data. It does not prove that your application works on that database: for that, you need to start it against the restored container and request a page. It says nothing either about what has been created since the last dump. That gap is your RPO, and it is tuned with the backup frequency, not with the tests.

FAQ

How often should a restore be tested?

As often as the backup runs, if you can. A nightly restore test surfaces a broken backup the morning after it breaks, rather than the day you need it.

A backup job that finishes green means the backup works, doesn't it?

It means the job ran without reporting an error. It says nothing about whether the file can be replayed. A pg_dump | gzip pipeline without pipefail returns success on an empty file. A dump taken by a role that cannot read every table restores perfectly with half the data. A schedule that stopped firing leaves behind a dump that still restores. Only a restore, followed by counting what came out, distinguishes a backup that exists from a backup that restores.

What does pg_dump not back up?

Roles and their passwords, the server-level configuration (postgresql.conf, pg_hba.conf), and the other databases on the same server. Roles are dumped separately with pg_dumpall --globals-only. Extensions are in the dump as CREATE EXTENSION, which means they must be installed on the server you restore onto, or the restore stops on that line.

Originally published on restoreproof.io.

RestoreProof runs this kind of restore test on a schedule, inside your own infrastructure, and signs a report proving the backup restores. Your data never leaves your servers. Free plan, no credit card: https://app.restoreproof.io