High-performance and highly available VPS/VDS with automatic installation and full root access to the OS. The ordered resources are guaranteed to be reserved for you.
Fortify your operational continuity with our resilient disaster recovery solutions, ensuring swift recovery and minimal downtime in the face of unforeseen challenges.
PITR (Point-in-Time Recovery) is typically needed not for a routine infrastructure failure, but for logical data corruption: an accidental DELETE, DROP TABLE, a failed migration, an application bug, or a compromised account. Its purpose is to return the database to the last safe state before the error.
The main risk is choosing the wrong recovery point. If you restore to the time when the incident was discovered, the database may already be corrupted. Everything that happened after the safe point will have to be separately reconciled, replayed, transferred, or accepted as lost.
Technically, PITR works only when a chain has been prepared in advance: a base backup plus continuous transaction logs. In the cloud, the result is often a separate restored instance or clone, so PITR is not an emergency button, but a validated procedure with clear RPO/RTO, retention period, permissions, resources, and regular restore testing.
When the database is running, but the data can no longer be trusted
The most serious database incidents do not always look like outages. The instance is available, all monitors are green, replicas are synchronizing, and the application continues to respond — but the data has already been corrupted by a successful operation: a failed migration, a DELETE that affected the wrong rows, a DROP TABLE, an error in the admin panel, or a bug that wrote incorrect values for several hours.
For the business, this is worse than a typical outage: the system appears to be working, but its state can no longer be trusted.
The cloud reduces some infrastructure risks, but it does not eliminate logical errors. Replication can quickly propagate the problem to a standby, a regular snapshot may be too coarse-grained in time, and the phrase “backups are enabled” does not mean the team will be able to restore the required state without a long period of improvisation.
Point-in-Time Recovery, or PITR, is designed specifically for these cases: as a predefined procedure for restoring the database to a point before the error, with clear data loss expectations, recovery time, and operational steps after the restore.
At first glance, PITR looks like a “put it back the way it was” button. In practice, the first step is to answer a tougher question: exactly what point in time should the database be restored to, and why is that point almost always before the erroneous action, rather than the moment when the incident was detected?
What PITR Is and Why the Recovery Point Is Chosen Before the Error
PITR, or Point-in-Time Recovery, is the process of restoring a database to a selected point in time within the available recovery window. It is not an “undo” operation for a single command in a live database, and it does not let you return to any arbitrary second in the company’s entire history. PITR restores the entire database state to a specific moment: data, transactions, and relationships between tables.
The main risk is choosing the wrong time. A team detects an incident at 10:30 and wants to “restore to 10:29.” But if the error occurred earlier, the database is already carrying its consequences by that point. This type of restore simply returns the system to an already corrupted state.
Example:
10:15 — a table was deleted in the production environment;
10:30 — the incident was detected via monitoring, a ticket, or user complaints;
10:14 — the target recovery time, because this is the last safe state before the error.
The critical takeaway for an incident is this: the target point must be the last safe state before the error, not the moment an alert appeared in monitoring or a ticket was created. Otherwise, the team may formally complete the restore, but the business will not get a working database.
That is why, in PITR, the first step is to identify not when the issue was detected, but when the destructive change began. This is determined from application logs, audit logs of user actions, deployment history, events in the change management system, and messages from the on-call team.
If the timing is uncertain, the target point is usually chosen with a small buffer before the event: for example, 10:14 instead of 10:15:00. The cost of losing one extra minute of valid transactions is often lower than the risk of restoring the database to a point after the error had already started and having to repeat the entire cycle.
This approach makes business sense. PITR defines a clear boundary for data loss: everything written after the selected point is absent from the restored database. Those operations must be reconciled separately, replayed, migrated manually, or accepted as lost.
After the point is selected, the technical question is what must be retained in the infrastructure for the database to be able to return to that exact moment at all. This is where the mechanics of PITR begin: a base backup, transaction logs, and replaying changes up to the required time.
How PITR works: base backups, logs, and replay to a timestamp
If the infrastructure has only one backup snapshot, the database can only be restored to the state it was in at the time of that snapshot. That is not enough for precise recovery: hours may have passed between the backup and the error, along with thousands of orders, payments, and status changes.
PITR reconstructs the database state not from a single file, but from a chain. A base backup is the starting point for recovery: a consistent database state at a specific point in time. By itself, however, it has no record of what happened afterward. That history is stored in transaction logs.
In PostgreSQL, this role is performed by WAL — the write-ahead log. Other DBMSs use similar mechanisms: transaction logs, redo logs, binlogs, and archived logs. The details differ, but the idea is the same: the database stores not only the final state, but also the path it took to get there.
PITR can be thought of as assembling the database state from two parts:
A base backup created before the target point in time is used;
The transaction logs for the required period are made available to it;
The system sequentially replays the recorded changes;
Replay stops at the selected point in time;
The result is a restored copy of the database or instance in the required state.
The backup provides the foundation of the state, and the logs bring it to the required second. The system applies the transactions that occurred before the target recovery time and does not apply anything that happened after it. This makes it possible to restore to a point between two regular snapshots, rather than choosing only from coarse checkpoints.
A brief analogy: the base backup is a photo of a room, the logs are a record of every action after the photo was taken, and replay is playing that record up to the required frame. In a database, however, this is a strict mechanism: if the record of changes is continuous, the state can be restored precisely; if there is a break in the chain, precise PITR is no longer possible.
In practice, PITR usually produces a separate restored copy of the database or a cloud instance. The team then works with it separately: they validate the data, compare it with the current state, and decide what to switch over or transfer.
The mechanism has an inherent weak point: PITR works only as long as the entire chain is preserved — the base backup and continuous logs up to the required point in time. If there is no base backup or no logs for the required period, it will not be possible to recover precisely to the selected timestamp.
PITR, snapshots, dumps, and replication: where the boundary lies
Once you understand how PITR works, it becomes easier to distinguish it from tools that are often lumped together in conversation under the single term “backups.” That oversimplification is risky during incident analysis. It may mean a backup snapshot, a logical dump, a replica, or PITR, even though these are different mechanisms.
Misclassifying them can be costly: the team expects a precise rollback but gets only the state from last night. Or it expects protection from an accidental DELETE, while the replica has already dutifully reproduced that deletion.
At first glance, all of these mechanisms look similar: each seems to help “restore data.” In practice, they answer different questions.
PITR
PITR restores the database to its state at a selected point in time within the available recovery window. It is used when you need to return to a safe point between regular checkpoints: before an accidental DELETE, a failed migration, a bulk data update, or an application bug.
Its main strength is point-in-time precision. However, PITR does not work retroactively: base backups, transaction logs, the retention period, and the recovery procedure must be configured in advance. The result is often a separate restored instance that still needs to be validated.
Snapshot restore
A snapshot restore returns a disk, instance, or database to the state it was in when the snapshot was taken. This is useful for quickly rolling back to a known good state before a release, before a migration, or after an infrastructure failure.
The limitation is that there is a “blind spot” between snapshots. If the snapshot was taken at night and the error occurred during the day, restoring from it may erase many valid transactions that were created after the snapshot.
Logical Dump
A logical dump, such as pg_dump, saves both structure and data: tables, schemas, and individual objects. It is useful for data migration, auditing, restoring individual tables, or preparing environments.
However, a dump captures the state at the time of export. By itself, it does not replay the transaction history up to an arbitrary point in time and can be slow for large databases.
Replication
Replication maintains an up-to-date copy of the data on another node. It is not used for rollback, but for availability: if the primary node becomes unavailable, the service can be quickly switched to the standby node without waiting for a restore from a dump.
However, replication does not copy only valid changes. A logical error, a dropped table, or large-scale data corruption can quickly reach the standby. As a result, replication carries the current state forward rather than returning the database to a safe point.
PITR does not replace snapshots, dumps, or replicas; it addresses a separate risk: recovery to a point between snapshots. Replication is used for availability, snapshots for reference states, dumps for logical exports, and PITR for precisely rolling back the database state to a point before a logical error.
For the business, this distinction eliminates a false sense of security. If a disaster recovery plan simply says “backups are enabled,” it is impossible to accurately assess RPO/RTO: how much data the company is prepared to lose and how quickly it can resume operations.
But understanding these differences does not by itself guarantee recovery readiness. Even if the team has selected PITR as the required mechanism, it will work only if the full chain is configured in advance: base backups, logs, retention period, permissions, quotas, and regular recovery testing.
What needs to be configured in advance
During an incident review, PITR often fails in places no one expected. Backups may appear to exist, but transaction logs were not being retained. The recovery window is shorter than the period during which the error may have started. The on-call engineer does not have permission to create a restored copy. The quota for new instances has been exhausted, and no one approved the budget for temporary storage.
At that point, the problem is no longer technical but operational: the data could have been recovered more precisely, but the readiness chain was incomplete.
PITR cannot be enabled in the middle of an incident like a standby generator. It works only if a base backup, continuous transaction logs, a clearly defined retention period, team permissions, and a runbook are already in place.
Base backups and logs
PITR requires a base backup as the starting point for recovery. However, a backup alone is not enough: WAL, archive logs, or other transaction logs must be saved continuously so the database can be restored to the selected point in time.
Before an incident occurs, you need to verify the backup schedule, the success of recent backups, coverage of the required databases, continuity of log archiving, and storage availability. If there is a gap anywhere in this chain, precise recovery to a timestamp may be impossible.
Recovery Window and Latest Available Recovery Point
Recovery is possible only within the available retention window. If the error began a week ago, but backups and logs are retained for three days, PITR can no longer restore the database to the state before the corruption began.
It is also important to know the latest available recovery point. It may lag behind the current time because of archiving delays. Therefore, the team needs to know where to check the latest restorable time and how much lag is acceptable for the business.
RPO, RTO, permissions, and resources
PITR must be aligned with business objectives. RPO defines the acceptable amount of data loss, while RTO defines the recovery time, including verification of the result. These values should be agreed in advance, not determined during an incident.
In addition to metrics, practical resources are required: access roles, cloud limits, available storage, quotas for new instances, and a process for approving temporary expenses. In the cloud, recovery often requires a separate copy or a new instance, rather than simply rolling back the current database in place.
Runbook and restore test
During an incident, you cannot rely on a single person’s memory. You need a step-by-step runbook: who starts the recovery, who selects the recovery point, who verifies the data, who decides whether to migrate or switch over, and where communication takes place.
A regular restore test validates not only the files, but also the team, access rights, recovery time, and whether the backup is usable. After the test, you should record whether the team met the RTO, which issues were found, and what needs to be changed in the procedure.
PITR readiness is not a checkbox saying “backups are enabled,” but a validated procedure. A test restore must prove that the team can actually obtain a working copy within the expected time: connect to it, verify data integrity, assess any discrepancies, and decide on the next steps.
After this kind of validation, PITR stops being a hope “just in case” and becomes a controlled process with clear data loss, timelines, and owners. But even with a properly assembled chain in the cloud, recovery often means not rolling back the current database in place, but creating a separate restored copy or instance—and that changes the team’s next steps.
Cloud-specific considerations: a restored copy instead of an in-place rollback
A managed cloud database does not always follow the “select 10:14, click restore, and the current database rolls back” scenario. PITR often results in a separate resource: a restored instance, a clone, or a copy of the database that the team still needs to work with.
This is an important decision point. For example, in one service, point-in-time recovery may create a new instance; in another, it may create a clone from an earlier state. The details depend on the provider, but the operational implication is similar: the cloud does not always overwrite the current production database; instead, it brings up the restored state alongside it.
This approach is safer than a direct rollback. The team can restore the state as of 10:14 into a separate clone, verify that the table deleted at 10:15 is actually present, compare rows, and assess which valid operations occurred after the target point. The production environment does not necessarily have to be touched immediately.
But this is where data recovery stops being the same as service recovery. After creating the copy, the team needs to decide:
Whether the selected point is actually safe;
Whether to move individual tables and rows or switch the application to the restored instance;
What access permissions, network rules, routes, and roles the new resource needs;
EU Cloud Infrastructure You Control
Run production workloads on dedicated resources across EU data centres. Transparent pricing, no hidden costs.
Full control over compute, storage, and networking.
What needs to change in connection strings, DNS, secrets, background jobs, and integrations;
How much the temporary instance, additional storage, and investigation will cost.
That is why cloud PITR should be planned not only as database recovery, but also as a procedure for returning the service to an operational state. The RTO will include not only the time required to create the copy, but also data validation, agreement on the source of truth, and either data migration or application cutover.
For the business, this means making a separate decision during the incident: what should be considered the correct state, and how to return it to the production environment safely. Cloud-specific behavior does not make PITR worse; it simply shows that after the restore, the team still has several important steps to complete.
When PITR Is Needed
When a server goes down, the problem is immediately visible: the service is unavailable, alerts are firing, and the team starts bringing the infrastructure back up. Logical corruption is harder to detect: the database continues to accept requests, replication reliably propagates changes, reports are generated, and users keep working—but the state is no longer trustworthy.
PITR is most often needed in exactly this situation: there is no hardware failure, but the data can no longer be trusted. The practical criterion is simple: you need to return the database to a consistent state from before the error began, not merely restore availability.
Common scenarios include:
An accidental DROP TABLE in production;
A bulk DELETE that affects the wrong rows;
A bulk UPDATE without the required condition;
A failed schema or data migration;
An application bug that wrote incorrect values for several hours;
A compromised account;
A large-scale operator error in the admin console.
In all of these cases, standard replication will not help: it can quickly propagate the error to the standby node. A snapshot from the previous night may also be too coarse: it will return the database to an older state and erase some valid operations performed during the day.
PITR is valuable not because it is “better than backups in general,” but because it sets a controlled limit on data loss. The team can tell the business: we are restoring the state from before the corruption began, and operations after that point will be reconciled, replayed, or compensated separately.
At the same time, PITR should not be treated as a universal rescue button. It is especially useful when you need to restore a trusted data state, but the scenario can still end badly if PITR was not enabled in advance, the retention window has already expired, or external systems have already accepted incorrect data as correct.
When PITR Won’t Help or Will Help Only Partially
PITR is useful only within infrastructure that has been prepared in advance. If base backups were not created, logs were not archived, and the retention window does not cover the time of the error, you can no longer restore to the required point. This is not a feature you can enable retroactively after a DROP TABLE.
It is best to check the main limitations in advance:
Limitation
What it means
PITR was not configured in advance
Without a base backup and continuous WAL/archive logs, the database has nothing to use to reconstruct its state at the required point in time
The retention window has already closed
If the error began a week ago and logs are retained for three days, an exact rollback is impossible
The latest available restore point is not suitable
The latest restorable time may lag behind the current time because of archiving delays
You need a single table, but the entire database is restored
Often, you have to bring up a separate copy, extract the required data, and carefully move it back
Incorrect data has propagated to external systems
PITR fixes the database, but it does not automatically roll back queues, caches, data marts, billing, or integrations
Valid operations occurred after the target point
Everything that happened after the selected time must be reconciled, replayed, moved manually, or treated as lost
These limitations do not make PITR a weak mechanism. They simply put it in its proper place: PITR addresses the risk of logical data corruption, but it does not replace auditing, change control, monitoring, restore testing, or a plan for handling external systems.
Even a successful restore to the correct point in time does not mean the business has already returned to normal. Once the restored copy is available, a separate effort begins: determining which data should be treated as the source of truth and how to safely return it to the production environment.
What Happens After Recovery
A restored database is the midpoint of the process, not the finish line. The team has obtained the state as of the selected point in time, but has not yet proven that it is suitable for use. The first step is to verify that the target point was chosen correctly: the deleted table is actually present, the corrupted rows have not yet been modified, the migration had not yet performed dangerous operations, and data integrity has not been compromised.
Next comes a decision point. Sometimes the restored instance becomes the new production source: the application is switched to it after validation and after updating network rules, secrets, DNS, or the connection string. In other cases, the production database is not replaced entirely; instead, specific tables, rows, or values are extracted from the restored copy and moved into the current environment.
Both options require discipline. The corrupted version must be shut down or isolated so the application does not continue writing to an invalid state. It is also important to account for background jobs, queues, caches, search indexes, analytical data marts, and integrations that may have preserved the effects of the error.
Operations after the target point are a separate task. If the database is restored to 10:14, all valid transactions after that time are missing from the restored copy. They need to be classified: what can be safely replayed, what should be transferred manually, what must be agreed with the business, and what can no longer be restored without risking a new inconsistency.
That is why a good PITR runbook ends with result validation: who confirmed the data, which environment is considered the source of truth, which external systems have been synchronized, which losses have been accepted, and what actions are needed to prevent the same error from happening again.
Practical PITR Readiness Checklist
After reviewing how PITR works and its limitations, it is worth distilling everything into a short checklist. Not for the sake of a polished document, but for a nighttime incident, when the team has no time to figure out who last checked the backups or whether a new instance can be created without quota approval.
Before relying on PITR, check the following:
Whether base backups are enabled for the required databases and environments;
Whether WAL/archive logs or other transaction logs are retained without gaps;
whether the retention window covers realistic scenarios where errors are discovered late;
whether the team knows where to find the latest restorable time and how much lag is acceptable;
whether RPO and RTO have been agreed with the business, not just with operations;
whether there are sufficient permissions, quotas, capacity, and budget for a restored copy or a separate instance;
whether the procedure for restoration, data validation, migration, or switchover is documented;
whether restore tests are performed regularly, with recovery time measured and any issues found recorded;
whether external systems have been accounted for: caches, queues, analytics, integrations, and background jobs.
This kind of checklist shows whether the team is ready not just to create a copy of the database, but to bring the service back to a working state. For PITR, files and logs are not the only things that matter; permissions, resources, instructions, data validation, and a decision on which environment should be treated as the source of truth are just as important.
Conclusion
PITR is useful when standard availability no longer solves the problem: the database is running, but its state can no longer be trusted. It helps roll data back to the last safe point before an error and reduces unnecessary loss of valid transactions compared with a coarse restore from a snapshot.
However, PITR only delivers value if it is in place before an incident. It requires base backups, continuous transaction logs, a sufficient retention window, permissions, resources, and regular restore testing. With these in place, an accidental DELETE, a failed migration, or data corruption turns from a chaotic investigation into a controlled procedure with clear risk boundaries.
FAQ
Does PITR guarantee recovery without data loss?
No. PITR reduces data loss, but it does not automatically make it zero. The actual RPO depends on the chosen recovery point, the log archiving delay, the latest available recovery point, and which legitimate operations occurred after the target time.
Can you restore only a single table with PITR?
PITR is typically used to restore an entire database, cluster, or instance to a selected point in time. If you need a single table or a subset of rows, the usual approach is to bring up a separate restored copy, verify it, and then extract the required data into the production database using a separate procedure.
How is PITR better than a regular snapshot restore?
A snapshot restores the database to the point in time when the snapshot was created. If the snapshot was taken at night and the error occurred during the day, many valid operations may be lost.
PITR uses a base backup and transaction logs, so it allows recovery to a more precise point within the available retention window.
Why Doesn’t Replication Replace PITR?
Replication improves availability when a node fails, but it typically propagates a logical error just as quickly as valid changes. If a table is dropped in the primary database or values are corrupted in bulk, the replica may receive the same corrupted state.
PITR is needed specifically to roll the database back to a point before such an error occurred.
What should you check before relying on PITR?
Make sure that base backups are enabled, WAL or other transaction logs are retained, the retention period covers real risks, the latest available recovery point is known, and there are sufficient permissions and quotas to create a restored instance.
It is also important to run restore tests regularly and verify not only the files, but the entire recovery procedure: selecting the recovery point, creating the copy, validating the data, and migrating or switching over.
Subscribe to our newsletter to get articles and news
Cookie consent
This site uses cookies to ensure it works properly and to track how you use it. By clicking 'Accept', you agree to these technologies. For more details, please see our Privacy Policy and Cookies Policy
Functional
Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
