How to Configure PostgreSQL Point-In-Time Recovery (PITR) for Instant Data Restoration

In a database system, data is fundamental and its safety is paramount; backup and recovery become essential when data loss or corruption occurs.

PostgreSQL is a powerful open‑source object‑relational database that has earned a solid reputation for reliability, feature robustness, and performance over more than 30 years. Its Point‑In‑Time Recovery (PITR) feature allows instant restoration of data lost by user error once WAL archiving and a base backup are enabled.

This article demonstrates how to configure PITR in PostgreSQL and how to recover data immediately after accidental deletion.

Environment Preparation

Prepare Database

Set up a PostgreSQL 11 environment.

# echo "$PGHOME" to view
PGHOME=/usr/lib/postgresql/11

# echo "$PGDATA" to view the data directory
PGDATA=/data/pgsql/main

# Show configuration file location
PGCONFIG_FILE=/etc/postgresql/11/main/postgresql.conf

# PostgreSQL start‑log location
PG_LOG=/data/pglog/start.log

PITR Data Backup

When a user mistakenly deletes data, PITR can restore the database to a specific timestamp. The following steps enable backup and perform recovery.

Enable WAL Archiving

Modify the configuration file to turn on WAL archiving.

# Create directory for archived WAL files and set ownership
$ mkdir -p /data/wallog_back
$ chown -R postgres:postgres /data/wallog_back

# Edit postgresql.conf
$ vim /etc/postgresql/11/main/postgresql.conf

# Set the following parameters:
wal_level = archive
archive_mode = on
archive_command = 'test ! -f /data/wallog_back/%f && cp %p /data/wallog_back/%f'

# Restart PostgreSQL
# Switch to postgres user
$ su postgres
# Stop server
postgres:$ /usr/lib/postgresql/11/bin/pg_ctl stop
# Start server with custom config
postgres:$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

Configuration saved; WAL archiving is now active.

Prepare Data

# Create test table
CREATE TABLE test_table(
    ID INT NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL
);

# Insert 200,001 rows of test data
INSERT INTO test_table(id, name, age)
SELECT generate_series(0,200000),
       substr(md5(random()::text),0,25),
       generate_series(0,200000);

Perform Base Backup

Check current time to label the backup.

postgres=# SELECT now();
          now
-------------------------------
 2021-11-04 14:50:42.482253+08
(1 row)

Start backup

# Start backup
postgres=# SELECT pg_start_backup('backup_2021-11-04 14:50:42');
 pg_start_backup
-----------------
 0/9000060
(1 row)

# Create tar of the data directory
$ cd /data
$ sudo tar -cvzf pgsql.tar pgsql

# Stop backup
postgres=# SELECT pg_stop_backup();
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
 pg_stop_backup
----------------
 0/9000168
(1 row)

Force a WAL switch so the archive receives the latest segment.

# SELECT pg_switch_wal();
 pg_switch_wal
---------------
 0/A000078
(1 row)

Check archiver status; if archived_count is 0, the setup failed.

# SELECT * FROM pg_stat_archiver;

Note:

After backup, the /data/wallog_back/ directory should contain archived WAL files.

Run SELECT * FROM pg_stat_archiver;; if archived_count is 0, the archiving configuration failed and should be re‑checked or the service restarted.

PITR Data Recovery

Simulate Data Deletion

# Delete rows with id > 10000 to simulate accidental loss
DELETE FROM test_table WHERE id > 10000;
-- 190,000 rows deleted

# Record current time to determine recovery target
SELECT now();
          now
-------------------------------
 2021-11-04 14:56:17.452967+08
(1 row)

Instant Data Restoration

# Stop PostgreSQL
$ /usr/lib/postgresql/11/bin/pg_ctl stop

# Rename current data directory
$ cd /data
$ mv pgsql pgsql_back

# Restore backup archive
$ sudo tar -zxvf pgsql.tar

# Create or edit recovery.done (or recovery.conf) with the following settings
restore_command = 'cp /data/wallog_back/%f %p'
recovery_target_time = '2021-11-04 14:56:17.452967'
recovery_target_timeline = 'latest'

Restart Database

$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

Data Recovery Verification

# Verify restored data
SELECT * FROM test_table;
SELECT count(*) FROM test_table;
 count 
-------
200001
(1 row)

The table contains the original 200,001 rows, confirming successful recovery.

Conclusion

Once configured, PostgreSQL PITR enables point‑in‑time data restoration, allowing you to recover from accidental deletions effectively.

References

https://www.postgresql.org/

https://blog.csdn.net/arcticJian/article/details/102746287

https://my.oschina.net/Kenyon/blog/58112