Back Up PostgreSQL to S3: pg_dump vs pgBackRest (2026)

Every PostgreSQL horror story ends the same way: the database is gone, someone opens the backup bucket, and what they find there has never been restored by anyone. The dump job "ran green" for months — against a database that had since been renamed, or with a role that could not read half the schemas, or into a bucket whose lifecycle rule quietly deleted everything older than seven days, including the only full backup.

A backup you have never restored is a hope, not a backup. This guide walks through the two families of PostgreSQL backups — logical dumps with pg_dump and physical backups with pgBackRest — how to point both at S3-compatible object storage in the EU, how to set retention that will not eat your last copy, and how to run a restore drill that takes fifteen minutes a month and removes the guesswork.

Everything below works against any S3-compatible endpoint, including DanubeData Object Storage hosted in Germany.

Logical vs physical: pick the right family first

PostgreSQL gives you two fundamentally different ways to back up, and most teams eventually want both:

• Logical backups (pg_dump) re-create your database as SQL statements or an archive of table data. They are portable across PostgreSQL major versions and even across architectures, you can restore a single table, and the tooling is already on your machine. The cost: they capture one moment in time, restores are slower on big databases, and there is no point-in-time recovery.
• Physical backups (pgBackRest, WAL archiving) copy the actual data files plus the write-ahead log. Restores are fast, incremental backups are cheap, and continuous WAL archiving gives you point-in-time recovery (PITR) — the ability to restore to 14:32, right before the bad migration. The cost: more moving parts, same-major-version restores only, and you back up the whole cluster, not one database.

Rule of thumb: if your database fits comfortably in a nightly dump window and losing up to 24 hours of writes is survivable, pg_dump to S3 is honest, simple and enough. The moment either of those stops being true, add pgBackRest.

Option 1: pg_dump straight to S3

The dump itself

Always use the custom format (-Fc). It is compressed, it restores with pg_restore, and it lets you cherry-pick tables later:

pg_dump -h db.example.com -U app -Fc -f app_$(date +%F).dump app_production

For large databases, the directory format dumps tables in parallel — eight jobs routinely cut dump time by 4-6x:

pg_dump -h db.example.com -U app -Fd -j 8 -f app_$(date +%F).dir app_production

Two things pg_dump does not capture: roles and grants that live at the cluster level. Dump them separately once per cluster:

pg_dumpall -h db.example.com -U app --globals-only > globals_$(date +%F).sql

Streaming to the bucket

You do not need local disk for the dump at all. Stream it straight into object storage with rclone (we covered rclone setup in depth in our rclone guide):

pg_dump -h db.example.com -U app -Fc app_production \
  | rclone rcat dd:my-backups/postgres/app_$(date +%F).dump

A minimal nightly job as a systemd timer (cron works just as well):

# /etc/systemd/system/pg-backup.service
[Unit]
Description=Nightly pg_dump to S3

[Service]
Type=oneshot
Environment=PGPASSFILE=/root/.pgpass
ExecStart=/bin/bash -c 'pg_dump -h db.example.com -U app -Fc app_production | rclone rcat dd:my-backups/postgres/app_$(date +%%F).dump'

# /etc/systemd/system/pg-backup.timer
[Timer]
OnCalendar=*-*-* 02:30:00
Persistent=true

Put credentials in ~/.pgpass (mode 0600), never in the unit file or the crontab line — both end up in logs.

One pitfall that bites managed-Postgres users

If your provider gives you a connection pooler (pgBouncer in transaction mode — ours does, as an option), do not run pg_dump through it. Dumps rely on session state — one long transaction, one consistent snapshot — that transaction pooling does not preserve. Always point backup jobs at the direct database endpoint, not the pooler endpoint.

Option 2: pgBackRest with an S3-compatible repository

pgBackRest is the workhorse of serious PostgreSQL operations: parallel physical backups, three backup types (full, differential, incremental), integrated WAL archiving, encryption, and retention management. It speaks S3 natively — you just have to tell it about your endpoint.

Configuration

# /etc/pgbackrest/pgbackrest.conf
[global]
repo1-type=s3
repo1-s3-bucket=my-pg-backups
repo1-s3-endpoint=s3.your-provider.example
repo1-s3-region=eu-central-1
repo1-s3-key=YOUR_ACCESS_KEY
repo1-s3-key-secret=YOUR_SECRET_KEY
repo1-s3-uri-style=path
repo1-path=/prod-cluster
repo1-retention-full=2
repo1-retention-diff=6
process-max=4
compress-type=zst
start-fast=y

