Re: PITR

Hi Mat,

Thank you for the pointers on pause_at_recovery_target and recovery_target_time. It worked but I encountered an unexpected situation.

I wanted to test three recovery times by checking data at each point and then proceed to the next. It worked as expected first 2 recovery times but the last one did not give me an opportunity to check data. It simply completed recovery and switched timeline. This means I cannot rollforward anymore unless I restore the database again. Do you think I did something wrong?

Thanks,
Murthy

Following are my recovery.conf settings:

pause_at_recovery_target = true
#recovery_target_time = '2014-02-22 19:15:00'
#recovery_target_time = '2014-02-22 19:44:00'
recovery_target_time = '2014-02-22 19:50:00'
restore_command = 'cp /pgdata/backups/xlogs/minerva_ecl_test/%f %p'

Following is the pg_log of my 3rd recovery:

,2014-02-22 22:04:16 CSTLOG: database system was shut down in recovery at 2014-02-22 22:02:42 CST
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004.history" from archive
,2014-02-22 22:04:16 CSTLOG: starting point-in-time recovery to 2014-02-22 19:50:00-06
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000004" from archive
,2014-02-22 22:04:16 CSTLOG: redo starts at 8/4000020
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000005" from archive
,2014-02-22 22:04:16 CSTLOG: consistent recovery state reached at 8/5F51EA8
,2014-02-22 22:04:16 CSTLOG: database system is ready to accept read only connections
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000006" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000007" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000008" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "000000040000000800000009" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000A" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000B" from archive
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C" from archive
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000004000000080000000D': No such file or directory
,2014-02-22 22:04:16 CSTLOG: unexpected pageaddr 7/BC000000 in log file 8, segment 13, offset 0
,2014-02-22 22:04:16 CSTLOG: redo done at 8/C0000B8
,2014-02-22 22:04:16 CSTLOG: last completed transaction was at log time 2014-02-22 19:48:29.205898-06
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004000000080000000C" from archive
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000005.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000006.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000007.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000008.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/00000009.history': No such file or directory
cp: cannot stat `/pgdata/backups/xlogs/minerva_ecl_test/0000000A.history': No such file or directory
,2014-02-22 22:04:16 CSTLOG: selected new timeline ID: 10
,2014-02-22 22:04:16 CSTLOG: restored log file "00000004.history" from archive
,2014-02-22 22:04:17 CSTLOG: archive recovery complete
,2014-02-22 22:04:17 CSTLOG: database system is ready to accept connections
,2014-02-22 22:04:17 CSTLOG: autovacuum launcher started

From: desmodemone [mailto:desmodemone(at)gmail(dot)com]
Sent: Saturday, February 22, 2014 10:34 AM
To: Murthy Nunna
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] PITR

2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna(at)fnal(dot)gov<mailto:mnunna(at)fnal(dot)gov>>:
All,

I am testing PITR.... I am looking for recovery.conf parameters where you can recovery the WALs available in the restore_command but do not complete recovery. I want to be able to connect to the database and check database in read only and if I am not there yet, I will feed more WALs in the archive directory and resume recovery. I would like to prevent multiple base restorations. I want to roll forward with WALs but check in between. Also, I do not want to set up replication standby.

Is it possible? If so, could you tell me what are the relevant recovery.conf params?

Thanks,
Murthy

Hi Murthy,
look at parameter "pause_at_recovery_target" and "recovery_target_time" with these parameters in the recovery.conf you could reach a point in the timeline to recover the database and then see if it's all ok and then finish the recovery with pg_xlog_replay_resume. If you want instead continue the recovery you have to stop the backend and change the parameter of recovery_target_time in the recovery.conf and restart the postmaster.
Mat DBA