Thread: PITR timeline question
If you're using multiple timelines with PITR, do you have to re-copy all the data files into the cluster every time you start a new timeline? Or can you copy the data files out of the backup once, and then perform multiple recoveries, each to different timelines? Ultimately, what I'd like to be able to do is periodically bring a 'PITR slave' live. I know I can do this by first copying the base files and then recovering every time, but it would be nice if I didn't have to actually copy the base files first. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim Nasby <jnasby@pervasive.com> writes:
> If you're using multiple timelines with PITR, do you have to re-copy
> all the data files into the cluster every time you start a new
> timeline? Or can you copy the data files out of the backup once, and
> then perform multiple recoveries, each to different timelines?
The only way to "roll time backwards" is to replace the data directory
with a backup, so if I understand your question correctly, the answer
is no.
regards, tom lane
On Aug 3, 2006, at 3:01 PM, Tom Lane wrote: > Jim Nasby <jnasby@pervasive.com> writes: >> If you're using multiple timelines with PITR, do you have to re-copy >> all the data files into the cluster every time you start a new >> timeline? Or can you copy the data files out of the backup once, and >> then perform multiple recoveries, each to different timelines? > > The only way to "roll time backwards" is to replace the data directory > with a backup, so if I understand your question correctly, the answer > is no. Well, specifically I was thinking of... Roll PITR forward and start database Do a bunch of stuff Got more WAL files... start database back in recovery and roll forward to end of new WAL files (throwing away everything that was done previously). -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim Nasby <jnasby@pervasive.com> writes:
> On Aug 3, 2006, at 3:01 PM, Tom Lane wrote:
>> The only way to "roll time backwards" is to replace the data directory
>> with a backup, so if I understand your question correctly, the answer
>> is no.
> Well, specifically I was thinking of...
> Roll PITR forward and start database
> Do a bunch of stuff
> Got more WAL files... start database back in recovery and roll
> forward to end of new WAL files (throwing away everything that was
> done previously).
No, that definitely won't work, since any data pages touched by the
"bunch of stuff" and not by the new WAL files will not have been
reverted to their prior states --- while any pages touched in common
*will* have been overwritten. Net result: inconsistent database.
There's been some speculation about allowing a standby server to execute
purely read-only operations, but it's just speculation so far.
regards, tom lane
> There's been some speculation about allowing a standby server to execute > purely read-only operations, but it's just speculation so far. To add some more speculations, I wonder if that can be workable at all... given that the data pages are overwritten by WAL records, and not under transactional control of the standby server, how can you ensure that a read only transaction on the standby will see a consistent snapshot of the data ? It is possible that some data on a page which should be visible for the read only transaction is overwritten or even deleted by some incoming WAL record. I think the only solution would be to only recover up to the oldest local transaction's start time, but then if you have long running transactions on the stand-by, you'll also have long delays in recovery... which might not be what people want... Cheers, Csaba.