[main]
pg1-path=/var/lib/postgresql/18/main

The one option people miss with S3-compatible providers is repo1-s3-uri-style=path — most non-AWS endpoints (including ours) use path-style URLs rather than per-bucket virtual hosts.

Wire up WAL archiving and take the first backup

# postgresql.conf
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
wal_level = replica

sudo -u postgres pgbackrest --stanza=main stanza-create
sudo -u postgres pgbackrest --stanza=main check
sudo -u postgres pgbackrest --stanza=main --type=full backup

From there, a typical schedule is a weekly full, nightly differentials, and WAL flowing continuously. Retention is handled by pgBackRest itself — repo1-retention-full=2 keeps two full backups plus every differential and WAL segment needed to restore from them, and expires the rest. Do not add a bucket lifecycle rule on top of a pgBackRest repository: pgBackRest tracks its own files, and a lifecycle rule that deletes "old" WAL breaks point-in-time recovery silently. Lifecycle rules are for the pg_dump prefix, not the pgBackRest one.

Point-in-time recovery: the payoff

Someone ran a DELETE without a WHERE at 14:31. With WAL archiving, you restore to 14:30:59:

sudo systemctl stop postgresql
sudo -u postgres pgbackrest --stanza=main --delta \
  --type=time --target="2026-07-22 14:30:59+02" \
  --target-action=promote restore
sudo systemctl start postgresql

That single capability is why physical backups are worth the extra setup for any database whose last 24 hours you cannot afford to lose.

The restore drill: fifteen minutes a month

Whichever family you use, schedule a monthly drill. The drill is the backup system. For dumps, it fits in a scratch container on any VPS:

# Pull the latest dump and restore it into a throwaway Postgres
rclone copy dd:my-backups/postgres/app_2026-07-22.dump /tmp/drill/
docker run -d --name drill -e POSTGRES_PASSWORD=drill -v /tmp/drill:/drill postgres:18-alpine
docker exec drill createdb -U postgres app_restore
docker exec drill pg_restore -U postgres -d app_restore -j 4 /drill/app_2026-07-22.dump

# Sanity checks: row counts, latest timestamps
docker exec drill psql -U postgres -d app_restore -c \
  "SELECT count(*) FROM orders; SELECT max(created_at) FROM orders;"
docker rm -f drill

Write down the two numbers that matter: how long the restore took (that is your real recovery time) and the newest timestamp in the restored data (that is your real recovery point). If either surprises you, better to learn it in a drill.

Common failure modes, from support tickets

• Version mismatch — restore with a pg_restore at least as new as the server you dumped from; dumps restore forward (15 → 18), not backward.
• The invisible lifecycle rule — a 7-day expiry rule on the whole bucket will delete your monthly fulls. Scope lifecycle rules to a prefix, and exclude the pgBackRest repo entirely.
• Dumping through the pooler — see above; use the direct endpoint.
• Backups in the same blast radius — a backup on the database server's own disk dies with the server. Object storage in a different failure domain is the point.
• Credentials with too much power — the backup job needs write access to one bucket, not your account's root keys. Create a dedicated access key scoped to the backup bucket.
• Compliance forgot the backups — if your data must stay in the EU, that includes every copy. A GDPR-clean primary with backups in us-east-1 is not GDPR-clean.

Where DanubeData fits

Both halves of this setup are things we run as products, hosted in Germany, under EU jurisdiction:

• DanubeData Object Storage is S3-compatible and works as shown above with rclone, pg_dump streams and pgBackRest (path-style URIs). Buckets support versioning and per-prefix lifecycle rules, so your dump retention is a bucket policy rather than a shell script.
• Managed PostgreSQL (15 through 18) takes automated snapshots on a schedule, supports on-demand snapshots before risky migrations, and grows storage automatically before a full disk becomes an outage. You can — and should — still run your own logical dumps to a bucket you control: provider snapshots and self-owned dumps protect against different failure modes.

That last sentence is our honest position, and it applies to any provider, not just us: managed backups protect you from infrastructure failure; your own dumps in your own bucket protect you from account-level problems, fat-fingered deletions of the instance itself, and provider migrations. The 3-2-1 rule survives the cloud era intact.

Related reading: managed PostgreSQL hosting compared, the full Linux server backup guide (restic, for everything that is not the database), and S3-compatible object storage in Europe.

Create a DanubeData account and you can have an EU backup bucket and a managed Postgres with scheduled snapshots running before your next deploy.