Point-in-time recovery after failover - Mailing list pgsql-general

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