How do I set up PostgreSQL backups for a production application?

For a production PostgreSQL application, the usual goal is recoverability, not just “having a dump.” A good backup setup should protect you against:

• accidental deletes/updates
• database corruption
• failed deploys/migrations
• disk/server loss
• ransomware or operator mistakes

1) Use the right backup strategy

Option A: Logical backups

Use pg_dump / pg_dumpall.

Good for:

• smaller databases
• portability
• restoring a single database or table
• schema changes/migrations

Limitations:

• slower for large databases
• doesn’t give point-in-time recovery by itself
• can miss very recent changes if only done daily

Option B: Physical backups + WAL archiving

Use base backups plus Write-Ahead Log (WAL) archiving.

Good for:

• medium/large production databases
• fast recovery
• point-in-time recovery (PITR)

This is usually the best production choice.

---

2) Recommended production approach

Best practice

Use:

• periodic base backups
• continuous WAL archiving
• retention policy
• offsite storage
• automated restore testing

This lets you restore:

• to the latest state
• or to a specific moment before a bad change

---

3) Configure WAL archiving

In postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

More robust example using rsync, S3 tooling, or a backup tool is better than plain cp.

Also set:

max_wal_senders = 5
wal_keep_size = 1024MB

Then reload PostgreSQL.

Important

Your archive_command must be:

• reliable
• idempotent
• fast
• able to alert on failure

If archiving breaks, your backups may become unusable for PITR.

---

4) Take base backups

Use pg_basebackup to create a physical base backup.

Example:

pg_basebackup -h localhost -U replicator -D /backup/base/$(date +%F_%H-%M-%S) -Fp -Xs -P -R

What the flags mean:

• -Fp plain format
• -Xs stream WAL during backup
• -P show progress
• -R write replication config

You can schedule this daily or weekly depending on change volume and retention.

---

5) Prefer a backup tool for production

Instead of hand-rolling scripts, consider one of these:

• pgBackRest — very popular, robust, supports full/incremental/differential backups, compression, encryption, S3, retention policies
• Barman — solid for PostgreSQL backup and PITR
• WAL-G — good cloud-native option, often used with S3-compatible storage

If you’re running production PostgreSQL, pgBackRest is often the safest choice.

---

6) Example pgBackRest setup

Typical benefits:

• full + incremental backups
• WAL archiving
• retention rules
• remote storage
• checksums and verification

High-level steps:

1. Install pgBackRest
2. Create a repo location
3. Configure PostgreSQL archiving to call pgBackRest
4. Define backup retention
5. Schedule backups
6. Test restores

Example config snippet:

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=7
compress-type=lz4

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

Then archive command like:

archive_command = 'pgbackrest --stanza=main archive-push %p'

---

7) Store backups off the database server

Never keep backups only on the same machine.

Use:

• separate disk/server
• object storage like S3
• encrypted backup storage
• cross-region copy if availability requirements are strict

A common pattern:

• primary DB server
• backup repository server or S3 bucket
• optionally immutable/object-lock storage

---

8) Encrypt backups

Backups should be encrypted:

• at rest
• in transit

Depending on tool:

• pgBackRest supports encryption
• cloud storage can use server-side encryption
• still protect keys carefully

---

9) Set retention policies

You need to balance cost and recoverability.

Example policy:

• keep daily backups for 7 days
• weekly backups for 4 weeks
• monthly backups for 12 months

The exact numbers depend on compliance and operational needs.

---

10) Test restores regularly

This is one of the most important steps.

A backup is only useful if you can restore it.

Test:

• full restore to a staging server
• restore to a specific point in time
• application startup after restore
• data integrity checks

Automate this if possible.

---

11) Consider logical backups too

Even if you use physical backups, it can be useful to also run:

pg_dump -Fc -f mydb.dump mydb

This is helpful for:

• restoring a single database separately
• extracting specific objects
• migration support

Many teams use:

• physical backups for disaster recovery
• logical backups for convenience and migration

---

12) Monitor backup health

Alert on:

• failed backup jobs
• WAL archiving failures
• storage nearing capacity
• backup age exceeding threshold
• restore test failures

If backups run silently broken for weeks, that’s a common production disaster.

---

13) Simple production checklist

At minimum:

• automatic backups enabled
• WAL archiving enabled
• backups stored off-host
• encryption enabled
• retention policy defined
• restore procedure documented
• restore tests run regularly
• monitoring/alerts in place

---

14) If you want a simple starting point

If your database is not huge, start with:

• nightly pg_dump for logical backup
• daily pg_basebackup
• WAL archiving
• offsite storage
• weekly restore test

If your app is mission-critical, move quickly to:

• pgBackRest + WAL archiving + PITR

---

If you want, I can give you:

1. a minimal pgBackRest production configuration,
2. a Docker/Kubernetes backup setup, or
3. a step-by-step restore procedure.