Project

General

Profile

PostgreSQL PITR backup restoration » History » Version 28

« Previous - Version 28/52 (diff) - Next » - Current version
Amit Belani, 08/22/2015 09:54 AM


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 -lh /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/
[enstore@dmsen03 ~]$ ls -lh /tmp/srv3-enstore-backups-pg_base_backup-drivestat-2015-08-21_04-30-03.tar.xz
-rw-r--r-- 1 enstore enstore 2.4M Aug 22 07:54 /tmp/srv3-enstore-backups-pg_base_backup-drivestat-2015-08-21_04-30-03.tar.xz
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$ENSTORE_CONFIG_HOST_REAL

Extract

Extract the identified base backup into an appropriate empty directory on a node where the database is to be hosted.

[enstore@dmsen03 ~]$ mkdir /tmp/pitr_restore_test1
[enstore@dmsen03 ~]$ tar -xf /srv3/enstore/pg_pitr/pg_base_backup/drivestat/2015-08-21_04-30-03.tar.xz -C /tmp/pitr_restore_test1/
[enstore@dmsen03 ~]$ ls /tmp/pitr_restore_test1/
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 all xlog-pack files since the last base backup from tape to disk. Since backup2Tape is scheduled to run once a day, there should typically only be up to one such xlog-pack file. Identify and extract it into an empty directory.

[enstore@dmsen03 ~]$ ls -lh /pnfs/eagle/dmsen-backups/$(date +%Y/%m/%d)/*/*pg_xlog_pack-drivestat*
-rw-r--r-- 1 enstore enstore 3.6M Aug 21 08:02 /pnfs/eagle/dmsen-backups/2015/08/21/07/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar
[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_xlog_pack-drivestat-2015-08-21_08-02-41.tar /tmp/
[enstore@dmsen03 ~]$ ls -lh /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar
-rw-r--r-- 1 enstore enstore 3.6M Aug 22 09:36 /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$ENSTORE_CONFIG_HOST_REAL

[enstore@dmsen03 ~]$ mkdir /tmp/pitr_xlogs_test1
[enstore@dmsen03 ~]$ cd /tmp/pitr_xlogs_test1/
[enstore@dmsen03 ~]$ tar -xf /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar -C /tmp/pitr_xlogs_test1/
[enstore@dmsen03 ~]$ ls -lh /tmp/pitr_xlogs_test1/ | tail -2
-rw------- 1 enstore enstore 6.2K Aug 21 07:30 000000010000000A0000007C.xz
-rw------- 1 enstore enstore 5.9K Aug 21 08:00 000000010000000A0000007D.xz

Apply

Update cluster configuration