Project

General

Profile

PostgreSQL PITR backup restoration » History » Version 35

Amit Belani, 08/22/2015 11:18 AM

1 1 Amit Belani
h1. PostgreSQL PITR backup restoration
2 1 Amit Belani
3 1 Amit Belani
{{toc}}
4 1 Amit Belani
5 9 Amit Belani
This article uses the @drivestat@ database cluster hosted on @dmsen03@ as an example.
6 7 Amit Belani
7 33 Amit Belani
h2. Restore base backup
8 1 Amit Belani
9 5 Amit Belani
h3. Identify
10 4 Amit Belani
11 2 Amit Belani
Identify the most recent base backup for the database cluster.
12 2 Amit Belani
13 2 Amit Belani
If the RAID on the host serving the database is available, identify the recent base backups on it.
14 2 Amit Belani
15 2 Amit Belani
<pre>
16 2 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show drivestat_server PITR_area
17 2 Amit Belani
/srv3/enstore/pg_pitr
18 2 Amit Belani
[enstore@dmsen03 ~]$ ls -lh $(enstore config --show drivestat_server PITR_area)/pg_base_backup/drivestat | tail -2
19 2 Amit Belani
-rw-r--r-- 1 enstore enstore 2.4M Aug 20 04:30 2015-08-20_04-30-02.tar.xz
20 2 Amit Belani
-rw-r--r-- 1 enstore enstore 2.4M Aug 21 04:30 2015-08-21_04-30-03.tar.xz
21 2 Amit Belani
</pre>
22 2 Amit Belani
23 2 Amit Belani
As an alternative, if the RAID on the remote backup host is available, identify the recent base backups on it.
24 2 Amit Belani
25 2 Amit Belani
<pre>
26 2 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup_dir
27 2 Amit Belani
/srv3/enstore/backups
28 2 Amit Belani
[enstore@dmsen03 ~]$ ls -lh $(enstore config --show crons backup_dir)/pg_base_backup/drivestat | tail -2
29 2 Amit Belani
-rw-r--r-- 1 enstore enstore 2.4M Aug 20 04:30 2015-08-20_04-30-02.tar.xz
30 2 Amit Belani
-rw-r--r-- 1 enstore enstore 2.4M Aug 21 04:30 2015-08-21_04-30-03.tar.xz
31 2 Amit Belani
</pre>
32 2 Amit Belani
33 15 Amit Belani
As a final alternative, if base backups are available on tape only, identify and copy the most recent base backup from tape to disk.
34 15 Amit Belani
35 15 Amit Belani
<pre>
36 18 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /pnfs/eagle/dmsen-backups/$(date +%Y/%m/%d)/*/*pg_base_backup-drivestat*
37 1 Amit Belani
-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
38 16 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST_REAL=$ENSTORE_CONFIG_HOST
39 16 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup2tape_config_host
40 16 Amit Belani
conf-stken.fnal.gov
41 16 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$(enstore config --show crons backup2tape_config_host)
42 16 Amit Belani
[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/
43 17 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /tmp/srv3-enstore-backups-pg_base_backup-drivestat-2015-08-21_04-30-03.tar.xz
44 17 Amit Belani
-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
45 17 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$ENSTORE_CONFIG_HOST_REAL
46 15 Amit Belani
</pre>
47 4 Amit Belani
48 4 Amit Belani
h3. Extract
49 4 Amit Belani
50 10 Amit Belani
Extract the identified base backup into an appropriate empty directory on a node where the database is to be hosted.
51 4 Amit Belani
52 4 Amit Belani
<pre>
53 21 Amit Belani
[enstore@dmsen03 ~]$ mkdir /tmp/pitr_restore_test1
54 26 Amit Belani
[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/
55 26 Amit Belani
[enstore@dmsen03 ~]$ ls /tmp/pitr_restore_test1/
56 4 Amit Belani
backup_label  global       pg_hba.conf.old       pg_log        pg_serial     pg_stat_tmp  pg_twophase  postgresql.conf
57 4 Amit Belani
base          pg_clog      pg_hba.conf.original  pg_multixact  pg_snapshots  pg_subtrans  PG_VERSION   postgresql.conf.old
58 4 Amit Belani
conf.d        pg_hba.conf  pg_ident.conf         pg_notify     pg_stat       pg_tblspc    pg_xlog
59 4 Amit Belani
</pre>
60 1 Amit Belani
61 5 Amit Belani
h2. Restore transaction logs
62 1 Amit Belani
63 6 Amit Belani
h3. Identify
64 6 Amit Belani
65 12 Amit Belani
Identify the location of the transaction log (xlog) archive for the database cluster.
66 12 Amit Belani
67 12 Amit Belani
If the RAID on the host serving the database is available, identify the xlog archive on it.
68 12 Amit Belani
69 12 Amit Belani
<pre>
70 12 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show drivestat_server PITR_area
71 12 Amit Belani
/srv3/enstore/pg_pitr
72 12 Amit Belani
[enstore@dmsen03 pitr_restore_test1]$ ls -lh $(enstore config --show drivestat_server PITR_area)/pg_xlog_archive/drivestat | tail -2
73 12 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz
74 12 Amit Belani
-rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
75 1 Amit Belani
</pre>
76 13 Amit Belani
77 13 Amit Belani
As an alternative, if the RAID on the remote backup host is available, identify the xlog archive on it.
78 13 Amit Belani
79 13 Amit Belani
<pre>
80 13 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup_dir
81 13 Amit Belani
/srv3/enstore/backups
82 13 Amit Belani
[enstore@dmsen03 ~]$ ls -lh $(enstore config --show crons backup_dir)/pg_xlog_archive/drivestat | tail -2
83 13 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz
84 13 Amit Belani
-rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
85 13 Amit Belani
</pre>
86 13 Amit Belani
87 29 Amit Belani
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.
88 20 Amit Belani
89 20 Amit Belani
<pre>
90 20 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /pnfs/eagle/dmsen-backups/$(date +%Y/%m/%d)/*/*pg_xlog_pack-drivestat*
91 20 Amit Belani
-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
92 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST_REAL=$ENSTORE_CONFIG_HOST
93 20 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup2tape_config_host
94 20 Amit Belani
conf-stken.fnal.gov
95 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$(enstore config --show crons backup2tape_config_host)
96 20 Amit Belani
[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/
97 22 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar
98 24 Amit Belani
-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
99 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$ENSTORE_CONFIG_HOST_REAL
100 22 Amit Belani
101 22 Amit Belani
[enstore@dmsen03 ~]$ mkdir /tmp/pitr_xlogs_test1
102 23 Amit Belani
[enstore@dmsen03 ~]$ cd /tmp/pitr_xlogs_test1/
103 25 Amit Belani
[enstore@dmsen03 ~]$ tar -xf /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar -C /tmp/pitr_xlogs_test1/
104 27 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /tmp/pitr_xlogs_test1/ | tail -2
105 27 Amit Belani
-rw------- 1 enstore enstore 6.2K Aug 21 07:30 000000010000000A0000007C.xz
106 27 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 08:00 000000010000000A0000007D.xz
107 20 Amit Belani
</pre>
108 12 Amit Belani
109 31 Amit Belani
h3. Restoration
110 1 Amit Belani
111 32 Amit Belani
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.
112 30 Amit Belani
113 30 Amit Belani
<pre>
114 30 Amit Belani
[enstore@dmsen03 ~]$ echo restore_command = 'unxz <"/srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/%f.xz" >"%p"' > /tmp/pitr_restore_test1/recovery.conf
115 35 Amit Belani
[enstore@dmsen03 ~]$ cat /tmp/pitr_restore_test1/recovery.conf 
116 35 Amit Belani
restore_command = unxz <"/srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/%f.xz" >"%p"
117 30 Amit Belani
</pre>
118 30 Amit Belani
119 31 Amit Belani
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.
120 30 Amit Belani
121 5 Amit Belani
h2. Update cluster configuration
122 34 Amit Belani
123 34 Amit Belani
As relevant, write a configuration file into the @conf.d@ subdirectory of the data directory.