Point-in-Time Recovery - PITR

PITR

• Point-in-time recovery (PITR) is the ability to restore a database cluster up to the present or to a specified point of time in the past

• Uses a full database cluster backup and the write-ahead logs found in the /pg_xlog subdirectory 

• Must be configured before it is needed (write-ahead log archiving must be enabled)

Step 1 

• Edit the "postgresql.conf" file and set the “archive_command” parameter

• Unix:
  archive_command= ‘cp –i %p /mnt/server/archivedir/%f

• Windows:
  archive_command= 'copy "%p" c:\\mnt\\server\\archivedir\\"%f"
  
  %p is absolute path of WAL otherwise you can define the path 
  %f is a unique file name which will be created on above path.

Step 2

• Make a base backup

• Connect using edb-psql and issue the command: 

• SELECT pg_start_backup(‘any useful label’);

• Use a standard file system backup utility to back up the /data subdirectory 

• Connect using edb-psql and issue the command:

• SELECT pg_stop_backup();

• Continuously archive the WAL segment files 

Final Step: 

• Recovering the database

• Clean out all existing files in the /data directory and subdirectories (be sure to backup configuration files if you have not already done so)

• Restore the database files from the backup dump

• Copy any unarchived WAL files into the /pg_xlog directory

• Create a recovery.conf file in the /data directory

• Restart the database server

Settings in the recovery.conf file:

• restore_command(string)

       Unix:
       restore_command = 'cp /mnt/server/archivedir/%f "%p"‘

     Windows:
      restore_command = 'copy c:\\mnt\\server\\archivedir\\"%f" "%p"'

      recovery_target_time(timestamp)

      recovery_target_xid(string)

      recovery_target_inclusive(boolean)