PostgreSQL PITR backup restoration » History » Version 16
« Previous -
Version 16/52
(diff) -
Next » -
Current version
Amit Belani, 08/22/2015 07:51 AM
PostgreSQL PITR backup restoration¶
- Table of contents
- PostgreSQL PITR backup restoration
This article uses the drivestat database cluster hosted on dmsen03 as an example.
Restore a base backup¶
Identify¶
Identify the most recent base backup for the database cluster.
If the RAID on the host serving the database is available, identify the recent base backups on it.
[enstore@dmsen03 ~]$ enstore config --show drivestat_server PITR_area /srv3/enstore/pg_pitr [enstore@dmsen03 ~]$ ls -lh $(enstore config --show drivestat_server PITR_area)/pg_base_backup/drivestat | tail -2 -rw-r--r-- 1 enstore enstore 2.4M Aug 20 04:30 2015-08-20_04-30-02.tar.xz -rw-r--r-- 1 enstore enstore 2.4M Aug 21 04:30 2015-08-21_04-30-03.tar.xz
As an alternative, if the RAID on the remote backup host is available, identify the recent base backups on it.
[enstore@dmsen03 ~]$ enstore config --show crons backup_dir /srv3/enstore/backups [enstore@dmsen03 ~]$ ls -lh $(enstore config --show crons backup_dir)/pg_base_backup/drivestat | tail -2 -rw-r--r-- 1 enstore enstore 2.4M Aug 20 04:30 2015-08-20_04-30-02.tar.xz -rw-r--r-- 1 enstore enstore 2.4M Aug 21 04:30 2015-08-21_04-30-03.tar.xz
As a final alternative, if base backups are available on tape only, identify and copy the most recent base backup from tape to disk.
[enstore@dmsen03 ~]$ ls -lhR /pnfs/eagle/dmsen-backups/$(date +%Y/%m/%d)/*/*pg_base_backup-drivestat* -rw-r--r-- 1 enstore enstore 2.4M Aug 21 08:02 /pnfs/eagle/dmsen-backups/2015/08/21/07/srv3-enstore-backups-pg_base_backup-drivestat-2015-08-21_04-30-03.tar.xz [enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST_REAL=$ENSTORE_CONFIG_HOST [enstore@dmsen03 ~]$ enstore config --show crons backup2tape_config_host conf-stken.fnal.gov [enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$(enstore config --show crons backup2tape_config_host) [enstore@dmsen03 ~]$ encp /pnfs/eagle/dmsen-backups/2015/08/21/07/srv3-enstore-backups-pg_base_backup-drivestat-2015-08-21_04-30-03.tar.xz /tmp/
Extract¶
Extract the identified base backup into an appropriate empty directory on a node where the database is to be hosted.
[enstore@dmsen03 tmp]$ mkdir /tmp/pitr_restore_test1 [enstore@dmsen03 tmp]$ cd /tmp/pitr_restore_test1/ [enstore@dmsen03 pitr_restore_test1]$ ls backup_label global pg_hba.conf.old pg_log pg_serial pg_stat_tmp pg_twophase postgresql.conf base pg_clog pg_hba.conf.original pg_multixact pg_snapshots pg_subtrans PG_VERSION postgresql.conf.old conf.d pg_hba.conf pg_ident.conf pg_notify pg_stat pg_tblspc pg_xlog
Restore transaction logs¶
Identify¶
Identify the location of the transaction log (xlog) archive for the database cluster.
If the RAID on the host serving the database is available, identify the xlog archive on it.
[enstore@dmsen03 ~]$ enstore config --show drivestat_server PITR_area /srv3/enstore/pg_pitr [enstore@dmsen03 pitr_restore_test1]$ ls -lh $(enstore config --show drivestat_server PITR_area)/pg_xlog_archive/drivestat | tail -2 -rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz -rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
As an alternative, if the RAID on the remote backup host is available, identify the xlog archive on it.
[enstore@dmsen03 ~]$ enstore config --show crons backup_dir /srv3/enstore/backups [enstore@dmsen03 ~]$ ls -lh $(enstore config --show crons backup_dir)/pg_xlog_archive/drivestat | tail -2 -rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz -rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
As a final alternative, if xlogs since the last base backup are available on tape only, copy the most recent xlog-pack from tape to disk. Extract the pack into an empty directory.