In this article, we will see Point in Time Recovery(PITR) in PostgreSQL 12/13 versions step by step procedure.
What is Point in Time Recovery?
Assume that we have full(basebackup) backup at midnight at 12:00AM and at 7:20AM some server crash happened due to various reasons. In this scenario WAL(write-ahead-log(transaction log) comes into picture. To restore up to 7:20AM, first we need to restore full backup (backup taken by using pg_basebackup) and then we have to replay WAL files. This restore process is nothing but PITR(Point time recovery).
Environment details:
Linux server: Ubuntu
PostgreSQL server version: 12/13
Step1: Configure continuous archiving on PostgreSQL Cluster:
Configure archive_mode, archive_command and wal_level
a). Create archive directory first and change ownership to postgres.
mkdir /pg13_archive_`date +”%Y%m%d”`
chown postgres:postgres pg13_archive_20210324
b. Add archive_mode, archive_command and wal_level
archive_mode = on archive_command = 'test ! -f /pg13_archive_20210324/%f && cp %p / pg13_archive_20210324-17031616588235/%f' wal_level=replica
c. To effect these changes, we have to restart PostgreSQL Server.
sudo systemctl restart postgresql
d. Now goto pg_13_archive…. Directory and verify any wal files copied or not. If not generated, then run below command from OS(Linux) level.
sudo -u postgres psql -c "SELECT pg_switch_wal();"
After executing above command, there should be file created in pg_13_archive…. directory.
Step2: Perform basebackup(Full backup)
1. Create empty directory and grant permissions to postgres user as shown below.
mkdir /pg13_backup sudo chown postgres:postgres /pg13_backup
2.Take basebackup by using below command.
pg_basebackup -h localhost -U postgres -p 5432 -D /pg13_backup/r2schools`date +"%Y%m%d"`
Step3: Login PostgreSQL server and perform some action
1). Create table, insert data into that table and then
create table foo(c1 int, c2 timestamp default current_timestamp); CREATE TABLE insert into foo select generate_series(1, 1000000), clock_timestamp(); INSERT 0 1000000 select current_timestamp; current_timestamp ---------------------------------- 2021-03-24 16:16:14.063663+05:30 pitr=# select now(); now ---------------------------------- 2021-03-24 16:19:15.585974+05:30 (1 row) delete from foo; DELETE 1000000 select current_timestamp; current_timestamp ---------------------------------- 2021-03-24 16:19:46.755801+05:30
Step4: Performing Point-In-Time-Recovery on the Database Cluster
1) Shutdown the cluster
sudo systemctl stop postgresql
2) Once, PostgreSQL is down, we have to remove all files from PostgreSQL data (main) directory. As safe practice first, you need to move the pg_wal directory to a different place as this might contain unarchived WAL files that are important for recovery.
mkdir /pg13_wal_backup chown postgres:postgres /pg13_wal_backup sudo mv /var/lib/postgresql/13/main/pg_wal /pg13_wal_backup
3) Now remove PostgreSQL main or data directory.
sudo rm -rf /var/lib/postgresql/13/main
4) Then, create new directory /var/lib/postgresql/13/main
sudo mkdir /var/lib/postgresql/13/main
5) Copy basebackup directory files and folders to /var/lib/postgresql/13/main. Which we have taken in step 2.
sudo cp -a /pg13_backup /r2schools20210324/. /var/lib/postgresql/13/main/ sudo chown postgres:postgres /var/lib/postgresql/13/main sudo chmod 700 /var/lib/postgresql/13/main
6) Remove the pg_wal file in the /var/lib/postgresql/13/main directory as follows:
sudo rm -rf /var/lib/postgresql/13/main/pg_wal
7) Now copy the files from the pg_wal directory you saved before clearing out the data directory:
sudo cp -a /pg13_wal_backup/pg_wal /var/lib/postgresql/13/main/pg_wal
Step 5: Set the restore command, recovery targets and create recovery.signal.
restore_command = 'cp /pg13_archive_20210324-17031616588235/%f %p' recovery_target_time = '2021-03-24 16:18:11.319298+05:30'
sudo touch /var/lib/postgresql/13/main/recovery.signal
Step 6: Restart PostgreSQL using below command.
sudo systemctl start postgresql Step7: Check the table foo, that has been restored to the time '2021-03-24 16:18:11.319298+05:30'.
select count(*) from foo;
count
---------
1000000
Now remove both standby.signal and recovery.signal files from /var/lib/postgresql/13/main/.
If we don’t remove them, Server will continue in standby mode.