Dylan Luong wrote: > We are on Postgres 9.6 and we have primary/standby wal replication setup for HA. > > I am trying to perform a point-in-time recovery after a failover has occurred. > > 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/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 > > > 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 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 You are missing 0000000500000111000000AE or 0000000600000111000000AE. One of these files should be there. I suspect that there is a problem with your WAL archiving in connection with failover. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
