How do I add backups and point-in-time restore to PostgreSQL?

To add backups and point-in-time restore (PITR) to PostgreSQL, you need two things:

1. A base backup of the database cluster
2. Continuous archiving of WAL (Write-Ahead Log) files

With those, you can restore to any moment in time after the base backup.

---

1) Enable WAL archiving in postgresql.conf

Set these parameters:

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'

Notes:

• archive_mode = on enables archiving.
• archive_command copies each completed WAL segment to your archive location.
• The example uses a local directory; in production, this is often S3, NFS, or another backup server.
• Make sure the archive destination is reliable and not on the same disk as the database.

You may also want:

max_wal_senders = 3
hot_standby = on

These are useful for replication, but not strictly required just for PITR.

Restart PostgreSQL after changing settings that require it.

---

2) Take a base backup

Use pg_basebackup to create a consistent physical backup:

pg_basebackup -D /backups/base/$(date +%F_%H%M%S) -Fp -Xs -P -U replication_user

What the options mean:

• -D backup directory
• -Fp plain format
• -Xs stream WAL during backup
• -P show progress
• -U use a replication-capable user

If you don’t want to use streaming, you can also take a filesystem-level copy with pg_start_backup / pg_stop_backup in older versions, but pg_basebackup is preferred.

---

3) Archive WAL continuously

Ensure WAL segments are being archived successfully.

Example archive directory:

mkdir -p /backups/wal

Check PostgreSQL logs for archive failures. If archive_command fails, WAL accumulation can eventually fill disk.

A safer archive_command should:

• be idempotent
• verify the file wasn’t already archived
• handle failures correctly

Example:

archive_command = 'cp %p /backups/wal/%f'

Better:

archive_command = 'test ! -f /backups/wal/%f && cp %p /backups/wal/%f'

---

4) Restore from backup

To restore:

1. Stop PostgreSQL
2. Replace the data directory with the base backup
3. Configure recovery
4. Start PostgreSQL and let it replay WAL up to the target time

PostgreSQL 12 and newer

Create a recovery.signal file in the data directory and set recovery options in postgresql.conf or postgresql.auto.conf.

Example recovery settings:

restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2026-10-07 10:15:00'
recovery_target_action = 'pause'

Then create:

touch /var/lib/postgresql/data/recovery.signal

Start PostgreSQL and it will replay WAL until the specified time.

PostgreSQL 11 and older

Use recovery.conf in the data directory instead.

---

5) Example PITR workflow

Backup time

Suppose you made a base backup at 02:00 and WAL archiving has been running since then.

Accident

At 10:17, someone drops a table.

Restore

You restore the base backup and replay WAL until 10:16:59:

recovery_target_time = '2026-10-07 10:16:59'

That brings the database to just before the bad event.

---

6) Recommended backup strategy

For production, use:

• Daily base backups
• Continuous WAL archiving
• Periodic restore tests
• Off-site storage
• Retention policy

Example retention:

• Keep 7 daily base backups
• Keep WAL needed for all backups
• Replicate backups to another region/cloud account

---

7) Common pitfalls

WAL archiving not working

If archive_command fails, backups won’t be usable for PITR.

Backup and WAL on same machine

If the server dies, you lose both. Store backups elsewhere.

Not testing restores

A backup you haven’t restored is not a backup you can trust.

Missing WAL after base backup

You can’t recover beyond the WAL you have archived.

Permission issues

Ensure PostgreSQL can write to the archive directory or remote destination.

---

8) Consider using backup tooling

For easier management, consider:

• pgBackRest
• Barman
• WAL-G

These tools handle:

• full/incremental backups
• WAL archiving
• compression
• encryption
• retention
• restore automation

For most production environments, these are better than hand-rolled scripts.

---

Minimal example

postgresql.conf

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

Take backup

pg_basebackup -D /backups/base/backup1 -Fp -Xs -P -U replication_user

Restore

restore_command = 'cp /backups/wal/%f %p'
recovery_target_time = '2026-10-07 10:15:00'

Then start PostgreSQL with the restored base backup.

---

If you want, I can give you:

1. a step-by-step setup for PostgreSQL 16,
2. a Docker/Kubernetes version, or
3. a pgBackRest-based production setup.