Enstore

h1. PostgreSQL PITR backup restoration

{{toc}}

This article uses the @drivestat@ database cluster hosted on @dmsen03@ as an example.

h2. Restore base backup

h3. 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.

<pre>

[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

</pre>

As an alternative, if the RAID on the remote backup host is available, identify the recent base backups on it.

<pre>

[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

</pre>

As a final alternative, if base backups are available on tape only, identify and copy the most recent base backup from tape to disk.

<pre>

[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

</pre>

h3. Extract

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

<pre>

[enstore@dmsen03 ~]$ mkdir -m 0700 /tmp/pitr_restore_test1

</pre>

Note that if "@-m 0700@" is not specified above, there occurs a fatal error upon starting the database cluster, indicating that the data directory has group or world access, and that its permissions should be u=rwx (0700).

<pre>

[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

</pre>

h2. Restore transaction logs

h3. 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.

<pre>

[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

</pre>

As an alternative, if the RAID on the remote backup host is available, identify the xlog archive on it.

<pre>

[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

</pre>

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. Note that it's possible that no xlog-pack file had been written to tape yet since the last base backup.

<pre>

[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

</pre>

h3. Restoration

Write a @recovery.conf@ file in the data directory. It will be used by PostgreSQL to restore transaction logs since the base backup.  It must be customized with an accessible location of the xlog archive.

<pre>

[enstore@dmsen03 ~]$ cat /tmp/pitr_restore_test1/recovery.conf 

restore_command = 'unxz <"/srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/%f.xz" >"%p"'

</pre>

After the recovery is done, @recovery.conf@ will be renamed by PostgreSQL to @recovery.done@. Also after the recovery is done, the latest pg_log file for the database cluster is likely to contain @sh@ messages such as @"00000002.history.xz: No such file or directory"@ and @"00000001.history.xz: No such file or directory"@. These @sh@ errors can be ignored.

h2. Update cluster configuration

This section is relevant especially if the restoration is being performed as a test. Even if it's not a test, @archive_command@ may still need customization in order for the transaction logs to be archived to an appropriate location. In this case, write or modify the configuration in the @conf.d@ subdirectory of the data directory.

<pre>

[enstore@dmsen03 ~]$ cat /tmp/pitr_restore_test1/conf.d/9_restore.conf 

port = 9912  # Non-conflicting with that of original instance

archive_command = '/bin/true'  # Effectively disables archiving, and prevents writing WAL into same directory

</pre>

Settings contained in a file prefixed with "@9_@" should supersede all prior configuration settings, thereby preventing a conflict with the original database cluster instance.

h2. Start database cluster

The database cluster can be started normally or by using @pg_ctl@.

<pre>

[enstore@dmsen03 ~]$ pg_ctl start -D /tmp/pitr_restore_test1/

server starting

[enstore@dmsen03 ~]$ < 2015-08-22 12:16:35.772 CDT >LOG:  redirecting log output to logging collector process

< 2015-08-22 12:16:35.772 CDT >HINT:  Future log output will appear in directory "pg_log".

</pre>

Confirm startup.

<pre>

[enstore@dmsen03 ~]$ pg_ctl status -D /tmp/pitr_restore_test1/

pg_ctl: server is running (PID: 19330)

/usr/pgsql-9.3/bin/postgres "-D" "/tmp/pitr_restore_test1"

[enstore@dmsen03 ~]$ ls -tr /tmp/pitr_restore_test1/pg_log/* | tail -1 | xargs tail -vn +1 | grep -v 'restored log file'

==> /tmp/pitr_restore_test1/pg_log/postgresql-Sat.log <==

< 2015-08-22 12:22:49.959 CDT >LOG:  database system was interrupted; last known up at 2015-08-21 04:30:03 CDT

< 2015-08-22 12:22:50.393 CDT >LOG:  starting archive recovery

< 2015-08-22 12:22:50.611 CDT >LOG:  redo starts at A/76000090

< 2015-08-22 12:22:50.646 CDT >LOG:  consistent recovery state reached at A/760000B8

sh: /srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/000000010000000A000000B7.xz: No such file or directory

< 2015-08-22 12:23:01.301 CDT >LOG:  redo done at A/B6000090

sh: /srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/00000002.history.xz: No such file or directory

< 2015-08-22 12:23:02.924 CDT >LOG:  selected new timeline ID: 2

sh: /srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/00000001.history.xz: No such file or directory

< 2015-08-22 12:23:03.397 CDT >LOG:  archive recovery complete

< 2015-08-22 12:23:03.831 CDT >LOG:  MultiXact member wraparound protections are now enabled

< 2015-08-22 12:23:03.835 CDT >LOG:  autovacuum launcher started

< 2015-08-22 12:23:03.836 CDT >LOG:  database system is ready to accept connections

</pre>