Hi We are on Postgres 9.6 and we have primary/standby wal replication setup for HA.
For PITR, we have scheduled nightly base backup and hourly archive backups on the primary server. https://www.postgresql.org/docs/9.6/static/continuous-archiving.html I am trying to perform a point-in-time recovery after a failover has occurred. For example: 1 am archive backup on P1 1:30 am base backup on P1 (primary) (timeline 5) 2 am archive backup on P1 3 am archive backup on P1 Etc.. 1:49 pm failover P1 crashed, S1 got promoted. (timeline 6) 2 pm archive backup on S1 3 pm archive backup on S1 For example: At 3:15pm something got corrupted and need to restore from backup to 1:54pm. I extracted the base backups (tar files) to the data directory and extracted the all the archive backups (P1 and S1) to the archive directory. Followed the steps from Created recovery.conf with the following, but the recovery only recovers, to the point before the failover and comes online. Example of my recovery.conf : restore_command = 'cp /pg_backup/backup/archive/%f %p' recovery_target_time = '2018-03-13 13:54:00' recovery_target_inclusive = 'false' recovery_target_timeline = '6' (I also tried revovery_target_timeline = 'lastest) Is it possible to recovery to PITR after a failover using base backup from before failover and both archive backups from P1 and S1? Here is the log output : 018-03-13 20:46:53 ACDT [154912]: [1-1] db=,user= app=,host= LOG: database system was interrupted; last known up at 2018-03-1 3 01:31:25 ACDT 2018-03-13 20:46:53 ACDT [154912]: [2-1] db=,user= app=,host= LOG: restored log file "00000006.history" from archive cp: cannot stat '/pg_backup/backup/archive/00000007.history': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [3-1] db=,user= app=,host= LOG: starting point-in-time recovery to 2018-03-13 13:54:00+10:3 0 2018-03-13 20:46:53 ACDT [154912]: [4-1] db=,user= app=,host= LOG: restored log file "00000006.history" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000087': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [5-1] db=,user= app=,host= LOG: restored log file "000000050000011100000087" from archive cp: cannot stat '/pg_backup/backup/archive/00000005.history': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [6-1] db=,user= app=,host= LOG: redo starts at 111/87000028 2018-03-13 20:46:53 ACDT [154912]: [7-1] db=,user= app=,host= LOG: consistent recovery state reached at 111/870B4418 2018-03-13 20:46:53 ACDT [154908]: [3-1] db=,user= app=,host= LOG: database system is ready to accept read only connections cp: cannot stat '/pg_backup/backup/archive/000000060000011100000088': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [8-1] db=,user= app=,host= LOG: restored log file "000000050000011100000088" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000089': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [9-1] db=,user= app=,host= LOG: restored log file "000000050000011100000089" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008A': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [10-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008A" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008B': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [11-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008B" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008C': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [12-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008C" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008D': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [13-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008D" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008E': No such file or directory 2018-03-13 20:46:53 ACDT [154912]: [14-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008E" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000008F': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [15-1] db=,user= app=,host= LOG: restored log file "00000005000001110000008F" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000090': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [16-1] db=,user= app=,host= LOG: restored log file "000000050000011100000090" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000091': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [17-1] db=,user= app=,host= LOG: restored log file "000000050000011100000091" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000092': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [18-1] db=,user= app=,host= LOG: restored log file "000000050000011100000092" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000093': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [19-1] db=,user= app=,host= LOG: restored log file "000000050000011100000093" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000094': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [20-1] db=,user= app=,host= LOG: restored log file "000000050000011100000094" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000095': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [21-1] db=,user= app=,host= LOG: restored log file "000000050000011100000095" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000096': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [22-1] db=,user= app=,host= LOG: restored log file "000000050000011100000096" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000097': No such file or directory 2018-03-13 20:46:54 ACDT [154912]: [23-1] db=,user= app=,host= LOG: restored log file "000000050000011100000097" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000098': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [24-1] db=,user= app=,host= LOG: restored log file "000000050000011100000098" from archive cp: cannot stat '/pg_backup/backup/archive/000000060000011100000099': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [25-1] db=,user= app=,host= LOG: restored log file "000000050000011100000099" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009A': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [26-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009A" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009B': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [27-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009B" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009C': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [28-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009C" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009D': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [29-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009D" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009E': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [30-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009E" from archive cp: cannot stat '/pg_backup/backup/archive/00000006000001110000009F': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [31-1] db=,user= app=,host= LOG: restored log file "00000005000001110000009F" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A0': No such file or directory 2018-03-13 20:46:55 ACDT [154912]: [32-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A0" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A1': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [33-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A1" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A2': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [34-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A2" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A3': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [35-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A3" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A4': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [36-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A4" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A5': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [37-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A5" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A6': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [38-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A6" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A7': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [39-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A7" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A8': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [40-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A8" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000A9': No such file or directory 2018-03-13 20:46:56 ACDT [154912]: [41-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000A9" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AA': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [42-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000AA" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AB': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [43-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000AB" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AC': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [44-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000AC" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AD': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [45-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000AD" from archive cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AE': No such file or directory cp: cannot stat '/pg_backup/backup/archive/0000000500000111000000AE': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [46-1] db=,user= app=,host= LOG: redo done at 111/ADFFE160 2018-03-13 20:46:57 ACDT [154912]: [47-1] db=,user= app=,host= LOG: last completed transaction was at log time 2018-03-13 13:4 0:02.36347+10:30 cp: cannot stat '/pg_backup/backup/archive/0000000600000111000000AD': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [48-1] db=,user= app=,host= LOG: restored log file "0000000500000111000000AD" from archive cp: cannot stat '/pg_backup/backup/archive/00000007.history': No such file or directory 2018-03-13 20:46:57 ACDT [154912]: [49-1] db=,user= app=,host= LOG: selected new timeline ID: 7 2018-03-13 20:46:57 ACDT [154912]: [50-1] db=,user= app=,host= LOG: restored log file "00000006.history" from archive 2018-03-13 20:46:57 ACDT [154912]: [51-1] db=,user= app=,host= LOG: archive recovery complete 2018-03-13 20:46:57 ACDT [154919]: [1-1] db=,user= app=,host= LOG: checkpoint starting: end-of-recovery immediate wait 2018-03-13 20:46:59 ACDT [154919]: [2-1] db=,user= app=,host= LOG: checkpoint complete: wrote 53374 buffers (0.6%); 0 transact ion log file(s) added, 0 removed, 32 recycled; write=1.140 s, sync=0.019 s, total=1.224 s; sync files=68, longest=0.013 s, aver age=0.000 s; distance=638968 kB, estimate=638968 kB 2018-03-13 20:46:59 ACDT [154912]: [52-1] db=,user= app=,host= LOG: MultiXact member wraparound protections are now enabled 2018-03-13 20:46:59 ACDT [154908]: [4-1] db=,user= app=,host= LOG: database system is ready to accept connections Here is what is in the archive directory after extracting from the archive backups and after database comes online: -rw-------. 1 postgres postgres 16777216 Mar 12 21:13 000000050000011100000075 -rw-------. 1 postgres postgres 16777216 Mar 12 21:30 000000050000011100000076 -rw-------. 1 postgres postgres 16777216 Mar 12 21:47 000000050000011100000077 -rw-------. 1 postgres postgres 16777216 Mar 12 22:05 000000050000011100000078 -rw-------. 1 postgres postgres 16777216 Mar 12 22:22 000000050000011100000079 -rw-------. 1 postgres postgres 16777216 Mar 12 22:38 00000005000001110000007A -rw-------. 1 postgres postgres 16777216 Mar 12 22:56 00000005000001110000007B -rw-------. 1 postgres postgres 16777216 Mar 12 23:13 00000005000001110000007C -rw-------. 1 postgres postgres 16777216 Mar 12 23:30 00000005000001110000007D -rw-------. 1 postgres postgres 16777216 Mar 12 23:36 00000005000001110000007E -rw-------. 1 postgres postgres 16777216 Mar 12 23:45 00000005000001110000007F -rw-------. 1 postgres postgres 16777216 Mar 13 00:02 000000050000011100000080 -rw-------. 1 postgres postgres 16777216 Mar 13 00:20 000000050000011100000081 -rw-------. 1 postgres postgres 16777216 Mar 13 00:36 000000050000011100000082 -rw-------. 1 postgres postgres 16777216 Mar 13 00:53 000000050000011100000083 -rw-------. 1 postgres postgres 16777216 Mar 13 01:10 000000050000011100000084 -rw-------. 1 postgres postgres 16777216 Mar 13 01:27 000000050000011100000085 -rw-------. 1 postgres postgres 16777216 Mar 13 01:30 000000050000011100000086 -rw-------. 1 postgres postgres 16777216 Mar 13 01:31 000000050000011100000087 -rw-------. 1 postgres postgres 307 Mar 13 01:31 000000050000011100000087.00000028.backup -rw-------. 1 postgres postgres 16777216 Mar 13 01:48 000000050000011100000088 -rw-------. 1 postgres postgres 16777216 Mar 13 02:05 000000050000011100000089 -rw-------. 1 postgres postgres 16777216 Mar 13 02:22 00000005000001110000008A -rw-------. 1 postgres postgres 16777216 Mar 13 02:39 00000005000001110000008B -rw-------. 1 postgres postgres 16777216 Mar 13 02:55 00000005000001110000008C -rw-------. 1 postgres postgres 16777216 Mar 13 03:12 00000005000001110000008D -rw-------. 1 postgres postgres 16777216 Mar 13 03:30 00000005000001110000008E -rw-------. 1 postgres postgres 16777216 Mar 13 03:47 00000005000001110000008F -rw-------. 1 postgres postgres 16777216 Mar 13 04:04 000000050000011100000090 -rw-------. 1 postgres postgres 16777216 Mar 13 04:21 000000050000011100000091 -rw-------. 1 postgres postgres 16777216 Mar 13 04:38 000000050000011100000092 -rw-------. 1 postgres postgres 16777216 Mar 13 04:55 000000050000011100000093 -rw-------. 1 postgres postgres 16777216 Mar 13 05:12 000000050000011100000094 -rw-------. 1 postgres postgres 16777216 Mar 13 05:28 000000050000011100000095 -rw-------. 1 postgres postgres 16777216 Mar 13 05:46 000000050000011100000096 -rw-------. 1 postgres postgres 16777216 Mar 13 06:02 000000050000011100000097 -rw-------. 1 postgres postgres 16777216 Mar 13 06:19 000000050000011100000098 -rw-------. 1 postgres postgres 16777216 Mar 13 06:36 000000050000011100000099 -rw-------. 1 postgres postgres 16777216 Mar 13 06:53 00000005000001110000009A -rw-------. 1 postgres postgres 16777216 Mar 13 07:11 00000005000001110000009B -rw-------. 1 postgres postgres 16777216 Mar 13 07:29 00000005000001110000009C -rw-------. 1 postgres postgres 16777216 Mar 13 07:47 00000005000001110000009D -rw-------. 1 postgres postgres 16777216 Mar 13 08:02 00000005000001110000009E -rw-------. 1 postgres postgres 16777216 Mar 13 08:02 00000005000001110000009F -rw-------. 1 postgres postgres 16777216 Mar 13 08:19 0000000500000111000000A0 -rw-------. 1 postgres postgres 16777216 Mar 13 08:36 0000000500000111000000A1 -rw-------. 1 postgres postgres 16777216 Mar 13 08:55 0000000500000111000000A2 -rw-------. 1 postgres postgres 16777216 Mar 13 09:13 0000000500000111000000A3 -rw-------. 1 postgres postgres 16777216 Mar 13 09:15 0000000500000111000000A4 -rw-------. 1 postgres postgres 16777216 Mar 13 09:15 0000000500000111000000A5 -rw-------. 1 postgres postgres 16777216 Mar 13 09:17 0000000500000111000000A6 -rw-------. 1 postgres postgres 16777216 Mar 13 10:03 0000000500000111000000A7 -rw-------. 1 postgres postgres 16777216 Mar 13 10:15 0000000500000111000000A8 -rw-------. 1 postgres postgres 16777216 Mar 13 13:40 0000000500000111000000A9 -rw-------. 1 postgres postgres 16777216 Mar 13 13:40 0000000500000111000000AA -rw-------. 1 postgres postgres 16777216 Mar 13 13:40 0000000500000111000000AB -rw-------. 1 postgres postgres 16777216 Mar 13 13:40 0000000500000111000000AC -rw-------. 1 postgres postgres 16777216 Mar 13 13:40 0000000500000111000000AD -rw-------. 1 postgres postgres 16777216 Mar 13 13:48 0000000500000111000000B1.partial -rw-------. 1 postgres postgres 16777216 Mar 13 13:49 0000000600000111000000B1 -rw-------. 1 postgres postgres 16777216 Mar 13 14:04 0000000600000111000000B2 -rw-------. 1 postgres postgres 221 Mar 13 13:48 00000006.history -rw-------. 1 postgres postgres 16777216 Mar 13 20:48 0000000700000111000000AD -rw-------. 1 postgres postgres 271 Mar 13 20:46 00000007.history Dylan
