Re: PITR

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx]
Sent: Saturday, February 22, 2014 10:34 AM
To: Murthy Nunna
Cc: pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: PITR

 

 

 

2014-02-22 17:06 GMT+01:00 Murthy Nunna <mnunna@xxxxxxxx>:

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

 


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux