PostgreSQL backups to S3

A backup that has never been restored isn't a backup: it's hope with a timestamp. In this guide you'll see how to design a complete strategy for backing up PostgreSQL to S3: from the simple pg_dump to pgBackRest with WAL archiving for point-in-time recovery, encryption, cron automation, anti-ransomware immutability with Object Lock and, most importantly, how to actually verify that your backups restore.

Logical vs. physical: two backup philosophies

Before touching a single command, it helps to understand that PostgreSQL has two families of backup, and they don't compete so much as complement each other.

• Logical backup (pg_dump, pg_dumpall): exports the database contents as SQL statements or as a custom-format file. It's portable across major versions and architectures, ideal for cloning a specific database or migrating. Its Achilles' heel is time: on large databases, dumping and restoring can take hours and doesn't let you recover to an exact second.
• Physical backup (a copy of the cluster files + WAL archiving): copies the data at the block level and keeps the transaction log (Write-Ahead Log). It enables Point-in-Time Recovery (PITR), much faster restores on large volumes, and incremental backups. This is the approach used by tools like pgBackRest or WAL-G and the one recommended in production.

A practical rule: below a few GB, pg_dump to S3 will get the job done; beyond that, or if you need an RPO (recovery point objective) of minutes, go straight to pgBackRest.

pg_dump and pg_dumpall: the logical dump

pg_dump exports a single database; pg_dumpall adds the cluster's global objects (roles, tablespaces, and privileges) that pg_dump doesn't include. In practice you'll want both: the dump of each database and a dump of globals.

The custom format (-Fc) is the most useful: it's compressed, allows selective restoration of tables, and is restored with pg_restore in parallel. You can upload it directly to your bucket via streaming, without writing to intermediate disk:

#!/usr/bin/env bash
set -euo pipefail

FECHA=$(date +%F)
ENDPOINT=https://es-mad-1.s3.otterstorage.io
BUCKET=backups-pg

# Dump a database in compressed custom format, straight to S3
pg_dump -Fc -Z6 midb \
  | aws s3 cp - "s3://${BUCKET}/midb/midb-${FECHA}.dump" \
    --endpoint-url "${ENDPOINT}"

# Cluster globals (roles, privileges, tablespaces)
pg_dumpall --globals-only \
  | aws s3 cp - "s3://${BUCKET}/globals/globals-${FECHA}.sql" \
    --endpoint-url "${ENDPOINT}"

To configure the client credentials, follow the guide on access keys isolated per bucket: ideally the key this script uses should only have permission over backups-pg and nothing else. You can rely on the AWS CLI documentation for the profile details.

Encrypt before uploading

Your backups contain sensitive data, so encrypt them at the source (client) so they travel and rest encrypted. The simplest way is to encrypt the stream with GnuPG before sending it:

# Symmetric AES-256 encryption before uploading
pg_dump -Fc midb \
  | gpg --symmetric --cipher-algo AES256 --batch --passphrase-file /etc/pg/backup.key \
  | aws s3 cp - s3://backups-pg/midb/midb-$(date +%F).dump.gpg \
    --endpoint-url https://es-mad-1.s3.otterstorage.io

If you'd rather not manage the cryptography yourself, restic encrypts by default with AES-256 and Poly1305 authentication, and deduplicates. We use it as a target further down. Also review the security guide for the complete model.

pgBackRest with an S3 repository (full configuration)

pgBackRest is the de facto standard for physical PostgreSQL backups: it handles full, differential, and incremental copies, verifies checksums, compresses, encrypts, and stores directly in an S3 repository like OtterStorage. A stanza is the configuration unit that associates a cluster with its repository.

The pgbackrest.conf file

[global]
# Repository on S3 (OtterStorage)
repo1-type=s3
repo1-s3-endpoint=es-mad-1.s3.otterstorage.io
repo1-s3-bucket=backups-pg
repo1-s3-region=eu-mad
repo1-s3-key=AKIAOTTEREXAMPLE
repo1-s3-key-secret=clave_secreta_aqui
repo1-s3-uri-style=path
repo1-path=/pgbackrest

# Client-side encryption of the repository
repo1-cipher-type=aes-256-cbc
repo1-cipher-pass=frase_larga_y_aleatoria

# Retention: keep 4 full backups
repo1-retention-full=4

# Compression and performance
compress-type=zst
compress-level=3
process-max=4
start-fast=y

[midb]
pg1-path=/var/lib/postgresql/16/main
pg1-port=5432

The repo1-s3-uri-style=path parameter is important with S3-compatible endpoints: it forces the endpoint/bucket style instead of the virtual-host style. Use eu-mad (Madrid), eu-fra (Frankfurt), or us-east depending on where you want the copy.

WAL archiving for PITR

To be able to restore to any second, you need PostgreSQL to send its WAL segments to the repository continuously. Configure in postgresql.conf:

archive_mode = on
archive_command = 'pgbackrest --stanza=midb archive-push %p'
archive_timeout = 60
max_wal_senders = 3
wal_level = replica

Reload the configuration, create the stanza, and verify that everything fits before relying on it:

# Create the repository and the stanza
pgbackrest --stanza=midb stanza-create

# Check that archive-push and the S3 connection work
pgbackrest --stanza=midb check

Run and schedule the backups

# Full backup (weekly)
pgbackrest --stanza=midb --type=full backup

# Differential backup (daily): only what changed since the last full
pgbackrest --stanza=midb --type=diff backup

# Incremental backup (hourly): only what changed since the previous one
pgbackrest --stanza=midb --type=incr backup

# View the repository status
pgbackrest --stanza=midb info

Schedule with cron

Automation is what turns a script into a backup policy. A typical scheme with pgBackRest combines a weekly full, daily differentials, and hourly incrementals:

# /etc/cron.d/pgbackrest
# m  h  dom mon dow  user      command
  0  2  *   *   0    postgres  pgbackrest --stanza=midb --type=full backup
  0  2  *   *   1-6  postgres  pgbackrest --stanza=midb --type=diff backup
  0  *  *   *   *    postgres  pgbackrest --stanza=midb --type=incr backup
# Verification restore, first day of each month at 5:00
  0  5  1   *   *    postgres  /opt/scripts/verificar-restore.sh

With OtterStorage's model of no charge per request (PUT/GET/LIST) or per delete, running frequent incrementals or uploading thousands of WAL segments doesn't inflate the bill: you only pay per TB stored. That changes the math compared to other providers where every operation counts.

Upload to a bucket with rclone or restic

If you do logical backups and want a layer of synchronization, or of encryption and deduplication, rclone and restic are excellent complements.

rclone to sync dumps

Configure a remote pointing to the endpoint and sync your dumps directory:

# ~/.config/rclone/rclone.conf
[otter]
type = s3
provider = Other
endpoint = https://es-mad-1.s3.otterstorage.io
region = eu-mad
access_key_id = AKIAOTTEREXAMPLE
secret_access_key = clave_secreta_aqui

# Upload local dumps and delete from the target those that no longer exist at the source
rclone sync /var/backups/pg otter:backups-pg/midb \
  --transfers 8 --checksum --progress

You'll find the details in the rclone guide.

restic for encrypted, deduplicated snapshots

export RESTIC_REPOSITORY="s3:https://es-mad-1.s3.otterstorage.io/backups-pg/restic"
export RESTIC_PASSWORD_FILE="/etc/pg/restic.pass"
export AWS_ACCESS_KEY_ID="AKIAOTTEREXAMPLE"
export AWS_SECRET_ACCESS_KEY="clave_secreta_aqui"

# Initialize the repository once
restic init

# Streaming backup of the dump (without touching disk), with a tag
pg_dump -Fc midb | restic backup --stdin --stdin-filename midb.dump --tag postgresql

# Retention policy: 7 daily, 4 weekly, 6 monthly
restic forget --keep-daily 7 --keep-weekly 4 --keep-monthly 6 --prune

Check the restic guide to fine-tune retention. Restic encrypts everything client-side, so not even we could read your snapshots.

pg_dump vs. pgBackRest: when to use each

Immutability with Object Lock against ransomware

Modern ransomware goes after your backups first: if it encrypts or deletes them, you have no alternative to the ransom. The defense is to make your copies immutable. Enable Object Lock (WORM) on the backup bucket with OtterVault: during the retention period, not even an attacker with your credentials can overwrite or delete the objects.

OtterStorage offers two modes. Governance lets users with a special permission bypass retention (useful for fixing mistakes); Compliance allows no exceptions, not even for the root account, which meets regulatory immutability requirements. For protection at the full-bucket level you also have Legal Hold.

# Create the bucket with versioning and Object Lock enabled (a WORM requirement)
aws s3api create-bucket --bucket backups-pg \
  --object-lock-enabled-for-bucket \
  --endpoint-url https://es-mad-1.s3.otterstorage.io

# Default retention: 30 days in Compliance mode
aws s3api put-object-lock-configuration --bucket backups-pg \
  --object-lock-configuration '{"ObjectLockEnabled":"Enabled","Rule":{"DefaultRetention":{"Mode":"COMPLIANCE","Days":30}}}' \
  --endpoint-url https://es-mad-1.s3.otterstorage.io

Object Lock requires versioning enabled on the bucket. Dig deeper in the Object Lock guide and in versioning; for automatic retention of old WAL, combine the above with lifecycle rules.

Test the restore (what really matters)

Repeat after us: an untested restore is technical debt. Schedule periodic restores in an isolated environment and check that the database comes up and the data adds up. For a logical dump:

# Download and restore to a verification database
aws s3 cp s3://backups-pg/midb/midb-2026-06-11.dump - \
  --endpoint-url https://es-mad-1.s3.otterstorage.io \
  | pg_restore --clean --if-exists -d midb_restore

# Quick integrity check
psql -d midb_restore -c "SELECT count(*) FROM clientes;"

With pgBackRest, restoring with PITR is straightforward: you recover the cluster and replay the WAL up to the chosen instant.

# Restore to an exact moment before an accidental deletion
pgbackrest --stanza=midb \
  --type=time "--target=2026-06-11 14:25:00+02" \
  --delta restore

# Afterward, in postgresql.auto.conf pgBackRest leaves recovery_target;
# start PostgreSQL and let it replay the WAL up to that point
pg_ctl start

Document how long it takes (your real RTO) and automate the monthly verification with the script we added to cron earlier. Follow the 3-2-1 principle: three copies, on two media, one off your infrastructure. S3 with Object Lock covers that external immutable copy. If you're coming from another provider, OtterBridge helps with the assisted migration and OtterSync replicates across regions to keep your off-site copy in EU-FRA or US-EAST.

Take a look at the backups solution and the per-TB pricing (HDD €8, SSD €16, NVMe €45, no per-request cost) to size your strategy.

Frequently asked questions