Re: PITR
| От | Murthy Nunna |
|---|---|
| Тема | Re: PITR |
| Дата | |
| Msg-id | BD7563973EF8894D905C80593EE9A4ED33106E@MAIL01.fnal.gov обсуждение исходный текст |
| Ответ на | Re: PITR (desmodemone <desmodemone@gmail.com>) |
| Ответы |
Re: PITR
(Raghavendra <raghavendra.rao@enterprisedb.com>)
|
| Список | pgsql-admin |
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@gmail.com]
Sent: Saturday, February 22, 2014 10:34 AM
To: Murthy Nunna
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] PITR
2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@fnal.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
В списке pgsql-admin по дате отправления: