Point In Time Recovery for table - PostgreSQL

Point In Time Recovery - PostgreSQL

Point-in-time Recovery (Incremental Backup) in PostgreSQL server.
Backup steps:

• Modify postgresql.conf to support archive log
• Make a base backup (full database backup)
• Backup base backup to remote storage.
• Backup WAL (archive log files) to remote storage (continuous process) 

Point-in-time Recovery Steps:

• Extract files from base backup
• Copy files from pg_xlog folder
• Create recovery.conf file
• Start Recover

Let's us discuss below scenario, how we can perform PITR ?

Basebackup -            6 am

Dropped table -         9 am

Realized drop table - 10 am

Perform table PITR ?

Note:- PITR is possible only if the required WAL files exists either pg_wal directory OR archive_directory 

How to switch log file ?

postgres=# select pg_switch_wal();

Basebackup is important for PITR:-

$] pg_basebackup -D /var/lib/pgsql/backup -X fetch -P -p5433 ------> Backup taken

After backup taken we are going to create one new table

Note:- In our pg_basebackup ---> Only test.emp table was backed up.

and test.emp2 table's backup is not there ....

We have both the tables at this mentioned time.

How to check time?

postgres=# select now();

Let us drop the emp2 table 

Go to pg_log directory :- and check the time of table dropped

=============================================

From here, Actual Table PITR steps starts

=============================================

1) Go to your backup folder where you have taken backup :- pg_basebackup

/var/lib/pg_log/backup

2) Copy WAL files from Old "pg_wal" to archive directory  :-

$] cd /var/lib/pgsql/11/pg_wal ---> current running instance

From old pg_wal – copying files to /var/lib/pgsql/archive/

Why we are copying ? Because it should not miss the PITR 

3) Go to Backup Directory and create "recovery.conf" file  (Consider creating New instance):-

$] cd /var/lib/pg_log/backup

in recovery.conf file add below commands

restore_command='cp /var/lib/pgsql/archive/%f %p' --> this is your current instance location where table was dropped

recovery_target_time='2019-06-19 06:21:11.913543-07'

/var/lib/pg_log/backup – backup directory

4) We are starting postgre server from backup location:-

Before starting give the permission 0700 to /var/lib/pg_log/backup

and change the recovery.conf file for other parameters which are related to LOG

 Also change the port number in recovery.conf file 

Now server is start from backup :_

Now go to log directory

You can see in log file that it is saying to execute pg_wal_replay_resume();

Once above statement is executed, it will proceed with recovery.

you can check the log file 

Once recovery is done, recovery.conf file name is changed to recovery.done

5) Validation :- connect to Newly connect instance and check the tables

You can see that drop table emp2 is present here.

Even though we don't have backup of emp2 table, we have recovered emp2 table by using 'basebackup + archive files'

6)  You have to take the backup using pg_dump from newly created server and restore it to old server 

Backup from New server

$] pg_dump -t test.emp2 -d raj -p5434>tablebackup.sql

restore to Old server:-

$] psql -p5433 -d raj -f tablebackup.sql

 We are done with PITR ...... 😉