How do I set up backups and point-in-time recovery in PostgreSQL?

To set up backups and point-in-time recovery (PITR) in PostgreSQL, you generally need two things:

1. A base backup of the database cluster
2. Archived Write-Ahead Logs (WAL) so PostgreSQL can replay changes up to a specific moment in time

This is the standard PostgreSQL-native approach.

---

1) Understand the pieces

Base backup

A full copy of the PostgreSQL data directory taken while the server is running or stopped.

WAL archiving

PostgreSQL writes every change to WAL. If you save those WAL files externally, you can restore a base backup and replay WAL to:

• a specific timestamp
• a transaction ID
• a named recovery target
• just before a bad query, accidental delete, etc.

---

2) Enable WAL archiving

Edit postgresql.conf:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Notes

• wal_level = replica is usually sufficient for PITR.
• archive_command should copy completed WAL segments to safe storage.
• Make sure the archive directory exists and is writable by PostgreSQL.

Example:

mkdir -p /var/lib/postgresql/wal_archive
chown -R postgres:postgres /var/lib/postgresql/wal_archive

Reload or restart PostgreSQL after changes:

systemctl restart postgresql

---

3) Take a base backup

You can use pg_basebackup, which is the simplest built-in tool.

Example:

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

Explanation

• -D destination directory
• -Fp plain format
• -Xs stream WAL during backup
• -P show progress
• -R writes recovery settings for standby use; optional depending on workflow

If you want a backup that is specifically for restore/PITR, store it somewhere safe and immutable if possible.

---

4) Ensure WAL is archived continuously

Once archiving is enabled:

• PostgreSQL will call archive_command for each completed WAL segment
• You should verify files are appearing in the archive directory

Check with:

ls -lh /var/lib/postgresql/wal_archive

Also monitor logs for archive failures. If archiving fails, WAL files can pile up and eventually fill disk.

---

5) Restore for point-in-time recovery

Step A: Stop PostgreSQL

systemctl stop postgresql

Step B: Replace the data directory with the base backup

Move aside the current cluster and restore the backup into the data directory.

Example:

mv /var/lib/postgresql/data /var/lib/postgresql/data_old
cp -a /backups/base/2026-10-07_120000 /var/lib/postgresql/data
chown -R postgres:postgres /var/lib/postgresql/data

Step C: Configure recovery

For PostgreSQL 12+, create one of these files in the data directory:

Option 1: recovery.signal

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

Then add recovery settings to postgresql.conf or postgresql.auto.conf, such as:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-10-07 12:34:56'
recovery_target_action = 'pause'

Option 2: use postgresql.auto.conf

Some tools write recovery settings automatically, but manual configuration is fine.

---

6) Choose a recovery target

Common targets:

Recover to a specific time

recovery_target_time = '2026-10-07 12:34:56'

Recover to before a transaction

recovery_target_xid = '123456'

Recover to before a named restore point

First create a restore point before a risky operation:

SELECT pg_create_restore_point('before_migration');

Then recover to it:

recovery_target_name = 'before_migration'

Stop recovery before a bad event

You can recover up to just before a time or event, then inspect data before promoting the cluster.

---

7) Start PostgreSQL and let recovery run

systemctl start postgresql

PostgreSQL will:

1. restore the base backup
2. fetch WAL using restore_command
3. replay WAL until it reaches the recovery target

Check logs to confirm it recovered successfully.

---

8) Promote after recovery

If recovery pauses at the target and you want the server to become writable:

pg_ctl promote -D /var/lib/postgresql/data

Or restart depending on your setup.

---

9) Best practices

Backup strategy

• Use base backups + WAL archiving
• Schedule regular base backups, e.g. daily or weekly
• Keep WAL archiving continuous

Store backups offsite

Don’t keep backups on the same disk or server only.

Test restores

A backup is only useful if you can restore it. Regularly test recovery in a staging environment.

Monitor archive failures

If archive_command fails, PITR may be broken.

Retention

Keep:

• enough base backups for your recovery window
• enough WAL segments between backups

Consider backup tools

For production, many people use:

• pgBackRest
• Barman
• WAL-G

These tools simplify:

• WAL archival
• retention policies
• compression
• encryption
• restore validation
• incremental backups

---

10) Minimal example setup

postgresql.conf

wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
max_wal_senders = 3

Take backup

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

Restore command

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-10-07 12:34:56'

---

11) Important caution

If you want real PITR, backups alone are not enough. You must have:

• a valid base backup
• all WAL since that backup
• correct restore_command
• working archive retention

Without WAL archiving, PostgreSQL cannot recover to a point in time.

---

If you want, I can give you:

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