Table of contents of the article:
Introduction
When it comes to database backups, the most common mistake is to think that the problem is simply "having a copy." In reality, in a production environment, the real issue isn't just backups, but being able to restore data at the exact moment it's needed. A nightly backup may be sufficient for trivial scenarios, but it becomes insufficient when the incident occurs in the middle of the day: a table accidentally deleted, a botched application migration, a mass update performed without a specific clause. WHERE, an accidental deletion by a management system or an application bug that corrupts data already present.
In these cases the PITR, Short for Point-in-Time Recovery. With PostgreSQL, PITR is an extremely robust, native and mature feature, based on a very precise concept: restoring a physical backup base of the cluster and then reapply the files WAL, Write-Ahead Log, up to the desired point in time. The result is a database restored to the consistent state it had at a specific point in time, for example, a few seconds before a human error.
Compared to a simple pg_dumpPITR works at a different level. It doesn't involve the logical export of a single table or database, but the physical recovery of the entire PostgreSQL cluster. This is important: PITR isn't designed to selectively recover a single table within a database while leaving everything else unchanged. Instead, it's a disaster recovery procedure that allows you to rebuild the entire PostgreSQL environment up to a specific point in time.
How PITR works in PostgreSQL
PostgreSQL, like many modern relational databases, uses a logging system called Write-Ahead LoggingBefore a change is considered permanently persistent in the data files, PostgreSQL writes the necessary information to the WALs. This mechanism is used to ensure consistency, crash recovery, physical replication, and, of course, point-in-time recovery.
WALs are an ordered sequence of segments that describe changes that have occurred in the cluster: transactions, inserts, updates, deletes, index changes, and other internal operations. If we have a physical backup taken at a certain point in time and have all the WALs produced since then, PostgreSQL can reproduce those changes until the desired state is reached.
The logical scheme is simple:
Base backup fisico + WAL archiviati = possibilità di recovery fino a un punto nel tempo
The base backup represents the starting point. WALs represent the next history. The recovery target tells PostgreSQL where to stop: a specific date and time, a transaction ID, an LSN, or a named restore point.
This architecture makes PostgreSQL particularly suitable for environments where the Recovery Point Objective (RPO) must be low. If WALs are archived correctly and consistently, data loss can be reduced to a few seconds, or in some cases, almost zero, compared to the time of the last available WAL.
Logical Backup and PITR: Why pg_dump Isn't Enough
pg_dump e pg_dumpall These are essential tools, but they're not the right tool for implementing a PITR. They produce logical backups, i.e., an SQL representation of objects and data. They're very useful for migrations, selective exports, single database restores, or cross-version compatibility, but they don't contain the physical information needed to reapply a WAL sequence.
The PITR instead requires a physical backup of the clusterThis can be achieved with pg_basebackup, with specialized tools such as pgBackRest or Barman, or with filesystem snapshot procedures properly integrated with PostgreSQL. In this article we will use a native approach based on pg_basebackup and WAL archiving, because it allows for a clear understanding of how the mechanism actually works.
Required components
To run a functioning PITR you need four elements:
1. A valid backup base, taken before the moment to which we wish to return.
2. Continuous archiving of WALs, configured before the accident. If the WALs have not been saved, changes made after the backup cannot be reconstructed.
3. A WAL restore command, that is, the parameter restore_command, which tells PostgreSQL where to retrieve the archived segments during recovery.
4. A recovery target, for example recovery_target_time, which defines the exact point at which to stop the replay.
It's worth reiterating: PITR can't be improvised after the disaster. It must be designed in advance, periodically tested, and integrated into a coherent backup strategy. Having a backup without archived WALs means you can only go back to the time of the backup. Having a WAL without a valid backup base means you don't have a consistent starting point.
Enable WAL archiving
The first step is to configure PostgreSQL to copy each completed WAL segment to a secure archive directory. In a real-world environment, this directory should be on separate storage, preferably remote or replicated. For this example, we'll use a local directory.
sudo mkdir -p /backup/postgresql/wal_archive sudo chown postgres:postgres /backup/postgresql/wal_archive sudo chmod 700 /backup/postgresql/wal_archive
In the file postgresql.conf Let's configure the essential parameters:
wal_level = replica archive_mode = on archive_command = 'test ! -f /backup/postgresql/wal_archive/%f && cp %p /backup/postgresql/wal_archive/%f' archive_timeout = 300 max_wal_senders = 10
The parameter archive_mode = on Enables WAL archiving. The parameter archive_command It is executed by PostgreSQL when a WAL segment is ready to be archived. The variables %p e %f represent respectively the path of the original WAL file and the name of the file to be saved in the archive.
The example command uses test ! -f to avoid overwriting an existing file. This is important: a WAL storage system must be conservative. If the command fails, PostgreSQL will continue to retry, avoiding losing the segment. In production, you can use more sophisticated commands, such as rsync, scp, rclone, aws cli, or dedicated tools like pgBackRest and Barman.
After the change you need to restart PostgreSQL, because archive_mode requires restart:
sudo systemctl restart postgresql
You can force a WAL switch and verify that storage is working:
SELECT pg_switch_wal();
ls -lh /backup/postgresql/wal_archive/
If files with names similar to appear in the directory 00000001000000000000000A, storage is working.
Create a user for pg_basebackup
pg_basebackup It connects to PostgreSQL via the replication protocol. Therefore, it is good practice to create a dedicated user with privileges. REPLICATION:
CREATE ROLE backup_user WITH LOGIN REPLICATION PASSWORD 'PASSWORD_FORTE';
In the file pg_hba.conf Replication connections must be allowed, adapting addresses and security policies to your environment:
host replication backup_user 127.0.0.1/32 scram-sha-256
After the edit:
sudo systemctl reload postgresql
Perform a basic physical backup
Now we can perform a basic backup. Suppose we want to save the backup in /backup/postgresql/base_2026-06-10:
sudo mkdir -p /backup/postgresql/base_2026-06-10 sudo chown postgres:postgres /backup/postgresql/base_2026-06-10 sudo -u postgres pg_basebackup \ -h 127.0.0.1 \ -U backup_user \ -D /backup/postgresql/base_2026-06-10 \ -Fp \ -Xs \ -P
The option -D indicates the destination directory. -Fp produces a plain format backup, that is, a physical copy of the data directory. -Xs includes streaming of the necessary WALs during backup. -P shows the progress of the operation.
This backup is not an SQL dump: it's a physical copy of the PostgreSQL cluster. It includes databases, system catalogs, indexes, tables, configurations in the data directory, and the files needed to restart. For this reason, the restore occurs at the cluster level, not at the individual database level.
Example scenario: accidental deletion
Let's imagine this scenario. At 02:00 AM, a base backup is performed. During the morning, the database operates normally and the WALs are archived. At 14:36:12 PM, an operator accidentally executes a malicious query:
DELETE FROM ordini;
The goal is to restore PostgreSQL to its immediately previous state, for example to 2026-06-10 14:36:00+02If we have the base backup at 02:00 AM and all the WALs generated up to that time, we can perform a PITR.
The safest procedure is to perform the restore on a separate server, validate the recovered data, and only then decide whether to replace the primary server, export the corrected tables, or proceed with a reversion strategy. Performing PITR directly on the production server is possible, but it increases operational risk if you don't have a clear plan.
PITR restore procedure
First, stop PostgreSQL on the restore server or the affected server:
sudo systemctl stop postgresql
The path to the data directory varies depending on the distribution. On Debian and Ubuntu systems it is often similar to /var/lib/postgresql/16/main; on RHEL, AlmaLinux and Rocky Linux systems it is often similar to /var/lib/pgsql/16/dataIn this example, we'll use a variable to make the commands more readable:
export PGDATA=/var/lib/pgsql/16/data
Let's secure the current data directory:
sudo mv $PGDATA ${PGDATA}.broken.$(date +%F-%H%M%S)
sudo mkdir -p $PGDATA
sudo chown postgres:postgres $PGDATA
sudo chmod 700 $PGDATA
Let's restore the base backup:
sudo -u postgres rsync -aH --numeric-ids \ /backup/postgresql/base_2026-06-10/ \ $PGDATA/
Now we need to tell PostgreSQL to boot into recovery mode. This is no longer used in version 12 and later. recovery.conf; instead, an empty file called recovery.signal in the data directory and configure the recovery parameters in postgresql.conf o postgresql.auto.conf.
sudo -u postgres touch $PGDATA/recovery.signal
Let's add or modify these parameters:
restore_command = 'cp /backup/postgresql/wal_archive/%f %p' recovery_target_time = '2026-06-10 14:36:00+02' recovery_target_inclusive = false recovery_target_action = 'pause'
restore_command indicates how to recover each archived WAL. recovery_target_time this is the moment we want to reach. recovery_target_inclusive = false asks PostgreSQL to stop before the target when applicable. recovery_target_action = 'pause' This is a prudent choice: when the target is reached, PostgreSQL stops in recovery and allows you to inspect the data before the final promotion.
Let's start the service:
sudo systemctl start postgresql
During startup, PostgreSQL will read the backup, use the backup_label to locate the starting point of the replay, it will call the restore_command to retrieve archived WALs and will apply the changes up to the specified time target.
Check the result
If we have chosen recovery_target_action = 'pause', the database may be read-only while still in the recovery state. We can check the status with:
SELECT pg_is_in_recovery();
We can then check whether the data has returned to the desired state:
SELECT count(*) FROM ordini; SELECT max(updated_at) FROM ordini;
If the content is correct, we can complete the recovery and promote the instance:
SELECT pg_wal_replay_resume();
Alternatively, if you prefer to automatically promote your server when the target is reached, you can use:
recovery_target_action = 'promote'
In mission-critical environments, however, pausing is often preferable because it allows for manual verification before making the recovered cluster writable.
Named restore points
PostgreSQL also allows you to create named restore points. This is very useful before risky operations, such as application upgrades, mass migrations, complex deployments, or manual data manipulation.
SELECT pg_create_restore_point('prima_migrazione_ordini');
During the recovery phase you can then use:
restore_command = 'cp /backup/postgresql/wal_archive/%f %p' recovery_target_name = 'prima_migrazione_ordini' recovery_target_action = 'pause'
This approach is more elegant than just using a timestamp when the critical point is known in advance. The restore point is recorded in the WALs, so it still requires that the WAL archive be properly active.
Operational attentions
PostgreSQL's PITR is powerful, but it requires discipline. The first rule is to periodically test the restore. An untested backup is just a hope. You need to verify that the base backups are readable, that the WALs are complete, that the restore_command functions and that recovery times are compatible with business objectives.
The second rule is to protect the WAL archive. If the WALs are stored on the same disk as the database, a storage failure can compromise both the primary data and the possibility of recovery. In production, it is preferable to store them on remote storage, a dedicated repository, object storage, or a separate backup server.
The third rule is to properly manage timelines. After a PITR and a promotion, PostgreSQL creates a new timeline. This means that the cluster's history forks: from that point on, a new WAL sequence exists, derived from the recovery point. This is normal behavior, but it must be understood when managing replicas, additional recoveries, or shared WAL archives.
The fourth rule is to remove or comment out recovery parameters after the restore, when they are no longer needed. From version 12 onwards, the file recovery.signal It's managed by the recovery process, but the parameters entered in the configuration files can remain. Leaving them active or forgotten can cause confusion in subsequent replication or restore operations.
Advanced Tools: pgBackRest and Barman
The procedure described in this article uses native tools to demonstrate how PITR actually works. However, in production, it's often advisable to use specialized tools. pgBackRest e Bartender They allow you to manage full, differential, incremental, retention, compression, verification, WAL archiving, remote repositories and point-in-time restore backups with a much higher level of automation.
These tools don't change the basic principle: PITR is still based on backups and WAL replay. However, they make daily operations more robust and repeatable, reducing the risk of human error. For companies managing PostgreSQL in production, e-commerce, management, CRM, or mission-critical platforms, relying on a structured solution is almost always preferable to unmonitored, hand-crafted scripts.
Conclusion
Point-in-time recovery in PostgreSQL is one of the most important features for ensuring business continuity and true data protection. Unlike a simple logical dump, it allows you to reconstruct the entire cluster back to a specific point in time, leveraging a combination of physical backup base and continuous WAL archiving.
The procedure can be summarized in a few steps: enable WAL archiving, perform regular consistent backups, store WALs safely, restore the backup when needed, create recovery.signal, configure restore_command and set the desired recovery target. From that point, PostgreSQL rereads its transaction history and stops at the set point.
The value of PITR isn't measured only at the time of disaster, but in the preparation beforehand. Without archived WALs, restore tests, and documented procedures, PITR remains a theory. With proper implementation, however, it becomes one of the most effective tools for reducing data loss and methodically addressing accidental deletions, application bugs, incorrect migrations, and operational incidents.
For those managing PostgreSQL in production, the message is simple: it's not enough to ask whether a backup exists. You need to ask yourself how far back you can truly go, in what time frame, and with what level of certainty. PITR serves precisely this purpose: transforming backup from a simple passive copy to a true controlled recovery strategy.
