Point-in-time recovery in Postgres: WAL, backups, restores

Point-in-time recovery (PITR) restores a PostgreSQL database to its exact state at a moment you choose, for example 10:42:00, a few seconds before a faulty data fix deleted thousands of rows. It works by combining a physical base backup with the continuous archive of write-ahead log (WAL) files recorded since, replayed up to your target time. A nightly pg_dump can't do this: it can only take you back to last night, losing a day of payments, enrolments and test attempts.

Why pg_dump alone isn't a backup strategy

pg_dump writes a logical copy of one database: SQL, or an archive of table data and definitions. It's consistent and portable, and it has real uses. But as the only backup of a production database it falls short:

Recovery objectives decide which you need; our guide to RTO and RPO covers how to set them. For any database holding payments or exam data, the answer is almost always base backups plus WAL archiving, with pg_dump kept for the jobs it's good at.

Base backups and WAL

PostgreSQL writes every change to the write-ahead log before changing the data files, which is how it recovers from crashes. If you keep all those WAL files, you can replay history. The documentation on continuous archiving spells out the benefit: starting from a base backup, you can stop replay at any point and get a consistent snapshot of the database as it was at that time.

Two pieces are needed:

• A base backup: a physical copy of the data directory, taken while the server runs. pg_basebackup takes one without affecting other clients and, by default, streams the WAL needed to make it consistent. PostgreSQL 17 added incremental backups: with WAL summarisation turned on, pg_basebackup --incremental copies only changed blocks, and pg_combinebackup reassembles a full backup at restore time.
  
  
• A WAL archive: every completed WAL segment copied somewhere safe, such as object storage in another region.
  
  

# postgresql.conf on the primary
wal_level = replica
archive_mode = on
archive_command = 'pgbackrest --stanza=main archive-push %p'
archive_timeout = 60

Postgres treats a zero exit status from archive_command as proof the file is safe, so the command must fail loudly on any error and should refuse to overwrite an existing archive file. archive_timeout forces a switch to a new WAL segment at least once a minute, so on a quiet night your archived recovery point stays within about a minute of the present; the documentation calls a minute or so usually reasonable.

Watch the archiver closely. If archiving keeps failing, WAL files pile up in pg_wal, and if that disk fills, PostgreSQL shuts down with a PANIC. No committed data is lost, but the database stays offline until you free space. Alert on pg_stat_archiver failures and on pg_wal disk usage, alongside the other signals in our guide to monitoring vs observability.

How point-in-time recovery works

1. Restore onto a new server. Never overwrite production to investigate; restore alongside it.
   
   
2. Copy back the latest base backup taken before your target time.
   
   
3. Configure recovery. Set restore_command and a recovery target in postgresql.conf, and create an empty recovery.signal file in the data directory. Since PostgreSQL 12, recovery.conf no longer exists.
   
   
4. Start the server. It fetches archived WAL and replays it up to the target.
   
   
5. Check, then finish. By default, recovery_target_action is pause, so with hot standby on you can query the database and confirm it's the right moment. pg_wal_replay_resume() then ends recovery; the server starts a new timeline and accepts writes.
   
   

# postgresql.conf on the restore server
restore_command = 'pgbackrest --stanza=main archive-get %f "%p"'
recovery_target_time = '2026-09-18 10:42:00+05:30'
recovery_target_inclusive = off
recovery_target_action = 'pause'

# then: touch $PGDATA/recovery.signal and start the server

Write the target time with a numeric UTC offset such as +05:30. The recovery settings don't accept time zone abbreviations unless configured to, so "IST" won't work. With recovery_target_inclusive = off, recovery stops just before any transaction that committed at exactly the target time. If you know the offending transaction, recovery_target_xid or recovery_target_lsn is more precise, and before a risky data fix you can call pg_create_restore_point('before_fix') and later recover to that name.

A worked example: the DELETE at 10:42

An illustrative incident. A release goes out at 10:30. At 10:42:07 a data-fix script meant to remove one batch's expired enrolments runs with a missing filter and deletes enrolments for every batch. Support tickets arrive at 10:50, and at 10:55 the team decides to recover.

• They restore the 02:00 base backup to a new server and set the target to 10:42:00, a few seconds before the delete. Replay covers about eight and a half hours of WAL.
  
  
• Recovery pauses at the target. They check that the enrolments table has its full count and that the newest rows are from 10:41.
  
  
• They do not roll production back. Between 10:42 and the fix, students paid fees, submitted tests and joined classes; rolling the whole database back would destroy those writes. Instead they export the deleted enrolments from the recovered server and insert into production only the rows that are missing, matched by primary key.
  
  

