Point-in-Time Recovery (PITR) in PostgreSQ
Point-in-Time Recovery (PITR) is a powerful feature in PostgreSQL that allows database administrators to restore a database to a specific moment in time—usually just before a failure, human error, or data corruption event. It’s an essential part of a solid disaster recovery plan.
PITR relies on two key elements:
-
Base Backup – A full copy of the database at a specific point.
-
WAL (Write-Ahead Logging) – All changes after the base backup are written to WAL files, which are replayed during recovery.
Advantages
-
Undoing accidental
DROP
orDELETE
operations. -
Recovering from logical corruption or bugs.
-
Setting up test environments by cloning data at a specific time.
Example
1. Enable WAL Archiving in postgresql.conf
Edit the following parameters:
wal_level = replica archive_mode = on archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
Restart PostgreSQL:
sudo systemctl restart postgresql
2. Take a Base Backup
Using pg_basebackup
:
pg_basebackup -D /var/lib/postgresql/base_backup -F tar -X fetch -P -U postgres
3. Simulate a Disaster
Assume the table was dropped and now you need recovery.
Stop PostgreSQL:
sudo systemctl stop postgresql
4. Restore from Backup
cd /var/lib/postgresql/data rm -rf * tar -xvf /var/lib/postgresql/base_backup/base.tar
5. Create a recovery.conf
or set recovery parameters
touch standby.signal
Edit postgresql.conf
or create postgresql.auto.conf
:
sudo systemctl start postgresql
PostgreSQL will apply WAL files and stop exactly at the given timestamp.
You can confirm recovery by checking the logs:
cat /var/log/postgresql/postgresql-13-main.log
At Learnomate Technologies, we provide the best training on PostgreSQL and other database technologies. For more insights, visit our YouTube channel. Don’t forget to check out our website for more details about our training programs. Follow my Medium account @ankush.thavali for more articles and updates.