Home PostgreSQL PostreSQL 13 — Point-In-Time Recovery

PostreSQL 13 — Point-In-Time Recovery


In one of my previous post ,I have discussed about Postgres 13 Continuous Archiving Complete setup where I said, PostgreSQL maintains Write Ahead Log(WAL) files in ‘pg_wal/’ subdirectory in PostgreSQL 13 version and ‘pg_xlog/’ subdirectory on prior versions. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state or to a particular point in time. 


Steps 1:

Set up continuous archiving . Please check the above link for the setup.

Step 2: 

Make sure to have full database backup which is taken using “pg_basebackup” utility. You can recover or replay the WAL logs on top of full backup to a certain point in time. For demonstration purpose I will take three pg_basebackup. I will make some change in the database after each backup.

Backup1: taken at 18:18

pg_basebackup --host= --port=5432 --username=postgres --progress --pgdata=/opt/PosgreSQL/13/pgbasebackup/bck1

Created one table named “after_backup1” and inserted 3 records.

Backup2: taken at 18:23

pg_basebackup --host= --port=5432 --username=postgres --progress --pgdata=/opt/PosgreSQL/13/pgbasebackup/bck2

Created another table named “after_backup2” and inserted 3 records.

Backup3: taken at 18:27

pg_basebackup --host= --port=5432 --username=postgres --progress --pgdata=/opt/PosgreSQL/13/pgbasebackup/bck3

Created another table named “after_backup3” and inserted 3 records.

Step 3:

Suppose I want to bring back my database at 18:00 hours. Is it possible ? Answer is No, because there is no full_backup before 18:00 hours. We have taken our first full backup at 18:18 hours. Hence we can bring database after 18:18 hours.

We will test 2 cases,

Case I: Bring back database at 18:21 hours.

Content of “archivedir” folder.

  1. Cleanup any obsolete archivelogs created before first backup using following commands.
/usr/pgsql-13/bin/pg_archivecleanup -d "/opt/PosgreSQL/13/archivedir" 0000000100000000000000D6.00000028.backup

2. Shutdown postgres service and rename data directory and keep it for backup purpose.

/usr/pgsql-13/bin/pg_ctl stop -D /opt/PosgreSQL/13/data

mv /opt/PosgreSQL/13/data /opt/PosgreSQL/13/data_160120201

3. Create an empty “data” folder and copy contents of “bck1” to data folder and remove all contents of “pg_wal/” folder. Change data directory permission to “0700”

mkdir /opt/PosgreSQL/13/data

cp -r /opt/PosgreSQL/13/pgbasebackup/bck1/* /opt/PosgreSQL/13/data

rm /opt/PosgreSQL/13/data/pg_wal/*

chmod 0700 /opt/PosgreSQL/13/data

4. Change following parameters in postgresql.conf file. This is applicable for Postgres 13 . For earlier version add following parameters in recovery.conf file.

restore_command = 'cp /opt/PosgreSQL/13/archivedir/%f %p'
recovery_target_time = '2021-01-17 18:21:00.417587+05:30'

5. Touch recovery.signal file.

touch /opt/PosgreSQL/13/data/recovery.signal

6. Start the instance.

/usr/pgsql-13/bin/pg_ctl start -D /opt/PosgreSQL/13/data

7. Check  whether table after_backup1 and after_backup2 exists or not.

   You can see after_backup1 table exists but after_backup2 does not exists as it was not created at that time.

Case II: Bring back database at 19:00 hours.

All the steps will be same as Case I except we will copy backup from bck3 and change the target time.

We can clearly see above that database is recovered till 19:00 hours where all three tables exist with data.

Please let me know if anyone need any further assistance on PITR in any Postgres Version.