PostgreSQL PITR: where data is lost between copies
PostgreSQL PITR rests on two pillars: a base backup and a continuous transaction log archive.
Point-in-time recovery is sequential replay of the log on top of the backup up to a specified mark, not a “database rollback.” While the archive is being replenished, the recovery point slides with the load; as soon as archiving stops, the data loss window grows every minute.
The gap forms unnoticed. If the archiver crashes or cannot keep up with the stream of records, log segments remain on disk and are reused instead of being sent to storage.
The stream breaks at the last accepted segment: recovery stops earlier than the required mark or does not start at all, and the actual point moves back to the previous full archive.
In an incident it looks like this: at night the archiver went down, the disk for logs overflowed and rotation kicked in, and no signal about it reached anyone.
In the morning, a mark half an hour before the failure is needed — it is not there, recovery fails at a gap of several hours, and everything that happened after the last successful archive shipment is lost.
Separately — database integrity after a failure. Even when the mark is found, the state at that point may be inconsistent with external systems: charges went through, events were sent, and the database rolls back to before them.
The point is chosen by the log and application logs, not “by eye.” Let's break down how this window is closed.
Continuous log archiving: what changes in practice
Continuous log archiving changes the very framing of the task: instead of a daily dump, write-ahead log segments go to separate storage every few minutes. The recovery point stops depending on the backup schedule and shifts with the last confirmed transaction.
Recovery point instead of whole days
At acceptance we measure two numbers: how far back the database can be rolled back without loss and how long the rollback itself takes.
The first is set by the frequency of segment archiving and the stability of archive_command, the second by the size of the base backup and the speed of replaying the log. We measure database recovery time on a copy of the staging environment, not estimate it by eye.
Which class of failures is covered
The main scenario is not hardware failure but error: a mass UPDATE or DELETE without a condition, a failed schema migration, data corruption by a defective release.
A replica does not help here because it faithfully repeats the same change, so rollback is possible only to a point before the failure.
Recovery into a separate instance and regular drills turn the procedure from a document into a working skill and directly raise the share of successful recoveries.
We separately check that archiving stops when storage is unavailable and catches up after it returns, that a notification arrives about missed segments, and that the archive retention period covers the rollback window the business needs.
Full backup, transaction log, or replication: comparison
None of these mechanisms replaces the others — they cover different classes of tasks.
A full backup answers the question “how to return the database as a whole,” the transaction log — “how to roll back to a specific second,” replication — “how to survive a node failure without downtime.” The choice begins with how much data the business is prepared to lose during a failure.
| Option | When it fits | Limitations |
|---|---|---|
| Full backup | A complete database backup is needed, recovery to the time it was taken, preparation of a test environment | The recovery point is tied to the schedule: everything after the backup is lost |
| Transaction log | Rollback to an arbitrary second, error investigation, cancellation of a failed release | Works only together with a base backup; the archive grows, the chain must be checked for integrity |
| Streaming replication | Hot standby, fast switchover on node failure, reads from replicas | The duplicate also repeats logical errors: dropping a table will propagate to the replica |
| Logical replication | Transfer between major versions, selective transfer of tables, merging databases | Does not cover all objects, lags on key conflicts |
| Disk snapshot | Fast return of the whole node, cloning of environments | Without preparation does not provide transaction-level consistency, the point is the snapshot moment |
When a full backup is enough
If the loss of several hours of work is acceptable, the database fits within a reasonable backup window, and there is no requirement to “roll back a minute before the failure” — regular full backups and periodic recovery testing on a separate environment are enough. The environment is simple to maintain, but the recovery point is rigidly tied to the schedule.
When a combined scheme is needed
When minutes matter — payments, orders, warehouse stock — a full backup is not enough: it is supplemented with a continuous transaction log archive, and together they provide point-in-time recovery.
Streaming replication adds a hot standby and switchover without downtime, logical replication — transfer between major versions. The scheme works only if the archive chain has been verified and the recovery scenario has been rehearsed.
Implementation order for point-in-time recovery
Implementation of point-in-time recovery is a sequence with verification at every step. First an audit: what is being backed up now, how the write-ahead log is archived, and where the segment stream breaks.
- Audit of backups and the log — inventory of base backups, continuity of the log archive, retention depth, and detected gaps.
- Archive storage scheme — base backups and log segments are separated by lifecycle, locations, and rules for offloading outside the environment.
- Archiving setup — enable continuous segment shipping, record configuration parameters, remove everything that breaks the stream.
- Reference backup and integrity control — take a base backup and verify its readability before it is needed.
- Target point — agree on the point to which recovery is performed: the last transaction, the start of the failure, or a timestamp in the log.
- Scenario drills — practice recovery to an intermediate point, not only to the last available one.
Testing on a separate environment
We bring recovery up on an isolated stand, not on the production node. We compare checksums, volumes by key tables, and record the time from command to readiness to accept load.
Handover to operations and regulations
We hand over settings, the storage scheme, and regulations: who takes backups, how often they are checked, what to do when the log breaks. Separately — the point-in-time recovery procedure and responsible persons, so that launch does not depend on one engineer.
What remains in the environment: configurations, schemes, regulations
PITR setup lives not in chat but in configuration. So that point-in-time recovery works without us, the customer receives a complete set: archiving parameters, retention periods, actions in case of failure, and a memo for the on-call engineer.
The format is deliberately boring — a configuration file, a schedule table, a document of a few pages.
Archiving settings and retention periods
- archiving configuration: changed parameters (
archive_mode,archive_command,wal_level) with an explanation of why each is needed and what will break if it is reverted to the default value; - full and incremental backup schedule, launch window, success log, and notification on a miss;
- backup retention period: how long the change log and full backup live, what is cleaned automatically, and which rule leaves a reserve for rollback;
- verification: scheduled test recovery on a separate instance with the result recorded.
Documentation and on-call memo
- recovery regulations: steps, target point in time, success criterion, and traffic switchover procedure;
- database documentation: schema, critical tables, volumes, and growth points;
- one-page memo: where backups and keys are stored, who is authorized to start recovery, and where the report on the performed operation is written.
Case: recovery after mass data deletion
A logical failure is the only class of accidents where a replica does not help: a DELETE without a condition instantly propagates into the replication stream, and the standby node becomes just as empty a copy of the PostgreSQL cluster as the primary.
In this case, the orders table lost its working set in seconds, and it was noticed only several hours later from operator complaints. Below is how recovery was arranged before the incident and what was changed.
How it was before
Recovery relied on a nightly full dump: the data loss depth was almost a day, so a failure in the middle of the day wiped out the entire working day of operations.
Deployment was manual and took hours, and no one restored copies — the dump was considered valid simply because it existed. Recovery had to be done over the production environment, blindly bringing up related services.
What changed after
Continuous write-ahead log archiving and a base backup were enabled: now the recovery point is set with minute precision within the retention window, not “before the night starts.” An isolated environment was allocated for deployment — the database is brought up there, a set of control queries for relationship integrity is run, and only then is a decision made about migration.
The recovery window shrank from hours to tens of minutes, and the data loss depth — to a few minutes.
Recovery testing became a regular procedure: once a quarter we deploy a copy to an arbitrary point in the past and compare row counts and checksums for key tables.
It was this practice that closed the class of failures entirely — recovery after data deletion stopped being irreversible, and service restoration timelines became known in advance instead of being estimated as “roughly overnight.”
How PostgreSQL PITR differs from replication and disk snapshots
Replication is a continuous stream of changes from the write-ahead log, repeating the current state on the copy. It keeps a second copy up to date and offloads reads, but does not roll back: a corrupted command reaches the replica in fractions of a second.
Transaction rollback through it is impossible — it already has the same state as the primary server.
A disk snapshot captures exactly one moment. Between two snapshots lies an interval that nothing can close: recovery accuracy equals the snapshot period, and data for the gap is absent.
Moreover, a volume snapshot does not guarantee page consistency if it was taken on a running system without synchronization with writes.
Point-in-time recovery is arranged differently: a base backup of the cluster plus a continuous write-ahead log archive. The point is set by a timestamp or transaction number — the server replays the log up to it and stops. Any point within the archive retention period can be chosen.
The difference is in the task. Replication is responsible for availability and read offloading, a snapshot — for fast return after volume loss. Point-in-time recovery closes errors: an unsuccessful update, mass deletion, batch processing failure.
Selective table recovery is done through a separate instance: it is brought up at the required point, the affected tables are exported and transferred to the production cluster without rolling it back entirely.
The trade-off for flexibility is infrastructure: the archive accumulates separately, and recovery itself takes time proportional to the log volume.
Limitations and common point-in-time recovery failures
Point-in-time recovery (PITR) rests on a continuous chain of transaction logs: a base snapshot plus all segments after it. If one segment is lost or shipped with an error, recovery stops at the last continuous place, and a point inside the gap becomes unreachable.
Therefore archive completeness is monitored continuously, not found out at the moment of an accident.
The rollback depth is set by the archive retention period: if segments from last month were deleted, a point from a month ago can no longer be brought up. Below are technical concerns most often discussed before starting.
What to do if a gap is found in the log chain?
Recovery proceeds to the last continuous segment; anything later cannot be applied. After the incident, we set up constant monitoring of archive shipping and notifications about misses, so that the next gap is detected immediately, not during an attempted rollback.
How to make sure a backup is suitable for recovery?
Suitability is checked not by reading the log but by regular test recovery on a separate stand: compare segment checksums, bring up the backup, verify data integrity. In this case, the retention period is taken with a reserve relative to the deepest rollback scenario.
Will recovery affect the production environment? Can a single table be brought up?
Analysis is performed in an isolated environment: we do not stop the production server, and user load does not suffer. If a separate table is needed, we deploy a copy alongside and transfer the required rows from it — this is safer than a full database rollback.
Let's discuss your data recovery task
So that the assessment is not “by eye” but based on facts, at the first step we need a minimal snapshot of your environment: where base backups are now stored, how write-ahead log archiving is arranged, and how regularly anyone has attempted recovery.
On our side, we will return an estimate of work, an implementation plan, and timelines — broken down by stages, so that the decision can be made in parts.
What we need from you:
- current backup scheme and backup storage location;
- required recovery point and acceptable service downtime;
- database size, write intensity, and presence of replicas;
- constraints on the maintenance window and access procedure for stands;
- which operations most often lead to data loss.
What we will return at the first step:
- estimate of work by stages: archiving, integrity control, recovery regulations;
- implementation plan with the order of changes and rollback points;
- timelines and composition of checks — from training recovery on a copy to checksum comparison;
- backup storage scheme with retention policy and access separation;
- document with regulations and the person responsible for regular checks.
Describe your task — we will get back with an estimate and plan, without general words.