POSTGRESQL DATABASE RECOVERY METHODS USING PGBACKREST
1. PostgreSQL Database Recovery Checklist
Follow sections based on environment as per the database restore strategy decision table in section
1.1 Restore from Weekly Full, Daily Differential and 2 Hours Incremental Backups:
1.1.1 Restore on the same server and same instance.
Step | Tasks |
1. | Find the list of backups and get the required details pgbackrest info |
2. | Stop the postgresql services by using below command: pg_ctl stop -D $PGDATA |
3. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> --delta –-log-level-console=info restore |
4. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
1.1.2 Restore on the same server in different location.
1. | Find the list of backups and get the required details pgbackrest info |
1. | Create the empty data directory . Mkdir -p < New Data dir location> |
2. | Create the different data directories for tablespaces. Mkdir -p “<tablespace new location >“ |
3. | Change the pgbackrest.conf file with New data directory location and comment the existing location. Sample conf file : cat /etc/pgbackrest.conf [online_backups] db-path=< new data dir location> #db-path=/u02/pgdata01/9.5/data backup-user=postgres db-socket-path=/tmp [global] retention-full=3 repo-path=/pgback start-fast=y stop-auto=y |
4. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> –-log-level-console=info --tablespace-map-all= <new tablespace location> restore |
5. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
1.1.3 Restore on the same server in different location
Tasks | |
1. | Find the list of backups and get the required details on source server pgbackrest info |
1. | Copy the required backups into pgdbwork DML share so that it is available for other servers. a. Copy the backup and archive directories from “/pgback” to /pgdbawork area. Mkdir -p /pgdbawork/adhoc_restore cp -r /pgback/backup /pgdbawork/adhoc_restore/ cp -r /pgback/archive /pgdbawork/adhoc_restore / |
2. | Connect other server and copy the adhoc_restore directory from pgdbawork into /pgback location. Cp -r /pgdbawork/adhoc_restore /pgback |
3. | Create the empty data directory on other server. mkdir -p < New Data dir location> |
4. | Create the different data directory for tablespaces. Mkdir -p “<tablespace new location >“ |
5. | Change the pgbackrest.conf file with New data directory location and comment the existing location. Sample conf file : cat /etc/pgbackrest.conf [online_backups] db-path=< different dara dir> #db-path=/u02/pgdata01/9.5/data backup-user=postgres db-socket-path=/tmp [global] retention-full=3 repo-path=/pgback/adhoc_restore start-fast=y stop-auto=y |
6. | Recover the database by using below restore query: pgbackrest --stanza=online_backups --tablespace-map-all= <new tablespace location> restore |
7. | Start the postgresql services by using below command: pg_ctl start -D <new PGDATA > start |
3.2 Restore Particular database from Online backup.
3.2.1 Restore on same server
Note : This is not applicable if we have assigned database in different tablespace location.
Step | Tasks |
1. | Locate the path of database backup files. pgbackrest info |
2. | Stop the postgresql services by using below command: pg_ctl stop -D $PGDATA |
3. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> --delta –-log-level-console=info –-db-include=<database_name> restore |
4. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
3.2.2 Restore on same server in different location.
1. | Find the list of backups and get the required details. pgbackrest info |
2. | create the empty data directory. Mkdir -p < New Data dir location> |
3. | Change the pgbackrest.conf file with New data directory location and comment the existing location. Sample conf file : cat /etc/pgbackrest.conf [online_backups] db-path=< different data dir> #db-path=/u02/pgdata01/9.5/data backup-user=postgres db-socket-path=/tmp [global] retention-full=3 repo-path=/pgback start-fast=y stop-auto=y |
4. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> --delta –-log-level-console=info –-db-include=<database_name> restore |
5. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
2.2 Point in Recovery (PITR)
2.2.1 PITR on the same server and same instance.
Step | Tasks |
1. | Locate the path of database backup files. pgbackrest info |
2. | Stop the postgresql services by using below command: pg_ctl stop -D $PGDATA |
3. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> --delta –-log-level-console=info --type=time "--target= <yyyy-mm-dd hh:mm:ss" --set= <backup file > restore |
4. | Check the restore_command and recovery_target_time matches with our values cat /u02/pgdata01/9.5/data/recovery.conf |
5. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
2.2.2 PITR on the same server on different location.
Step | Tasks |
1. | Find the list of backups and get the required details pgbackrest info |
2. | Create the empty data directory. Mkdir -p < New Data dir location> |
3. | Create the different data directories for tablespaces. Mkdir -p “<tablespace new location >“ |
4. | Change the pgbackrest.conf file with New data directory location and comment the existing location. Sample conf file : cat /etc/pgbackrest.conf [online_backups] db-path=< different data dir> #db-path=/u02/pgdata01/9.5/data backup-user=postgres db-socket-path=/tmp [global] retention-full=3 repo-path=/pgback start-fast=y stop-auto=y |
5. | Recover the database by using below restore query: pgbackrest –-stanza=<stanza-name> –-log-level-console=info --type=time "--target= <yyyy-mm-dd hh:mm:ss" --set= <backup file > --tablespace-map-all= <new tablespace location> restore |
6. | Start the postgresql services by using below command: pg_ctl start -D $PGDATA |
2.2.2 PITR on the different server on different location.
Step | Tasks | |
1. | Find the list of backups and get the required details on source server pgbackrest info | |
2. | Copy the required backups into pgdbwork DML share so that it is available for other servers. copy the backup and archive directories from “/pgback” to /pgdbawork area. mkdir -p /pgdbawork/adhoc_restore cp -r /pgback/backup /pgdbawork/adhoc_restore/ cp -r /pgback/archive /pgdbawork/adhoc_restore / | |
3. | Connect Other servers and copy the adhoc_restore directory from pgdbawork into /pgback location. Cp -r /pgdbawork/adhoc_restore /pgback | |
4. | Create the empty data directory on other server. mkdir -p < New Data dir location> | |
5. | Create the different data directory for tablespaces. mkdir -p “<tablespace new location >“ | |
6. | Change the pgbackrest.conf file with New data directory location and comment the existing location. Sample conf file : cat /etc/pgbackrest.conf [online_backups] db-path=< different data dir> #db-path=/u02/pgdata01/9.5/data backup-user=postgres db-socket-path=/tmp [global] retention-full=3 repo-path=/pgback/adhoc_restore start-fast=y stop-auto=y | |
7. | Recover the database by using below restore query: pgbackrest --stanza=online_backups --type=time "--target= <yyyy-mm-dd hh:mm:ss" --set= <backup file > --tablespace-map-all= <new tablespace location> restore | |
8. | Start the postgresql services by using below command: pg_ctl start -D <new PGDATA start > | |