Steps to perform recovery after restore operation
The procedure to perform post-recovery is as follows for various platforms:
For Windows (VSS):
- Go to Control Panel > System and Security > Administrative Tools > Services.
- Select PostgreSQL service and stop it.
- Delete or move everything from the PostgreSQL data directory.
Note:
Post restores, change the attributes of the restored data directory and files by using the following command:
attrib -S restore_path/*.* /S /D
- Copy all the contents of the restored data directory to PostgreSQL data directory.
- Edit the
postgresql.conffile from the PostgreSQL data directory and edit therestore_commandparameter as restore_command = 'copy "restored_WAL_directory\\%f" "%p"'.For precise point-in-time recovery, specify the timestamp up to which recovery is performed. Update the
recovery_target_timeparameter as recovery_target_time = 'yyyy-mm-dd hh:mm:ss' - Create an empty file in the data directory and name it
recovery.signal. - Start PostgreSQL service.
For Linux (LVM):
- Stop PostgreSQL services.
- Delete or move everything from the PostgreSQL data directory.
- Extract and copy the data directory and WAL directory contents to respective location.
- Create an empty file with name
recovery.signalin data directory. - Edit the
postgresql.conffile from the PostgreSQL data directory and edit the restore_command parameter as restore_command = 'copy "restored_WAL_directory\\%f" "%p"'.For precise point-in-time recovery, specify the timestamp up to which recovery is performed. Update the recovery_target_time parameter as recovery_target_time = 'yyyy-mm-dd hh:mm:ss'
- Change ownership of PostgreSQL data directory and permission to 700.
For example:
chown -R postgres:postgres /full/path/of/PostgreSQL/Data/Dir
chmod - R 700 /full/path/of/PostgreSQL/Data/Dir
- Start PostgreSQL service.
Recovery steps for backup done by pg_basebackup utility
- Stop PostgreSQL services.
- Delete or move everything from the PostgreSQL data directory.
- Extract and copy the data directory and WAL directory contents to respective location.
- Create an empty file with name
recovery.signalin data directory. - Edit the
postgresql.conffile from the PostgreSQL data directory and edit therestore_commandparameter as restore_command = 'copy "restored_WAL_directory\\%f" "%p"'.For precise point-in-time recovery, specify the timestamp up to which recovery is performed. Update the
recovery_target_timeparameter as recovery_target_time = 'yyyy-mm-dd hh:mm:ss' - (For Windows) Provide access to data directory for network service.
- (For Linux) Change ownership of PostgreSQL data directory and permission to 700.
For example:
chown -R postgres:postgres /full/path/of/PostgreSQL/Data/Dir chmod - R 700 /full/path/of/PostgreSQL/Data/Dir
- Start PostgreSQL service.
Note:
Remove the restore data from the restored path
/full/path/of/restore/directoryafter successful recovery, else the next backup job may fail.
pg_dumpall utilityFor Windows: psql.exe -h localhost -p port_num -U username -f full\path\to\dumpall\file\filename.out
For Linux: psql -h localhost -p port_num -U username -f full/path/to/dumpall/file/filename.out
pgdump utilityFor Windows: pg_restore -U username -d dbname full\path\to\dump\file\filename.dump
For Linux: pg_restore -U username -d dbname full/path/of/dump/file/filename.dump