PostgreSQL backups point in time recovery

PostgreSQL Point-in-Time Recovery (PITR) lets you restore a database backup and then “replay” WAL (Write-Ahead Log) files up to a specific moment in time, transaction ID, or WAL location.

Core idea

To do PITR you need:

1. A base backup
   • A consistent full backup of the data directory.
2. WAL archiving enabled
   • PostgreSQL must save WAL segments continuously.
3. A restore process
   • Restore the base backup, then apply WAL until the desired recovery target.

---

Typical setup

1) Enable WAL archiving

In postgresql.conf:

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

Notes:

• wal_level = replica is usually enough for PITR.
• archive_command copies completed WAL files to an archive location.
• Make sure the command returns 0 on success.

Reload or restart PostgreSQL after changes.

---

2) Take a base backup

You can use pg_basebackup:

pg_basebackup -D /backups/basebackup -Fp -Xs -P -U backup_user

Common options:

• -Fp = plain format
• -Xs = include WAL during backup
• -P = show progress

If you use another backup tool like pgBackRest or Barman, they also support PITR and often simplify this process.

---

3) Restore the backup

Stop PostgreSQL, then restore the base backup into the data directory:

systemctl stop postgresql
rm -rf /var/lib/postgresql/data/*
cp -a /backups/basebackup/* /var/lib/postgresql/data/

---

4) Configure recovery

Create a recovery signal file and tell PostgreSQL where to find archived WAL:

For PostgreSQL 12+:

• Create recovery.signal
• Set recovery parameters in postgresql.conf or postgresql.auto.conf

Example:

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-10-07 12:30:00'
recovery_target_action = 'promote'

Then create:

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

For older versions, use recovery.conf.

---

5) Start PostgreSQL

When PostgreSQL starts, it will:

• restore the base backup
• fetch archived WAL files
• replay transactions until the target time

---

Recovery targets

You can recover to different points:

Time

recovery_target_time = '2026-10-07 12:30:00'

Transaction ID

recovery_target_xid = '123456'

Named restore point

First create one before disaster:

SELECT pg_create_restore_point('before_migration');

Then recover to it:

recovery_target_name = 'before_migration'

Immediate

Recover as far as possible:

recovery_target_action = 'promote'

---

Important best practices

• Test restores regularly — backups are only useful if recovery works.
• Archive WAL off-host — don’t store only on the same server.
• Monitor archive_command failures — missed WAL means broken PITR.
• Retain WAL long enough to cover your recovery window.
• Use automation — pgBackRest/Barman are better than hand-rolled scripts for production.
• Document your recovery steps before an incident.

---

Common pitfalls

• Missing WAL files in the archive
• Base backup not consistent
• Wrong permissions on archive directories
• Clock/timezone confusion when using recovery_target_time
• Forgetting to create recovery.signal
• Archiving configured but not actually working

---

Quick summary

PITR = base backup + WAL archiving + recovery target.

If you want, I can also provide:

1. a production-ready PITR setup for PostgreSQL,
2. an example using pgBackRest or Barman, or
3. a step-by-step restore runbook.