Project

General

Profile

PostgreSQL PITR backup restoration » History » Version 46

Amit Belani, 08/22/2015 12:22 PM

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 44 Amit Belani
[enstore@dmsen03 ~]$ mkdir -m 0700 /tmp/pitr_restore_test1
54 45 Amit Belani
</pre>
55 45 Amit Belani
56 45 Amit Belani
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).
57 45 Amit Belani
58 45 Amit Belani
<pre>
59 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/
60 26 Amit Belani
[enstore@dmsen03 ~]$ ls /tmp/pitr_restore_test1/
61 4 Amit Belani
backup_label  global       pg_hba.conf.old       pg_log        pg_serial     pg_stat_tmp  pg_twophase  postgresql.conf
62 4 Amit Belani
base          pg_clog      pg_hba.conf.original  pg_multixact  pg_snapshots  pg_subtrans  PG_VERSION   postgresql.conf.old
63 4 Amit Belani
conf.d        pg_hba.conf  pg_ident.conf         pg_notify     pg_stat       pg_tblspc    pg_xlog
64 4 Amit Belani
</pre>
65 45 Amit Belani
66 45 Amit Belani
67 1 Amit Belani
68 5 Amit Belani
h2. Restore transaction logs
69 1 Amit Belani
70 6 Amit Belani
h3. Identify
71 6 Amit Belani
72 12 Amit Belani
Identify the location of the transaction log (xlog) archive for the database cluster.
73 12 Amit Belani
74 12 Amit Belani
If the RAID on the host serving the database is available, identify the xlog archive on it.
75 12 Amit Belani
76 12 Amit Belani
<pre>
77 12 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show drivestat_server PITR_area
78 12 Amit Belani
/srv3/enstore/pg_pitr
79 12 Amit Belani
[enstore@dmsen03 pitr_restore_test1]$ ls -lh $(enstore config --show drivestat_server PITR_area)/pg_xlog_archive/drivestat | tail -2
80 12 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz
81 12 Amit Belani
-rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
82 1 Amit Belani
</pre>
83 13 Amit Belani
84 13 Amit Belani
As an alternative, if the RAID on the remote backup host is available, identify the xlog archive on it.
85 13 Amit Belani
86 13 Amit Belani
<pre>
87 13 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup_dir
88 13 Amit Belani
/srv3/enstore/backups
89 13 Amit Belani
[enstore@dmsen03 ~]$ ls -lh $(enstore config --show crons backup_dir)/pg_xlog_archive/drivestat | tail -2
90 13 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 23:00 000000010000000A0000009B.xz
91 13 Amit Belani
-rw------- 1 enstore enstore 6.9K Aug 21 23:30 000000010000000A0000009C.xz
92 13 Amit Belani
</pre>
93 13 Amit Belani
94 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.
95 20 Amit Belani
96 20 Amit Belani
<pre>
97 20 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /pnfs/eagle/dmsen-backups/$(date +%Y/%m/%d)/*/*pg_xlog_pack-drivestat*
98 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
99 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST_REAL=$ENSTORE_CONFIG_HOST
100 20 Amit Belani
[enstore@dmsen03 ~]$ enstore config --show crons backup2tape_config_host
101 20 Amit Belani
conf-stken.fnal.gov
102 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$(enstore config --show crons backup2tape_config_host)
103 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/
104 22 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /tmp/srv3-enstore-backups-pg_xlog_pack-drivestat-2015-08-21_08-02-41.tar
105 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
106 20 Amit Belani
[enstore@dmsen03 ~]$ ENSTORE_CONFIG_HOST=$ENSTORE_CONFIG_HOST_REAL
107 22 Amit Belani
108 22 Amit Belani
[enstore@dmsen03 ~]$ mkdir /tmp/pitr_xlogs_test1
109 23 Amit Belani
[enstore@dmsen03 ~]$ cd /tmp/pitr_xlogs_test1/
110 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/
111 27 Amit Belani
[enstore@dmsen03 ~]$ ls -lh /tmp/pitr_xlogs_test1/ | tail -2
112 27 Amit Belani
-rw------- 1 enstore enstore 6.2K Aug 21 07:30 000000010000000A0000007C.xz
113 27 Amit Belani
-rw------- 1 enstore enstore 5.9K Aug 21 08:00 000000010000000A0000007D.xz
114 20 Amit Belani
</pre>
115 12 Amit Belani
116 31 Amit Belani
h3. Restoration
117 1 Amit Belani
118 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.
119 30 Amit Belani
120 30 Amit Belani
<pre>
121 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
122 35 Amit Belani
[enstore@dmsen03 ~]$ cat /tmp/pitr_restore_test1/recovery.conf 
123 35 Amit Belani
restore_command = unxz <"/srv3/enstore/pg_pitr/pg_xlog_archive/drivestat/%f.xz" >"%p"
124 30 Amit Belani
</pre>
125 30 Amit Belani
126 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.
127 30 Amit Belani
128 5 Amit Belani
h2. Update cluster configuration
129 34 Amit Belani
130 38 Amit Belani
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.
131 36 Amit Belani
132 36 Amit Belani
<pre>
133 36 Amit Belani
[enstore@dmsen03 ~]$ cat /tmp/pitr_restore_test1/conf.d/9_restore.conf 
134 36 Amit Belani
port = 9912  # Non-conflicting with that of original instance
135 36 Amit Belani
archive_command = '/bin/true'  # Effectively disables archiving, and prevents writing WAL into same directory
136 1 Amit Belani
</pre>
137 38 Amit Belani
138 40 Amit Belani
Settings contained in a file prefixed with "@9_@" should supersede all prior configuration settings, thereby preventing a conflict with the original database cluster instance.
139 41 Amit Belani
140 41 Amit Belani
h2. Start database cluster
141 42 Amit Belani
142 43 Amit Belani
The database cluster can be started normally or by using @pg_ctl@.
143 46 Amit Belani
144 46 Amit Belani
[enstore@dmsen03 ~]$ pg_ctl start -D /tmp/pitr_restore_test1/
145 46 Amit Belani
server starting
146 46 Amit Belani
[enstore@dmsen03 ~]$ < 2015-08-22 12:16:35.772 CDT >LOG:  redirecting log output to logging collector process
147 46 Amit Belani
< 2015-08-22 12:16:35.772 CDT >HINT:  Future log output will appear in directory "pg_log".