That pattern, recover to the side and repair surgically, is usually right for logical mistakes. A full rollback suits only cases where the whole database is damaged. Notice too what set the recovery time: restoring the base backup, then replaying WAL from 02:00. More frequent base or incremental backups shorten the replay and the RTO.

Tools: pgBackRest, WAL-G and managed services

For self-managed Postgres, pgBackRest is a common choice: point archive_command at archive-push, schedule full and differential backups, and keep one repository nearby for fast restores and a second in another region. On a managed service, the equivalent work is choosing a retention period, copying backups to another region or account, and practising the restore, which always creates a new instance with a new endpoint your application must be pointed at.

Retention and storage

Keep backups for longer than it takes you to notice a problem. A bug that quietly corrupts marks may surface only when a student complains ten days later; if your oldest restore point is seven days old, you can't get the correct data back. A sensible starting pattern is a weekly full backup, daily differential or incremental backups, continuous WAL, and retention of two to five weeks, plus monthly logical dumps kept longer for audits.

An illustrative sizing: a 300 GB database that generates 40 GB of WAL a day, with 14 days of point-in-time recovery, weekly full backups and daily differentials averaging 20 GB. At the edge of the window you hold up to three full backups (900 GB), about 12 differentials (240 GB) and 14 days of WAL (560 GB): around 1.7 TB before compression. Measure your own WAL rate by sampling pg_current_wal_lsn() a day apart and comparing the two with pg_wal_lsn_diff().

Store at least one copy outside the production account, and make it immutable, for example with S3 Object Lock, so ransomware or a compromised credential can't delete your history. Encrypt backups, and keep the encryption keys somewhere other than next to the backups.

Test your restores

An untested backup is a hope. The PostgreSQL documentation advises setting up and testing WAL archiving before taking the first base backup; after that, test restores on a schedule:

1. Automatically restore the latest backup to a scratch server every week or month, and recover to "now minus five minutes".
   
   
2. Check that the server reaches a consistent state, that row counts on key tables look right, and that the newest rows are recent. That age is your real RPO.
   
   
3. Run a smoke test of the application against it, then record how long the whole restore took. That's your real RTO.
   
   
4. Alert if the job fails, and investigate any drift in restore time as the database grows.
   
   

Restoring also gives you a safe copy for other work: rehearsing a risky migration from our guide to zero-downtime migrations, or testing a large query without touching production. Remember that read replicas are not backups; a mistaken DELETE reaches them within seconds, and only a restore point from before the mistake brings the data back.

Key takeaways

• PITR combines a base backup with archived WAL, replayed to any moment you choose.
  
  
• Set archive_timeout to bound data loss on quiet systems, and alert on archive failures before pg_wal fills the disk.
  
  
• Recover onto a separate server, pause at the target, and repair production surgically instead of rolling it back.
  
  
• Use pgBackRest or WAL-G, or a managed service, and keep an immutable copy in another account and region.
  
  
• Test restores on a schedule and measure the real RTO and RPO.
  
  

Frequently asked questions

What is point in time recovery PITR?

Point-in-time recovery is the ability to restore a database to its exact state at any chosen moment within a retention window, not just to the time of the last backup. In PostgreSQL it works by restoring a base backup and replaying archived write-ahead log up to a target time, transaction or named restore point, so you can recover to just before a mistake.

What is point in time restore?

Point-in-time restore is the operation managed databases offer on top of PITR: you pick a timestamp in the console or API, and the provider builds a new database instance from its backups and logs as of that moment. On Amazon RDS, for example, the restore creates a new instance rather than changing the original, so you then point your application at it or copy data back.

How to backup PostgreSQL database automatically?

For a self-managed server, turn on WAL archiving, then schedule full and incremental or differential backups with a tool such as pgBackRest or WAL-G, sending both to object storage with a retention policy. Add alerts for failed backups and archiving, and a scheduled restore test. On a managed service, set the backup retention period and copy backups to another region or account.

Can I restore just one table with point-in-time recovery?

Not directly: PostgreSQL recovers the whole cluster to the chosen moment. The usual approach is to run the recovery on a separate server, then export just the table or rows you need, with pg_dump or COPY, and load them into production. That way you repair the damaged data without losing everything else written since the target time.