pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpoint-in-time recovery
111+ messages / 20 participants
[nested] [flat]
* point-in-time recovery
@ 2003-04-24 01:17 Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 2 replies; 111+ messages in thread
From: Gaetano Mendola @ 2003-04-24 01:17 UTC (permalink / raw)
To: pgsql-admin
Hi,
is this features already present in 7.4Devel ?
If yes how is possible try it ?
Ciao
Gaetano
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-24 01:20 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 111+ messages in thread
From: Bruce Momjian @ 2003-04-24 01:20 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-admin
Gaetano Mendola wrote:
> Hi,
> is this features already present in 7.4Devel ?
> If yes how is possible try it ?
It will be in 7.4, but that will out only in a few months --- it isn't
in CVS yet, but should be by May.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 05:45 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 1 reply; 111+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-25 05:45 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin
What is this PITR anyway ?
when it comes will i be able to
1. reduce by pg_dump times ?
2. can afford to run pg_dump less frequently ?
3. Will be able to rollback committed transactions of past 5 mins?
any pointers to discussions?
On Thursday 24 Apr 2003 6:47 am, Gaetano Mendola wrote:
> Hi,
> is this features already present in 7.4Devel ?
> If yes how is possible try it ?
>
>
> Ciao
> Gaetano
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 14:17 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2003-04-25 14:17 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-admin
Rajesh Kumar Mallah wrote:
>
>
> What is this PITR anyway ?
>
> when it comes will i be able to
>
> 1. reduce by pg_dump times ?
> 2. can afford to run pg_dump less frequently ?
> 3. Will be able to rollback committed transactions of past 5 mins?
You can do a dump, then do continuous logging of all database changes
until the next backup. If you crash in the middle of the day, you can
recover up to the point of the crash.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 14:45 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-25 14:45 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-admin
Thanks for responding , but i still have
more to discuss.
On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > What is this PITR anyway ?
> >
> > when it comes will i be able to
> >
> > 1. reduce by pg_dump times ?
> > 2. can afford to run pg_dump less frequently ?
> > 3. Will be able to rollback committed transactions of past 5 mins?
>
> You can do a dump,
Do u mean pg_dump here, or some special command
thats doing a quick snapshot dump?
then do continuous logging of all database changes
is this logging happening automatically for i have to give some
special command to start logging?
> until the next backup. If you crash in the middle of the day, you can
> recover up to the point of the crash.
say i do not crash but do a
psql> DELETE from TABLE_NAME ;
DELETE 1021
psql> Ooops what did i do , damn my last backup is 6 hrs old ??
does PITR help me here?
And Sorry if its too early to ask
Regds
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 14:47 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2003-04-25 14:47 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-admin
Rajesh Kumar Mallah wrote:
>
>
> Thanks for responding , but i still have
> more to discuss.
>
> On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > Rajesh Kumar Mallah wrote:
> > > What is this PITR anyway ?
> > >
> > > when it comes will i be able to
> > >
> > > 1. reduce by pg_dump times ?
> > > 2. can afford to run pg_dump less frequently ?
> > > 3. Will be able to rollback committed transactions of past 5 mins?
> >
> > You can do a dump,
>
> Do u mean pg_dump here, or some special command
> thats doing a quick snapshot dump?
You do one pg_dump, the start PITR logging.
>
> then do continuous logging of all database changes
> is this logging happening automatically for i have to give some
> special command to start logging?
Not sure yet --- we arn't done, but it will probably be a special
command.
>
> > until the next backup. If you crash in the middle of the day, you can
> > recover up to the point of the crash.
>
> say i do not crash but do a
>
> psql> DELETE from TABLE_NAME ;
> DELETE 1021
> psql> Ooops what did i do , damn my last backup is 6 hrs old ??
>
> does PITR help me here?
Ideally, you will be able to restore to any point you wish, such as
before the DELETE. However, I do not know if that control will be
implemented in 7.4, but if not, certainly 7.5.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 15:09 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-25 15:09 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-admin
On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > Thanks for responding , but i still have
> > more to discuss.
> >
> > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > Rajesh Kumar Mallah wrote:
> > > > What is this PITR anyway ?
> > > >
> > > > when it comes will i be able to
> > > >
> > > > 1. reduce by pg_dump times ?
> > > > 2. can afford to run pg_dump less frequently ?
> > > > 3. Will be able to rollback committed transactions of past 5 mins?
> > >
> > > You can do a dump,
> >
> > Do u mean pg_dump here, or some special command
> > thats doing a quick snapshot dump?
>
> You do one pg_dump, the start PITR logging.
I dunno Abt the general case but we have seen
more instances of data loss by our own actions (faulty SQL eg)
rather than HW's or postgresql's
Say for my live database.
Say i start my pg_dump at 5:00p and it finishes say by 5:30p
then i give the command for PITR logging at 5:31
Then i accidently delete at say 9pm
Then am i expected to :
1. restore the snapshot of 5:00 pm
2. ROLL FORWARD using PITR log till 8:59 or so
using some special command ?
In that case what happens to the transactions from 5:00pm
to 5:30 pm which were not captured in the pg_dump nor in the
PITR log?
I have read WAL does transaction logging is PITR log
synonymous to WAL log or its different ?
Sorry i do not have RDBMS experience in any other than PostgreSQL
thats why not aware of PITR but i search the web and found this
feature is called tablespace PITR (TSPITR) in Oracle but unless
you have worked on it its difficult to understand.
Regds
Mallah.
>
> > then do continuous logging of all database changes
> > is this logging happening automatically for i have to give some
> > special command to start logging?
>
> Not sure yet --- we arn't done, but it will probably be a special
> command.
>
> > > until the next backup. If you crash in the middle of the day, you can
> > > recover up to the point of the crash.
> >
> > say i do not crash but do a
> >
> > psql> DELETE from TABLE_NAME ;
> > DELETE 1021
> > psql> Ooops what did i do , damn my last backup is 6 hrs old ??
> >
> > does PITR help me here?
>
> Ideally, you will be able to restore to any point you wish, such as
> before the DELETE. However, I do not know if that control will be
> implemented in 7.4, but if not, certainly 7.5.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 15:11 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2003-04-25 15:11 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-admin
Rajesh Kumar Mallah wrote:
> On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> > Rajesh Kumar Mallah wrote:
> > > Thanks for responding , but i still have
> > > more to discuss.
> > >
> > > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > > Rajesh Kumar Mallah wrote:
> > > > > What is this PITR anyway ?
> > > > >
> > > > > when it comes will i be able to
> > > > >
> > > > > 1. reduce by pg_dump times ?
> > > > > 2. can afford to run pg_dump less frequently ?
> > > > > 3. Will be able to rollback committed transactions of past 5 mins?
> > > >
> > > > You can do a dump,
> > >
> > > Do u mean pg_dump here, or some special command
> > > thats doing a quick snapshot dump?
> >
> > You do one pg_dump, the start PITR logging.
>
> I dunno Abt the general case but we have seen
> more instances of data loss by our own actions (faulty SQL eg)
> rather than HW's or postgresql's
>
> Say for my live database.
> Say i start my pg_dump at 5:00p and it finishes say by 5:30p
> then i give the command for PITR logging at 5:31
>
> Then i accidently delete at say 9pm
>
> Then am i expected to :
> 1. restore the snapshot of 5:00 pm
> 2. ROLL FORWARD using PITR log till 8:59 or so
> using some special command ?
>
> In that case what happens to the transactions from 5:00pm
> to 5:30 pm which were not captured in the pg_dump nor in the
> PITR log?
You have to start PITR logging probably before the pg_dump so there will
be some overlap, but the system will know how to handle it.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: point-in-time recovery
@ 2003-04-25 15:37 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-25 15:37 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-admin
On Friday 25 Apr 2003 8:41 pm, Bruce Momjian wrote:
> Rajesh Kumar Mallah wrote:
> > On Friday 25 Apr 2003 8:17 pm, Bruce Momjian wrote:
> > > Rajesh Kumar Mallah wrote:
> > > > Thanks for responding , but i still have
> > > > more to discuss.
> > > >
> > > > On Friday 25 Apr 2003 7:47 pm, Bruce Momjian wrote:
> > > > > Rajesh Kumar Mallah wrote:
> > > > > > What is this PITR anyway ?
> > > > > >
> > > > > > when it comes will i be able to
> > > > > >
> > > > > > 1. reduce by pg_dump times ?
> > > > > > 2. can afford to run pg_dump less frequently ?
> > > > > > 3. Will be able to rollback committed transactions of past 5
> > > > > > mins?
> > > > >
> > > > > You can do a dump,
> > > >
> > > > Do u mean pg_dump here, or some special command
> > > > thats doing a quick snapshot dump?
> > >
> > > You do one pg_dump, the start PITR logging.
> >
> > I dunno Abt the general case but we have seen
> > more instances of data loss by our own actions (faulty SQL eg)
> > rather than HW's or postgresql's
> >
> > Say for my live database.
> > Say i start my pg_dump at 5:00p and it finishes say by 5:30p
> > then i give the command for PITR logging at 5:31
> >
> > Then i accidently delete at say 9pm
> >
> > Then am i expected to :
> > 1. restore the snapshot of 5:00 pm
> > 2. ROLL FORWARD using PITR log till 8:59 or so
> > using some special command ?
> >
> > In that case what happens to the transactions from 5:00pm
> > to 5:30 pm which were not captured in the pg_dump nor in the
> > PITR log?
>
> You have to start PITR logging probably before the pg_dump so there will
> be some overlap, but the system will know how to handle it.
Thanks for this discussion.
I hope to read more abt in in 7.4 Docs.
My congratulations and regds to the PostgreSQL
developers for making it what it is today !
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Point in Time Recovery
@ 2004-07-05 20:45 Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-05 20:45 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
Taking advantage of the freeze bubble allowed us... there are some last
minute features to add.
Summarising earlier thoughts, with some detailed digging and design from
myself in last few days - we're now in a position to add Point-in-Time
Recovery, on top of whats been achieved.
The target for the last record to recover to can be specified in 2 ways:
- by transactionId - not that useful, unless you have a means of
identifying what has happened from the log, then using that info to
specify how to recover - coming later - not in next few days :(
- by time - but the time stamp on each xlog record only specifies to the
second, which could easily be 10 or more commits (we hope....)
Should we use a different datatype than time_t for the commit timestamp,
one that offers more fine grained differentiation between checkpoints?
If we did, would that be portable?
Suggestions welcome, because I know very little of the details of
various *nix systems and win* on that topic.
Only COMMIT and ABORT records have timestamps, allowing us to circumvent
any discussion about partial transaction recovery and nested
transactions.
When we do recover, stopping at the timestamp is just half the battle.
We need to leave the xlog in which we stop in a state from which we can
enter production smoothly and cleanly. To do this, we could:
- when we stop, keep reading records until EOF, just don't apply them.
When we write a checkpoint at end of recovery, the unapplied
transactions are buried alive, never to return.
- stop where we stop, then force zeros to EOF, so that no possible
record remains of previous transactions.
I'm tempted by the first plan, because it is more straightforward and
stands much less chance of me introducing 50 wierd bugs just before
close.
Also, I think it is straightforward to introduce control file duplexing,
with a second copy stored and maintained in the pg_xlog directory. This
would provide additional protection for pg_control, which takes on more
importance now that archive recovery is working. pg_xlog is a natural
home, since on busy systems it's on its own disk away from everything
else, ensuring that at least one copy survives. I can't see a downside
to that, but others might... We can introduce user specifiable
duplexing, in later releases.
For later, I envisage an off-line utility that can be used to inspect
xlog records. This could provide a number of features:
- validate archived xlogs, to check they are sound.
- produce summary reports, to allow identification of transactionIds and
the effects of particular transactions
- performance analysis to allow decisions to be made about whether group
commit features could be utilised to good effect
(Not now...)
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-05 21:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-05 21:46 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> Should we use a different datatype than time_t for the commit timestamp,
> one that offers more fine grained differentiation between checkpoints?
Pretty much everybody supports gettimeofday() (time_t and separate
integer microseconds); you might as well use that. Note that the actual
resolution is not necessarily microseconds, and it'd still not be
certain that successive commits have distinct timestamps --- so maybe
this refinement would be pointless. You'll still have to design a user
interface that allows selection without the assumption of distinct
timestamps.
> - when we stop, keep reading records until EOF, just don't apply them.
> When we write a checkpoint at end of recovery, the unapplied
> transactions are buried alive, never to return.
> - stop where we stop, then force zeros to EOF, so that no possible
> record remains of previous transactions.
Go with plan B; it's best not to destroy data (what if you chose the
wrong restart point the first time)?
Actually this now reminds me of a discussion I had with Patrick
Macdonald some time ago. The DB2 practice in this connection is that
you *never* overwrite existing logfile data when recovering. Instead
you start a brand new xlog segment file, which is given a new "branch
number" so it can be distinguished from the future-time xlog segments
that you chose not to apply. I don't recall what the DB2 terminology
was exactly --- not "branch number" I don't think --- but anyway the
idea is that when you restart the database after an incomplete recovery,
you are now in a sort of parallel universe that has its own history
after the branch point (PITR stop point). You need to be able to
distinguish archived log segments of this parallel universe from those
of previous and subsequent incarnations. I'm not sure whether Vadim
intended our StartUpID to serve this purpose, but it could perhaps be
used that way, if we reflected it in the WAL file names.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-05 23:11 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-05 23:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Should we use a different datatype than time_t for the commit timestamp,
> > one that offers more fine grained differentiation between checkpoints?
>
> Pretty much everybody supports gettimeofday() (time_t and separate
> integer microseconds); you might as well use that. Note that the actual
> resolution is not necessarily microseconds, and it'd still not be
> certain that successive commits have distinct timestamps --- so maybe
> this refinement would be pointless. You'll still have to design a user
> interface that allows selection without the assumption of distinct
> timestamps.
Well, I agree, though without the desired-for UI now, I think some finer
grained mechanism would be good. This means extending the xlog commit
record by a couple of bytes...OK, lets live a little.
> > - when we stop, keep reading records until EOF, just don't apply them.
> > When we write a checkpoint at end of recovery, the unapplied
> > transactions are buried alive, never to return.
> > - stop where we stop, then force zeros to EOF, so that no possible
> > record remains of previous transactions.
>
> Go with plan B; it's best not to destroy data (what if you chose the
> wrong restart point the first time)?
>
eh? Which way round? The second plan was the one where I would destroy
data by overwriting it, thats exactly why I preferred the first.
Actually, the files are always copied from archive, so re-recovery is
always an available option in the design thats been implemented.
No matter...
> Actually this now reminds me of a discussion I had with Patrick
> Macdonald some time ago. The DB2 practice in this connection is that
> you *never* overwrite existing logfile data when recovering. Instead
> you start a brand new xlog segment file,
Now thats a much better plan...I suppose I just have to rack up the
recovery pointer to the first record on the first page of a new xlog
file, similar to first plan, but just fast-forwarding rather than
forwarding.
My only issue was to do with the secondary Checkpoint marker, which is
always reset to the place you just restored FROM, when you complete a
recovery. That could lead to a situation where you recover, then before
next checkpoint, fail and lose last checkpoint marker, then crash
recover from previous checkpoint (again), but this time replay the
records you were careful to avoid.
> which is given a new "branch
> number" so it can be distinguished from the future-time xlog segments
> that you chose not to apply. I don't recall what the DB2 terminology
> was exactly --- not "branch number" I don't think --- but anyway the
> idea is that when you restart the database after an incomplete recovery,
> you are now in a sort of parallel universe that has its own history
> after the branch point (PITR stop point). You need to be able to
> distinguish archived log segments of this parallel universe from those
> of previous and subsequent incarnations.
Thats a good idea, if only because you so easily screw your test data
during multiple recovery situations. But if its good during testing, it
must be good in production too...since you may well perform
recovery...run for a while, then discover that you got it wrong first
time, then need to re-recover again. I already added that to my list of
gotchas and that would solve it.
I was going to say hats off to the Blue-hued ones, when I remembered
this little gem from last year
http://www.danskebank.com/link/ITreport20030403uk/$file/ITreport20030403uk.pdf
> I'm not sure whether Vadim
> intended our StartUpID to serve this purpose, but it could perhaps be
> used that way, if we reflected it in the WAL file names.
>
Well, I'm not sure about StartUpId....but certainly the high 2 bytes of
LogId looks pretty certain never to be anything but zeros. You have 2.4
x 10^14...which is 9,000 years at 1000 log file/sec
We could use the scheme you descibe:
add xFFFF to the logid every time you complete an archive recovery...so
the log files look like 0001000000000CE3 after youve recovered a load of
files that look like 0000000000000CE3
If you used StartUpID directly, you might just run out....but its very
unlikely you would ever perform 65000 recovery situations - unless
you've run the <expletive> code as often as I have :(.
Doing that also means we don't have to work out how to do that with
StartUpID. Of course, altering the length and makeup of the xlog files
is possible too, but that will cause other stuff to stop working....
[We'll have to give this a no-SciFi name, unless we want to make
in-roads into the Dr.Who fanbase :) Don't get them started. Better
still, dont give it a name at all.]
I'll sleep on that lot.
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-06 19:00 Richard Huxton <dev@archonet.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Richard Huxton @ 2004-07-06 19:00 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
Simon Riggs wrote:
> On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
>
>>Simon Riggs <simon@2ndquadrant.com> writes:
>>
>>>Should we use a different datatype than time_t for the commit timestamp,
>>>one that offers more fine grained differentiation between checkpoints?
>>
>>Pretty much everybody supports gettimeofday() (time_t and separate
>>integer microseconds); you might as well use that. Note that the actual
>>resolution is not necessarily microseconds, and it'd still not be
>>certain that successive commits have distinct timestamps --- so maybe
>>this refinement would be pointless. You'll still have to design a user
>>interface that allows selection without the assumption of distinct
>>timestamps.
>
>
> Well, I agree, though without the desired-for UI now, I think some finer
> grained mechanism would be good. This means extending the xlog commit
> record by a couple of bytes...OK, lets live a little.
At the risk of irritating people, I'll repeat what I suggested a few
weeks ago...
Add a table: pg_pitr_checkpt (pitr_id SERIAL, pitr_ts timestamptz,
pitr_comment text)
Let the user insert rows in transactions as desired. Let them stop the
restore when a specific (pitr_ts,pitr_comment) gets inserted (or on
pitr_id if they record it).
IMHO time is seldom relevant, event boundaries are.
If you want to add special syntax for this, fine. If not, an INSERT
statement is a convenient way to do this anyway.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-06 21:39 Simon Riggs <simon@2ndquadrant.com>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-06 21:39 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
On Tue, 2004-07-06 at 20:00, Richard Huxton wrote:
> Simon Riggs wrote:
> > On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
> >
> >>Simon Riggs <simon@2ndquadrant.com> writes:
> >>
> >>>Should we use a different datatype than time_t for the commit timestamp,
> >>>one that offers more fine grained differentiation between checkpoints?
> >>
> >>Pretty much everybody supports gettimeofday() (time_t and separate
> >>integer microseconds); you might as well use that. Note that the actual
> >>resolution is not necessarily microseconds, and it'd still not be
> >>certain that successive commits have distinct timestamps --- so maybe
> >>this refinement would be pointless. You'll still have to design a user
> >>interface that allows selection without the assumption of distinct
> >>timestamps.
> >
> >
> > Well, I agree, though without the desired-for UI now, I think some finer
> > grained mechanism would be good. This means extending the xlog commit
> > record by a couple of bytes...OK, lets live a little.
>
> At the risk of irritating people, I'll repeat what I suggested a few
> weeks ago...
>
All feedback is good. Thanks.
> Add a table: pg_pitr_checkpt (pitr_id SERIAL, pitr_ts timestamptz,
> pitr_comment text)
> Let the user insert rows in transactions as desired. Let them stop the
> restore when a specific (pitr_ts,pitr_comment) gets inserted (or on
> pitr_id if they record it).
>
It's a good plan, but the recovery is currently offline recovery and no
SQL is possible. So no way to insert, no way to access tables until
recovery completes. I like that plan and probably would have used it if
it was viable.
> IMHO time is seldom relevant, event boundaries are.
>
Agreed, but time is the universally agreed way of describing two events
as being simultaneous. No other way to say "recover to the point when
the message queue went wild".
As of last post to Andreas, I've said I'll not bother changing the
granularity of the timestamp.
> If you want to add special syntax for this, fine. If not, an INSERT
> statement is a convenient way to do this anyway.
The special syntax isn't hugely important - I did suggest a kind of
SQL-like syntax previously, but thats gone now. Invoking recovery via a
command file IS, so we are able to tell the system its not in crash
recovery AND that when you've finished I want you to respond to crashes
without re-entering archive recovery.
Thanks for your comments. I'm not making this more complex than needs
be; in fact much of the code is very simple - its just the planning
that's complex.
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-06 21:40 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-06 21:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; mascarm@mascari.com; ZeugswetterA@spardat.at; +Cc: pgsql-hackers@postgresql.org
On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > - when we stop, keep reading records until EOF, just don't apply them.
> > When we write a checkpoint at end of recovery, the unapplied
> > transactions are buried alive, never to return.
> > - stop where we stop, then force zeros to EOF, so that no possible
> > record remains of previous transactions.
>
> Go with plan B; it's best not to destroy data (what if you chose the
> wrong restart point the first time)?
>
> Actually this now reminds me of a discussion I had with Patrick
> Macdonald some time ago. The DB2 practice in this connection is that
> you *never* overwrite existing logfile data when recovering. Instead
> you start a brand new xlog segment file, which is given a new "branch
> number" so it can be distinguished from the future-time xlog segments
> that you chose not to apply. I don't recall what the DB2 terminology
> was exactly --- not "branch number" I don't think --- but anyway the
> idea is that when you restart the database after an incomplete recovery,
> you are now in a sort of parallel universe that has its own history
> after the branch point (PITR stop point). You need to be able to
> distinguish archived log segments of this parallel universe from those
> of previous and subsequent incarnations. I'm not sure whether Vadim
> intended our StartUpID to serve this purpose, but it could perhaps be
> used that way, if we reflected it in the WAL file names.
>
Some more thoughts...focusing on the what do we do after we've finished
recovering. The objectives, as I see them, are to put the system into a
state, that preserves these features:
1. we never overwrite files, in case we want to re-run recovery
2. we never write files that MIGHT have been written previously
3. we need to ensure that any xlog records skipped at admins request (in
PITR mode) are never in a position to be re-applied to this timeline.
4. ensure we can re-recover, if we need to, without further problems
Tom's concept above, I'm going to call timelines. A timeline is the
sequence of logs created by the execution of a server. If you recover
the database, you create a new timeline. [This is because, if you've
invoked PITR you absolutely definitely want log records written to, say,
xlog15 to be different to those that were written to xlog15 in a
previous timeline that you have chosen not to reapply.]
Objective (1) is complex.
When we are restoring, we always start with archived copies of the xlog,
to make sure we don't finish too soon. We roll forward until we either
reach PITR stop point, or we hit end of archived logs. If we hit end of
logs on archive, then we switch to a local copy, if one exists that is
higher than those, we carry on rolling forward until either we reach
PITR stop point, or we hit end of that log. (Hopefully, there isn't more
than one local xlog higher than the archive, but its possible).
If we are rolling forward on local copies, then they are our only
copies. We'd really like to archive them ASAP, but the archiver's not
running yet - we don't want to force that situation in case the archive
device (say a tape) is the one being used to recover right now. So we
write an archive_status of .ready for that file, ensuring that the
checkpoint won't remove it until it gets copied to archive, whenever
that starts working again. Objective (1) met.
When we have finished recovering we:
- create a new xlog at the start of a new ++timeline
- copy the last applied xlog record to it as the first record
- set the record pointer so that it matches
That way, when we come up and begin running, we never overwrite files
that might have been written previously. Objective (2) met.
We do the other stuff because recovery finishes up by pointing to the
last applied record...which is what was causing all of this extra work
in the first place.
At this point, we also reset the secondary checkpoint record, so that
should recovery be required again before next checkpoint AND the
shutdown checkpoint record written after recovery completes is
wrong/damaged, the recovery will not autorewind back past the PITR stop
point and attempt to recover the records we have just tried so hard to
reverse/ignore. Objective (3) met. (Clearly, that situation seems
unlikely, but I feel we must deal with it...a newly restored system is
actually very fragile, so a crash again within 3 minutes or so is very
commonplace, as far as these things go).
Should we need to re-recover, we can do so because the new timeline
xlogs are further forward than the old timeline, so never get seen by
any processes (all of which look backwards). Re-recovery is possible
without problems, if required. This means you're a lot safer from some
of the mistakes you might of made, such as deciding you need to go into
recovery, then realising it wasn't required (or some other painful
flapping as goes on in computer rooms at 3am).
How do we implement timelines?
The main presumption in the code is that xlogs are sequential. That has
two effects:
1. during recovery, we try to open the "next" xlog by adding one to the
numbers and then looking for that file
2. during checkpoint, we look for filenames less than the current
checkpoint marker
Creating a timeline by adding a larger number to LogId allows us to
prevent (1) from working, yet without breaking (2).
Well, Tom does seem to have something with regard to StartUpIds. I feel
it is easier to force a new timeline by adding a very large number to
the LogId IF, and only if, we have performed an archive recovery. That
way, we do not change at all the behaviour of the system for people that
choose not to implement archive_mode.
Should we implement timelines?
Yes, I think we should. I've already hit the problems that timelines
solve in my testing and so that means they'll be hit when you don't need
the hassle.
Comments much appreciated, assuming you read this far...
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 11:38 Simon Riggs <simon@2ndquadrant.com>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 11:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Alvaro Herrera <alvherre@dcc.uchile.cl>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: mascarm@mascari.com; ZeugswetterA@spardat.at; pgsql-hackers@postgresql.org
On Tue, 2004-07-06 at 22:40, Simon Riggs wrote:
> On Mon, 2004-07-05 at 22:46, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
>
> > > - when we stop, keep reading records until EOF, just don't apply them.
> > > When we write a checkpoint at end of recovery, the unapplied
> > > transactions are buried alive, never to return.
> > > - stop where we stop, then force zeros to EOF, so that no possible
> > > record remains of previous transactions.
> >
> > Go with plan B; it's best not to destroy data (what if you chose the
> > wrong restart point the first time)?
> >
> > Actually this now reminds me of a discussion I had with Patrick
> > Macdonald some time ago. The DB2 practice in this connection is that
> > you *never* overwrite existing logfile data when recovering. Instead
> > you start a brand new xlog segment file, which is given a new "branch
> > number" so it can be distinguished from the future-time xlog segments
> > that you chose not to apply. I don't recall what the DB2 terminology
> > was exactly --- not "branch number" I don't think --- but anyway the
> > idea is that when you restart the database after an incomplete recovery,
> > you are now in a sort of parallel universe that has its own history
> > after the branch point (PITR stop point). You need to be able to
> > distinguish archived log segments of this parallel universe from those
> > of previous and subsequent incarnations. I'm not sure whether Vadim
> > intended our StartUpID to serve this purpose, but it could perhaps be
> > used that way, if we reflected it in the WAL file names.
> >
>
> Some more thoughts...focusing on the what do we do after we've finished
> recovering. The objectives, as I see them, are to put the system into a
> state, that preserves these features:
> 1. we never overwrite files, in case we want to re-run recovery
> 2. we never write files that MIGHT have been written previously
> 3. we need to ensure that any xlog records skipped at admins request (in
> PITR mode) are never in a position to be re-applied to this timeline.
> 4. ensure we can re-recover, if we need to, without further problems
>
> Tom's concept above, I'm going to call timelines. A timeline is the
> sequence of logs created by the execution of a server. If you recover
> the database, you create a new timeline. [This is because, if you've
> invoked PITR you absolutely definitely want log records written to, say,
> xlog15 to be different to those that were written to xlog15 in a
> previous timeline that you have chosen not to reapply.]
>
> Objective (1) is complex.
> When we are restoring, we always start with archived copies of the xlog,
> to make sure we don't finish too soon. We roll forward until we either
> reach PITR stop point, or we hit end of archived logs. If we hit end of
> logs on archive, then we switch to a local copy, if one exists that is
> higher than those, we carry on rolling forward until either we reach
> PITR stop point, or we hit end of that log. (Hopefully, there isn't more
> than one local xlog higher than the archive, but its possible).
> If we are rolling forward on local copies, then they are our only
> copies. We'd really like to archive them ASAP, but the archiver's not
> running yet - we don't want to force that situation in case the archive
> device (say a tape) is the one being used to recover right now. So we
> write an archive_status of .ready for that file, ensuring that the
> checkpoint won't remove it until it gets copied to archive, whenever
> that starts working again. Objective (1) met.
>
> When we have finished recovering we:
> - create a new xlog at the start of a new ++timeline
> - copy the last applied xlog record to it as the first record
> - set the record pointer so that it matches
> That way, when we come up and begin running, we never overwrite files
> that might have been written previously. Objective (2) met.
> We do the other stuff because recovery finishes up by pointing to the
> last applied record...which is what was causing all of this extra work
> in the first place.
>
> At this point, we also reset the secondary checkpoint record, so that
> should recovery be required again before next checkpoint AND the
> shutdown checkpoint record written after recovery completes is
> wrong/damaged, the recovery will not autorewind back past the PITR stop
> point and attempt to recover the records we have just tried so hard to
> reverse/ignore. Objective (3) met. (Clearly, that situation seems
> unlikely, but I feel we must deal with it...a newly restored system is
> actually very fragile, so a crash again within 3 minutes or so is very
> commonplace, as far as these things go).
>
> Should we need to re-recover, we can do so because the new timeline
> xlogs are further forward than the old timeline, so never get seen by
> any processes (all of which look backwards). Re-recovery is possible
> without problems, if required. This means you're a lot safer from some
> of the mistakes you might of made, such as deciding you need to go into
> recovery, then realising it wasn't required (or some other painful
> flapping as goes on in computer rooms at 3am).
>
> How do we implement timelines?
> The main presumption in the code is that xlogs are sequential. That has
> two effects:
> 1. during recovery, we try to open the "next" xlog by adding one to the
> numbers and then looking for that file
> 2. during checkpoint, we look for filenames less than the current
> checkpoint marker
> Creating a timeline by adding a larger number to LogId allows us to
> prevent (1) from working, yet without breaking (2).
> Well, Tom does seem to have something with regard to StartUpIds. I feel
> it is easier to force a new timeline by adding a very large number to
> the LogId IF, and only if, we have performed an archive recovery. That
> way, we do not change at all the behaviour of the system for people that
> choose not to implement archive_mode.
>
> Should we implement timelines?
> Yes, I think we should. I've already hit the problems that timelines
> solve in my testing and so that means they'll be hit when you don't need
> the hassle.
>
I'm still wrestling with the cleanup-after-stopping-at-point-in-time
code and have some important conclusions.
Moving forward on a timeline is somewhat tricky for xlogs, as shown
above,...but...
My earlier treatment seems to have neglected to include the clog also.
If we stop before end of log, then we also have potentially many (though
presumably at least one) committed transactions that we do not want to
be told about ever again.
The starting a new timeline thought works for xlogs, but not for clogs.
No matter how far you go into the future, there is a small (yet
vanishing) possibility that there is a yet undiscovered committed
transaction in the future. (Because transactions are ordered in the clog
because xids are assigned sequentially at txn start, but not ordered in
the xlog where they are recorded in the order the txns complete).
Please tell me that we can ignore the state of the clog, but I think we
can't - if a new xid re-used a previous xid that had committed AND then
we crashed...we would have inconsistent data. Unless we physically write
zeros to clog for every begin transaction after a recovery...err, no...
The only recourse that I can see is to "truncate the future" of the
clog, which would mean:
- keeping track of the highest xid provided by any record from the xlog,
in xact.c, xact_redo
- using that xid to write zeros to the clog after this point until EOF
- drop any clog segment files past the new "high" segment
- no idea how that effects NT or not...
The timeline idea works for xlog because once we've applied the xlog
records and checkpointed, we can discard the xlog records. We can't do
that with clog records (unless we followed recovery with a vacuum full -
which is possible, but not hugely desirable) - though this doesn't solve
the issue that xlog records don't have any prescribed position in the
file, clog records do.
Right now, I don't know where to start with the clog code and the
opportunity for code-overlap with NT seems way high. These problems can
be conquered, given time and "given enough eyeballs".
I'm all ears for some bright ideas...but I'm getting pretty wary that we
may introduce some unintended features if we try to get this stabilised
within two weeks. My current conclusion is: lets commit archive recovery
in this release, then wait until next dot release for full recovery
target features. We've hit all the features which were a priority and
the fundamental architecture is there, so i think it is time to be happy
with what we've got, for now.
Comments, please....remembering that I'd love it if I've missed
something that simplifies the task. Fire away.
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 14:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-13 14:29 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; Bruce Momjian <pgman@candle.pha.pa.us>; mascarm@mascari.com; ZeugswetterA@spardat.at; pgsql-hackers@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> Please tell me that we can ignore the state of the clog,
We can.
The reason that keeping track of timelines is interesting for xlog is
simply to take pity on the poor DBA who needs to distinguish the various
archived xlog files he's got laying about, and so that we can detect
errors like supplying inconsistent sets of xlog segments during restore.
This does not apply to clog because it's not archived. It's no more
than a data file. If you think you have trouble recreating clog then
you have the same issues recreating data files.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 20:25 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 20:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Tue, 2004-07-13 at 15:29, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Please tell me that we can ignore the state of the clog,
>
> We can.
>
In general, you are of course correct.
> The reason that keeping track of timelines is interesting for xlog is
> simply to take pity on the poor DBA who needs to distinguish the various
> archived xlog files he's got laying about, and so that we can detect
> errors like supplying inconsistent sets of xlog segments during restore.
>
> This does not apply to clog because it's not archived. It's no more
> than a data file. If you think you have trouble recreating clog then
> you have the same issues recreating data files.
I'm getting carried away with the improbable....but this is the rather
strange, but possible scenario I foresee:
A sequence of times...
1. We start archiving xlogs
2. We take a checkpoint
3. we commit an important transaction
4. We take a backup
5. We take a checkpoint
As stands currently, when we restore the backup, controlfile says that
last checkpoint was at 2, so we rollforward from 2 THRU 4 and continue
on past 5 until end of logs. Normally, end of logs isn't until after
4...
When we specify a recovery target, it is possible to specify the
rollforward to complete just before point 3. So we use the backup taken
at 4 to rollforward to a point in the past (from the backups
perspective). The backup taken at 4 may now have data and clog records
written by bgwriter.
Given that time between checkpoints is likely to be longer than
previously was the case...this becomes a non-zero situation.
I was trying to solve this problem head on, but the best way is to make
sure we never allow ourselves such a muddled situation:
ISTM the way to avoid this is to insist that we always rollforward
through at least one checkpoint to guarantee that this will not occur.
...then I can forget I ever mentioned the ****** clog again.
I'm ignoring this issue for now....whether it exists or not!
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 21:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-13 21:19 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> I'm getting carried away with the improbable....but this is the rather
> strange, but possible scenario I foresee:
> A sequence of times...
> 1. We start archiving xlogs
> 2. We take a checkpoint
> 3. we commit an important transaction
> 4. We take a backup
> 5. We take a checkpoint
> When we specify a recovery target, it is possible to specify the
> rollforward to complete just before point 3.
No, it isn't possible. The recovery *must* proceed at least as far as
wherever the end of the log was at the time the backup was completed.
Otherwise everything is broken, not only clog, because you may have disk
blocks in your backup that postdate where you stopped log replay.
To have a consistent recovery at all, you must replay the log starting
from a checkpoint before the backup began and extending to the time that
the backup finished. You only get to decide where to stop after that
point.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 22:25 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 22:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-hackers@postgresql.org
On Tue, 2004-07-13 at 22:19, Tom Lane wrote:
> To have a consistent recovery at all, you must replay the log starting
> from a checkpoint before the backup began and extending to the time that
> the backup finished. You only get to decide where to stop after that
> point.
>
So the situation is:
- You must only stop recovery at a point in time (in the logs) after the
backup had completed.
No way to enforce that currently, apart from procedurally. Not exactly
frequent, so I think I just document that and move on, eh?
Thanks for your help,
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 22:42 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 2 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-13 22:42 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
Simon Riggs wrote:
> On Tue, 2004-07-13 at 22:19, Tom Lane wrote:
>
> > To have a consistent recovery at all, you must replay the log starting
> > from a checkpoint before the backup began and extending to the time that
> > the backup finished. You only get to decide where to stop after that
> > point.
> >
>
> So the situation is:
> - You must only stop recovery at a point in time (in the logs) after the
> backup had completed.
>
> No way to enforce that currently, apart from procedurally. Not exactly
> frequent, so I think I just document that and move on, eh?
If it happens, could you use your previous full backup and the PITR logs
from before stop stopped logging, and then after? Is there a period
where they could not restore reliably?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 22:52 Simon Riggs <simon@2ndquadrant.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 22:52 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
On Tue, 2004-07-13 at 23:42, Bruce Momjian wrote:
> Simon Riggs wrote:
> > On Tue, 2004-07-13 at 22:19, Tom Lane wrote:
> >
> > > To have a consistent recovery at all, you must replay the log starting
> > > from a checkpoint before the backup began and extending to the time that
> > > the backup finished. You only get to decide where to stop after that
> > > point.
> > >
> >
> > So the situation is:
> > - You must only stop recovery at a point in time (in the logs) after the
> > backup had completed.
> >
> > No way to enforce that currently, apart from procedurally. Not exactly
> > frequent, so I think I just document that and move on, eh?
>
> If it happens, could you use your previous full backup and the PITR logs
> from before stop stopped logging, and then after?
Yes.
> Is there a period
> where they could not restore reliably?
Good question. No is the answer.
The situation is that the backup isn't timestamped with respect to the
logs, so its possible to attempt to use the wrong backup for recovery.
The solution is procedural - make sure you timestamp your backup files,
so you know which ones to recover with...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 22:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-13 22:56 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> So the situation is:
> - You must only stop recovery at a point in time (in the logs) after the
> backup had completed.
Right.
> No way to enforce that currently, apart from procedurally. Not exactly
> frequent, so I think I just document that and move on, eh?
The procedure that generates a backup has got to be responsible for
recording both the start and stop times. If it does not do so then
it's fatally flawed. (Note also that you had better be careful to get
the time as seen on the server machine's clock ... this could be a nasty
gotcha if the backup is run on a different machine, such as an NFS
server.)
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 23:01 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-13 23:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-hackers@postgresql.org
Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > So the situation is:
> > - You must only stop recovery at a point in time (in the logs) after the
> > backup had completed.
>
> Right.
>
> > No way to enforce that currently, apart from procedurally. Not exactly
> > frequent, so I think I just document that and move on, eh?
>
> The procedure that generates a backup has got to be responsible for
> recording both the start and stop times. If it does not do so then
> it's fatally flawed. (Note also that you had better be careful to get
> the time as seen on the server machine's clock ... this could be a nasty
> gotcha if the backup is run on a different machine, such as an NFS
> server.)
OK, but procedurally, how do you correlate the start/stop time of the
tar backup with the WAL numeric file names?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-13 23:13 Simon Riggs <simon@2ndquadrant.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 3 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 23:13 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; pgsql-admin
PITR Patch v5_1 just posted has Point in Time Recovery working....
Still some rough edges....but we really need some testers now to give
this a try and let me know what you think.
Klaus Naumann and Mark Wong are the only [non-committers] to have tried
to run the code (and let me know about it), so please have a look at
[PATCHES] and try it out.
Many thanks,
Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 23:21 Simon Riggs <simon@2ndquadrant.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 23:21 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org
On Wed, 2004-07-14 at 00:01, Bruce Momjian wrote:
> Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.com> writes:
> > > So the situation is:
> > > - You must only stop recovery at a point in time (in the logs) after the
> > > backup had completed.
> >
> > Right.
> >
> > > No way to enforce that currently, apart from procedurally. Not exactly
> > > frequent, so I think I just document that and move on, eh?
> >
> > The procedure that generates a backup has got to be responsible for
> > recording both the start and stop times. If it does not do so then
> > it's fatally flawed. (Note also that you had better be careful to get
> > the time as seen on the server machine's clock ... this could be a nasty
> > gotcha if the backup is run on a different machine, such as an NFS
> > server.)
>
> OK, but procedurally, how do you correlate the start/stop time of the
> tar backup with the WAL numeric file names?
No need. You just correlate the recovery target with the backup file
times. Mostly, you'll only ever use your last backup and won't need to
fuss with the times.
Backup should begin with a CHECKPOINT...then wait for that to complete,
just to make the backup as current as possible.
If you want to start purging your archives of old archived xlogs, you
can use the filedate (assuming you preserved that on your copy to
archive - but even if not, they'll be fairly close).
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 23:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-13 23:28 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-hackers@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> OK, but procedurally, how do you correlate the start/stop time of the
> tar backup with the WAL numeric file names?
Ideally the procedure for making a backup would go something like:
1. Inquire of the server its current time and the WAL position of the
most recent checkpoint record (which is what you really need).
2. Make the backup.
3. Inquire of the server its current time and the current end-of-WAL
position.
4. Record items 1 and 3 along with the backup itself.
I think the current theory was you could fake #1 by copying pg_control
before everything else, but this doesn't really help for step #3, so
it would probably be better to add some server functions to get this
info.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-13 23:54 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-13 23:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-hackers@postgresql.org
On Wed, 2004-07-14 at 00:28, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > OK, but procedurally, how do you correlate the start/stop time of the
> > tar backup with the WAL numeric file names?
>
> Ideally the procedure for making a backup would go something like:
>
> 1. Inquire of the server its current time and the WAL position of the
> most recent checkpoint record (which is what you really need).
>
> 2. Make the backup.
>
> 3. Inquire of the server its current time and the current end-of-WAL
> position.
>
> 4. Record items 1 and 3 along with the backup itself.
>
> I think the current theory was you could fake #1 by copying pg_control
> before everything else, but this doesn't really help for step #3, so
> it would probably be better to add some server functions to get this
> info.
>
err...I think at this point we should review the PITR patch....
The recovery mechanism doesn't rely upon you knowing 1 or 3. The
recovery reads pg_control (from the backup) and then attempts to
de-archive the appropriate xlog segment file and then starts rollforward
from there. Effectively, restore assumes it has access to an infinite
timeline of logs....which clearly isn't the case, but its up to *you* to
check that you have the logs that go with the backups. (Or put another
way, if this sounds hard, buy some software that administers the
procedure for you). That's the mechanism that allows "infinite
recovery".
In brief, the code path is as identical as possible to the current crash
recovery situation...archive recovery restores the files from archive
when they are needed, just as if they had always been in pg_xlog, in a
way that ensures pg_xlog never runs out of space.
Recovery ends when: it reaches the recovery target you specified, or it
runs out of xlogs (first it runs out of archived xlogs, then tries to
find a more recent local copy if there is one).
I think the current theory was you could fake #1 by copying pg_control
> before everything else, but this doesn't really help for step #3, so
> it would probably be better to add some server functions to get this
> info.
Not sure what you mean by "fake"....
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 02:31 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Simon Riggs <simon@2ndquadrant.com>
2 siblings, 1 reply; 111+ messages in thread
From: Christopher Kings-Lynne @ 2004-07-14 02:31 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org, Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; pgsql-admin
Can you give us some suggestions of what kind of stuff to test? Is
there a way we can artificially kill the backend in all sorts of nasty
spots to see if recovery works? Does kill -9 simulate a 'power off'?
Chris
Simon Riggs wrote:
> PITR Patch v5_1 just posted has Point in Time Recovery working....
>
> Still some rough edges....but we really need some testers now to give
> this a try and let me know what you think.
>
> Klaus Naumann and Mark Wong are the only [non-committers] to have tried
> to run the code (and let me know about it), so please have a look at
> [PATCHES] and try it out.
>
> Many thanks,
>
> Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 07:21 Simon Riggs <simon@2ndquadrant.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 4 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-14 07:21 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org, Tom Lane <tgl@sss.pgh.pa.us>; pgsql-hackers@postgresql.org; pgsql-admin
On Wed, 2004-07-14 at 03:31, Christopher Kings-Lynne wrote:
> Can you give us some suggestions of what kind of stuff to test? Is
> there a way we can artificially kill the backend in all sorts of nasty
> spots to see if recovery works? Does kill -9 simulate a 'power off'?
>
I was hoping some fiendish plans would be presented to me...
But please start with "this feels like typical usage" and we'll go from
there...the important thing is to try the first one.
I've not done power off tests, yet. They need to be done just to
check...actually you don't need to do this to test PITR...
We need to exhaustive tests of...
- power off
- scp and cross network copies
- all the permuted recovery options
- archive_mode = off (i.e. current behaviour)
- deliberately incorrectly set options (idiot-proof testing)
I'd love some help assembling a test document with numbered tests...
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 13:20 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-14 13:20 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Bruce Momjian <pgman@candle.pha.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-hackers@postgresql.org; pgsql-admin
Simon Riggs <simon@2ndquadrant.com> writes:
> I've not done power off tests, yet. They need to be done just to
> check...actually you don't need to do this to test PITR...
I agree, power off is not really the point here. What we need to check
into is (a) the mechanics of archiving WAL segments and (b) the
process of restoring given a backup and a bunch of WAL segments.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 15:55 markw@osdl.org
parent: Simon Riggs <simon@2ndquadrant.com>
2 siblings, 1 reply; 111+ messages in thread
From: markw@osdl.org @ 2004-07-14 15:55 UTC (permalink / raw)
To: simon@2ndquadrant.com; +Cc: pgman@candle.pha.pa.us; kn@mgnet.de; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org; pgsql-admin
On 14 Jul, Simon Riggs wrote:
> PITR Patch v5_1 just posted has Point in Time Recovery working....
>
> Still some rough edges....but we really need some testers now to give
> this a try and let me know what you think.
>
> Klaus Naumann and Mark Wong are the only [non-committers] to have tried
> to run the code (and let me know about it), so please have a look at
> [PATCHES] and try it out.
>
> Many thanks,
>
> Simon Riggs
Simon,
I just tried applying the v5_1 patch against the cvs tip today and got a
couple of rejections. I'll copy the patch output here. Let me know if
you want to see the reject files or anything else:
$ patch -p0 < ../../../pitr-v5_1.diff
patching file backend/access/nbtree/nbtsort.c
Hunk #2 FAILED at 221.
1 out of 2 hunks FAILED -- saving rejects to file backend/access/nbtree/nbtsort.c.rej
patching file backend/access/transam/xlog.c
Hunk #11 FAILED at 1802.
Hunk #15 FAILED at 2152.
Hunk #16 FAILED at 2202.
Hunk #21 FAILED at 3450.
Hunk #23 FAILED at 3539.
Hunk #25 FAILED at 3582.
Hunk #26 FAILED at 3833.
Hunk #27 succeeded at 3883 with fuzz 2.
Hunk #28 FAILED at 4446.
Hunk #29 succeeded at 4470 with fuzz 2.
8 out of 29 hunks FAILED -- saving rejects to file backend/access/transam/xlog.c.rej
patching file backend/postmaster/Makefile
patching file backend/postmaster/postmaster.c
Hunk #3 succeeded at 1218 with fuzz 2 (offset 70 lines).
Hunk #4 succeeded at 1827 (offset 70 lines).
Hunk #5 succeeded at 1874 (offset 70 lines).
Hunk #6 succeeded at 1894 (offset 70 lines).
Hunk #7 FAILED at 1985.
Hunk #8 succeeded at 2039 (offset 70 lines).
Hunk #9 succeeded at 2236 (offset 70 lines).
Hunk #10 succeeded at 2996 with fuzz 2 (offset 70 lines).
1 out of 10 hunks FAILED -- saving rejects to file backend/postmaster/postmaster.c.rej
patching file backend/storage/smgr/md.c
Hunk #1 succeeded at 162 with fuzz 2.
patching file backend/utils/misc/guc.c
Hunk #1 succeeded at 342 (offset 9 lines).
Hunk #2 succeeded at 1387 (offset 9 lines).
patching file backend/utils/misc/postgresql.conf.sample
Hunk #1 succeeded at 113 (offset 10 lines).
patching file bin/initdb/initdb.c
patching file include/access/xlog.h
patching file include/storage/pmsignal.h
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 19:33 Simon Riggs <simon@2ndquadrant.com>
parent: markw@osdl.org
0 siblings, 2 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-14 19:33 UTC (permalink / raw)
To: markw@osdl.org; +Cc: pgman@candle.pha.pa.us; kn@mgnet.de; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org; pgsql-admin
On Wed, 2004-07-14 at 16:55, markw@osdl.org wrote:
> On 14 Jul, Simon Riggs wrote:
> > PITR Patch v5_1 just posted has Point in Time Recovery working....
> >
> > Still some rough edges....but we really need some testers now to give
> > this a try and let me know what you think.
> >
> > Klaus Naumann and Mark Wong are the only [non-committers] to have tried
> > to run the code (and let me know about it), so please have a look at
> > [PATCHES] and try it out.
> >
> I just tried applying the v5_1 patch against the cvs tip today and got a
> couple of rejections. I'll copy the patch output here. Let me know if
> you want to see the reject files or anything else:
>
I'm on it. Sorry 'bout that all - midnight fingers.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-14 22:56 Simon Riggs <simon@2ndquadrant.com>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 2 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-14 22:56 UTC (permalink / raw)
To: markw@osdl.org; +Cc: pgman@candle.pha.pa.us; kn@mgnet.de; tgl@sss.pgh.pa.us; pgsql-patches@postgresql.org
On Wed, 2004-07-14 at 20:33, Simon Riggs wrote:
> On Wed, 2004-07-14 at 16:55, markw@osdl.org wrote:
> > On 14 Jul, Simon Riggs wrote:
> > > PITR Patch v5_1 just posted has Point in Time Recovery working....
> > >
> > > Still some rough edges....but we really need some testers now to give
> > > this a try and let me know what you think.
> > >
> > > Klaus Naumann and Mark Wong are the only [non-committers] to have tried
> > > to run the code (and let me know about it), so please have a look at
> > > [PATCHES] and try it out.
> > >
>
> > I just tried applying the v5_1 patch against the cvs tip today and got a
> > couple of rejections. I'll copy the patch output here. Let me know if
> > you want to see the reject files or anything else:
> >
>
> I'm on it. Sorry 'bout that all - midnight fingers.
Latest version, pitr_v5_2.patch...
- Updated to cvs tip
- Additional tip changes located and patched
- Full re-test of both recover to point in time and recover to xid
- 2 additional bug fixes
- corrected recovery.conf sample
- Patch test
- Patch manually inspected
(pgarch.c, pgarch.h and README identical to previous post)
Go for it...
Best regards, Simon
Attachments:
pitr_v5_2.patch (text/x-patch, 57.9K) download
Index: backend/access/nbtree/nbtsort.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/access/nbtree/nbtsort.c,v
retrieving revision 1.83
diff -c -r1.83 nbtsort.c
*** backend/access/nbtree/nbtsort.c 11 Jul 2004 18:01:45 -0000 1.83
--- backend/access/nbtree/nbtsort.c 14 Jul 2004 22:41:19 -0000
***************
*** 67,72 ****
--- 67,73 ----
#include "miscadmin.h"
#include "storage/smgr.h"
#include "utils/tuplesort.h"
+ #include "access/xlog.h"
/*
***************
*** 220,235 ****
wstate.index = btspool->index;
/*
! * We need to log index creation in WAL iff WAL archiving is enabled
* AND it's not a temp index.
- *
- * XXX when WAL archiving is actually supported, this test will likely
- * need to change; and the hardwired extern is cruddy anyway ...
*/
{
! extern char XLOG_archive_dir[];
!
! wstate.btws_use_wal = XLOG_archive_dir[0] && !wstate.index->rd_istemp;
}
/* reserve the metapage */
wstate.btws_pages_alloced = BTREE_METAPAGE + 1;
--- 221,231 ----
wstate.index = btspool->index;
/*
! * We need to log index creation in WAL if WAL archiving is enabled
* AND it's not a temp index.
*/
{
! wstate.btws_use_wal = XLogArchiveMode && !wstate.index->rd_istemp;
}
/* reserve the metapage */
wstate.btws_pages_alloced = BTREE_METAPAGE + 1;
Index: backend/access/transam/xlog.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/access/transam/xlog.c,v
retrieving revision 1.147
diff -c -r1.147 xlog.c
*** backend/access/transam/xlog.c 1 Jul 2004 00:49:50 -0000 1.147
--- backend/access/transam/xlog.c 14 Jul 2004 22:41:24 -0000
***************
*** 36,47 ****
#include "storage/proc.h"
#include "storage/sinval.h"
#include "storage/spin.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/relcache.h"
#include "miscadmin.h"
-
/*
* This chunk of hackery attempts to determine which file sync methods
* are available on the current platform, and to choose an appropriate
--- 36,47 ----
#include "storage/proc.h"
#include "storage/sinval.h"
#include "storage/spin.h"
+ #include "storage/pmsignal.h"
#include "utils/builtins.h"
#include "utils/guc.h"
#include "utils/relcache.h"
#include "miscadmin.h"
/*
* This chunk of hackery attempts to determine which file sync methods
* are available on the current platform, and to choose an appropriate
***************
*** 85,96 ****
/* User-settable parameters */
int CheckPointSegments = 3;
int XLOGbuffers = 8;
char *XLOG_sync_method = NULL;
const char XLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
- char XLOG_archive_dir[MAXPGPATH]; /* null string means
- * delete 'em */
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
--- 85,98 ----
/* User-settable parameters */
+ bool XLogArchiveMode = false;
+ bool XLogArchiveDEBUG = false;
+ char *XLogArchiveDest;
+ char *XLogArchiveProgram;
int CheckPointSegments = 3;
int XLOGbuffers = 8;
char *XLOG_sync_method = NULL;
const char XLOG_sync_method_default[] = DEFAULT_SYNC_METHOD_STR;
#ifdef WAL_DEBUG
bool XLOG_DEBUG = false;
***************
*** 127,132 ****
--- 129,157 ----
/* Are we doing recovery by reading XLOG? */
bool InRecovery = false;
+ bool InArchiveRecovery = false;
+ bool restoreFromArchive = false;
+ bool InRecoveryCleanup = false;
+
+ FILE *archiveStatusLogFD;
+
+ static void readRecoveryCommandFile(void);
+
+ static char recoveryCommandFile[MAXPGPATH];
+
+ static char recoveryRestoreProgram[MAXPGPATH];
+ static char recoveryTargetTimeStr[MAXPGPATH];
+ static char recoveryTargetXidStr[MAXPGPATH];
+
+ static bool recoveryTarget = true;
+ static bool recoveryTargetExact = false;
+ static bool recoveryTargetInclusive = true;
+
+ static bool recoveryDebugLog = false;
+ static bool recoveryUseLocalTime = true;
+
+ static TransactionId recoveryTargetXid;
+ static time_t recoveryTargetTime;
/*
* MyLastRecPtr points to the start of the last XLOG record inserted by the
***************
*** 393,398 ****
--- 418,424 ----
/* File path names */
static char XLogDir[MAXPGPATH];
+ static char archiveStatusDir[MAXPGPATH];
static char ControlFilePath[MAXPGPATH];
/*
***************
*** 434,439 ****
--- 460,468 ----
static bool InRedo = false;
+ static void XLogArchiveNotify(uint32 log, uint32 seg);
+ static bool XLogArchiveDone(char xlog[MAXPGPATH]);
+ static void XLogArchiveCleanup(char xlog[32]);
static bool AdvanceXLInsertBuffer(void);
static bool WasteXLInsertBuffer(void);
***************
*** 444,449 ****
--- 473,479 ----
bool find_free, int max_advance,
bool use_lock);
static int XLogFileOpen(uint32 log, uint32 seg, bool econt);
+ static void RestoreRecoveryXlog(char *path, uint32 log, uint32 seg);
static void PreallocXlogFiles(XLogRecPtr endptr);
static void MoveOfflineLogs(uint32 log, uint32 seg, XLogRecPtr endptr);
static XLogRecord *ReadRecord(XLogRecPtr *RecPtr, int emode, char *buffer);
***************
*** 455,464 ****
static void ReadControlFile(void);
static char *str_time(time_t tnow);
static void issue_xlog_fsync(void);
- #ifdef WAL_DEBUG
static void xlog_outrec(char *buf, XLogRecord *record);
- #endif
-
/*
* Insert an XLOG record having the specified RMID and info bytes,
--- 485,491 ----
***************
*** 912,917 ****
--- 939,1076 ----
}
/*
+ * XLogArchiveNotify
+ *
+ * Writes an archive notification file to the archiveStatusDir
+ *
+ * The name of the notification file is the message that will be picked up
+ * by the archiver, e.g. we write archiveStatusDir/00000001000000C6.ready
+ * and the archiver then knows to archive XLogDir/00000001000000C6,
+ * then when complete, rename it to archiveStatusDir/00000001000000C6.done
+ *
+ * Called only when in XLogArchiveMode by one backend process
+ * or by Archive Restore in various situations
+ */
+ static void
+ XLogArchiveNotify(uint32 log, uint32 seg)
+ {
+ char archiveStatusLog[32];
+ char archiveStatusLogPath[MAXPGPATH];
+
+ /* insert an otherwise empty file called <XLOG>.ready */
+ sprintf(archiveStatusLog, "%08X%08X.ready", log, seg);
+ snprintf(archiveStatusLogPath, MAXPGPATH, "%s/%s", archiveStatusDir, archiveStatusLog);
+ archiveStatusLogFD = NULL;
+ archiveStatusLogFD = AllocateFile(archiveStatusLogPath, "w");
+ if (archiveStatusLogFD == NULL) {
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write archive_status file \"%s\": %m",
+ archiveStatusLogPath)));
+ return;
+ }
+ FreeFile(archiveStatusLogFD);
+
+ /* the existence of this file is the message to the archiver to identify
+ * which files require archiving
+ *
+ * if this file is written OK, we then signal the ARCHIVER to do its thang
+ */
+
+ if (XLogArchiveDEBUG) {
+ sprintf(archiveStatusLog, "%08X%08X", log, seg);
+ ereport(LOG,
+ (errmsg("transaction log file \"%s\" is now ready to be archived", archiveStatusLog)));
+ }
+
+ /*
+ * don't send a signal if we know that the archiver isn't there (yet)
+ * - the archiver will see the archive_status file as soon as it starts
+ */
+ if (!InArchiveRecovery)
+ SendPostmasterSignal(PMSIGNAL_WAKEN_ARCHIVER);
+
+ return;
+ }
+
+ /*
+ * XLogArchiveDone
+ *
+ * Searches for an archive notification file in archiveStatusDir
+ *
+ * Reads archiveStatusDir looking for a specific filename. If that filename ends with .done
+ * then we know that the filename refers to an xlog in XLogDir that is safe to
+ * recycle. If the filename ends .ready then thats OK, else we have an error.
+ *
+ * Called only when in XLogArchiveMode by bgwriter (when performing checkpoint)
+ *
+ * XXX code is rehacked from an earlier version, so needs streamlining
+ */
+ static bool
+ XLogArchiveDone(char xlog[32])
+ {
+ char archiveStatusLogPath[MAXPGPATH];
+ struct stat tmpStatBuf;
+
+ /* If <XLOG>.done exists then return true
+ */
+ snprintf(archiveStatusLogPath, MAXPGPATH, "%s/%s.done", archiveStatusDir, xlog);
+ if (stat(archiveStatusLogPath, &tmpStatBuf) == 0) {
+ return true;
+ }
+ else
+ {
+ /*
+ * else if <XLOG>.ready exists then return false and issue WARNING
+ * ...this indicates archiver is either not working at all or
+ * if it is, then its just way too slow or incorrectly configured
+ */
+ snprintf(archiveStatusLogPath, MAXPGPATH, "%s/%s.ready", archiveStatusDir, xlog);
+ if (stat(archiveStatusLogPath, &tmpStatBuf) == 0) {
+ ereport(WARNING,
+ (errcode(ERRCODE_WARNING),
+ errmsg("archiving of log file \"%s\" has not yet started",
+ archiveStatusLogPath),
+ errhint("you should check whether your archive_program"
+ "\"%s\" is still functioning correctly", XLogArchiveProgram)));
+ return false;
+ }
+ else
+ {
+ /* else issue a WARNING.... a notification file should exist, except when you
+ * have just enabled archive_mode
+ * assume that error is simply a missing file...
+ */
+ ereport(WARNING,
+ (errcode_for_file_access(),
+ errmsg("archive_status file \"%s\" is missing: %m",
+ archiveStatusLogPath),
+ errhint("if you have just enabled archive_mode you should:"
+ "take a full physical backup, then remove \"%s\"",xlog)));
+ return false;
+ }
+ }
+ }
+
+ /*
+ * XLogArchiveCleanup
+ *
+ * Cleanup an archive notification file for a particular xlog in XLogDir
+ *
+ * Called only when in XLogArchiveMode by bgwriter (when performing checkpoint)
+ *
+ */
+ static void
+ XLogArchiveCleanup(char xlog[32])
+ {
+ char archiveStatusLogPath[MAXPGPATH];
+
+ snprintf(archiveStatusLogPath, MAXPGPATH, "%s/%s.done", archiveStatusDir, xlog);
+ unlink(archiveStatusLogPath);
+
+ }
+
+ /*
* Advance the Insert state to the next buffer page, writing out the next
* buffer if it still contains unwritten data.
*
***************
*** 1260,1265 ****
--- 1419,1430 ----
{
issue_xlog_fsync();
LogwrtResult.Flush = LogwrtResult.Write; /* end of current page */
+
+ /*
+ * write archive_status file to notify that xlog is ready to archive
+ */
+ if (XLogArchiveMode)
+ XLogArchiveNotify(openLogId, openLogSeg);
}
if (ispartialpage)
***************
*** 1619,1625 ****
bool use_lock)
{
char path[MAXPGPATH];
! struct stat stat_buf;
XLogFileName(path, log, seg);
--- 1784,1790 ----
bool use_lock)
{
char path[MAXPGPATH];
! struct stat tmpStatBuf;
XLogFileName(path, log, seg);
***************
*** 1637,1643 ****
else
{
/* Find a free slot to put it in */
! while (stat(path, &stat_buf) == 0)
{
if (--max_advance < 0)
{
--- 1802,1808 ----
else
{
/* Find a free slot to put it in */
! while (stat(path, &tmpStatBuf) == 0)
{
if (--max_advance < 0)
{
***************
*** 1686,1692 ****
char path[MAXPGPATH];
int fd;
! XLogFileName(path, log, seg);
fd = BasicOpenFile(path, O_RDWR | PG_BINARY | XLOG_SYNC_BIT,
S_IRUSR | S_IWUSR);
--- 1851,1859 ----
char path[MAXPGPATH];
int fd;
! XLogFileName(path, log, seg);
! if (restoreFromArchive)
! RestoreRecoveryXlog(path, log, seg);
fd = BasicOpenFile(path, O_RDWR | PG_BINARY | XLOG_SYNC_BIT,
S_IRUSR | S_IWUSR);
***************
*** 1706,1715 ****
--- 1873,2104 ----
path, log, seg)));
}
+ /*
+ * if we switched back to local xlogs after having been
+ * restoring from archive, we need to make sure that the
+ * local files dont get removed by end-of-recovery checkpoint
+ * in case we need to re-run the recovery
+ *
+ * we want to copy these away as soon as possible, so set
+ * the archive_status flag to .ready for them
+ * in case admin isn't cautious enough to have done this anyway
+ */
+ if (InArchiveRecovery && !restoreFromArchive) {
+ snprintf(path, MAXPGPATH, "%s/%08X%08X.ready", archiveStatusDir, log, seg);
+ archiveStatusLogFD = NULL;
+ archiveStatusLogFD = AllocateFile(path, "w");
+ if (archiveStatusLogFD == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write archive_status file \"%s\" ",
+ path)));
+ FreeFile(archiveStatusLogFD);
+ }
+
return (fd);
}
/*
+ * Get next logfile segment to allow recovery
+ *
+ */
+ static void
+ RestoreRecoveryXlog(char *path, uint32 log, uint32 seg)
+ {
+ char tmpXlog[32];
+ char restoreXlog[32];
+ char tmppath[MAXPGPATH];
+ char xlogRestoreCmd[MAXPGPATH];
+ char recoveryXlog[MAXPGPATH];
+ char lastrecoXlog[MAXPGPATH];
+ int rc;
+ struct stat tmpStatBuf;
+ uint32 prevlog, prevseg;
+
+ /*
+ * If a RecoveryFile exists, then we know we are in media recovery
+ * in which case we choose to recover files from archive, even
+ * though a file of that name may already exist in XLogDir
+ *
+ * By doing this, we do not effect crash recovery code path
+ * when we are not in archive_mode
+ *
+ * We take the archived file because, at the point we took backup,
+ * the current xlog will most probably be only partially full,
+ * so we MUST refer to the full version of this file and
+ * NOT the version of the file that exists with the backup.
+ *
+ * We could try to optimize this slightly by checking the local
+ * copy lastchange timestamp against the archived copy,
+ * but we have no API to do this, nor can we guarantee that the
+ * lastchange timestamp was preserved correctly when we copied
+ * to archive. Our aim is robustness, so we elect not to do this.
+ *
+ * Try to copy full xlog from archive to pg_xlog, if it is available
+ * If that succeeds, we pass the RecoveryXlog filepath back for opening
+ * If that fails, then we try to read a local file if one exists.
+ * This allows us to cater for situations where the current xlog
+ * is still available locally and hasn't yet made it to archive.
+ * This could happen if:
+ * - we decide to recover database to undo user data changes
+ * - we have XLogDir on a different disk and the main DataDir drive
+ * fails, leaving us with just the XLogDir
+ *
+ * Notice that we don't actually overwrite any files when we copy back
+ * from archive because the recoveryRestoreProgram may inadvertently
+ * restore inappropriate xlogs, or they may be corrupt, so we may
+ * have to fallback to the segments remaining in current XLogDir later.
+ * The copy-from-archive xlog is always the same, ensuring that we
+ * don't run out of disk space on long recoveries.
+ *
+ * simon@2ndquadrant.com
+ */
+
+ snprintf(recoveryXlog, MAXPGPATH, "%s/RECOVERYXLOG", XLogDir);
+ snprintf(lastrecoXlog, MAXPGPATH, "%s/LASTRECOXLOG", XLogDir);
+
+ if (stat(recoveryXlog, &tmpStatBuf) == 0) {
+ /*
+ * save a copy of the last xlog, before we try to restore
+ * if the restore fails, we will need it to become current xlog
+ */
+ rc = rename(recoveryXlog, lastrecoXlog);
+ if (rc !=0)
+ ereport(WARNING,
+ (errcode_for_file_access(),
+ errmsg("rename failed \"%s\" \"%s\":%m",
+ recoveryXlog, lastrecoXlog)));
+ /*
+ * if it fails, ignore it - we'll create one soon...
+ */
+ }
+
+ /*
+ * Copy xlog from archive_dest to XLogDir
+ */
+ sprintf(restoreXlog, "%08X%08X", log, seg);
+ snprintf(xlogRestoreCmd, MAXPGPATH, recoveryRestoreProgram,
+ XLogArchiveDest, restoreXlog, recoveryXlog);
+ if (XLogArchiveDEBUG)
+ ereport(LOG,
+ (errmsg("restore_program=\"%s\"",
+ xlogRestoreCmd)));
+
+ rc = system(xlogRestoreCmd);
+ if (rc!=0) {
+ /*
+ * remember, we rollforward UNTIL the restore fails
+ * so failure here is just part of the process...
+ * that makes it difficult to determine whether the restore
+ * failed because there isn't an archive to restore, or
+ * because the administrator has specified the restore
+ * program incorrectly...
+ * we could try to restore the testfile that the archiver writes
+ * when it starts up, but the absence of that file isn't
+ * very reliable evidence that the restore itself is broken,
+ * so just trust that the administrator has it correctly,
+ * XXX enhance that later
+ */
+ ereport(LOG,
+ (errmsg("could not restore \"%s\" from archive",
+ restoreXlog)));
+ /*
+ * if an archived file is not available, there might just be
+ * a partially full version of this file still in XLogDir
+ * so return this as the filename to open.
+ * In many recovery scenarios we expect this to fail also...
+ * ...note that once we return to local mode, we could
+ * find more than one xlog there that were not in the archive,
+ * if for example, the archiver had stopped working prior
+ * to the event which led to recovery
+ */
+ snprintf(recoveryXlog, MAXPGPATH, "%s/%s", XLogDir, restoreXlog);
+ restoreFromArchive = false;
+ if (stat(recoveryXlog, &tmpStatBuf) == 0) {
+ ereport(LOG,
+ (errmsg("recovery will continue using local copy of \"%s\"",
+ restoreXlog)));
+ }
+ /*
+ * if this file isn't available, then we need to setup the previous
+ * restored xlog to be the last and current xlog, if it exists
+ * remember: we've been restoring from recoverXlog, which isn't
+ * named the same as the normal xlog chain...
+ * also remember to output a corresponding archive_status of .done
+ */
+ else if ((stat(lastrecoXlog, &tmpStatBuf) == 0) && log==0 && seg > 0) {
+ prevlog = log;
+ prevseg = seg;
+ PrevLogSeg(prevlog, prevseg);
+ XLogFileName(tmppath, prevlog, prevseg);
+ if (XLogArchiveDEBUG)
+ ereport(LOG,
+ (errmsg("moving last restored xlog to \"%s\"", tmppath)));
+ rc = rename(lastrecoXlog, tmppath);
+ if (rc!=0) {
+ ereport(LOG,
+ (errcode_for_file_access(),
+ errmsg("rename failed \"%s\" \"%s\":%m",
+ lastrecoXlog, tmppath)));
+ ereport(PANIC,
+ (errcode_for_file_access(),
+ errmsg("could not open file \"%s\" (log file %u, segment %u): %m",
+ tmpXlog, log, seg)));
+ }
+
+ /*
+ * write out an archive_status file for previous xlog
+ * to allow xlog to be recycled when recovered database
+ * is all up and working again
+ * ...looks wrong, but checkpointer is smart enough
+ * not to archive the current xlog!
+ */
+ sprintf(tmpXlog, "%08X%08X", prevlog, prevseg);
+ snprintf(tmppath, MAXPGPATH, "%s/%s.done", archiveStatusDir, tmpXlog);
+ archiveStatusLogFD = NULL;
+ archiveStatusLogFD = AllocateFile(tmppath, "w");
+ if (archiveStatusLogFD == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write archive_status file \"%s\" ",
+ tmppath)));
+ FreeFile(archiveStatusLogFD);
+ }
+ /*
+ * there is NO else here...we just return the filename
+ * knowing that it isn't there...which then throws the usual error,
+ * will end with a clear message as to why...but not a problem
+ */
+ }
+ else {
+ /* restore success */
+ /*
+ * if backup restored an xlog, yet we didnt use the local copy
+ * because we used the xlog version of that name from the
+ * archive instead, we need to write out an archive_status for
+ * it to show it can be recycled later
+ */
+ XLogFileName(tmppath, log, seg);
+ if (stat(tmppath, &tmpStatBuf) == 0) {
+ sprintf(tmpXlog, "%08X%08X", log, seg);
+ snprintf(tmppath, MAXPGPATH, "%s/%s.done", archiveStatusDir, tmpXlog);
+ archiveStatusLogFD = NULL;
+ archiveStatusLogFD = AllocateFile(tmppath, "w");
+ if (archiveStatusLogFD == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not write archive_status file \"%s\": %m",
+ tmppath)));
+ FreeFile(archiveStatusLogFD);
+ }
+ ereport(LOG,
+ (errmsg("restored log file \"%s\" from archive", restoreXlog)));
+ }
+ strcpy(path, recoveryXlog);
+ return;
+ }
+
+ /*
* Preallocate log files beyond the specified log endpoint, according to
* the XLOGfile user parameter.
*/
***************
*** 1747,1752 ****
--- 2136,2142 ----
struct dirent *xlde;
char lastoff[32];
char path[MAXPGPATH];
+ bool recycle=false;
XLByteToPrevSeg(endptr, endlogId, endlogSeg);
***************
*** 1762,1786 ****
errno = 0;
while ((xlde = readdir(xldir)) != NULL)
{
if (strlen(xlde->d_name) == 16 &&
strspn(xlde->d_name, "0123456789ABCDEF") == 16 &&
strcmp(xlde->d_name, lastoff) <= 0)
{
snprintf(path, MAXPGPATH, "%s/%s", XLogDir, xlde->d_name);
! if (XLOG_archive_dir[0])
! {
! ereport(LOG,
! (errmsg("archiving transaction log file \"%s\"",
! xlde->d_name)));
! elog(WARNING, "archiving log files is not implemented");
! }
! else
{
/*
* Before deleting the file, see if it can be recycled as
* a future log segment. We allow recycling segments up
! * to XLOGfileslop segments beyond the current XLOG
! * location.
*/
if (InstallXLogFileSegment(endlogId, endlogSeg, path,
true, XLOGfileslop,
--- 2152,2194 ----
errno = 0;
while ((xlde = readdir(xldir)) != NULL)
{
+ /* if correct length and alphanumeric makeup of file looks correct
+ * use the alphanumeric sorting property of the filenames to decide
+ * which ones are earlier than the lastoff transaction log
+ * ...maybe should read lastwrite datetime of lastoff, then check that
+ * only files last written earlier than this are removed/recycled
+ */
if (strlen(xlde->d_name) == 16 &&
strspn(xlde->d_name, "0123456789ABCDEF") == 16 &&
strcmp(xlde->d_name, lastoff) <= 0)
{
snprintf(path, MAXPGPATH, "%s/%s", XLogDir, xlde->d_name);
! if (XLogArchiveMode) {
! if (InRecoveryCleanup)
! /*
! * this allows recycling of transaction logs
! * during the shutdown checkpoint at end of recovery
! * - we may have restored logs that were not used
! * in the recovery sequence, and so will not have
! * had an archive_status file written for them.
! * - end-of-recovery doesn't clean up ALL xlogs,
! * which is why we also write archive_status files
! * as well as doing this
! */
! recycle=true;
! else
! recycle=XLogArchiveDone(xlde->d_name);
! }
! else
! recycle=false;
!
! if ( recycle )
{
/*
* Before deleting the file, see if it can be recycled as
* a future log segment. We allow recycling segments up
! * until there are XLOGfileslop segments beyond the
! * current XLOG location, otherwise they are removed.
*/
if (InstallXLogFileSegment(endlogId, endlogSeg, path,
true, XLOGfileslop,
***************
*** 1794,1803 ****
{
/* No need for any more future segments... */
ereport(LOG,
! (errmsg("removing transaction log file \"%s\"",
xlde->d_name)));
unlink(path);
}
}
}
errno = 0;
--- 2202,2212 ----
{
/* No need for any more future segments... */
ereport(LOG,
! (errmsg("too many transaction log files, removing \"%s\"",
xlde->d_name)));
unlink(path);
}
+ XLogArchiveCleanup(xlde->d_name);
}
}
errno = 0;
***************
*** 2255,2260 ****
--- 2664,2670 ----
{
/* Init XLOG file paths */
snprintf(XLogDir, MAXPGPATH, "%s/pg_xlog", DataDir);
+ snprintf(archiveStatusDir, MAXPGPATH, "%s/archive_status", XLogDir);
snprintf(ControlFilePath, MAXPGPATH, "%s/global/pg_control", DataDir);
}
***************
*** 2772,2777 ****
--- 3182,3345 ----
}
/*
+ * read in restore command from recovery.conf
+ *
+ * XXX longer term intention is to expand this to
+ * cater for additional parameters and controls
+ * possibly using a bison grammar to control it
+ */
+ static void
+ readRecoveryCommandFile(void)
+ {
+ FILE *fd;
+ char *tok1 = NULL;
+ char *tok2 = NULL;
+ char cmdline[MAXPGPATH];
+ bool syntaxError = false;
+ struct tm tm;
+
+ fd = AllocateFile(recoveryCommandFile, "r");
+ if (fd == NULL) {
+ ereport(FATAL,
+ (errcode_for_file_access(),
+ errmsg("could not open recovery command file \"%s\"",recoveryCommandFile)));
+ return;
+ }
+
+ ereport(LOG,
+ (errmsg("recovery command file found...")));
+
+ /*
+ * expecting |restore_program = Qcommand stringQ|
+ * e.g. |restore_program = 'cp %s/%s %s'|
+ * where | denote the beginning and end of the string
+ */
+ while (!syntaxError && fgets(cmdline, MAXPGPATH, fd) != NULL) {
+
+ /* ignore # comments ... */
+ if(strncmp(cmdline, "#", 1) == 0) continue;
+
+ tok1 = strtok(cmdline, "'");
+ tok2 = strtok(NULL, "'");
+
+ if (tok1 != NULL && tok2 != NULL) {
+ tok1 = strtok(cmdline, " =");
+ if (tok1 == NULL) {
+ syntaxError = true;
+ continue;
+ }
+ if (strcmp(tok1,"restore_program") == 0) {
+ strcpy(recoveryRestoreProgram, tok2);
+ ereport(LOG,
+ (errmsg("restore_program = %s", recoveryRestoreProgram)));
+ recoveryTarget = false;
+ }
+ else if (strcmp(tok1,"recovery_target_xid") == 0) {
+ /*
+ * if recovery_target_xid specified, then this overrides
+ * recovery_target_time, if it is also set
+ */
+ strcpy(recoveryTargetXidStr, tok2);
+ recoveryTargetXid = (TransactionId) strtoul(tok2, NULL, 10);
+ if (errno == EINVAL || errno == ERANGE ) {
+ ereport(FATAL,
+ (errmsg("recovery_target_xid is not a valid xid \"%s\"",
+ recoveryTargetTimeStr)));
+ }
+ ereport(LOG,(errmsg("recovery_target_xid = %s", recoveryTargetXidStr)));
+ recoveryTarget = true;
+ recoveryTargetExact = true;
+ }
+ else if (strcmp(tok1,"recovery_target_time") == 0) {
+ if (recoveryTargetExact)
+ continue;
+ strcpy(recoveryTargetTimeStr, tok2);
+ recoveryTarget = true;
+ recoveryTargetExact = false;
+ /*
+ * convert the time string given
+ * by the user to the time_t format.
+ * The input string format has to be like
+ * '2004-07-13-16:09:07'.
+ */
+ if (strptime(recoveryTargetTimeStr,
+ "%Y-%m-%d-%H:%M:%S", &tm) == NULL ) {
+ ereport(FATAL,
+ (errmsg("recovery_target_time has a format error \"%s\"",
+ recoveryTargetTimeStr),
+ errhint("correct format is YYYY-MM-DD-hh:mm:ss")));
+ }
+ recoveryTargetTime = mktime(&tm);
+ if (recoveryTargetTime < 0) {
+ ereport(FATAL,
+ (errmsg("recovery_target_time is an invalid time \"%s\"",
+ recoveryTargetTimeStr),
+ errhint("correct format is YYYY-MM-DD-hh:mm:ss")));
+ }
+ ereport(LOG,(errmsg("recovery_target_time = %s",
+ recoveryTargetTimeStr)));
+ }
+ else if (strcmp(tok1,"recovery_target_inclusive") == 0) {
+ /*
+ * does nothing if a recovery_target is not also set
+ */
+ if (strcmp(tok2, "true") == 0)
+ recoveryTargetInclusive = true;
+ else
+ recoveryTargetInclusive = false;
+ if (recoveryTargetInclusive)
+ strcpy(tok2, "true");
+ else
+ strcpy(tok2, "false");
+ ereport(LOG,(errmsg("recovery_target_inclusive = %s", tok2)));
+ }
+ else if (strcmp(tok1,"recovery_debug_log") == 0) {
+ if (strcmp(tok2, "true") == 0)
+ recoveryDebugLog = true;
+ else
+ recoveryDebugLog = false;
+ if (recoveryDebugLog)
+ strcpy(tok2, "true");
+ else
+ strcpy(tok2, "false");
+ ereport(LOG,(errmsg("recovery_debug_log = %s", tok2)));
+ }
+ else if (strcmp(tok1,"use_local_time") == 0) {
+ if (strcmp(tok2, "true") == 0)
+ recoveryUseLocalTime = true;
+ else
+ recoveryUseLocalTime = false;
+ if (recoveryUseLocalTime)
+ strcpy(tok2, "true");
+ else
+ strcpy(tok2, "false");
+ ereport(LOG,(errmsg("use_local_time = %s", tok2)));
+ }
+ else {
+ ereport(LOG,
+ (errmsg("unrecognized parameter \"%s\"", tok1)));
+ syntaxError = true;
+ }
+ }
+ else {
+ ereport(LOG,
+ (errmsg("incorrectly formatted parameter \"%s\"", cmdline),
+ errhint("parameter = 'value'")));
+ syntaxError = true;
+ }
+ }
+
+ FreeFile(fd);
+
+ if (syntaxError) {
+ ereport(FATAL,
+ (errmsg("fatal error in \"%s\"", recoveryCommandFile)));
+ }
+
+ return;
+ }
+
+ /*
* This must be called ONCE during postmaster or standalone-backend startup
*/
void
***************
*** 2787,2792 ****
--- 3355,3361 ----
XLogRecord *record;
char *buffer;
uint32 freespace;
+ struct stat tmpStatBuf;
/* Use malloc() to ensure record buffer is MAXALIGNED */
buffer = (char *) malloc(_INTL_MAXLOGRECSZ);
***************
*** 2833,2838 ****
--- 3402,3437 ----
pg_usleep(60000000L);
#endif
+ /*
+ * Check now for recovery.conf
+ *
+ * if this file exists, it demonstrates the intention of the administrator
+ * to recover this database using archived xlogs
+ *
+ * we do this now because the first xlog is about to be opened for the
+ * first time. We've read the checkpoint pointer from the control file
+ * and we are about to use that to open the xlog it points to, and
+ * will begin rollforward recovery from that point
+ */
+ snprintf(recoveryCommandFile, MAXPGPATH, "%s/recovery.conf", DataDir);
+ if (stat(recoveryCommandFile, &tmpStatBuf) == 0) {
+
+ readRecoveryCommandFile();
+ /*
+ * clearly indicate our state
+ */
+ InArchiveRecovery = true;
+ /*
+ * set initial state for checking transaction logs
+ * this may change if the archive runs dry while still InArchiveRecovery
+ */
+ restoreFromArchive = true;
+
+ ereport(LOG,
+ (errmsg("starting archive recovery")));
+
+ }
+
/*
* Get the last valid checkpoint record. If the latest one according
* to pg_control is broken, try the next-to-last one.
***************
*** 2863,2874 ****
LastRec = RecPtr = checkPointLoc;
memcpy(&checkPoint, XLogRecGetData(record), sizeof(CheckPoint));
wasShutdown = (record->xl_info == XLOG_CHECKPOINT_SHUTDOWN);
!
ereport(LOG,
(errmsg("redo record is at %X/%X; undo record is at %X/%X; shutdown %s",
checkPoint.redo.xlogid, checkPoint.redo.xrecoff,
checkPoint.undo.xlogid, checkPoint.undo.xrecoff,
! wasShutdown ? "TRUE" : "FALSE")));
ereport(LOG,
(errmsg("next transaction ID: %u; next OID: %u",
checkPoint.nextXid, checkPoint.nextOid)));
--- 3462,3481 ----
LastRec = RecPtr = checkPointLoc;
memcpy(&checkPoint, XLogRecGetData(record), sizeof(CheckPoint));
wasShutdown = (record->xl_info == XLOG_CHECKPOINT_SHUTDOWN);
! /*
! * we report the state of the control_file, not the checkpoint, why?
! * wasShutdown refers to whether the last checkpoint was a
! * shutdown checkpoint, NOT whether the database was shutdown
! * correctly according to control file. This distinction is only
! * important InArchiveRecovery, since otherwise we could
! * report that the database was shutdown, when the control file disagrees
! */
ereport(LOG,
(errmsg("redo record is at %X/%X; undo record is at %X/%X; shutdown %s",
checkPoint.redo.xlogid, checkPoint.redo.xrecoff,
checkPoint.undo.xlogid, checkPoint.undo.xrecoff,
! (ControlFile->state == DB_SHUTDOWNED) ? "TRUE" : "FALSE")));
!
ereport(LOG,
(errmsg("next transaction ID: %u; next OID: %u",
checkPoint.nextXid, checkPoint.nextOid)));
***************
*** 2915,2921 ****
/* REDO */
if (InRecovery)
{
! int rmid;
ereport(LOG,
(errmsg("database system was not properly shut down; "
--- 3522,3537 ----
/* REDO */
if (InRecovery)
{
! int rmid;
! char recoveryLogPath[MAXPGPATH];
! bool recoveryContinue = true;
! bool recoveryApply = true;
! int recoveryLogFD = -1;
! char *recbuf = NULL;
! uint8 record_info;
! xl_xact_commit *recordXactCommitData;
! xl_xact_abort *recordXactAbortData;
! time_t recordXtime;
ereport(LOG,
(errmsg("database system was not properly shut down; "
***************
*** 2948,2953 ****
--- 3564,3589 ----
ereport(LOG,
(errmsg("redo starts at %X/%X",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff)));
+ #ifdef WAL_DEBUG
+ if (XLOG_DEBUG)
+ recoveryDebugLog = true;
+ #endif
+ if (XLogArchiveDEBUG)
+ recoveryDebugLog = true;
+
+ if (recoveryDebugLog) {
+ recbuf = (char *) malloc(BLCKSZ);
+ snprintf(recoveryLogPath, MAXPGPATH, "%s/recovery.log", DataDir);
+ unlink(recoveryLogPath);
+ recoveryLogFD = BasicOpenFile(recoveryLogPath, O_RDWR | O_CREAT | O_EXCL,
+ S_IRUSR | S_IWUSR);
+ if (recoveryLogFD < 0)
+ recoveryDebugLog = false;
+ }
+
+ /*
+ * main redo apply loop
+ */
do
{
/* nextXid must be beyond record's xid */
***************
*** 2958,2990 ****
TransactionIdAdvance(ShmemVariableCache->nextXid);
}
! #ifdef WAL_DEBUG
! if (XLOG_DEBUG)
{
! char buf[8192];
!
! sprintf(buf, "REDO @ %X/%X; LSN %X/%X: ",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff,
EndRecPtr.xlogid, EndRecPtr.xrecoff);
! xlog_outrec(buf, record);
! strcat(buf, " - ");
! RmgrTable[record->xl_rmid].rm_desc(buf,
record->xl_info, XLogRecGetData(record));
! elog(LOG, "%s", buf);
}
- #endif
if (record->xl_info & XLR_BKP_BLOCK_MASK)
RestoreBkpBlocks(record, EndRecPtr);
- RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);
- record = ReadRecord(NULL, LOG, buffer);
- } while (record != NULL);
ereport(LOG,
(errmsg("redo done at %X/%X",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff)));
LastRec = ReadRecPtr;
InRedo = false;
}
else
ereport(LOG,
--- 3594,3729 ----
TransactionIdAdvance(ShmemVariableCache->nextXid);
}
! if (recoveryDebugLog && record->xl_rmid == 1)
{
! sprintf(recbuf, "\nREDO %X/%X;LSN %X/%X: ",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff,
EndRecPtr.xlogid, EndRecPtr.xrecoff);
! sprintf(recbuf + strlen(recbuf), "rm %u;info %u ",
! record->xl_rmid, record->xl_info);
! xlog_outrec(recbuf, record);
! strcat(recbuf, "--");
! RmgrTable[record->xl_rmid].rm_desc(recbuf,
record->xl_info, XLogRecGetData(record));
! write(recoveryLogFD, recbuf, strlen(recbuf));
}
if (record->xl_info & XLR_BKP_BLOCK_MASK)
RestoreBkpBlocks(record, EndRecPtr);
+ /*
+ * Have we reached our recovery target?
+ * check whether this is the last record we should apply -
+ * we can only stop at transaction end record types,
+ * COMMIT or ABORT, which are only ever associated
+ * with the XLOG resource manager (1)
+ */
+ if (recoveryTarget == true &&
+ record->xl_rmid == 1) {
+
+ record_info = record->xl_info & ~XLR_INFO_MASK;
+
+ if (record_info == XLOG_XACT_COMMIT ||
+ record_info == XLOG_XACT_ABORT) {
+
+ if (recoveryTargetExact) {
+ /*
+ * there can be only one transaction end record
+ * with this exact transactionid
+ *
+ * when testing for an xid, we MUST test for
+ * equality only, since transactions are numbered
+ * in the order they start, not the order they
+ * complete. A higher numbered xid will complete
+ * before you about 50% of the time...
+ */
+ if (record->xl_xid == recoveryTargetXid) {
+ recoveryContinue = false;
+ recoveryApply = recoveryTargetInclusive;
+ }
+ if (!recoveryContinue) {
+ ereport(LOG,
+ (errmsg("rollforward stopping at commit/abort:"
+ " transactionId %u",
+ record->xl_xid)));
+ }
+ }
+ else {
+ /*
+ * there can be many transactions that
+ * share the same commit time, so
+ * we stop after the last one, if we are
+ * inclusive, or stop at the first one
+ * if we are exclusive
+ *
+ * timestamp is first part of record body
+ */
+ if (record_info == XLOG_XACT_COMMIT) {
+ recordXactCommitData =
+ (xl_xact_commit *) XLogRecGetData(record);
+ recordXtime = recordXactCommitData->xtime;
+ }
+ else {
+ recordXactAbortData =
+ (xl_xact_abort *) XLogRecGetData(record);
+ recordXtime = recordXactAbortData->xtime;
+ }
+
+ if (recoveryTargetInclusive) {
+ if ((unsigned int)(recordXtime) >
+ (unsigned int)(recoveryTargetTime))
+ recoveryContinue = false;
+ /*
+ * because we have already just included the
+ * records that match (==), and we now want
+ * to stop just beyond
+ */
+ recoveryApply = false;
+ }
+ else {
+ if ((unsigned int)(recordXtime) >=
+ (unsigned int)(recoveryTargetTime))
+ recoveryContinue = false;
+ recoveryApply = false;
+ }
+ if (!recoveryContinue) {
+ struct tm *tm = localtime(&recordXtime);
+ ereport(LOG,
+ (errmsg("rollforward stopping at commit/abort:"
+ " time %04u-%02u-%02u %02u:%02u:%02u",
+ tm->tm_year + 1900, tm->tm_mon + 1, tm->tm_mday,
+ tm->tm_hour, tm->tm_min, tm->tm_sec)));
+ }
+ }
+ }
+ }
+
+ if (recoveryContinue || recoveryApply) {
+ RmgrTable[record->xl_rmid].rm_redo(EndRecPtr, record);
+ record = ReadRecord(NULL, LOG, buffer);
+ }
+
+ } while (record != NULL && recoveryContinue);
+ /*
+ * end of main redo apply
+ */
+
+ if (recoveryLogFD >= 0) {
+ close(recoveryLogFD);
+ if (recoveryDebugLog)
+ free(recbuf);
+ }
ereport(LOG,
(errmsg("redo done at %X/%X",
ReadRecPtr.xlogid, ReadRecPtr.xrecoff)));
LastRec = ReadRecPtr;
InRedo = false;
+ if (InArchiveRecovery) {
+ InRecoveryCleanup = true;
+ InArchiveRecovery = false;
+ /* LastRec.xlogid = LastRec.xlogid + TIMELINE_INCREMENT */
+ }
+ Assert(restoreFromArchive == false);
}
else
ereport(LOG,
***************
*** 3106,3118 ****
* Note that we write a shutdown checkpoint. This is correct since
* the records following it will use SUI one more than what is
* shown in the checkpoint's ThisStartUpID.
! *
* In case we had to use the secondary checkpoint, make sure that it
* will still be shown as the secondary checkpoint after this
* CreateCheckPoint operation; we don't want the broken primary
! * checkpoint to become prevCheckPoint...
*/
! ControlFile->checkPoint = checkPointLoc;
CreateCheckPoint(true, true);
/*
--- 3845,3864 ----
* Note that we write a shutdown checkpoint. This is correct since
* the records following it will use SUI one more than what is
* shown in the checkpoint's ThisStartUpID.
! */
! ControlFile->checkPoint = checkPointLoc;
! /*
* In case we had to use the secondary checkpoint, make sure that it
* will still be shown as the secondary checkpoint after this
* CreateCheckPoint operation; we don't want the broken primary
! * checkpoint to become prevCheckPoint...unless we have just
! * performed an archive recovery, in which case reset the
! * secondary checkpoint as well...to avoid going back over
! * old ground
*/
! if (InRecoveryCleanup)
! ControlFile->prevCheckPoint = checkPointLoc;
!
CreateCheckPoint(true, true);
/*
***************
*** 3149,3154 ****
--- 3895,3906 ----
* Okay, we're officially UP.
*/
InRecovery = false;
+ if (InRecoveryCleanup) {
+ unlink(recoveryCommandFile);
+ InRecoveryCleanup = false;
+ ereport(LOG,
+ (errmsg("archive recovery complete")));
+ }
ControlFile->state = DB_IN_PRODUCTION;
ControlFile->time = time(NULL);
***************
*** 3706,3719 ****
strcat(buf, "UNKNOWN");
}
- #ifdef WAL_DEBUG
static void
xlog_outrec(char *buf, XLogRecord *record)
{
int bkpb;
int i;
! sprintf(buf + strlen(buf), "prev %X/%X; xprev %X/%X; xid %u",
record->xl_prev.xlogid, record->xl_prev.xrecoff,
record->xl_xact_prev.xlogid, record->xl_xact_prev.xrecoff,
record->xl_xid);
--- 4458,4470 ----
strcat(buf, "UNKNOWN");
}
static void
xlog_outrec(char *buf, XLogRecord *record)
{
int bkpb;
int i;
! sprintf(buf + strlen(buf), "prv %X/%X;xprv %X/%X;xid %u",
record->xl_prev.xlogid, record->xl_prev.xrecoff,
record->xl_xact_prev.xlogid, record->xl_xact_prev.xrecoff,
record->xl_xid);
***************
*** 3731,3738 ****
sprintf(buf + strlen(buf), ": %s",
RmgrTable[record->xl_rmid].rm_name);
}
- #endif /* WAL_DEBUG */
-
/*
* GUC support
--- 4482,4487 ----
Index: backend/commands/tablecmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v
retrieving revision 1.119
diff -c -r1.119 tablecmds.c
*** backend/commands/tablecmds.c 11 Jul 2004 23:13:53 -0000 1.119
--- backend/commands/tablecmds.c 14 Jul 2004 22:41:46 -0000
***************
*** 5398,5413 ****
FlushRelationBuffers(rel, 0);
/*
! * We need to log the copied data in WAL iff WAL archiving is enabled
* AND it's not a temp rel.
- *
- * XXX when WAL archiving is actually supported, this test will likely
- * need to change; and the hardwired extern is cruddy anyway ...
*/
{
! extern char XLOG_archive_dir[];
!
! use_wal = XLOG_archive_dir[0] && !rel->rd_istemp;
}
nblocks = RelationGetNumberOfBlocks(rel);
--- 5398,5408 ----
FlushRelationBuffers(rel, 0);
/*
! * We need to log the copied data in WAL if WAL archiving is enabled
* AND it's not a temp rel.
*/
{
! use_wal = XLogArchiveMode && !rel->rd_istemp;
}
nblocks = RelationGetNumberOfBlocks(rel);
Index: backend/postmaster/Makefile
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/Makefile,v
retrieving revision 1.15
diff -c -r1.15 Makefile
*** backend/postmaster/Makefile 29 May 2004 22:48:19 -0000 1.15
--- backend/postmaster/Makefile 14 Jul 2004 22:41:46 -0000
***************
*** 12,18 ****
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o
all: SUBSYS.o
--- 12,18 ----
top_builddir = ../../..
include $(top_builddir)/src/Makefile.global
! OBJS = postmaster.o bgwriter.o pgstat.o pgarch.o
all: SUBSYS.o
Index: backend/postmaster/postmaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v
retrieving revision 1.411
diff -c -r1.411 postmaster.c
*** backend/postmaster/postmaster.c 12 Jul 2004 19:14:56 -0000 1.411
--- backend/postmaster/postmaster.c 14 Jul 2004 22:41:48 -0000
***************
*** 117,123 ****
#include "utils/ps_status.h"
#include "bootstrap/bootstrap.h"
#include "pgstat.h"
!
/*
* List of active backends (or child processes anyway; we don't actually
--- 117,123 ----
#include "utils/ps_status.h"
#include "bootstrap/bootstrap.h"
#include "pgstat.h"
! #include "pgarch.h"
/*
* List of active backends (or child processes anyway; we don't actually
***************
*** 198,203 ****
--- 198,204 ----
/* PIDs of special child processes; 0 when not running */
static pid_t StartupPID = 0,
BgWriterPID = 0,
+ PgArchPID = 0,
PgStatPID = 0;
/* Startup/shutdown state */
***************
*** 1217,1222 ****
--- 1218,1228 ----
kill(BgWriterPID, SIGUSR2);
}
+ /* If we have lost the archiver, try to start a new one */
+ if (XLogArchiveMode && PgArchPID == 0 &&
+ StartupPID == 0 && !FatalError && Shutdown == NoShutdown)
+ PgArchPID = pgarch_start();
+
/* If we have lost the stats collector, try to start a new one */
if (PgStatPID == 0 &&
StartupPID == 0 && !FatalError && Shutdown == NoShutdown)
***************
*** 1821,1826 ****
--- 1827,1835 ----
/* Tell pgstat to shut down too; nothing left for it to do */
if (PgStatPID != 0)
kill(PgStatPID, SIGQUIT);
+ /* Tell pgarch to shut down too; nothing left for it to do */
+ if (PgArchPID != 0)
+ kill(PgArchPID, SIGQUIT);
break;
case SIGINT:
***************
*** 1865,1870 ****
--- 1874,1882 ----
/* Tell pgstat to shut down too; nothing left for it to do */
if (PgStatPID != 0)
kill(PgStatPID, SIGQUIT);
+ /* Tell pgarch to shut down too; nothing left for it to do */
+ if (PgArchPID != 0)
+ kill(PgArchPID, SIGQUIT);
break;
case SIGQUIT:
***************
*** 1882,1887 ****
--- 1894,1901 ----
kill(BgWriterPID, SIGQUIT);
if (PgStatPID != 0)
kill(PgStatPID, SIGQUIT);
+ if (PgArchPID != 0)
+ kill(PgArchPID, SIGQUIT);
if (DLGetHead(BackendList))
SignalChildren(SIGQUIT);
ExitPostmaster(0);
***************
*** 1971,1978 ****
*/
if (Shutdown > NoShutdown && BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
! else if (PgStatPID == 0 && Shutdown == NoShutdown)
! PgStatPID = pgstat_start();
continue;
}
--- 1985,1996 ----
*/
if (Shutdown > NoShutdown && BgWriterPID != 0)
kill(BgWriterPID, SIGUSR2);
! else if (Shutdown == NoShutdown) {
! if (PgStatPID == 0)
! PgStatPID = pgstat_start();
! if (PgArchPID == 0)
! PgArchPID = pgarch_start();
! }
continue;
}
***************
*** 2021,2026 ****
--- 2039,2060 ----
}
/*
+ * Was it the archiver? If so, just try to start a new
+ * one; no need to force reset of the rest of the system. (If fail,
+ * we'll try again in future cycles of the main loop.)
+ */
+ if (PgArchPID != 0 && pid == PgArchPID)
+ {
+ PgArchPID = 0;
+ if (exitstatus != 0)
+ LogChildExit(LOG, gettext("archiver process"),
+ pid, exitstatus);
+ if (StartupPID == 0 && !FatalError && Shutdown == NoShutdown)
+ PgArchPID = pgarch_start();
+ continue;
+ }
+
+ /*
* Else do standard backend child cleanup.
*/
CleanupProc(pid, exitstatus);
***************
*** 2202,2207 ****
--- 2236,2252 ----
kill(PgStatPID, SIGQUIT);
}
+ /* Force a power-cycle of the pgarch processes too */
+ /* (Shouldn't be necessary, but just for luck) */
+ if (PgArchPID != 0 && !FatalError)
+ {
+ ereport(DEBUG2,
+ (errmsg_internal("sending %s to process %d",
+ "SIGQUIT",
+ (int) PgArchPID)));
+ kill(PgArchPID, SIGQUIT);
+ }
+
FatalError = true;
}
***************
*** 2951,2956 ****
--- 2996,3013 ----
if (Shutdown <= SmartShutdown)
SignalChildren(SIGUSR1);
}
+
+ if (CheckPostmasterSignal(PMSIGNAL_WAKEN_ARCHIVER))
+ {
+ /*
+ * Send SIGUSR1 to ARCHIVER process, to wake it up and begin
+ * archiving next transaction log file. Backend should only
+ * send if in XLogArchiveMode...
+ */
+ if (XLogArchiveMode && Shutdown == NoShutdown) {
+ kill(PgArchPID,SIGUSR1);
+ }
+ }
PG_SETMASK(&UnBlockSig);
Index: backend/utils/misc/guc.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.219
diff -c -r1.219 guc.c
*** backend/utils/misc/guc.c 12 Jul 2004 02:22:51 -0000 1.219
--- backend/utils/misc/guc.c 14 Jul 2004 22:41:52 -0000
***************
*** 342,347 ****
--- 342,363 ----
static struct config_bool ConfigureNamesBool[] =
{
+ {
+ {"archive_mode", PGC_POSTMASTER, WAL_SETTINGS,
+ gettext_noop("Enable archiving of full transaction log files to a specified archival destination."),
+ NULL
+ },
+ &XLogArchiveMode,
+ false, NULL, NULL
+ },
+ {
+ {"archive_debug", PGC_SIGHUP, WAL_SETTINGS,
+ gettext_noop("Provide debug output for archive activities."),
+ NULL
+ },
+ &XLogArchiveDEBUG,
+ false, NULL, NULL
+ },
{
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
***************
*** 1371,1376 ****
--- 1387,1410 ----
static struct config_string ConfigureNamesString[] =
{
+ {
+ {"archive_dest", PGC_POSTMASTER, WAL_SETTINGS,
+ gettext_noop("Specifies where to archive WAL logs."),
+ gettext_noop("A directory or specific location for archiving transation log files from PostgreSQL")
+ },
+ &XLogArchiveDest,
+ "", NULL, NULL
+ },
+
+ {
+ {"archive_program", PGC_POSTMASTER, WAL_SETTINGS,
+ gettext_noop("Archive program"),
+ gettext_noop("The external program that will be called to execute the archival process")
+ },
+ &XLogArchiveProgram,
+ "", NULL, NULL
+ },
+
{
{"client_encoding", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the client's character set encoding."),
Index: backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.115
diff -c -r1.115 postgresql.conf.sample
*** backend/utils/misc/postgresql.conf.sample 11 Jul 2004 21:48:25 -0000 1.115
--- backend/utils/misc/postgresql.conf.sample 14 Jul 2004 22:41:52 -0000
***************
*** 113,118 ****
--- 113,129 ----
#---------------------------------------------------------------------------
+ # ARCHIVING
+ #---------------------------------------------------------------------------
+
+ # - Settings -
+
+ #archive_mode = true # enables archiving of full txn log files
+ #archive_dest = '/tmp' # specifies destination of archive files
+ #archive_program = 'cp %s %s' # external archiving program command line
+
+
+ #---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
Index: bin/initdb/initdb.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/bin/initdb/initdb.c,v
retrieving revision 1.43
diff -c -r1.43 initdb.c
*** bin/initdb/initdb.c 14 Jul 2004 17:55:10 -0000 1.43
--- bin/initdb/initdb.c 14 Jul 2004 22:41:55 -0000
***************
*** 2023,2029 ****
char *pgdenv; /* PGDATA value got from sent to
* environment */
char *subdirs[] =
! {"global", "pg_xlog", "pg_clog", "pg_subtrans", "base", "base/1", "pg_tblspc"};
progname = get_progname(argv[0]);
set_pglocale_pgservice(argv[0], "initdb");
--- 2023,2030 ----
char *pgdenv; /* PGDATA value got from sent to
* environment */
char *subdirs[] =
!
! {"global", "pg_xlog", "pg_xlog/archive_status", "pg_clog", "pg_subtrans", "base", "base/1", "pg_tblspc"};
progname = get_progname(argv[0]);
set_pglocale_pgservice(argv[0], "initdb");
Index: include/access/xlog.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/access/xlog.h,v
retrieving revision 1.52
diff -c -r1.52 xlog.h
*** include/access/xlog.h 1 Jul 2004 00:51:38 -0000 1.52
--- include/access/xlog.h 14 Jul 2004 22:41:56 -0000
***************
*** 210,215 ****
--- 210,219 ----
extern int XLOGbuffers;
extern char *XLOG_sync_method;
extern const char XLOG_sync_method_default[];
+ extern bool XLogArchiveMode;
+ extern bool XLogArchiveDEBUG;
+ extern char *XLogArchiveDest;
+ extern char *XLogArchiveProgram;
#ifdef WAL_DEBUG
extern bool XLOG_DEBUG;
Index: include/storage/pmsignal.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/storage/pmsignal.h,v
retrieving revision 1.8
diff -c -r1.8 pmsignal.h
*** include/storage/pmsignal.h 29 May 2004 22:48:23 -0000 1.8
--- include/storage/pmsignal.h 14 Jul 2004 22:41:56 -0000
***************
*** 24,29 ****
--- 24,30 ----
{
PMSIGNAL_PASSWORD_CHANGE, /* pg_pwd file has changed */
PMSIGNAL_WAKEN_CHILDREN, /* send a SIGUSR1 signal to all backends */
+ PMSIGNAL_WAKEN_ARCHIVER, /* send a NOTIFY signal to ARCHIVER */
NUM_PMSIGNALS /* Must be last value of enum! */
} PMSignalReason;
recovery.conf.sample (text/plain, 2.3K) download
# -------------------------------
# PostgreSQL recovery config file
# -------------------------------
#
# Edit this file to provide the parameters that PostgreSQL
# needs to perform an archive recovery of a database
#
# This file consists of lines of the form:
#
# name = value
#
# (The ' = ' is NOT optional.)
#
# Comments are introduced with '#' at beginning of a line.
#
# The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file show optional values.
#
# These options cannot be specified in any other way. There are
# no command line switches, to ensure that when recovery completes
# we do not accidentally re-enter archive recovery.
#
# This file is read on postmaster startup.
#
#---------------------------------------------------------------------------
# REQUIRED PARAMETERS
#---------------------------------------------------------------------------
#
# restore program
#
# specifies the program that is called to copy log files
# back from archive for use
# command string must specify 3 parameters, using %s
# parameter1 will be substituted with archive_dest
# parameter2 will be substituted with the single log file
# that will be restored (one file per execution)
# parameter3 will be substituted with the full path of
# the file to which the restore program will restore
#
#
restore_program = 'cp %s/%s %s'
#
#
#---------------------------------------------------------------------------
# RECOVERY TARGET
#---------------------------------------------------------------------------
#
# By default, recovery will rollforward to the end of logs
# If you want to stop rollforward before that point, you
# MUST set a recovery target.
#
# You may set a recovery target either by transactionId, or
# by timestamp. Recovery may either include or exclude the
# records with the recovery target value.
#
#
#recovery_target_time = '2004-07-14-22:39:00'
#use_local_time = 'true'
#
recovery_target_xid = '11000'
#
# true or false
recovery_target_inclusive = 'true'
#
#---------------------------------------------------------------------------
# OPTIONAL PARAMETERS
#---------------------------------------------------------------------------
#
recovery_debug_log = 'true'
#
#---------------------------------------------------------------------------
pgarch.c (text/x-c, 13.7K) download
/* ----------
* pgarch.c
*
* PostgreSQL transaction log archiver
*
* All functions relating to archiver are included here
*
* - All functions executed by archiver process
*
* - archiver is forked from postmaster, and the two
* processes then communicate using signals. All functions
* executed by postmaster are included in this file.
*
* Simon Riggs simon@2ndquadrant.com
*
* ----------
*/
#include "postgres.h"
#include <unistd.h>
#include <fcntl.h>
#include <sys/param.h>
#include <sys/time.h>
#include <sys/types.h>
#include <errno.h>
#include <signal.h>
#include <time.h>
#include "pgarch.h"
#include "storage/fd.h"
#include "miscadmin.h"
#include "access/xlog.h"
#include "libpq/pqsignal.h"
#include "storage/ipc.h"
#include "postmaster/postmaster.h"
#include "storage/pg_shmem.h"
#include "storage/pmsignal.h"
#include "utils/ps_status.h"
/* ----------
* GUC parameters
* ----------
*/
bool XLogArchiveMode;
bool XLogArchiveDEBUG;
char *XLogArchiveDest;
char *XLogArchiveProgram;
/* ----------
* Other global variables
* ----------
*/
bool pgarch_is_running = false;
/* ----------
* Local data
* ----------
*/
static char XLogDir[MAXPGPATH];
static char XLogArchiveStatusDir[MAXPGPATH];
static time_t last_pgarch_start_time;
static time_t last_pgarch_archivercopy_time;
static time_t curtime;
static bool archiving_in_progress = false;
/* ----------
* Local function forward declarations
* ----------
*/
NON_EXEC_STATIC void pgarch_Main(int argc, char *argv[]);
static void pgarch_exit(SIGNAL_ARGS);
static void pgarch_waken(SIGNAL_ARGS);
static void pgarch_MainWaitLoop(void);
static void pgarch_ArchiverCopyLoop(void);
static bool pgarch_archiveXlog(char *xlog, bool testmode);
static bool pgarch_readyXlog(char *xlog);
static void pgarch_archiveDone(char *xlog);
/* ------------------------------------------------------------
* Public functions called from postmaster follow
* ------------------------------------------------------------
*/
/* ----------
* pgarch_start() -
*
* Called from postmaster at startup
* or after the previous archiver died
*
* Note: if fail, we will be called again from the postmaster main loop.
* ----------
*/
int
pgarch_start(void)
{
time_t curtime;
pid_t pgArchPid;
/*
* Do nothing if no archiver needed
*/
if (!XLogArchiveMode)
return 0;
/*
* Do nothing if too soon since last archiver start. This is a
* safety valve to protect against continuous respawn attempts if the
* archiver is dying immediately at launch. Note that since we will
* be re-called from the postmaster main loop, we will get another
* chance later.
*/
curtime = time(NULL);
if ((unsigned int) (curtime - last_pgarch_start_time) <
(unsigned int) PGARCH_RESTART_INTERVAL) {
return 0;
}
last_pgarch_start_time = curtime;
fflush(stdout);
fflush(stderr);
#ifdef __BEOS__
/* Specific beos actions before backend startup */
beos_before_backend_startup();
#endif
#ifdef EXEC_BACKEND
switch ((pgArchPid = (int) pgarch_forkexec(STAT_PROC_BUFFER)))
#else
switch ((pgArchPid = (int) fork()))
#endif
{
case -1:
#ifdef __BEOS__
/* Specific beos actions */
beos_backend_startup_failed();
#endif
ereport(LOG,
(errmsg("could not fork archiver")));
exit(1);
#ifndef EXEC_BACKEND
case 0:
/* in postmaster child ... */
#ifdef __BEOS__
/* Specific beos actions after backend startup */
beos_backend_startup();
#endif
/* Close the postmaster's sockets */
ClosePostmasterPorts();
/* Drop our connection to postmaster's shared memory, as well */
PGSharedMemoryDetach();
pgarch_Main(0, NULL);
break;
#endif
default:
return (int) pgArchPid;
}
/* shouldn't get here */
return 0;
}
/* ------------------------------------------------------------
* Local functions called by archiver follow
* ------------------------------------------------------------
*/
/* ----------
* pgarch_Main() -
*
*
* The argc/argv parameters are valid only in EXEC_BACKEND case.
* ----------
*/
NON_EXEC_STATIC void
pgarch_Main(int argc, char *argv[])
{
char testxlog[32] = "0000TEST0000TEST";
char testpath[MAXPGPATH];
FILE *rlogFD;
IsUnderPostmaster = true; /* we are a postmaster subprocess now */
MyProcPid = getpid(); /* reset MyProcPid */
/* Lose the postmaster's on-exit routines */
on_exit_reset();
/* Init XLOG file paths */
snprintf(XLogDir, MAXPGPATH, "%s/pg_xlog", DataDir);
snprintf(XLogArchiveStatusDir, MAXPGPATH, "%s/archive_status", XLogDir);
/*
* Test whether archive_program and archive_dest have been set
* correctly in postgresql.conf. This test does *not* look at
* archive_status, since it is a direct test of archival
*/
snprintf(testpath, MAXPGPATH, "%s/%s", XLogDir, testxlog);
unlink(testpath);
rlogFD = AllocateFile(testpath, "w");
if (rlogFD == NULL) {
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not write test file to pg_xlog \"%s\": %m",
testpath)));
exit(1);
}
FreeFile(rlogFD);
if (!pgarch_archiveXlog(testxlog, true)) {
ereport(ERROR,
(errmsg("your archive_program failed to archive a test file"),
errhint("your archive_program is not specified correctly "
"or the archive system is currently unavailable. "
"test your program again before restarting")));
exit(1);
}
unlink(testpath);
/*
* Ignore all signals usually bound to some action in the postmaster,
*/
pqsignal(SIGHUP, SIG_IGN);
pqsignal(SIGTERM, SIG_IGN);
pqsignal(SIGINT, SIG_IGN);
pqsignal(SIGQUIT, pgarch_exit);
pqsignal(SIGALRM, SIG_IGN);
pqsignal(SIGPIPE, SIG_IGN);
pqsignal(SIGUSR1, pgarch_waken);
pqsignal(SIGUSR2, SIG_IGN);
pqsignal(SIGCHLD, SIG_IGN);
pqsignal(SIGTTIN, SIG_DFL);
pqsignal(SIGTTOU, SIG_DFL);
pqsignal(SIGCONT, SIG_DFL);
pqsignal(SIGWINCH, SIG_DFL);
PG_SETMASK(&UnBlockSig);
#ifdef EXEC_BACKEND
pgstat_parseArgs(argc,argv);
#endif
/*
* Identify via ps
*/
init_ps_display("archiver process", "", "");
set_ps_display("");
ereport(LOG,
(errmsg("archiver started")));
/*
* When first started, check for outstanding archive files
* which may be present if we did a (normal) fast shutdown
* or if archiver died with some form of error
*/
pgarch_ArchiverCopyLoop();
pgarch_MainWaitLoop();
exit(0);
}
static void
pgarch_exit(SIGNAL_ARGS)
{
exit(0);
}
#ifdef NOT_USED
static bool shutdowncalled = false;
/*
* Experiment later with having archiver complete its work on smart shutdown...
*
*/
static void
pgarch_shutdown(SIGNAL_ARGS)
{
shutdowncalled = true;
/*
* we do not block signals here, to allow a later,
* upgraded (fast/immediate) request for shutdown to
* override this behaviour
*/
if (archiving_in_progress) {
/* complete archiving, then exit
*/
return;
}
else {
/* This is a Smart or Fast Shutdown,
* so try archiving one last time. This is consistent
* with behaviour of a fast shutdown, in that we still
* write a shutdown checkpoint and try to recycle the
* log files - so before we do this, we try to archive
* away the last few .ready xlogs
*
* XXX archive partial xlog file, even though not full
* though we'd have to remember which one was next to
* handle that correctly on restore/recover
*/
pgarch_ArchiverCopyLoop();
return;
}
}
#endif
static void
pgarch_waken(SIGNAL_ARGS)
{
pgarch_ArchiverCopyLoop();
return;
}
/* ----------
* pgarch_MainWaitLoop() -
*
* Main wait loop for archiver
* ----------
*/
static void
pgarch_MainWaitLoop(void)
{
/*
* There shouldn't be anything for the archiver to do except
* to wait, so we could use pause(3) here...
* ...however, the archiver exists to protect our data, so
* she wakes up occaisionally to allow herself to be proactive.
* This shouldn't be required, but our data is important
* and this won't hurt to be cautious
*/
do {
/*
* Sleep for a while, hoping to be interrupted by signal
* if no signal, then check anyway....just to be sure
*/
sleep(PGARCH_AUTOWAKE_INTERVAL);
curtime = time(NULL);
if ((unsigned int) (curtime - last_pgarch_archivercopy_time) >=
(unsigned int) PGARCH_AUTOWAKE_INTERVAL) {
pgarch_ArchiverCopyLoop();
}
last_pgarch_archivercopy_time = curtime;
} while (PostmasterIsAlive(true));
return;
}
/* ----------
* pgarch_ArchiverCopyLoop() -
*
* Archives all outstanding xlogs then exits
* ----------
*/
static void
pgarch_ArchiverCopyLoop(void)
{
char xlog[32];
int try = 1;
/*
* We continue to trap for all signals, except for the
* one that brought us here in the first place. We
* loop through all transaction log files that require
* archiving, so no need to be interrupted to continue
* that task
*/
pqsignal(SIGUSR1, SIG_IGN);
archiving_in_progress = true;
/*
* loop through all xlogs with archive_status of .ready
* then archive them...mostly we expect this to be a single
* file, though not just a simple loop because we may add new
* files onto the list of those that need archiving while we
* are still copying earlier archives
*/
while (pgarch_readyXlog(xlog) && try <= NUM_ARCHIVE_RETRIES)
{
if (pgarch_archiveXlog(xlog, false)) {
pgarch_archiveDone(xlog);
} else {
ereport(WARNING,
(errmsg("transaction log file \"%s\" could not be archived",
xlog),
errhint("archive_dest \"%s\" may not have enough disk space "
"or the archive system is currently unavailable",
XLogArchiveDest)));
}
/* if we have copied one file, we do not wait:
immediately loop back round and check to see if another is there.
Hopefully, we're quick enough....so we fall out and sleep again
*/
try++;
}
archiving_in_progress = false;
pqsignal(SIGUSR1, pgarch_waken);
return;
}
/*
* pgarch_archiveXlog
*
* Invokes system(3) to copy one archive file to XLogArchiveDest
* We assume xlog is a correct filename and that both
* XLogArchiveProgram and XLogArchiveDest are set correctly
*/
static bool
pgarch_archiveXlog(char *xlog, bool testmode)
{
char xlogarchcmd[MAXPGPATH];
char xlogpath[MAXPGPATH];
int rc;
snprintf(xlogpath, MAXPGPATH, "%s/%s", XLogDir,xlog);
/*
* set the string for the program and its parameters
* XLogArchiveProgram should contain 2 positional parameters
* xlog must be a full path to xlog
*/
snprintf(xlogarchcmd, MAXPGPATH, XLogArchiveProgram, xlogpath, XLogArchiveDest);
if (XLogArchiveDEBUG)
ereport(LOG,
(errmsg("archive command=\"%s\"",
xlogarchcmd)));
rc = system(xlogarchcmd);
if (rc != 0 ) {
ereport(LOG,
(errmsg("archive command=\"%s\",return code=%i",
xlogarchcmd, rc)));
return false;
}
if (!testmode)
ereport(LOG,
(errmsg("archived transaction log file \"%s\"", xlog)));
return true;
}
/*
* XLogArchiveXlogs
*
* Return name of the oldest xlog file that has not yet been archived.
* No notification is set that file archiving is now in progress, [so
* this would need to be extended if multiple concurrent archival
* tasks were created]. If a failure occurs, we would completely
* re-copy the file at the next available opportunity.
*
* It is important that we return the oldest, so that we archive xlogs
* in order that they were written, for two reasons:
* 1) to maintain the sequential chain of xlogs required for recovery
* 2) because the oldest ones will sooner become candidates for
* recycling at time of checkpoint
*
*/
static bool
pgarch_readyXlog(char *xlog)
{
/*
* open XLogArchive directory and read through list of
* rlogs that have the .ready suffix, looking for earliest file.
* It is possible to optimise this code, though only a single
* file is expected on the vast majority of calls, so....
*/
char newxlog[32];
char emptystr[32] = "\0";
DIR *rldir;
struct dirent *rlde;
bool firstfile;
rldir = AllocateDir(XLogArchiveStatusDir);
if (rldir == NULL)
ereport(ERROR,
(errcode_for_file_access(),
errmsg("could not open archive_status directory \"%s\": %m",
XLogArchiveStatusDir)));
firstfile = true;
while ((rlde = readdir(rldir)) != NULL)
{
if (strlen(rlde->d_name) == 22 &&
strspn(rlde->d_name, "0123456789ABCDEF") == 16 &&
strcmp(rlde->d_name + 16, ".ready") == 0)
{
if (firstfile) {
strcpy(newxlog, rlde->d_name);
firstfile = false;
} else {
if (strcmp(rlde->d_name, newxlog) <= 0)
strcpy(newxlog, rlde->d_name);
}
}
}
FreeDir(rldir);
if (!firstfile) {
strcpy(xlog, emptystr);
strncat(xlog, newxlog, 16);
return true;
}
else
return false;
}
/*
* pgarch_archiveDone
*
* Write notification that an xlog has now been successfully archived
*/
static void
pgarch_archiveDone(char *xlog)
{
char rlogready[MAXPGPATH];
char rlogdone[MAXPGPATH];
int rc;
snprintf(rlogready, MAXPGPATH, "%s/%s.ready", XLogArchiveStatusDir, xlog);
snprintf(rlogdone, MAXPGPATH, "%s/%s.done", XLogArchiveStatusDir, xlog);
rc = rename(rlogready, rlogdone);
if (rc < 0) {
ereport(WARNING,(errcode_for_file_access(),
errmsg("could not update archive_status for \"%s\"",
rlogready)));
return;
}
return;
}
pgarch.h (text/x-c-header, 480B) download
/* ----------
* pgarch.h
*
* Definitions for the PostgreSQL archiver daemon.
*
* ----------
*/
/* ----------
* Timer definitions.
* ----------
*/
#define PGARCH_AUTOWAKE_INTERVAL 600 /* How often to wake and poll */
#define PGARCH_RESTART_INTERVAL 60 /* How often to attempt to restart */
/* a failed statistics collector; in seconds. */
#define NUM_ARCHIVE_RETRIES 3
/* ----------
* Functions called from postmaster
* ----------
*/
extern int pgarch_start(void);
README (text/html, 11.6K) download
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.0.9">
</HEAD>
<BODY>
<TT>PITR Archive Recovery, 8 July 2004<BR>
<BR>
What's in this patch?<BR>
<BR>
- All code from previous patches: working now against CVS tip as of now<BR>
- Partially complete code for PITR, though not enabled as yet.<BR>
<BR>
This is a full, working patch for discussion and testing, while I complete the additional code.<BR>
<BR>
Archive Recovery Overview<BR>
<BR>
The rule is: when you startup with archive_mode on, take a backup.<BR>
<BR>
This patch provides an additional mode for transactional log recovery.<BR>
The current mode provides Crash Recovery, allowing the database to<BR>
restart and to read the last few online transaction logs to recover. The<BR>
additional mode is known as Archive Recovery, enabled in postgresql.conf<BR>
using archive_mode=true.<BR>
<BR>
The administrator also specifies and archive location and a program to<BR>
perform the copying (discussed later). As database writes occur and<BR>
transaction logs fill they are copied to the archive location by a new<BR>
sub-process of the postmaster, called the archiver process.<BR>
<BR>
If the database crashes when in archive_mode, it will recover just as<BR>
before. If a major system crash, or other reason to restore occurs, then<BR>
the new Archive Recovery mode can be invoked by:<BR>
- restoring the full physical backup<BR>
- creating DataDir/recovery.conf<BR>
- restart PostgreSQL<BR>
<BR>
PostgreSQL will recover using almost the same code path as before,<BR>
except: all transaction logs will be restored for use directly from<BR>
archive in a stream that prevents disk space from overflowing. Extended<BR>
recovery is possible, over many transaction logs - far more than was<BR>
previously possible.<BR>
<BR>
The essential aspect to recovery is maintaining a solid backup/log<BR>
chain. This must consist of:<BR>
i) a full backup of every file in PostgreSQL - miss none!<BR>
ii) archived transaction logs from just before backup taken to whenever<BR>
you want to recover to...<BR>
<BR>
If you "break the chain" i.e. the two sets of files don't match, or<BR>
you've deleted a transaction log or lost a tape - then you will only be<BR>
able to recover up to the point the chain broke. The longer the chain,<BR>
the easier it will be to break, so backup regularly and work out a<BR>
robust archiving policy.<BR>
<BR>
Extensibility<BR>
Archive Recovery allows the administrator to specify a program or script<BR>
to use when both archiving and restoring transaction log files. As a<BR>
result, the administrator can choose to store transaction log files on<BR>
any drive, any other system or integrating with any Backup Archive<BR>
Recovery (BAR) software.<BR>
<BR>
The Archive Program is provided with 3 parameters, which can be placed<BR>
anywhere within the supplied command string. Each parameter is<BR>
represented by a "%s" character string.<BR>
- The first parameter is replaced with the full path of the transaction<BR>
log to be archived.<BR>
- The second parameter is replaced with the value of the archive_dest<BR>
parameter<BR>
e.g. with these settings<BR>
archive_mode = true<BR>
archive_dest = '/mount/disk2/pgarchive'<BR>
archive_program = 'cp %s %s'<BR>
with transaction log 00000000000003A4 to archive<BR>
with DataDir of web123<BR>
<BR>
would execute the following command <BR>
'cp /usr/local/pgsql/web123/00000000000003A4 /disk2/pgarchive'<BR>
<BR>
To enter Archive Recovery the administrator must create a recovery.conf<BR>
file. [Currently an empty file will do, though intended as the one place<BR>
where all recovery options and parameters would be set]<BR>
<BR>
The Restore Program is provided with 2 parameters<BR>
- The first parameter is replaced with the value of the archive_dest<BR>
parameter<BR>
- The second parameter is replaced with the value of the transaction log<BR>
which recovery is requesting should now be restored<BR>
- The third parameter is replaced with the full path of the filename<BR>
which should be the target of the single file recovery operation.<BR>
[In this patch, XlogArchRestoreProgram is hardcoded to "cp %s/%s %s",<BR>
pending short discussion on how to specify this...]<BR>
<BR>
Possibilities<BR>
Using the current features, it is possible to implement an Automated<BR>
Standby Database. This is an active-passive High Availability option. In<BR>
this mode, the main server sends archived log files to a second, standby<BR>
server. The standby server is set up to be in "permanent recovery", by<BR>
using a RestoreProgram that waits for each file to be shipped to it. The<BR>
standby system receives each file, then recovers up to that point - so<BR>
the standby system is always a few seconds from completing its startup<BR>
should it be required.<BR>
<BR>
Patch Status & Current Caveats<BR>
- A number of recovery scenarios have been tested and the patch is<BR>
believed to be stable and ready for others to begin commentary and<BR>
testing...please understand that there are many scenarios that work and<BR>
many that do not...these last are not bugs<BR>
<BR>
- Recovery to a specific point in time is not yet implemented. The<BR>
administrator has the following recovery options:<BR>
--can recover the system fully, all the way to the end of logs<BR>
--stop recovery by withholding log files at an appropriate place,<BR>
thereby forcing the termination of recovery<BR>
<BR>
How it Works<BR>
<BR>
archive_debug is a hidden postgresql.conf setting which can be used to<BR>
show more debug output for the archive recovery facilities.<BR>
...archive_debug = true<BR>
<BR>
[This currently generates an additional log file called recovery.log,<BR>
showing the record headers and types of commit/abort xlog records.]<BR>
<BR>
Each PostgreSQL user has a corresponding backend process performing work<BR>
for them. These backends write any transaction log data to the log as<BR>
each operation occurs, then marks transactions as either committed or<BR>
aborted. The log is split into log segment files. When each log file<BR>
fills a notification file is created in archive_status, and at the same<BR>
time a signal is sent to the archiver (via the postmaster) to begin<BR>
archiving.<BR>
<BR>
The archiver executes the administrator's program or script using a<BR>
system(3) call, archiving each file one at a time.<BR>
<BR>
The notification file provides a number of features. First, its<BR>
simplicity ensures that we don't need a shared memory link between each<BR>
backend and the archiver, which would then be prone to failure - which<BR>
is exactly not what we want. Second, the archive_status file is written<BR>
whether or not the postmaster is still up, so will still function<BR>
correctly even when the worst has happened. Third, as a persistent<BR>
record, it will allow archiving to restart at the same point later on<BR>
(even after a backup/restore), allowing it to recover gracefully from<BR>
administrator's immediate shutdown requests as well as postmaster or<BR>
archive system failures.<BR>
<BR>
When recovery completes, archive_status files are written to ensure<BR>
cleanup of all transaction logs leaves the database in a fit state for<BR>
production. The recovery.conf file is removed to ensure that a<BR>
subsequent crash doesn't accidentally begin recovery from archives...<BR>
<BR>
The archiver process starts only when recovery has fully completed.<BR>
<BR>
How to Fail<BR>
<BR>
All of the following ways to fail have already occurred in testing...<BR>
<BR>
1. if you do all 3 of these, you will not have a backup/log chain:<BR>
a) use the 'cp' command, or any other command that can write to either a<BR>
file or a directory<BR>
b) use archive_dest to specify a directory<BR>
c) forget to create the directory, which the command in a) then<BR>
interprets as a file and then all archived logs overwrite each other and<BR>
break the chain. You will be unable to rollforward AT ALL.<BR>
<BR>
2. if you do all 5 of these, you will not rollforward very far, if any.<BR>
a) restore the database backup<BR>
b) forget to specify a recovery.conf file<BR>
c) startup and let database recover using only the xlogs that were<BR>
present when the backup was taken<BR>
d) then immediately after recovery run a job which causes xlog to be<BR>
written, and then a file to fill<BR>
e) use an archive command that allows overwriting of previously archived<BR>
files<BR>
This sequence causes the database to recover successfully, but not using<BR>
your full archive chain. The current redo pointer points half way<BR>
through your backup/log chain, so when the file is archived, it<BR>
overwrites a file in the middle of the chain, thus breaking the chain.<BR>
There isn't an option on cp that will prevent this...<BR>
<BR>
3. turn archive_mode on and off...<BR>
Turning archive_mode off means that when an xlog fills, it won't EVER<BR>
get archived. This will break the backup/log chain. If you turn<BR>
archive_mode back on, and an xlog filled, then it will not be picked up<BR>
as having filled and you will be missing a link in the chain. Sometimes<BR>
you'll get away with it and then you'll think it will work all the time.<BR>
The rule is: when you startup with archive_mode on, take a backup.<BR>
<BR>
4. Forget that xlogs are the same size, whether they are full or<BR>
not...you can't tell (yet) by looking at one whether it has a record<BR>
within it that will break the chain<BR>
<BR>
How to succeed<BR>
- Test your recovery procedures before you need them<BR>
- When you're in a recovery situation backup everything you can lay your<BR>
hands on, to make sure you have a known position to return to while you<BR>
ATTEMPT recovery - you may make a mistake and need to retry<BR>
- be cool - we all make mistakes, just don't let errors multiply<BR>
<BR>
- disable listen_addresses in postgresql.conf when you recover, to give<BR>
yourself some breathing space to check things, when you finally do get<BR>
the database ready<BR>
- recover with archive_debug = true to give me/others a chance to debug<BR>
any problems or answer any questions you may have<BR>
<BR>
The rule is: when you startup with archive_mode on, take a backup. <BR>
<BR>
When enabling archive_mode for the first time, if you have run<BR>
previously without enabling archive_mode, then when you will inevitably<BR>
get messages saying "cannot find archive_status file". After you have<BR>
taken a backup, you can manually create files in the archive_status<BR>
directory of <xlog>.done, which will then allow the bgwriter to clean<BR>
them up when it next takes a checkpoint.<BR>
<BR>
Additional Work<BR>
<BR>
- recovery.conf needs some minor work to specify recover options. It is<BR>
envisaged that this would be a short bison grammar, very very similar to<BR>
postgresql.conf<BR>
<BR>
- It seems possible to easily work around the requirement to take the<BR>
backup while the database is open. It would be a good option to have<BR>
both hot and cold backup options...<BR>
<BR>
- full documentation will also follow shortly<BR>
<BR>
Credits<BR>
<BR>
This work is the final push in a long series of patches and discussions<BR>
about how to achieve archive recovery with PostgreSQL. The work of<BR>
J.R.Nield and Patrick MacDonald has provided the detailed underpinnings<BR>
for this recent work, which in turn rests upon Vadim Mikheev's original<BR>
work on WAL and MVCC. Tom Lane has provided considerable technical<BR>
assistance and quality review, whilst Bruce Momjian has provided many of<BR>
the ideas and smoothed the way for much of the work. Thanks, all.<BR>
<BR>
Simon Riggs, <A HREF="mailto:simon@2ndquadrant.com"><U>simon@2ndquadrant.com</TT></U></A><BR>
<BR>
</BODY>
</HTML>
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-15 01:43 Mark Kirkwood <markir@coretech.co.nz>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-15 01:43 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: markw@osdl.org; pgman@candle.pha.pa.us; kn@mgnet.de; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org; pgsql-admin
I noticed that compiling with 5_1 patch applied fails due to
XLOG_archive_dir being removed from xlog.c , but
src/backend/commands/tablecmds.c still uses it.
I did the following to tablecmds.c :
5408c5408
< extern char XLOG_archive_dir[];
---
> extern char *XLogArchiveDest;
5410c5410
< use_wal = XLOG_archive_dir[0] && !rel->rd_istemp;
---
> use_wal = XLogArchiveDest[0] && !rel->rd_istemp;
Now I have to see if I have broken it with this change :-)
regards
Mark
Simon Riggs wrote:
>On Wed, 2004-07-14 at 16:55, markw@osdl.org wrote:
>
>
>>On 14 Jul, Simon Riggs wrote:
>>
>>
>>>PITR Patch v5_1 just posted has Point in Time Recovery working....
>>>
>>>Still some rough edges....but we really need some testers now to give
>>>this a try and let me know what you think.
>>>
>>>Klaus Naumann and Mark Wong are the only [non-committers] to have tried
>>>to run the code (and let me know about it), so please have a look at
>>>[PATCHES] and try it out.
>>>
>>>
>>>
>
>
>
>>I just tried applying the v5_1 patch against the cvs tip today and got a
>>couple of rejections. I'll copy the patch output here. Let me know if
>>you want to see the reject files or anything else:
>>
>>
>>
>
>I'm on it. Sorry 'bout that all - midnight fingers.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 01:49 SAKATA Tetsuo <sakata.tetsuo@lab.ntt.co.jp>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 111+ messages in thread
From: SAKATA Tetsuo @ 2004-07-15 01:49 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Hi, folks.
My colleages and I are planning to test PITR after the 7.5 beta release.
Now we are desinging test items, but some specification are enough clear
(to us).
For example, we are not clear which resouce manager order to store log
records.
- some access method (like B-tree) require to log its date or not.
- create/drop action of table space to be stored to the log or not.
We'll be pleased if someone informs them.
The test set we'll proceed has following items;
- PITR can recover ordinary commited transaction's data.
- tuple data themselves
- index data associated with them
- PITR can recover commited some special transaction's data.
- DDL; create database, table, index and so on
- maintenance commands (handling large amount of data);
truncate, vacuum, reindex and so on.
Items above are 'data aspects' of the test. Other aspects are as follows
- Place of the archival log's drive;
PITR can recover a database from archived log data
- stored in the same drive as xlog.
- stored in a different drive on the same machine
in which the PostgreSQL runs.
- stored in a different drive on a different machine.
- Duration between a checkpoint and recovery;
PITR can recover a database enough long after a checkpoint.
- Time to Recover;
- to end of the log.
- to some specified time.
- Type of failures;
- system down --- kill the PostgreSQL process (as a simulation).
- media lost --- delete database files (as a simulation).
- These two case will be tested by a simulated situation first,
and we would try some 'real' failure after.
(real power down of the test machine to the first case,
and 'plug off' the disk drive to the second one.
these action would damage test machine, this is because
we plan them after 'ordinary' test items.)
The test set is under construction and we'll test the 7.5 beta
for some weeks, and report the result of the test here.
Sincerely yours.
Tetsuo SAKATA.
--
sakata.tetsuo _at_ lab.ntt.co.jp
SAKATA, Tetsuo. Yokosuka JAPAN.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-15 07:15 Simon Riggs <simon@2ndquadrant.com>
parent: Mark Kirkwood <markir@coretech.co.nz>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-15 07:15 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: markw@osdl.org; pgman@candle.pha.pa.us; kn@mgnet.de; tgl@sss.pgh.pa.us; pgsql-hackers@postgresql.org; pgsql-admin
On Thu, 2004-07-15 at 02:43, Mark Kirkwood wrote:
> I noticed that compiling with 5_1 patch applied fails due to
> XLOG_archive_dir being removed from xlog.c , but
> src/backend/commands/tablecmds.c still uses it.
>
> I did the following to tablecmds.c :
>
> 5408c5408
> < extern char XLOG_archive_dir[];
> ---
> > extern char *XLogArchiveDest;
> 5410c5410
> < use_wal = XLOG_archive_dir[0] && !rel->rd_istemp;
> ---
> > use_wal = XLogArchiveDest[0] && !rel->rd_istemp;
>
>
Yes, I discovered that myself.
The fix is included in pitr_v5_2.patch...
Your patch follows the right thinking and looks like it would have
worked...
- XLogArchiveMode carries the main bool value for mode on/off
- XLogArchiveDest might also be used, though best to use the mode
Thanks for looking through the code...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 09:47 Mark Kirkwood <markir@coretech.co.nz>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-15 09:47 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
I tried what I thought was a straightforward scenario, and seem to have
broken it :-(
Here is the little tale
1) initdb
2) set archive_mode and archive_dest in postgresql.conf
3) startup
4) create database called 'test'
5) connect to 'test' and type 'checkpoint'
6) backup PGDATA using 'tar -zcvf'
7) create tables in 'test' and add data using COPY (exactly 2 logs worth)
8) shutdown and remove PGDATA
9) recover using 'tar -zxvf'
10) copy recovery.conf into PGDATA
11) startup
This is what I get :
LOG: database system was interrupted at 2004-07-15 21:24:04 NZST
LOG: recovery command file found...
LOG: restore_program = cp %s/%s %s
LOG: recovery_target_inclusive = true
LOG: recovery_debug_log = true
LOG: starting archive recovery
LOG: restored log file "0000000000000000" from archive
LOG: checkpoint record is at 0/A48054
LOG: redo record is at 0/A48054; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 496; next OID: 25419
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/A48094
LOG: restored log file "0000000000000001" from archive
LOG: record with zero length at 0/1FFFFE0
LOG: redo done at 0/1FFFF30
LOG: restored log file "0000000000000001" from archive
LOG: restored log file "0000000000000001" from archive
PANIC: concurrent transaction log activity while database system is
shutting down
LOG: startup process (PID 13492) was terminated by signal 6
LOG: aborting startup due to startup process failure
The concurrent access is a bit of a puzzle, as this is my home machine
(i.e. I am *sure* noone else is connected!)
Mark
P.s : CVS HEAD from about 1 hour ago, PITR 5.2, FreeBSD 4.10 on x86
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 12:16 HISADAMasaki <hisada.masaki@lab.ntt.co.jp>
parent: Simon Riggs <simon@2ndquadrant.com>
2 siblings, 1 reply; 111+ messages in thread
From: HISADAMasaki @ 2004-07-15 12:16 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Dear Simon,
I've just tested pitr_v5_2.patch and got an error message
during archiving process as follows.
-- begin
LOG: archive command="cp /usr/local/pgsql/data/pg_xlog/0000000000000000 /tmp",return code=-1
-- end
The command called in system(3) works, but it returns -1.
system(3) can not get right exit code from its child process,
when SIGCHLD is set as SIG_IGN.
So I did following change to pgarch_Main() in pgarch.c
-- line 236 ---
- pgsignal(SIGCHLD, SIG_IGN);
-- line 236 ---
+ pgsignal(SIGCHLD, SIG_DFL);
After that,
the error message doen't come out and it seems to be working propery.
Regards,
Hisada, Masaki
On Wed, 14 Jul 2004 00:13:37 +0100
Simon Riggs <simon@2ndquadrant.com> wrote:
> PITR Patch v5_1 just posted has Point in Time Recovery working....
>
> Still some rough edges....but we really need some testers now to give
> this a try and let me know what you think.
>
> Klaus Naumann and Mark Wong are the only [non-committers] to have tried
> to run the code (and let me know about it), so please have a look at
> [PATCHES] and try it out.
>
> Many thanks,
>
> Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
HISADA, Masaki <hisada.masaki@lab.ntt.co.jp>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 19:35 Simon Riggs <simon@2ndquadrant.com>
parent: Mark Kirkwood <markir@coretech.co.nz>
1 sibling, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-15 19:35 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-hackers@postgresql.org
On Thu, 2004-07-15 at 10:47, Mark Kirkwood wrote:
> I tried what I thought was a straightforward scenario, and seem to have
> broken it :-(
>
> Here is the little tale
>
> 1) initdb
> 2) set archive_mode and archive_dest in postgresql.conf
> 3) startup
> 4) create database called 'test'
> 5) connect to 'test' and type 'checkpoint'
> 6) backup PGDATA using 'tar -zcvf'
> 7) create tables in 'test' and add data using COPY (exactly 2 logs worth)
> 8) shutdown and remove PGDATA
> 9) recover using 'tar -zxvf'
> 10) copy recovery.conf into PGDATA
> 11) startup
>
> This is what I get :
>
> LOG: database system was interrupted at 2004-07-15 21:24:04 NZST
> LOG: recovery command file found...
> LOG: restore_program = cp %s/%s %s
> LOG: recovery_target_inclusive = true
> LOG: recovery_debug_log = true
> LOG: starting archive recovery
> LOG: restored log file "0000000000000000" from archive
> LOG: checkpoint record is at 0/A48054
> LOG: redo record is at 0/A48054; undo record is at 0/0; shutdown FALSE
> LOG: next transaction ID: 496; next OID: 25419
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: redo starts at 0/A48094
> LOG: restored log file "0000000000000001" from archive
> LOG: record with zero length at 0/1FFFFE0
> LOG: redo done at 0/1FFFF30
> LOG: restored log file "0000000000000001" from archive
> LOG: restored log file "0000000000000001" from archive
> PANIC: concurrent transaction log activity while database system is
> shutting down
> LOG: startup process (PID 13492) was terminated by signal 6
> LOG: aborting startup due to startup process failure
>
> The concurrent access is a bit of a puzzle, as this is my home machine
> (i.e. I am *sure* noone else is connected!)
First, thanks for sticking with it to test this.
I've not received such a message myself - this is interesting.
Is it possible to copy that directory to one side and re-run the test?
Add another parameter in postgresql.conf called "archive_debug = true"
Does it happen identically the second time?
What time difference was there between steps 5 and 6? I think I can here
Andreas saying "told you".... I'm thinking the backup might be somehow
corrupted because the checkpoint occurred during the backup. Hmmm...
Could you also post me the recovery.log file? (don't post to list)
Thanks, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 22:44 Simon Riggs <simon@2ndquadrant.com>
parent: HISADAMasaki <hisada.masaki@lab.ntt.co.jp>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-15 22:44 UTC (permalink / raw)
To: HISADAMasaki <hisada.masaki@lab.ntt.co.jp>; +Cc: pgsql-hackers@postgresql.org
On Thu, 2004-07-15 at 13:16, HISADAMasaki wrote:
> Dear Simon,
>
> I've just tested pitr_v5_2.patch and got an error message
> during archiving process as follows.
>
> -- begin
> LOG: archive command="cp /usr/local/pgsql/data/pg_xlog/0000000000000000 /tmp",return code=-1
> -- end
>
> The command called in system(3) works, but it returns -1.
> system(3) can not get right exit code from its child process,
> when SIGCHLD is set as SIG_IGN.
>
> So I did following change to pgarch_Main() in pgarch.c
>
> -- line 236 ---
> - pgsignal(SIGCHLD, SIG_IGN);
>
> -- line 236 ---
> + pgsignal(SIGCHLD, SIG_DFL);
>
Thank you for testing the patch. Very much appreciated.
I was aware of the potential issues of incorrect return codes, and that
exact part of the code is the part I'm least happy with.
I'm not sure I understand why its returned -1, though I'll take you
recommendation. I've not witnessed such an issue. What system are you
running, or is it a default shell issue?
Do people think that the change is appropriate for all systems, or just
the one you're using?
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 23:01 Alvaro Herrera <alvherre@dcc.uchile.cl>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Alvaro Herrera @ 2004-07-15 23:01 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: HISADAMasaki <hisada.masaki@lab.ntt.co.jp>; pgsql-hackers@postgresql.org
On Thu, Jul 15, 2004 at 11:44:02PM +0100, Simon Riggs wrote:
> On Thu, 2004-07-15 at 13:16, HISADAMasaki wrote:
> > -- line 236 ---
> > - pgsignal(SIGCHLD, SIG_IGN);
> >
> > -- line 236 ---
> > + pgsignal(SIGCHLD, SIG_DFL);
>
> I'm not sure I understand why its returned -1, though I'll take you
> recommendation. I've not witnessed such an issue. What system are you
> running, or is it a default shell issue?
>
> Do people think that the change is appropriate for all systems, or just
> the one you're using?
My manpage for signal(2) says that you shouldn't assign SIG_IGN to
SIGCHLD, according to POSIX. It goes on to say that BSD and SysV
behaviors differ on this aspect.
(This is on linux BTW)
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 23:13 Mark Kirkwood <markir@coretech.co.nz>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-15 23:13 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Simon Riggs wrote:
>
>First, thanks for sticking with it to test this.
>
>I've not received such a message myself - this is interesting.
>
>Is it possible to copy that directory to one side and re-run the test?
>Add another parameter in postgresql.conf called "archive_debug = true"
>Does it happen identically the second time?
>
>
>
Yes, identical results - I re-initdb'ed and ran the process again,
rather than reuse the files.
>What time difference was there between steps 5 and 6? I think I can here
>Andreas saying "told you".... I'm thinking the backup might be somehow
>corrupted because the checkpoint occurred during the backup. Hmmm...
>
>
>
I was wondering about this, so left a bit more time in between, and
forced a sync as well for good measure.
5) $ psql -d test -c "checkpoint"; sleep 30;sync;sleep 30
6) $ tar -zcvf /data1/dump/pgdata-7.5.tar.gz *
>
>Thanks, Simon Riggs
>
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 23:46 Mark Kirkwood <markir@coretech.co.nz>
parent: Mark Kirkwood <markir@coretech.co.nz>
1 sibling, 1 reply; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-15 23:46 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-hackers@postgresql.org
Simon Riggs wrote:
>
>So far:
>
>I've tried to re-create the problem as exactly as I can, but it works
>for me.
>
>This is clearly an important case to chase down.
>
>I assume that this is the very first time you tried recovery? Second and
>subsequent recoveries using the same set have a potential loophole,
>which we have been discussing.
>
>Right now, I'm thinking that the "exactly 2 logs worth" of data has
>brought you very close to the end of the log file (FFFFE0) ending with 1
>and the shutdown checkpoint that is then subsequently written is
>failing.
>
>Can you repeat this your end?
>
>
>
It is repeatable at my end. It is actually fairly easy to recreate the
example I am using, download
http://sourceforge.net/projects/benchw
and generate the dataset for Pg - but trim the large "fact0.dat" dump
file using head -100000.
Thus step 7 consists of creating the 4 tables and COPYing in the data
for them.
>The nearest I can get to the exact record pointers you show are to start
>recovery at A4807C and to end at with FFFF88.
>
>Overall, PITR changes the recovery process very little, if at all. The
>main areas of effect are to do with sequencing of actions and matching
>up the right logs with the right backup. I'm not looking for bugs in the
>code but in subtle side-effects and "edge" cases. Everything you can
>tell me will help me greatly in chasing that down.
>
>
>
I agree - I will try this sort of example again, but will change the
number of rows I am COPYing (currently 100000) and see if that helps.
>Best Regards, Simon Riggs
>
>
>
By way of contrast, using the *same* procedure (1-11), but generating 2
logs worth of INSERTS/UPDATES using 10 concurrent process *works fine* -
e.g :
LOG: database system was interrupted at 2004-07-16 11:17:52 NZST
LOG: recovery command file found...
LOG: restore_program = cp %s/%s %s
LOG: recovery_target_inclusive = true
LOG: recovery_debug_log = true
LOG: starting archive recovery
LOG: restored log file "0000000000000000" from archive
LOG: checkpoint record is at 0/A4803C
LOG: redo record is at 0/A4803C; undo record is at 0/0; shutdown FALSE
LOG: next transaction ID: 496; next OID: 25419
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/A4807C
postmaster starting
[postgres@shroudeater 7.5]$ LOG: restored log file "0000000000000001"
from archive
cp: cannot stat `/data1/pgdata/7.5-archive/0000000000000002': No such
file or directory
LOG: could not restore "0000000000000002" from archive
LOG: could not open file "/data1/pgdata/7.5/pg_xlog/0000000000000002"
(log file 0, segment 2): No such file or directory
LOG: redo done at 0/1FFFFD4
LOG: archive recovery complete
LOG: database system is ready
LOG: archiver started
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-15 23:51 Simon Riggs <simon@2ndquadrant.com>
parent: Alvaro Herrera <alvherre@dcc.uchile.cl>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-15 23:51 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@dcc.uchile.cl>; +Cc: HISADAMasaki <hisada.masaki@lab.ntt.co.jp>; pgsql-hackers@postgresql.org
On Fri, 2004-07-16 at 00:01, Alvaro Herrera wrote:
> On Thu, Jul 15, 2004 at 11:44:02PM +0100, Simon Riggs wrote:
> > On Thu, 2004-07-15 at 13:16, HISADAMasaki wrote:
>
> > > -- line 236 ---
> > > - pgsignal(SIGCHLD, SIG_IGN);
> > >
> > > -- line 236 ---
> > > + pgsignal(SIGCHLD, SIG_DFL);
> >
> > I'm not sure I understand why its returned -1, though I'll take you
> > recommendation. I've not witnessed such an issue. What system are you
> > running, or is it a default shell issue?
> >
> > Do people think that the change is appropriate for all systems, or just
> > the one you're using?
>
> My manpage for signal(2) says that you shouldn't assign SIG_IGN to
> SIGCHLD, according to POSIX. It goes on to say that BSD and SysV
> behaviors differ on this aspect.
>
POSIX rules OK!
So - I should be setting this to SIG_DFL and thats good for everyone?
OK. Will do.
Best regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-16 00:02 Simon Riggs <simon@2ndquadrant.com>
parent: Mark Kirkwood <markir@coretech.co.nz>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-16 00:02 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: pgsql-hackers@postgresql.org
On Fri, 2004-07-16 at 00:46, Mark Kirkwood wrote:
>
> By way of contrast, using the *same* procedure (1-11), but generating 2
> logs worth of INSERTS/UPDATES using 10 concurrent process *works fine* -
> e.g :
>
Great...at least we have shown that something works (or can work) and
have begun to isolate the problem whatever it is.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-16 03:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-16 03:49 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; HISADAMasaki <hisada.masaki@lab.ntt.co.jp>; pgsql-hackers@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> On Fri, 2004-07-16 at 00:01, Alvaro Herrera wrote:
>> My manpage for signal(2) says that you shouldn't assign SIG_IGN to
>> SIGCHLD, according to POSIX.
> So - I should be setting this to SIG_DFL and thats good for everyone?
Yeah, we learned the same lesson in the backend not too many releases
back. SIG_IGN'ing SIGCHLD is bad voodoo; it'll work on some platforms
but not others.
You could do worse than to look at the existing handling of signals in
the postmaster and its children; that code has been beat on pretty
heavily ...
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in Time Recovery
@ 2004-07-16 07:40 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-16 07:40 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@dcc.uchile.cl>; HISADAMasaki <hisada.masaki@lab.ntt.co.jp>; pgsql-hackers@postgresql.org
On Fri, 2004-07-16 at 04:49, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > On Fri, 2004-07-16 at 00:01, Alvaro Herrera wrote:
> >> My manpage for signal(2) says that you shouldn't assign SIG_IGN to
> >> SIGCHLD, according to POSIX.
>
> > So - I should be setting this to SIG_DFL and thats good for everyone?
>
> Yeah, we learned the same lesson in the backend not too many releases
> back. SIG_IGN'ing SIGCHLD is bad voodoo; it'll work on some platforms
> but not others.
Many thanks all, Best Regards Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-17 09:39 Gaetano Mendola <mendola@bigfoot.com>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 0 replies; 111+ messages in thread
From: Gaetano Mendola @ 2004-07-17 09:39 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>
Simon Riggs wrote:
> On Wed, 2004-07-14 at 03:31, Christopher Kings-Lynne wrote:
>
>>Can you give us some suggestions of what kind of stuff to test? Is
>>there a way we can artificially kill the backend in all sorts of nasty
>>spots to see if recovery works? Does kill -9 simulate a 'power off'?
>>
>
>
> I was hoping some fiendish plans would be presented to me...
>
> But please start with "this feels like typical usage" and we'll go from
> there...the important thing is to try the first one.
>
> I've not done power off tests, yet. They need to be done just to
> check...actually you don't need to do this to test PITR...
>
> We need to exhaustive tests of...
> - power off
> - scp and cross network copies
> - all the permuted recovery options
> - archive_mode = off (i.e. current behaviour)
> - deliberately incorrectly set options (idiot-proof testing)
If you write also how to perform these tests it's also good in order to show
which problem PITR is addressing, I mean I know that is addressing a power off
but how I will recover it ?
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-17 18:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-17 18:53 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
[ ... some desultory reading of PITR patch ... ]
What is the point of having both archive_program and archive_dest as
GUC variables? Wouldn't it be simpler to fold them into one parameter,
viz
archive_command = 'cp %s /archivedir'
For that matter, do we need a separate archive_mode boolean? The one
thing I can positively guarantee about archive_dest (or archive_command)
is that we cannot come up with a useful default for it (no, /tmp isn't
good). Therefore it does not seem very reasonable to let the user turn
on archiving without having explicitly specified an archive destination.
I propose that we fold all three GUC flags into a single archive_command
string whose built-in default is an empty string, and you enable
archiving by setting it to something nonempty.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-17 19:08 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-17 19:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
Tom Lane wrote:
> [ ... some desultory reading of PITR patch ... ]
>
> What is the point of having both archive_program and archive_dest as
> GUC variables? Wouldn't it be simpler to fold them into one parameter,
> viz
>
> archive_command = 'cp %s /archivedir'
>
> For that matter, do we need a separate archive_mode boolean? The one
> thing I can positively guarantee about archive_dest (or archive_command)
> is that we cannot come up with a useful default for it (no, /tmp isn't
> good). Therefore it does not seem very reasonable to let the user turn
> on archiving without having explicitly specified an archive destination.
I assume archive_dest is used for both archive and recovery of archives.
> I propose that we fold all three GUC flags into a single archive_command
> string whose built-in default is an empty string, and you enable
> archiving by setting it to something nonempty.
I think the idea is that you would turn archiving on and off regularly
while you might never change the archive_command value. Also, how would
you disable it? Set it to "", and if you do, you then have not way to
remember your command string when you want to re-enable it.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-17 19:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-17 19:18 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> What is the point of having both archive_program and archive_dest as
>> GUC variables?
> I assume archive_dest is used for both archive and recovery of archives.
You assume wrong; it's not used there. There isn't any real good
reason to suppose that the recovery process is going to fetch the files
from exactly where archiving put them, anyhow.
> I think the idea is that you would turn archiving on and off regularly
Why in the world would you do that? People who want PITR at all will
want it 24x7.
> while you might never change the archive_command value. Also, how would
> you disable it? Set it to "", and if you do, you then have not way to
> remember your command string when you want to re-enable it.
Leave the original value in a comment, if you're going to want it again
later.
I don't think any of the above arguments outweigh the risk of people
shooting themselves in the foot by enabling archive_mode without
specifying a proper command/destination.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-17 20:15 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-17 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> What is the point of having both archive_program and archive_dest as
> >> GUC variables?
>
> > I assume archive_dest is used for both archive and recovery of archives.
>
> You assume wrong; it's not used there. There isn't any real good
> reason to suppose that the recovery process is going to fetch the files
> from exactly where archiving put them, anyhow.
>
> > I think the idea is that you would turn archiving on and off regularly
>
> Why in the world would you do that? People who want PITR at all will
> want it 24x7.
>
> > while you might never change the archive_command value. Also, how would
> > you disable it? Set it to "", and if you do, you then have not way to
> > remember your command string when you want to re-enable it.
>
> Leave the original value in a comment, if you're going to want it again
> later.
>
> I don't think any of the above arguments outweigh the risk of people
> shooting themselves in the foot by enabling archive_mode without
> specifying a proper command/destination.
So you want to merge them all into a single command string. That does
seem less error-prone. I see a few variables that turn off
when set to '' like unix_socket_*. How would this command string work?
How do you specify the WAL file name to transfer?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-18 05:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 2 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-18 05:04 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> So you want to merge them all into a single command string. That does
> seem less error-prone. I see a few variables that turn off
> when set to '' like unix_socket_*. How would this command string work?
> How do you specify the WAL file name to transfer?
No different from before, necessarily. However I did not like the
restriction to a single %s in the submitted implementation. What I
have in my local copy is
%p -> full path of XLOG file to be archived
%f -> base name of XLOG file to be archived
and the suggested example becomes
archive_command = 'cp %p /mnt/server/pgarchive/%f'
Note that this example immediately eliminates one of the failure modes
Simon enumerates in his README, which is to try 'cp %s /foo' where /foo
isn't a directory. More generally, though, *only* a cp-to-directory
solution is likely to be very happy with not being able to get at the
base file name. Yes you can make a shellscript and use basename,
but I don't think you should have to do that if it could otherwise
be a one-liner.
(In case it's not obvious from the above, I am hacking with intent to
commit soon. Maybe tomorrow, if my wife doesn't make me paint the
bathroom instead...)
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-18 20:20 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-18 20:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
On Sun, 2004-07-18 at 06:04, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > So you want to merge them all into a single command string. That does
> > seem less error-prone. I see a few variables that turn off
> > when set to '' like unix_socket_*. How would this command string work?
> > How do you specify the WAL file name to transfer?
>
GUC-wise, I implemented what we agreed in discussions...
There are many things in need of refactoring, so my focus was on
delivering what we agreed, even knowing it would probably change...
A few notes on the patch (as I submitted it - so as not to confuse with
other versions being worked upon)
- archive_dest is definitely used in both archive and recovery. There
wasn't much need for this GUC apart from that and I think we are better
off without it. Removing it improves recovery flexibility (we cannot
assume the recovery is taking place in anything like the original
configuration).
- archive_mode I would prefer to keep - it is explicit then which mode
you are in, rather than implicit from the command string. In all other
ways I agree with everything Tom has said. It allows us to talk about
"being in archive_mode" without people saying "but I can't work out how
to turn archive mode on".
When archiver starts the FIRST thing it does is run a test to confirm
that the command string works, so setting archive_command to '' would
simply generate an error.
Also, I would suggest this:
- changing archive mode requires a postmaster restart
- changing archive command should just be a SIGHUP...we don't want to
force a restart just to switch to a new kind of archiving
If you can only change archive_program at postmaster start that is
restrictive, but making that SIGHUP would allow people to set it to ''
and turn off archiving while postmaster is up == lurking fault.
> No different from before, necessarily. However I did not like the
> restriction to a single %s in the submitted implementation. What I
> have in my local copy is
> %p -> full path of XLOG file to be archived
> %f -> base name of XLOG file to be archived
> and the suggested example becomes
> archive_command = 'cp %p /mnt/server/pgarchive/%f'
>
I'm happy with those changes and would have done them myself given
time... the 2 or 3 %s parameters wasn't the most user friendly way of
doing it.
> Note that this example immediately eliminates one of the failure modes
> Simon enumerates in his README, which is to try 'cp %s /foo' where /foo
> isn't a directory. More generally, though, *only* a cp-to-directory
> solution is likely to be very happy with not being able to get at the
> base file name. Yes you can make a shellscript and use basename,
> but I don't think you should have to do that if it could otherwise
> be a one-liner.
>
Good.
> (In case it's not obvious from the above, I am hacking with intent to
> commit soon. Maybe tomorrow, if my wife doesn't make me paint the
> bathroom instead...)
>
...just returned from there... :)
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 03:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 3 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-19 03:03 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: markw@osdl.org; pgman@candle.pha.pa.us; kn@mgnet.de; pgsql-patches@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> Latest version, pitr_v5_2.patch...
Reviewed and committed with some adjustments.
I see the following significant loose ends:
* Documentation is, um, lacking. (One point in particular is that I
inserted the recovery.conf.sample file into CVS, but did not fill in
the patch's lack of attempt to install it anywhere.)
* As Bruce has pointed out already, the process of making a backup
needs some improvements for more safety: the starting and ending WAL
offsets have got to be recorded somehow.
* As I have pointed out already, we need to invent "timelines" to
allow incompatible WAL segments to exist side-by-side. I will volunteer
to look into this.
* I think creating a .ready file during XLogFileOpen is completely bogus,
for reasons mentioned in committed comments (look for XXX). Possibly
this can go away with timelines.
* I am wondering if it wouldn't be a good idea to remove the local copy
of any segment we successfully obtain from archive. The existing
comments note that we might get a wrong or corrupted file from archive,
but aren't we in at least as much risk of using an obsolete segment
restored from backup if we leave the local segment in place? (The
archive recovery run itself will know not to do this, but if we crash
shortly thereafter, the ensuing recovery run would NOT know not to
trust such files.)
Perhaps the last point is really a backup-process issue. AFAICS there
is no good reason for a backup tarfile to include $PGDATA/pg_xlog at
all, and some good reasons for it not to. Can we redesign either the
backup process or the disk layout so that that will not happen? Then
we could stop worrying about stale local pg_xlog files.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 03:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-19 03:13 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> When archiver starts the FIRST thing it does is run a test to confirm
> that the command string works, so setting archive_command to '' would
> simply generate an error.
No, it would do no such thing; the test cannot really tell anything more
than whether system("foo") returns zero ... and at least on my machine,
system("") returns zero. It certainly does not prove that any data went
to anyplace safe.
I diked that test out of the committed patch because I felt it cluttered
the archive area without actually proving anything of interest. We can
revisit the point if you like.
> Also, I would suggest this:
> - changing archive mode requires a postmaster restart
Why?
> - changing archive command should just be a SIGHUP...
Check, as committed [and tested to work...]
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 03:14 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-19 03:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
What is the process of logging to tape? Ideally we could just do 'dd'
to the tape drive in append mode; however we need a way of signalling
that we want to change tapes.
The only method I can think of is to have PITR dump the files into a
holding directory, and have a daemon that scans the directory and writes
files to tape when they are completely copied (how do we detect that?
Use 'mv' after the copy? Seems like a good use for our new %
parameters). Then we need a control program to signal the daemon to
stop archiving to tape, have it set a flag file so we know it is
suspended tape writes, report that back to the client, change tapes,
then tell it to restart.
I am asking to make sure we don't need a PITR pause mode that prevents
WAL files from being archived but also prevents them from being
recycled. If we did that, we could probably append to tape directly,
but then we need to go into 'pause archive" mode in the PITR process,
and such switching seems like a pain and the wrong place to do it.
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > So you want to merge them all into a single command string. That does
> > seem less error-prone. I see a few variables that turn off
> > when set to '' like unix_socket_*. How would this command string work?
> > How do you specify the WAL file name to transfer?
>
> No different from before, necessarily. However I did not like the
> restriction to a single %s in the submitted implementation. What I
> have in my local copy is
> %p -> full path of XLOG file to be archived
> %f -> base name of XLOG file to be archived
> and the suggested example becomes
> archive_command = 'cp %p /mnt/server/pgarchive/%f'
>
> Note that this example immediately eliminates one of the failure modes
> Simon enumerates in his README, which is to try 'cp %s /foo' where /foo
> isn't a directory. More generally, though, *only* a cp-to-directory
> solution is likely to be very happy with not being able to get at the
> base file name. Yes you can make a shellscript and use basename,
> but I don't think you should have to do that if it could otherwise
> be a one-liner.
>
> (In case it's not obvious from the above, I am hacking with intent to
> commit soon. Maybe tomorrow, if my wife doesn't make me paint the
> bathroom instead...)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 03:21 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-19 03:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Latest version, pitr_v5_2.patch...
>
> Reviewed and committed with some adjustments.
>
> I see the following significant loose ends:
>
> * Documentation is, um, lacking. (One point in particular is that I
> inserted the recovery.conf.sample file into CVS, but did not fill in
> the patch's lack of attempt to install it anywhere.)
I figure it should go in share like the other sample files, and tell
people to copy it to /data and modify it for recovery.
> * As Bruce has pointed out already, the process of making a backup
> needs some improvements for more safety: the starting and ending WAL
> offsets have got to be recorded somehow.
Yep, we need those files in the archive location and the /data directory
tarball.
> * As I have pointed out already, we need to invent "timelines" to
> allow incompatible WAL segments to exist side-by-side. I will volunteer
> to look into this.
Great.
> * I think creating a .ready file during XLogFileOpen is completely bogus,
> for reasons mentioned in committed comments (look for XXX). Possibly
> this can go away with timelines.
>
> * I am wondering if it wouldn't be a good idea to remove the local copy
> of any segment we successfully obtain from archive. The existing
> comments note that we might get a wrong or corrupted file from archive,
> but aren't we in at least as much risk of using an obsolete segment
> restored from backup if we leave the local segment in place? (The
> archive recovery run itself will know not to do this, but if we crash
> shortly thereafter, the ensuing recovery run would NOT know not to
> trust such files.)
> Perhaps the last point is really a backup-process issue. AFAICS there
> is no good reason for a backup tarfile to include $PGDATA/pg_xlog at
> all, and some good reasons for it not to. Can we redesign either the
> backup process or the disk layout so that that will not happen? Then
> we could stop worrying about stale local pg_xlog files.
Seems we should just clear out the /pg_xlog directory before we start
recovery. We are going to rename recovery.conf to recovery.in-progress
or something to prevent us from clearing out the directory after a
crash, right? (I see you rename recovery.conf to recovery.done. Is
that wise? I thought we would disable recovery after a crash, or does
it just keep going? If so, nice.)
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 03:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-19 03:49 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> * Documentation is, um, lacking. (One point in particular is that I
>> inserted the recovery.conf.sample file into CVS, but did not fill in
>> the patch's lack of attempt to install it anywhere.)
> I figure it should go in share like the other sample files, and tell
> people to copy it to /data and modify it for recovery.
It should certainly go to /share as a .sample file. I was thinking that
initdb should perhaps copy it into $PGDATA (still as .sample, not as
.conf!) so it'd be right there when you need it.
>> Perhaps the last point is really a backup-process issue. AFAICS there
>> is no good reason for a backup tarfile to include $PGDATA/pg_xlog at
>> all, and some good reasons for it not to.
> Seems we should just clear out the /pg_xlog directory before we start
> recovery.
No, that's a horrid idea, because it loses the ability to combine
archival xlog files with recent files in /pg_xlog that are not yet
archived. We need to distinguish old files that were accidentally
captured by backup from very-recent files. I think the cleanest way to
do that is for backup not to capture them in the first place.
> We are going to rename recovery.conf to recovery.in-progress
> or something to prevent us from clearing out the directory after a
> crash, right?
I had second thoughts about that and didn't do it in the committed
patch, though it's certainly still open for debate.
> (I see you rename recovery.conf to recovery.done. Is
> that wise?
Yes. Once you've done with a PITR recovery you definitely do *not* want
a subsequent crash recovery to think it should obey your recovery_target
limit. But if you fail before you've finished the recovery run it
should theoretically be okay to retry, so I didn't add code to rename to
"recovery.inprogress". We can certainly add it later if we decide it's
a good idea.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 04:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-19 04:54 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What is the process of logging to tape? Ideally we could just do 'dd'
> to the tape drive in append mode; however we need a way of signalling
> that we want to change tapes.
The reason we use a user-specifiable shell command for archiving is
so that we do not have to answer the above ;-). It's the user's problem
to write a shell script that does things the way he wants. He can make
it connect to /dev/tty and ask the operator to swap tapes, or whatever.
Personally I am very accustomed to Hewlett-Packard's disk-to-tape backup
program "fbackup", which allows you to provide a shell script to handle
exactly this sort of thing, and it's worked well for me for many years.
> I am asking to make sure we don't need a PITR pause mode that prevents
> WAL files from being archived but also prevents them from being
> recycled.
WAL files will not be recycled until the archiver daemon has set a .done
flag file for them, so I see no problem here. (Note: I took out some
code in Simon's original patch that would start bleating on the basis
of totally unsupportable assumptions about long archival of a log
segment "ought to" take.)
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 06:39 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-19 06:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
On Mon, 2004-07-19 at 04:13, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > When archiver starts the FIRST thing it does is run a test to confirm
> > that the command string works, so setting archive_command to '' would
> > simply generate an error.
>
> No, it would do no such thing; the test cannot really tell anything more
> than whether system("foo") returns zero ... and at least on my machine,
> system("") returns zero. It certainly does not prove that any data went
> to anyplace safe.
>
> I diked that test out of the committed patch because I felt it cluttered
> the archive area without actually proving anything of interest. We can
> revisit the point if you like.
>
If the test doesn't guarantee success, then it needs to go....
Thanks for removing it.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 07:35 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-19 07:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: markw@osdl.org; pgman@candle.pha.pa.us; kn@mgnet.de; pgsql-patches@postgresql.org
On Mon, 2004-07-19 at 04:03, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
> > Latest version, pitr_v5_2.patch...
>
> Reviewed and committed with some adjustments.
>
Wow! Thanks very much - you work fast.
I'll be re-testing later today.
> I see the following significant loose ends:
>
> * Documentation is, um, lacking. (One point in particular is that I
> inserted the recovery.conf.sample file into CVS, but did not fill in
> the patch's lack of attempt to install it anywhere.)
>
Yes...wasn't sure what to do with that. Is everybody happy to install it
as a sample into the main Data Directory? (i.e. as recovery.conf.sample
rather than recovery.conf which would be a bad thing).
> * As Bruce has pointed out already, the process of making a backup
> needs some improvements for more safety: the starting and ending WAL
> offsets have got to be recorded somehow.
>
Haven't got to that yet, but will do.
> * As I have pointed out already, we need to invent "timelines" to
> allow incompatible WAL segments to exist side-by-side. I will volunteer
> to look into this.
Yes, discussing on the other thread.
>
> * I think creating a .ready file during XLogFileOpen is completely bogus,
> for reasons mentioned in committed comments (look for XXX). Possibly
> this can go away with timelines.
Yes, to some extent it would go away with timelines.
If you have a local copy at the end of a timeline that isn't archived,
then it seems a good idea to archive it, or at least copy it somewhere
safe. If you don't then you will not be able to revert to a full
recovery of that timeline in the future should you choose to do so.
The code and its location may be somewhat more suspect.... :)
>
> * I am wondering if it wouldn't be a good idea to remove the local copy
> of any segment we successfully obtain from archive. The existing
> comments note that we might get a wrong or corrupted file from archive,
> but aren't we in at least as much risk of using an obsolete segment
> restored from backup if we leave the local segment in place? (The
> archive recovery run itself will know not to do this, but if we crash
> shortly thereafter, the ensuing recovery run would NOT know not to
> trust such files.)
>
I agree they're a loose end that needs some thought.
I avoided that decision by going around the files. We originally agreed
that we would keep that data....reason was you can't tell whether the
files have been restored by a backup that forgot to exclude pg_xlog, or
that we are choosing to do a PITR recovery on an otherwise healthy
system (or as the comments explain maybe we lost everything except
pg_xlog).
If we crash during recovery it doesn't crash recover and restart.
If we crash after recovery, then the checkpoint record will have moved
forward and we so we don't then accidentally re-use those local copies.
Timelines will solve this...
>
> Perhaps the last point is really a backup-process issue. AFAICS there
> is no good reason for a backup tarfile to include $PGDATA/pg_xlog at
> all, and some good reasons for it not to. Can we redesign either the
> backup process or the disk layout so that that will not happen? Then
> we could stop worrying about stale local pg_xlog files.
>
Thats the way I saw it.
Seems fairly easy to say "don't backup pg_xlog", but you can't guarantee
they won't, even if you tell them not to...
What is stale today maybe considered to be actually your best option
when testing to see whether a recovery has achieved your objectives.
I'll read the who patch, your comments and test before I respond
further. Thanks for working so hard on this, so quickly.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 16:35 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-19 16:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> * Documentation is, um, lacking. (One point in particular is that I
> >> inserted the recovery.conf.sample file into CVS, but did not fill in
> >> the patch's lack of attempt to install it anywhere.)
>
> > I figure it should go in share like the other sample files, and tell
> > people to copy it to /data and modify it for recovery.
>
> It should certainly go to /share as a .sample file. I was thinking that
> initdb should perhaps copy it into $PGDATA (still as .sample, not as
> .conf!) so it'd be right there when you need it.
I think /share is best. I see other *.share file that aren't used until
you rename them and move them to the right directory, and
recovery.conf.sample seems the same. I think having the sample at the
top of data when for most people it will be unused is strange.
> >> Perhaps the last point is really a backup-process issue. AFAICS there
> >> is no good reason for a backup tarfile to include $PGDATA/pg_xlog at
> >> all, and some good reasons for it not to.
>
> > Seems we should just clear out the /pg_xlog directory before we start
> > recovery.
>
> No, that's a horrid idea, because it loses the ability to combine
> archival xlog files with recent files in /pg_xlog that are not yet
> archived. We need to distinguish old files that were accidentally
> captured by backup from very-recent files. I think the cleanest way to
> do that is for backup not to capture them in the first place.
I am confused. Aren't we always doing a restore from a backup? Are you
saying there are cases where we aren't and need the stuff in pg_xlog?
Are you saying we might have some new WAL files that we want to add to
pg_xlog before we do the restore, like the most recent WAL that wasn't
archived because it wasn't finished? Why would we be doing a recover if
we had such files? I see your point that we wouldn't know which file
to use, the archive version or the pg_xlog version, but actually
wouldn't the archive version always be preferred because we would know
it to be complete.
I don't see any reliable way to prevent people from having pg_xlog in
their backups seeing they might use snapshots, tar, etc.
> > We are going to rename recovery.conf to recovery.in-progress
> > or something to prevent us from clearing out the directory after a
> > crash, right?
>
> I had second thoughts about that and didn't do it in the committed
> patch, though it's certainly still open for debate.
How are we handling a crash during recovery?
> > (I see you rename recovery.conf to recovery.done. Is
> > that wise?
>
> Yes. Once you've done with a PITR recovery you definitely do *not* want
> a subsequent crash recovery to think it should obey your recovery_target
> limit. But if you fail before you've finished the recovery run it
> should theoretically be okay to retry, so I didn't add code to rename to
> "recovery.inprogress". We can certainly add it later if we decide it's
> a good idea.
Ah, OK, so it just keeps going. However, we don't know if what is in
pg_xlog was in the process of being copied from the archive at the time
of the crash, no? In fact I am wondering if we should be transfering
the archive files into temporary names than doing an 'mv' to make them
current so we don't get partial files in pg_xlog. However, we can't do
that because we are using a user-supplied command line. Should we pass
a fake name to the command string then do the 'mv' ourselves. With WAL
now, we do an fsync so we know the contents are crash-proof, but I am
not sure how to do that during recovery. I guess this gets back to how
to handle the contents of pg_xlog during recovery.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 16:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 3 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-19 16:56 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> It should certainly go to /share as a .sample file. I was thinking that
>> initdb should perhaps copy it into $PGDATA (still as .sample, not as
>> .conf!) so it'd be right there when you need it.
> I think /share is best.
Okay, we agree on that part at least; I'll take care of it. If anyone
wants to argue for further copying during initdb, that can be added
later.
> I am confused. Aren't we always doing a restore from a backup?
No. This code serves two purposes: recovery from archived WAL and
point-in-time recovery. You might want to do a PITR run at a time
where not all your WAL segments have been pushed to archive. Indeed
the latest one can never be so pushed, since it's unfinished. Suppose
you are trying to do PITR recovery to a time just a few minutes ago
that is still in the latest WAL segment --- there is simply not any
legal way to have that come from the archive.
So we can't simply zero out pg_xlog at the start of a PITR run, even
if there weren't a don't-destroy-data argument against it.
>> I had second thoughts about that and didn't do it in the committed
>> patch, though it's certainly still open for debate.
> How are we handling a crash during recovery?
Retry, perhaps. It doesn't seem any different from crash-during-recovery
in the non-archived scenario ...
> Ah, OK, so it just keeps going. However, we don't know if what is in
> pg_xlog was in the process of being copied from the archive at the time
> of the crash, no?
Nonissue. It goes into RECOVERYXLOG and we never assume that that's
initially good. See RestoreArchivedXLog().
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 20:24 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-19 20:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> It should certainly go to /share as a .sample file. I was thinking that
> >> initdb should perhaps copy it into $PGDATA (still as .sample, not as
> >> .conf!) so it'd be right there when you need it.
>
> > I think /share is best.
>
> Okay, we agree on that part at least; I'll take care of it. If anyone
> wants to argue for further copying during initdb, that can be added
> later.
>
> > I am confused. Aren't we always doing a restore from a backup?
>
> No. This code serves two purposes: recovery from archived WAL and
> point-in-time recovery. You might want to do a PITR run at a time
> where not all your WAL segments have been pushed to archive. Indeed
> the latest one can never be so pushed, since it's unfinished. Suppose
> you are trying to do PITR recovery to a time just a few minutes ago
> that is still in the latest WAL segment --- there is simply not any
> legal way to have that come from the archive.
>
> So we can't simply zero out pg_xlog at the start of a PITR run, even
> if there weren't a don't-destroy-data argument against it.
If we had some code that checks pg_xlog on recovery startup, it could
rename each pg_xlog file and then recover the file from the archive. If
it doesn't exist or is truncated, discard it. If it is the right size,
we need to check to see which one has a WAL eof-of-segment marker (we
have on of those, right?). This would seem to catch all the cases:
o file brought back by tar, but complete file in archive
o archive in process of writing during crash
o partially full file in pg_xlog
What it doesn't cover are cases where tar gets a partial copy of a
pg_xlog file but the file never made it to archive yet, and a new
pg_xlog file was created and we get some of that file too. In fact, the
backup could get holes in the pg_xlog file where the backup has zeros
but the real file had data added to it after the zeros:
in tar XXXXX 00000 XXXXX
real XXXXX XXXXX XXXXX
This could happen when file has this:
XXXXX 00000 00000
backup reads this:
XXXXX 00000
database writes this:
XXXXX XXXXX XXXXX
backup reads the remainder of the file:
XXXXX 00000 XXXXX
In this case the end-of-segment marker doesn't even help us, and their
might not be an archive copy of this because it didn't happen yet.
I think I see a solution. We are going to create a file during backup so
we know the wal offsets and xids. If we see that file, we know either
we have a restore of a backup or they currently running a backup. If we
tell them not to restore while a backup is running (seems pretty
obvious) we can then delete pg_xlog when the backup wal offset file
exists. In other cases, we know the WAL files are valid to use.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 20:45 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-19 20:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
On Mon, 2004-07-19 at 05:54, Tom Lane wrote:
> code in Simon's original patch that would start bleating
Code that bleats? LOL :) (is that a new log level?)
Some of it was perhaps a little woolly....
You've made my day, Simon Riggs (still laughing)
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 21:24 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-19 21:24 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> we need to check to see which one has a WAL eof-of-segment marker (we
> have on of those, right?).
No, we don't.
> I think I see a solution. We are going to create a file during backup so
> we know the wal offsets and xids. If we see that file, we know either
> we have a restore of a backup or they currently running a backup.
... or the last backup attempt failed, but they forgot to remove the
file it left. Or we are doing crash recovery after the system lost
power while a backup was running. Or half a dozen other obvious scenarios.
> If we tell them not to restore while a backup is running (seems pretty
> obvious) we can then delete pg_xlog when the backup wal offset file
> exists. In other cases, we know the WAL files are valid to use.
We're not deleting pg_xlog, period. IMHO it's too dangerous even to
have such a function in the code.
My original suggestion was to *replace* individual xlog files with data
extracted from archive, and only after determining that the archive
indeed has a copy of that particular file (and we can fetch it).
This at least has a fighting chance of not losing information. Wiping
pg_xlog in toto on the basis of a guess about the system status is just
a form of russian roulette. Sooner or later you will wipe some xlog
files that you can't get back from archive.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 21:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-19 21:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > we need to check to see which one has a WAL eof-of-segment marker (we
> > have on of those, right?).
>
> No, we don't.
>
> > I think I see a solution. We are going to create a file during backup so
> > we know the wal offsets and xids. If we see that file, we know either
> > we have a restore of a backup or they currently running a backup.
>
> ... or the last backup attempt failed, but they forgot to remove the
> file it left. Or we are doing crash recovery after the system lost
> power while a backup was running. Or half a dozen other obvious scenarios.
>
> > If we tell them not to restore while a backup is running (seems pretty
> > obvious) we can then delete pg_xlog when the backup wal offset file
> > exists. In other cases, we know the WAL files are valid to use.
>
> We're not deleting pg_xlog, period. IMHO it's too dangerous even to
> have such a function in the code.
>
> My original suggestion was to *replace* individual xlog files with data
> extracted from archive, and only after determining that the archive
> indeed has a copy of that particular file (and we can fetch it).
> This at least has a fighting chance of not losing information. Wiping
> pg_xlog in toto on the basis of a guess about the system status is just
> a form of russian roulette. Sooner or later you will wipe some xlog
> files that you can't get back from archive.
OK, if you don't want to place restrictions on recovery, fine, but how
do you handle the situation where you backup but the WAL file has holes
in the tar backup but you don't have an archive file to use because it
didn't make it to the archive before the drive died? Can we detect
holes in the WAL file recovered from backup? We might, but I am afraid
we might not.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-19 22:14 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-19 22:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
On Mon, 2004-07-19 at 17:56, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> I had second thoughts about that and didn't do it in the committed
> >> patch, though it's certainly still open for debate.
>
> > How are we handling a crash during recovery?
>
> Retry, perhaps. It doesn't seem any different from crash-during-recovery
> in the non-archived scenario ...
>
Well, a recovery is just re-applying already written logs at super
speed. We don't need to write WAL because we already wrote it once (and
that would really confuse the timeline issue).
I think if this was an issue, the solution would be to speed up recovery
since that would benefit us more than putting recovery-squared code in.
Just start over...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-20 01:22 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 111+ messages in thread
From: Christopher Kings-Lynne @ 2004-07-20 01:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; kn@mgnet.de; pgsql-patches@postgresql.org
> Okay, we agree on that part at least; I'll take care of it. If anyone
> wants to argue for further copying during initdb, that can be added
> later.
I reckon it should be copied into $PGDATA :) Otherwise, when I'm in a
panic at recovery time, I'd have to figure out where the heck my package
has installed the share conf file to, conf files usually aren't in
share, etc., etc.
Chris
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-20 15:36 markw@osdl.org
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 111+ messages in thread
From: markw@osdl.org @ 2004-07-20 15:36 UTC (permalink / raw)
To: tgl@sss.pgh.pa.us; simon@2ndquadrant.com; +Cc: pgman@candle.pha.pa.us; kn@mgnet.de; pgsql-patches@postgresql.org
On 18 Jul, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.com> writes:
>> Latest version, pitr_v5_2.patch...
>
> Reviewed and committed with some adjustments.
I pull from CVS and and got the following message when I tried starting
the database with the archive_mode parameter:
FATAL: unrecognized configuration parameter "archive_mode"
Have I missed something since it has been committed?
Mark
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-20 16:29 Klaus Naumann <kn@mgnet.de>
parent: markw@osdl.org
0 siblings, 2 replies; 111+ messages in thread
From: Klaus Naumann @ 2004-07-20 16:29 UTC (permalink / raw)
To: markw@osdl.org; +Cc: tgl@sss.pgh.pa.us; simon@2ndquadrant.com; pgman@candle.pha.pa.us; kn@mgnet.de; pgsql-patches@postgresql.org
On Tue, 20 Jul 2004 markw@osdl.org wrote:
> FATAL: unrecognized configuration parameter "archive_mode"
>
> Have I missed something since it has been committed?
Yes, Tom has removed this option in favorite of just setting
archive_command to a value which then enables the PITR code also.
But as I've seen this isn't discussed to the very end currently.
My 2ct: I'd prefer to have archive_mode in the config as it really makes
clear that this database is archiving. I fear users will not understand
that giving a program for archival will also enable the PITR function.
Greetings, Klaus
--
Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964 | E-Mail: (kn@mgnet.de)
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-20 17:27 Simon Riggs <simon@2ndquadrant.com>
parent: Klaus Naumann <kn@mgnet.de>
1 sibling, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-20 17:27 UTC (permalink / raw)
To: Klaus Naumann <kn@mgnet.de>; +Cc: markw@osdl.org; tgl@sss.pgh.pa.us; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
On Tue, 2004-07-20 at 17:29, Klaus Naumann wrote:
> On Tue, 20 Jul 2004 markw@osdl.org wrote:
>
> > FATAL: unrecognized configuration parameter "archive_mode"
> >
> > Have I missed something since it has been committed?
>
> Yes, Tom has removed this option in favorite of just setting
> archive_command to a value which then enables the PITR code also.
>
> But as I've seen this isn't discussed to the very end currently.
>
> My 2ct: I'd prefer to have archive_mode in the config as it really makes
> clear that this database is archiving. I fear users will not understand
> that giving a program for archival will also enable the PITR function.
>
I do also think that option should go back in, just to be explicit.
A more important omission is the deletion of a message to indicate that
the server is acting in archive_mode....so there's no visual clue in the
log to warn an admin that its been turned off now or incorrectly
specified (by somebody else, of course). (At least using the default log
mode).
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-20 22:09 Mark Kirkwood <markir@coretech.co.nz>
parent: Klaus Naumann <kn@mgnet.de>
1 sibling, 1 reply; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-20 22:09 UTC (permalink / raw)
To: Klaus Naumann <kn@mgnet.de>; +Cc: markw@osdl.org; tgl@sss.pgh.pa.us; simon@2ndquadrant.com; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
I'd vote for it as a clarity factor too.
Klaus Naumann wrote:
>On Tue, 20 Jul 2004 markw@osdl.org wrote:
>
>
>
>>FATAL: unrecognized configuration parameter "archive_mode"
>>
>>Have I missed something since it has been committed?
>>
>>
>
>Yes, Tom has removed this option in favorite of just setting
>archive_command to a value which then enables the PITR code also.
>
>But as I've seen this isn't discussed to the very end currently.
>
>My 2ct: I'd prefer to have archive_mode in the config as it really makes
>clear that this database is archiving. I fear users will not understand
>that giving a program for archival will also enable the PITR function.
>
>Greetings, Klaus
>
>
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-21 01:41 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Mark Kirkwood <markir@coretech.co.nz>
0 siblings, 0 replies; 111+ messages in thread
From: Christopher Kings-Lynne @ 2004-07-21 01:41 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: Klaus Naumann <kn@mgnet.de>; markw@osdl.org; tgl@sss.pgh.pa.us; simon@2ndquadrant.com; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
I'm in favour of how it is now, so long as the comment is clear. It's
the Unix Way :)
Chris
> I'd vote for it as a clarity factor too.
>
> Klaus Naumann wrote:
>
>> On Tue, 20 Jul 2004 markw@osdl.org wrote:
>>
>>
>>
>>> FATAL: unrecognized configuration parameter "archive_mode"
>>>
>>> Have I missed something since it has been committed?
>>>
>>
>>
>> Yes, Tom has removed this option in favorite of just setting
>> archive_command to a value which then enables the PITR code also.
>>
>> But as I've seen this isn't discussed to the very end currently.
>>
>> My 2ct: I'd prefer to have archive_mode in the config as it really makes
>> clear that this database is archiving. I fear users will not understand
>> that giving a program for archival will also enable the PITR function.
>>
>> Greetings, Klaus
>>
>>
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-21 05:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-21 05:49 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
Simon Riggs <simon@2ndquadrant.com> writes:
> A more important omission is the deletion of a message to indicate that
> the server is acting in archive_mode....so there's no visual clue in the
> log to warn an admin that its been turned off now or incorrectly
> specified (by somebody else, of course). (At least using the default log
> mode).
Hmm, we are apparently not reading the same code. My copy shows
LOG: starting archive recovery
LOG: restore_command = "cp /home/postgres/testversion/archive/%f %p"
... blah blah ...
LOG: archive recovery complete
Which part of this is insufficiently clear?
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-21 07:49 Klaus Naumann <kn@mgnet.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Klaus Naumann @ 2004-07-21 07:49 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
On Wed, 21 Jul 2004, Tom Lane wrote:
Hi Tom,
Simon doesn't mean the recovery part. Instead he means the "normal"
startup of the server. It has to be absolutely clear (in the logfile!) if
the server was started in archive mode or not. Otherwise you always have
to guess.
On server startup there should to be a message like
LOG: Database started in archive mode
or
LOG: Archive mode is DISABLED
To get the users attention.
Greetings, Klaus
> Simon Riggs <simon@2ndquadrant.com> writes:
> > A more important omission is the deletion of a message to indicate that
> > the server is acting in archive_mode....so there's no visual clue in the
> > log to warn an admin that its been turned off now or incorrectly
> > specified (by somebody else, of course). (At least using the default log
> > mode).
>
> Hmm, we are apparently not reading the same code. My copy shows
>
> LOG: starting archive recovery
> LOG: restore_command = "cp /home/postgres/testversion/archive/%f %p"
> ... blah blah ...
> LOG: archive recovery complete
>
> Which part of this is insufficiently clear?
>
> regards, tom lane
>
>
--
Full Name : Klaus Naumann | (http://www.mgnet.de/) (Germany)
Phone / FAX : ++49/177/7862964 | E-Mail: (kn@mgnet.de)
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-21 14:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Klaus Naumann <kn@mgnet.de>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-21 14:53 UTC (permalink / raw)
To: Klaus Naumann <kn@mgnet.de>; +Cc: Simon Riggs <simon@2ndquadrant.com>; markw@osdl.org; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
Klaus Naumann <kn@mgnet.de> writes:
> Simon doesn't mean the recovery part. Instead he means the "normal"
> startup of the server. It has to be absolutely clear (in the logfile!) if
> the server was started in archive mode or not. Otherwise you always have
> to guess.
Why would you guess? "SHOW archive_command" will tell you, without
question, at any time. I don't see the point of placing such a message
in the postmaster log --- in normal circumstances the postmaster will
still be running long after its starting messages have been discarded
due to log rotation.
Also, the current implementation allows you to stop and start archiving
on-the-fly, so a start-time message would be an unreliable guide to what
the postmaster is actually doing at the moment.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-21 20:00 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Simon Riggs @ 2004-07-21 20:00 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgman@candle.pha.pa.us; pgsql-patches@postgresql.org
On Wed, 2004-07-21 at 15:53, Tom Lane wrote:
> Klaus Naumann <kn@mgnet.de> writes:
> > Simon doesn't mean the recovery part. Instead he means the "normal"
> > startup of the server. It has to be absolutely clear (in the logfile!) if
> > the server was started in archive mode or not. Otherwise you always have
> > to guess.
>
> Why would you guess? "SHOW archive_command" will tell you, without
> question, at any time. I don't see the point of placing such a message
> in the postmaster log --- in normal circumstances the postmaster will
> still be running long after its starting messages have been discarded
> due to log rotation.
>
> Also, the current implementation allows you to stop and start archiving
> on-the-fly, so a start-time message would be an unreliable guide to what
> the postmaster is actually doing at the moment.
>
Overall, this is a small point and I think we should leave Tom alone, to
focus on the bigger issues that we care about.
Tom has done an amazingly good job in the last few days of refactoring
some reasonably ugly code on my part, all without a murmur. I relent on
this to allow everything to be finished in time.
The PITR journey has just begun, so there will be further opportunity to
discuss and agree what constitutes real issues and then correct them.
This may not be on that list later.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 01:51 Mark Kirkwood <markir@coretech.co.nz>
parent: Simon Riggs <simon@2ndquadrant.com>
3 siblings, 2 replies; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-22 01:51 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: pgsql-admin
Here is one for the 'idiot proof' category:
1) initdb and set archive_command
2) shutdown
3) do a backup
4) startup and run some transactions
5) shutdown and remove PGDATA
6) restore backup
7) startup
Obviously this does not work as the backup is performed with the
database shutdown.
This got me wondering for 2 reasons:
1) Some alternative database servers *require* a procedure like this to
enable their version of PITR - so the potential foot-gun thing is there.
2) Is is possible to make the recovery kick in even though pg_control
says the database state is shutdown?
Simon Riggs wrote:
>
>I was hoping some fiendish plans would be presented to me...
>
>But please start with "this feels like typical usage" and we'll go from
>there...the important thing is to try the first one.
>
>I've not done power off tests, yet. They need to be done just to
>check...actually you don't need to do this to test PITR...
>
>We need to exhaustive tests of...
>- power off
>- scp and cross network copies
>- all the permuted recovery options
>- archive_mode = off (i.e. current behaviour)
>- deliberately incorrectly set options (idiot-proof testing)
>
>
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 02:39 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Kirkwood <markir@coretech.co.nz>
1 sibling, 2 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-22 02:39 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Mark Kirkwood <markir@coretech.co.nz> writes:
> Here is one for the 'idiot proof' category:
> 1) initdb and set archive_command
> 2) shutdown
> 3) do a backup
> 4) startup and run some transactions
> 5) shutdown and remove PGDATA
> 6) restore backup
> 7) startup
> Obviously this does not work as the backup is performed with the
> database shutdown.
Huh? It works fine.
The bit you may be missing is that if you blow away $PGDATA including
pg_xlog/, you won't be able to recover past whatever you have in your WAL
archive area. The archive is certainly not going to include the current
partially-filled WAL segment, and it might be missing a few earlier
segments if the archival process isn't speedy. So you need to keep
those recent segments in pg_xlog/ if you want to recover to current time
or near-current time.
I'm becoming more and more convinced that we should bite the bullet and
move pg_xlog/ to someplace that is not under $PGDATA. It would just
make things a whole lot more reliable, both for backup and to deal with
scenarios like yours above. I tried to talk Bruce into this on the
phone the other day, but he wouldn't bite. I still think it's a good
idea though. It would
(1) eliminate the problem that a tar backup of $PGDATA would restore
stale copies of xlog segments, because the tar wouldn't include
pg_xlog in the first place.
(2) eliminate the problem that a naive "rm -rf $PGDATA" would blow away
xlog segments that you still need.
A possible compromise is that we should strongly suggest that pg_xlog
be pushed out to another place and symlinked if you are going to use
WAL archiving. That's already considered good practice for performance
if you have a separate disk spindle to put WAL on. It'll just have
to be good practive for WAL archiving too.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:06 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-22 03:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
I think we should push the partially complete WAL file to the archive
location before shutdown. I talked to you or Jan about it and you (or
Jan) wouldn't bite either, but I think when someone shuts down, they
assume they have things fully archived and can recover fully with a
previous backup and the archive files.
When you are running and finally fill up the WAL file it would then
overwrite the one in the archive but I think that is OK. Maybe we would
need to give it a special file extension so we only use it when we don't
have a full version.
---------------------------------------------------------------------------
Tom Lane wrote:
> Mark Kirkwood <markir@coretech.co.nz> writes:
> > Here is one for the 'idiot proof' category:
> > 1) initdb and set archive_command
> > 2) shutdown
> > 3) do a backup
> > 4) startup and run some transactions
> > 5) shutdown and remove PGDATA
> > 6) restore backup
> > 7) startup
>
> > Obviously this does not work as the backup is performed with the
> > database shutdown.
>
> Huh? It works fine.
>
> The bit you may be missing is that if you blow away $PGDATA including
> pg_xlog/, you won't be able to recover past whatever you have in your WAL
> archive area. The archive is certainly not going to include the current
> partially-filled WAL segment, and it might be missing a few earlier
> segments if the archival process isn't speedy. So you need to keep
> those recent segments in pg_xlog/ if you want to recover to current time
> or near-current time.
>
> I'm becoming more and more convinced that we should bite the bullet and
> move pg_xlog/ to someplace that is not under $PGDATA. It would just
> make things a whole lot more reliable, both for backup and to deal with
> scenarios like yours above. I tried to talk Bruce into this on the
> phone the other day, but he wouldn't bite. I still think it's a good
> idea though. It would
> (1) eliminate the problem that a tar backup of $PGDATA would restore
> stale copies of xlog segments, because the tar wouldn't include
> pg_xlog in the first place.
> (2) eliminate the problem that a naive "rm -rf $PGDATA" would blow away
> xlog segments that you still need.
>
> A possible compromise is that we should strongly suggest that pg_xlog
> be pushed out to another place and symlinked if you are going to use
> WAL archiving. That's already considered good practice for performance
> if you have a separate disk spindle to put WAL on. It'll just have
> to be good practive for WAL archiving too.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:12 Mark Kirkwood <markir@coretech.co.nz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-22 03:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Well that is interesting :_)
Here is what I am doing on the removal front (I am keeping pg_xlog *now*):
$ cd $PGDATA
$ pg_ctl stop
$ ls|grep -v pg_xlog|xargs rm -rf
The contents of the archive directory just before recovery starts:
$ ls -l $PGDATA/../7.5-archive
total 49212
-rw------- 1 postgres postgres 16777216 Jul 22 14:59
000000010000000000000000
-rw------- 1 postgres postgres 16777216 Jul 22 14:59
000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jul 22 14:59
000000010000000000000002
But here is recovery startup log:
LOG: database system was shut down at 2004-07-22 14:58:57 NZST
LOG: starting archive recovery
LOG: restore_command = "cp /data1/pgdata/7.5-archive/%f %p"
cp: cannot stat `/data1/pgdata/7.5-archive/00000001.history': No such
file or directory
LOG: restored log file "000000010000000000000000" from archive
LOG: checkpoint record is at 0/A4D3E8
LOG: redo record is at 0/A4D3E8; undo record is at 0/0; shutdown TRUE
LOG: next transaction ID: 496; next OID: 17229
LOG: archive recovery complete
LOG: database system is ready
regards
Mark
Tom Lane wrote:
>
>Huh? It works fine.
>
>The bit you may be missing is that if you blow away $PGDATA including
>pg_xlog/, you won't be able to recover past whatever you have in your WAL
>archive area. The archive is certainly not going to include the current
>partially-filled WAL segment, and it might be missing a few earlier
>segments if the archival process isn't speedy. So you need to keep
>those recent segments in pg_xlog/ if you want to recover to current time
>or near-current time.
>
>
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 4 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-22 03:29 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I think we should push the partially complete WAL file to the archive
> location before shutdown. ...
> When you are running and finally fill up the WAL file it would then
> overwrite the one in the archive but I think that is OK.
I don't think this can fly at all. Here are some off-the-top-of-the-head
objections:
1. We don't have the luxury of spending indefinite amounts of time to
do a database shutdown. Commonly we are under a twenty-second sentence
of death from init. I don't want to spend the 20 seconds waiting to see
if the archiver will manage to push 16MB onto a slow tape drive. Also,
if the archiver does fail to push the data in time, it'll likely leave a
broken (partial) xlog file in the archive, which would be really bad
news if the user then relies on that.
2. What if the archiver process entirely fails to push the file? (Maybe
there's not enough disk space, for instance.) In normal operation we'll
just retry every so often. We definitely can't do that during shutdown.
3. You're blithely assuming that the archival process can easily provide
overwrite semantics for multiple pushes of the same xlog filename. Stop
thinking about "cp to some directory" and start thinking "dump to tape"
or "burn onto CD" or something like that. We'll be raising the ante
considerably if we require the archive_command to deal with this.
I think the last one is really the most significant issue. We have to
keep the archiver API as simple as possible.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:36 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-22 03:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Agreed, it might not be possible, but your report does point out a
limitation in our implementation --- that a shutdown database contains
more information than a backup and the archive logs. That is not
intuitive.
In fact, if you shutdown your database and want to reproduce it on
another machine, how do you do it? Seems you have to copy pg_xlog
directory over to the new machine.
In fact, moving pg_xlog to a new location doesn't make that clear
either. Seems documentation might be the only way to make this clear.
One idea would be to just push the partial WAL file to the archive on
server shutdown and not reuse it and start with a new WAL file on
startup. At least for a normal system shutdown this will give us an
archive that contains all the information that is in pg_xlog.
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we should push the partially complete WAL file to the archive
> > location before shutdown. ...
> > When you are running and finally fill up the WAL file it would then
> > overwrite the one in the archive but I think that is OK.
>
> I don't think this can fly at all. Here are some off-the-top-of-the-head
> objections:
>
> 1. We don't have the luxury of spending indefinite amounts of time to
> do a database shutdown. Commonly we are under a twenty-second sentence
> of death from init. I don't want to spend the 20 seconds waiting to see
> if the archiver will manage to push 16MB onto a slow tape drive. Also,
> if the archiver does fail to push the data in time, it'll likely leave a
> broken (partial) xlog file in the archive, which would be really bad
> news if the user then relies on that.
>
> 2. What if the archiver process entirely fails to push the file? (Maybe
> there's not enough disk space, for instance.) In normal operation we'll
> just retry every so often. We definitely can't do that during shutdown.
>
> 3. You're blithely assuming that the archival process can easily provide
> overwrite semantics for multiple pushes of the same xlog filename. Stop
> thinking about "cp to some directory" and start thinking "dump to tape"
> or "burn onto CD" or something like that. We'll be raising the ante
> considerably if we require the archive_command to deal with this.
>
> I think the last one is really the most significant issue. We have to
> keep the archiver API as simple as possible.
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-22 03:42 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Agreed, it might not be possible, but your report does point out a
> limitation in our implementation --- that a shutdown database contains
> more information than a backup and the archive logs. That is not
> intuitive.
That's only because you are clinging to the broken assumption that
pg_xlog/ is part of the database, rather than part of the logs.
Separate that out as a distinct entity, and all gets better.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 03:54 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-22 03:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Agreed, it might not be possible, but your report does point out a
> > limitation in our implementation --- that a shutdown database contains
> > more information than a backup and the archive logs. That is not
> > intuitive.
>
> That's only because you are clinging to the broken assumption that
> pg_xlog/ is part of the database, rather than part of the logs.
> Separate that out as a distinct entity, and all gets better.
Imagine this. I stop the server. I have a tar backup and a copy of
the archive. I should be able to take them to another machine and
recover the system to the point I stopped.
You are saying I need a copy of pg_xlog directory too, and I need to
remove pg_xlog after I untar the data directory and put the saved
pg_xlog into there before I recover.
Should we create a server-side function that forces all WAL files to the
archive, including partially written ones. Maybe that fixes the problem
with people deleting pg_xlog before they untar. You tell them to run
the function before recovery. If the system can't be started, the it is
possible the WAL files are no good too, not sure.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 07:43 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-22 07:43 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Mark Kirkwood <markir@coretech.co.nz>; pgsql-admin
On Thu, 2004-07-22 at 04:29, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we should push the partially complete WAL file to the archive
> > location before shutdown. ...
> > When you are running and finally fill up the WAL file it would then
> > overwrite the one in the archive but I think that is OK.
>
> I don't think this can fly at all. Here are some off-the-top-of-the-head
> objections:
>
> 1. We don't have the luxury of spending indefinite amounts of time to
> do a database shutdown. Commonly we are under a twenty-second sentence
> of death from init. I don't want to spend the 20 seconds waiting to see
> if the archiver will manage to push 16MB onto a slow tape drive. Also,
> if the archiver does fail to push the data in time, it'll likely leave a
> broken (partial) xlog file in the archive, which would be really bad
> news if the user then relies on that.
>
> 2. What if the archiver process entirely fails to push the file? (Maybe
> there's not enough disk space, for instance.) In normal operation we'll
> just retry every so often. We definitely can't do that during shutdown.
>
> 3. You're blithely assuming that the archival process can easily provide
> overwrite semantics for multiple pushes of the same xlog filename. Stop
> thinking about "cp to some directory" and start thinking "dump to tape"
> or "burn onto CD" or something like that. We'll be raising the ante
> considerably if we require the archive_command to deal with this.
>
> I think the last one is really the most significant issue. We have to
> keep the archiver API as simple as possible.
>
Not read whole chain of conversation...but this idea came up before and
was rejected then. I agree with the 3 objections to that thought above.
There's already enough copies of full xlogs around to worry about.
If you need more granularity, reduce size of xlog files....
(Tom, SUID would be the correct timeline id in that situation? )
More later, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 20:19 Tom Lane <tgl@sss.pgh.pa.us>
parent: Mark Kirkwood <markir@coretech.co.nz>
1 sibling, 2 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-22 20:19 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Mark Kirkwood <markir@coretech.co.nz> writes:
> 2) Is is possible to make the recovery kick in even though pg_control
> says the database state is shutdown?
Yeah, I think you are right: presence of recovery.conf should force a
WAL scan even if pg_control claims it's shut down. Fix committed.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 23:08 Mark Kirkwood <markir@coretech.co.nz>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-22 23:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Excellent - Just updated and it is all good!
This change makes the whole "how do I do my backup" business nice and
basic - which the right way IMHO.
regards
Mark
Tom Lane wrote:
>Mark Kirkwood <markir@coretech.co.nz> writes:
>
>
>>2) Is is possible to make the recovery kick in even though pg_control
>>says the database state is shutdown?
>>
>>
>
>Yeah, I think you are right: presence of recovery.conf should force a
>WAL scan even if pg_control claims it's shut down. Fix committed.
>
> regards, tom lane
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-22 23:48 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-22 23:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; pgsql-admin
On Thu, 2004-07-22 at 21:19, Tom Lane wrote:
> Mark Kirkwood <markir@coretech.co.nz> writes:
> > 2) Is is possible to make the recovery kick in even though pg_control
> > says the database state is shutdown?
>
> Yeah, I think you are right: presence of recovery.conf should force a
> WAL scan even if pg_control claims it's shut down. Fix committed.
>
This *should* be possible but I haven't tested it.
There is a code path on secondary checkpoints that indicates that crash
recovery can occur even when the database was shutdown, since the code
forces recovery whether it was or not. On that basis, this may work, but
is yet untested. I didn't mention this because it might interfere with
getting hot backup to work...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-23 00:05 Mark Kirkwood <markir@coretech.co.nz>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 1 reply; 111+ messages in thread
From: Mark Kirkwood @ 2004-07-23 00:05 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
I have tested the "cold" backup - and retested my previous scenarios
using "hot" backup (just to be sure) . They all work AFAICS!
cheers
Mark
Simon Riggs wrote:
>On Thu, 2004-07-22 at 21:19, Tom Lane wrote:
>
>
>>Mark Kirkwood <markir@coretech.co.nz> writes:
>>
>>
>>>2) Is is possible to make the recovery kick in even though pg_control
>>>says the database state is shutdown?
>>>
>>>
>>Yeah, I think you are right: presence of recovery.conf should force a
>>WAL scan even if pg_control claims it's shut down. Fix committed.
>>
>>
>>
>
>This *should* be possible but I haven't tested it.
>
>There is a code path on secondary checkpoints that indicates that crash
>recovery can occur even when the database was shutdown, since the code
>forces recovery whether it was or not. On that basis, this may work, but
>is yet untested. I didn't mention this because it might interfere with
>getting hot backup to work...
>
>Best Regards, Simon Riggs
>
>
>
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-23 02:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
1 sibling, 0 replies; 111+ messages in thread
From: Tom Lane @ 2004-07-23 02:01 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; pgsql-admin
Simon Riggs <simon@2ndquadrant.com> writes:
> On Thu, 2004-07-22 at 21:19, Tom Lane wrote:
>> Yeah, I think you are right: presence of recovery.conf should force a
>> WAL scan even if pg_control claims it's shut down. Fix committed.
> This *should* be possible but I haven't tested it.
I did.
It's really not risky. The fact that the code doesn't look beyond the
checkpoint record when things seem to be kosher is just a speed
optimization (and probably a rather pointless one...) We have got to be
able to detect the end of WAL in any case, so we'd just find there are
no more records and stop.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-24 20:35 Simon Riggs <simon@2ndquadrant.com>
parent: Mark Kirkwood <markir@coretech.co.nz>
0 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2004-07-24 20:35 UTC (permalink / raw)
To: Mark Kirkwood <markir@coretech.co.nz>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Fri, 2004-07-23 at 01:05, Mark Kirkwood wrote:
> I have tested the "cold" backup - and retested my previous scenarios
> using "hot" backup (just to be sure) . They all work AFAICS!
> cheers
Yes, I'll drink to that! Thanks for your help.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-28 16:21 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 2 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-28 16:21 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
I do think we need a boolean for start/stop of archiving, rather than
setting it to '' to turn it off. Tom, I think the group agreed to this
on clarity grounds. I would like the server to throw an error if you
try to turn on archiving and the command is set to ''.
---------------------------------------------------------------------------
Simon Riggs wrote:
> On Wed, 2004-07-21 at 15:53, Tom Lane wrote:
> > Klaus Naumann <kn@mgnet.de> writes:
> > > Simon doesn't mean the recovery part. Instead he means the "normal"
> > > startup of the server. It has to be absolutely clear (in the logfile!) if
> > > the server was started in archive mode or not. Otherwise you always have
> > > to guess.
> >
> > Why would you guess? "SHOW archive_command" will tell you, without
> > question, at any time. I don't see the point of placing such a message
> > in the postmaster log --- in normal circumstances the postmaster will
> > still be running long after its starting messages have been discarded
> > due to log rotation.
> >
> > Also, the current implementation allows you to stop and start archiving
> > on-the-fly, so a start-time message would be an unreliable guide to what
> > the postmaster is actually doing at the moment.
> >
>
> Overall, this is a small point and I think we should leave Tom alone, to
> focus on the bigger issues that we care about.
>
> Tom has done an amazingly good job in the last few days of refactoring
> some reasonably ugly code on my part, all without a murmur. I relent on
> this to allow everything to be finished in time.
>
> The PITR journey has just begun, so there will be further opportunity to
> discuss and agree what constitutes real issues and then correct them.
> This may not be on that list later.
>
> Best Regards, Simon Riggs
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-28 16:25 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-28 16:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Mark Kirkwood <markir@coretech.co.nz>; Simon Riggs <simon@2ndquadrant.com>; pgsql-admin
Here is another open PITR issue that I think will have to be addressed
in 7.6. If you do a critical transaction, but do nothing else for eight
hours, that critical transaction hasn't been archived yet. It is still
sitting in pg_xlog until the WAL file fills.
I think we will need to document this behavior and address it in some
way in 7.6. We can't assume that we can send multiple copies of pg_xlog
to the archive (partial and full ones) because we might be going to a
tape drive. However, this is a non-intuitive behavior of our archiver.
We might need to tell people to archive the most recent WAL file every
minute to some other location or something.
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we should push the partially complete WAL file to the archive
> > location before shutdown. ...
> > When you are running and finally fill up the WAL file it would then
> > overwrite the one in the archive but I think that is OK.
>
> I don't think this can fly at all. Here are some off-the-top-of-the-head
> objections:
>
> 1. We don't have the luxury of spending indefinite amounts of time to
> do a database shutdown. Commonly we are under a twenty-second sentence
> of death from init. I don't want to spend the 20 seconds waiting to see
> if the archiver will manage to push 16MB onto a slow tape drive. Also,
> if the archiver does fail to push the data in time, it'll likely leave a
> broken (partial) xlog file in the archive, which would be really bad
> news if the user then relies on that.
>
> 2. What if the archiver process entirely fails to push the file? (Maybe
> there's not enough disk space, for instance.) In normal operation we'll
> just retry every so often. We definitely can't do that during shutdown.
>
> 3. You're blithely assuming that the archival process can easily provide
> overwrite semantics for multiple pushes of the same xlog filename. Stop
> thinking about "cp to some directory" and start thinking "dump to tape"
> or "burn onto CD" or something like that. We'll be raising the ante
> considerably if we require the archive_command to deal with this.
>
> I think the last one is really the most significant issue. We have to
> keep the archiver API as simple as possible.
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [ADMIN] Point in Time Recovery
@ 2004-07-28 16:27 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
3 siblings, 0 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-28 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
[ Sorry, sent to hackers now.]
Here is another open PITR issue that I think will have to be addressed
in 7.6. If you do a critical transaction, but do nothing else for eight
hours, that critical transaction hasn't been archived yet. It is still
sitting in pg_xlog until the WAL file fills.
I think we will need to document this behavior and address it in some
way in 7.6. We can't assume that we can send multiple copies of pg_xlog
to the archive (partial and full ones) because we might be going to a
tape drive. However, this is a non-intuitive behavior of our archiver.
We might need to tell people to copy the most recent WAL file every
minute to some other location or something.
---------------------------------------------------------------------------
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I think we should push the partially complete WAL file to the archive
> > location before shutdown. ...
> > When you are running and finally fill up the WAL file it would then
> > overwrite the one in the archive but I think that is OK.
>
> I don't think this can fly at all. Here are some off-the-top-of-the-head
> objections:
>
> 1. We don't have the luxury of spending indefinite amounts of time to
> do a database shutdown. Commonly we are under a twenty-second sentence
> of death from init. I don't want to spend the 20 seconds waiting to see
> if the archiver will manage to push 16MB onto a slow tape drive. Also,
> if the archiver does fail to push the data in time, it'll likely leave a
> broken (partial) xlog file in the archive, which would be really bad
> news if the user then relies on that.
>
> 2. What if the archiver process entirely fails to push the file? (Maybe
> there's not enough disk space, for instance.) In normal operation we'll
> just retry every so often. We definitely can't do that during shutdown.
>
> 3. You're blithely assuming that the archival process can easily provide
> overwrite semantics for multiple pushes of the same xlog filename. Stop
> thinking about "cp to some directory" and start thinking "dump to tape"
> or "burn onto CD" or something like that. We'll be raising the ante
> considerably if we require the archive_command to deal with this.
>
> I think the last one is really the most significant issue. We have to
> keep the archiver API as simple as possible.
>
> regards, tom lane
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-28 16:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-28 16:58 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I do think we need a boolean for start/stop of archiving, rather than
> setting it to '' to turn it off. Tom, I think the group agreed to this
> on clarity grounds.
I didn't see any consensus there, nor do I see a point to it.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-29 06:10 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: Bruce Momjian @ 2004-07-29 06:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I do think we need a boolean for start/stop of archiving, rather than
> > setting it to '' to turn it off. Tom, I think the group agreed to this
> > on clarity grounds.
>
> I didn't see any consensus there, nor do I see a point to it.
I saw a lot of people saying it was a good idea, and only you saying it
was a bad idea.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-29 17:07 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-29 17:07 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Tom Lane <tgl@sss.pgh.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
Bruce Momjian wrote:
>
> I do think we need a boolean for start/stop of archiving, rather than
> setting it to '' to turn it off. Tom, I think the group agreed to this
> on clarity grounds. I would like the server to throw an error if you
> try to turn on archiving and the command is set to ''.
Let me illustrate. To turn off archiving you have to change:
#archive_command = ''
archive_command = 'cp %p /mnt/server/archivedir/%f'
to
archive_command = ''
#archive_command = 'cp %p /mnt/server/archivedir/%f'
and if you comment both or neither, you have problems.
With a boolean it would be:
archive_mode = on
archive_command = 'cp %p /mnt/server/archivedir/%f'
archive_mode = off
archive_command = 'cp %p /mnt/server/archivedir/%f'
Now, if you say people will rarely turn archiving on/off, then one
parameter seems to make more sense.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-29 17:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Tom Lane @ 2004-07-29 17:16 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Now, if you say people will rarely turn archiving on/off, then one
> parameter seems to make more sense.
I really can't envision a situation where people would do that. If you
need PITR at all then you need it 24x7.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-07-29 17:33 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Bruce Momjian @ 2004-07-29 17:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Simon Riggs <simon@2ndquadrant.com>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Now, if you say people will rarely turn archiving on/off, then one
> > parameter seems to make more sense.
>
> I really can't envision a situation where people would do that. If you
> need PITR at all then you need it 24x7.
OK, then we are OK. If we find that isn't true, we can reevaluate.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-08-14 00:50 Simon@2ndquadrant.com <simon@2ndquadrant.com>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 111+ messages in thread
From: Simon@2ndquadrant.com @ 2004-08-14 00:50 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Now, if you say people will rarely turn archiving on/off, then one
> > > parameter seems to make more sense.
> >
> > I really can't envision a situation where people would do that. If you
> > need PITR at all then you need it 24x7.
>
I agree. The second parameter is only there to clarify the intent.
8.0 does introduce two good reasons to turn it on/off, however:
- index build speedups
- COPY speedups
I would opt to make enabling/disabling archive_command require a postmaster
restart. That way there would be no capability to take advantage of the
incentive to turn it on/off.
For TODO:
It would be my intention (in 8.1) to make those available via switches e.g.
NOT LOGGED options on CREATE INDEX and COPY, to allow users to take
advantage of the no logging optimization without turning off PITR system
wide. (Just as this is possible in Oracle and Teradata).
I would also aim to make the first Insert Select into an empty table not
logged (optionally). This is an important optimization for Oracle, teradata
and DB2 (which uses NOT LOGGED INITIALLY).
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-08-14 02:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon@2ndquadrant.com <simon@2ndquadrant.com>
0 siblings, 0 replies; 111+ messages in thread
From: Tom Lane @ 2004-08-14 02:29 UTC (permalink / raw)
To: Simon@2ndquadrant.com <simon@2ndquadrant.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Klaus Naumann <kn@mgnet.de>; markw@osdl.org; pgsql-patches@postgresql.org
"Simon@2ndquadrant.com" <simon@2ndquadrant.com> writes:
> I would opt to make enabling/disabling archive_command require a postmaster
> restart. That way there would be no capability to take advantage of the
> incentive to turn it on/off.
We're generally not in the habit of making GUC parameters more rigid
than the implementation absolutely requires.
> It would be my intention (in 8.1) to make those available via switches e.g.
> NOT LOGGED options on CREATE INDEX and COPY, to allow users to take
> advantage of the no logging optimization without turning off PITR system
> wide. (Just as this is possible in Oracle and Teradata).
Isn't this in direct conflict with your opinion above? And I cannot say
that I think this one is a good idea. We do not have support for
selective catalog xlogging; if you do something like this then you
*will* have a broken database after recovery, because it will contain
those indexes but with invalid contents.
> I would also aim to make the first Insert Select into an empty table not
> logged (optionally). This is an important optimization for Oracle, teradata
> and DB2 (which uses NOT LOGGED INITIALLY).
This is even worse: not only do you have a broken database, but you have
no way to recover. (At least with an unlogged index you could fix it by
REINDEX.) If you don't care about longevity of the table, then make it
a temp table.
The fact that Oracle does it does not automatically make it a good idea.
regards, tom lane
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: [HACKERS] Point in Time Recovery
@ 2004-09-30 15:54 JEDIDIAH <jedi@nomad.mishnet>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 111+ messages in thread
From: JEDIDIAH @ 2004-09-30 15:54 UTC (permalink / raw)
To: pgsql-admin
On 2004-07-28, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
>
> Here is another open PITR issue that I think will have to be addressed
> in 7.6. If you do a critical transaction, but do nothing else for eight
> hours, that critical transaction hasn't been archived yet. It is still
> sitting in pg_xlog until the WAL file fills.
>
> I think we will need to document this behavior and address it in some
> way in 7.6. We can't assume that we can send multiple copies of pg_xlog
> to the archive (partial and full ones) because we might be going to a
If a particular transaction is so important that it absolutely
positively needs to be archived offline for PITR, then why not just mark
it that way or allow for the application to trigger archival of this
critical REDO?
> tape drive. However, this is a non-intuitive behavior of our archiver.
> We might need to tell people to archive the most recent WAL file every
> minute to some other location or something.
[deletia]
--
Negligence will never equal intent, no matter how you
attempt to distort reality to do so. This is what separates |||
the real butchers from average Joes (or Fritzes) caught up in / | \
events not in their control.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Point in time recovery
@ 2011-11-23 12:39 Karuna Karpe <karuna.karpe@os3infotech.com>
0 siblings, 3 replies; 111+ messages in thread
From: Karuna Karpe @ 2011-11-23 12:39 UTC (permalink / raw)
To: pgsql-admin
Hello,
Can any one please explain me how to use PITR?
Regards,
karuna karpe.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in time recovery
@ 2011-11-23 14:30 Simon Riggs <simon@2ndQuadrant.com>
parent: Karuna Karpe <karuna.karpe@os3infotech.com>
2 siblings, 0 replies; 111+ messages in thread
From: Simon Riggs @ 2011-11-23 14:30 UTC (permalink / raw)
To: Karuna Karpe <karuna.karpe@os3infotech.com>; +Cc: pgsql-admin
On Wed, Nov 23, 2011 at 12:39 PM, Karuna Karpe
<karuna.karpe@os3infotech.com> wrote:
> Can any one please explain me how to use PITR?
Yes, the docs contain a great explanation.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in time recovery
@ 2011-11-23 15:51 Ian Lea <ian.lea@gmail.com>
parent: Karuna Karpe <karuna.karpe@os3infotech.com>
2 siblings, 1 reply; 111+ messages in thread
From: Ian Lea @ 2011-11-23 15:51 UTC (permalink / raw)
To: Karuna Karpe <karuna.karpe@os3infotech.com>; +Cc: pgsql-admin
> Can any one please explain me how to use PITR?
http://lmgtfy.com/?q=postgres+pitr
--
Ian.
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in time recovery
@ 2011-11-24 01:21 Craig Ringer <ringerc@ringerc.id.au>
parent: Karuna Karpe <karuna.karpe@os3infotech.com>
2 siblings, 0 replies; 111+ messages in thread
From: Craig Ringer @ 2011-11-24 01:21 UTC (permalink / raw)
To: Karuna Karpe <karuna.karpe@os3infotech.com>; +Cc: pgsql-admin
On 11/23/2011 08:39 PM, Karuna Karpe wrote:
> Hello,
>
> Can any one please explain me how to use PITR?
>
Are you struggling with one particular issue with PITR setup where the
documentation isn't helping? If so, please explain in more detail,
making sure to include all the appropriate information about your setup.
See: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems
If this is a more general question about how to use PITR and you haven't
tried it yet, start with the manual here:
http://www.postgresql.org/docs/current/static/continuous-archiving.html
http://www.postgresql.org/docs/current/static/warm-standby.html
--
Craig Ringer
^ permalink raw reply [nested|flat] 111+ messages in thread
* Re: Point in time recovery
@ 2011-11-24 08:55 white.heron white <white.heron@yahoo.com>
parent: Ian Lea <ian.lea@gmail.com>
0 siblings, 0 replies; 111+ messages in thread
From: white.heron white @ 2011-11-24 08:55 UTC (permalink / raw)
To: Ian Lea <ian.lea@gmail.com>; +Cc: Karuna Karpe <karuna.karpe@os3infotech.com>; pgsql-admin
Dear All,
Point in Time Recovery in the context of computers is a system whereby a set of data or a particular setting can be restored or recovered from a time in the past.
- Windows XP's feature of being able to restore operating system settings from a past date (before data corruption occurred, for example).
- PostgreSQL's feature of being able to view a database table and its data as it was at a particular date in the past.
- Also, Time Machine for Mac OS X is an example of Point-in-time recovery.
In PostgreSQL, Write-ahead logging (WAL) must be enabled for a particular database, in order for PITR to be used on that database; any time after WAL is enabled for a database, that database may be restored to any later time.
Regards,
MOHAMMAD ADLI BIN MT TAJUDIN
A8-2-7, DESA PANDAN APARTMENTS,
OFF JALAN KG. PANDAN,
55100 KUALA LUMPUR,
WEST MALAYSIA.
H/p number: (017) 362 3661
Email: white.heron@yahoo.com
________________________________
From: Ian Lea <ian.lea@gmail.com>
To: Karuna Karpe <karuna.karpe@os3infotech.com>
Cc: pgsql-admin@postgresql.org
Sent: Wednesday, November 23, 2011 11:51 PM
Subject: Re: [ADMIN] Point in time recovery
> Can any one please explain me how to use PITR?
http://lmgtfy.com/?q=postgres+pitr
--
Ian.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 111+ messages in thread
end of thread, other threads:[~2011-11-24 08:55 UTC | newest]
Thread overview: 111+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-24 01:17 point-in-time recovery Gaetano Mendola <mendola@bigfoot.com>
2003-04-24 01:20 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-04-25 05:45 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-25 14:17 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-04-25 14:45 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-25 14:47 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-04-25 15:09 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-25 15:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-04-25 15:37 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2004-07-05 20:45 Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-05 21:46 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-05 23:11 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-06 19:00 ` Re: Point in Time Recovery Richard Huxton <dev@archonet.com>
2004-07-06 21:39 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-06 21:40 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 11:38 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 14:29 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-13 20:25 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 21:19 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-13 22:25 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 22:42 ` Re: Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-13 22:52 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 23:13 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-14 02:31 ` Re: [HACKERS] Point in Time Recovery Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-14 07:21 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-14 13:20 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-15 01:49 ` Re: Point in Time Recovery SAKATA Tetsuo <sakata.tetsuo@lab.ntt.co.jp>
2004-07-17 09:39 ` Re: [HACKERS] Point in Time Recovery Gaetano Mendola <mendola@bigfoot.com>
2004-07-22 01:51 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-22 02:39 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-22 03:06 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-22 03:29 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-22 03:36 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-22 03:42 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-22 03:54 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-22 07:43 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-28 16:25 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-09-30 15:54 ` Re: [HACKERS] Point in Time Recovery JEDIDIAH <jedi@nomad.mishnet>
2004-07-28 16:27 ` Re: [ADMIN] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-22 03:12 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-22 20:19 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-22 23:08 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-22 23:48 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-23 00:05 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-24 20:35 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-23 02:01 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-14 15:55 ` Re: [HACKERS] Point in Time Recovery markw@osdl.org
2004-07-14 19:33 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-14 22:56 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-17 18:53 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 19:08 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-17 19:18 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-17 20:15 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-18 05:04 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-18 20:20 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-19 03:13 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 06:39 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-19 03:14 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-19 04:54 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 20:45 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-19 03:03 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 03:21 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-19 03:49 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 16:35 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-19 16:56 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 20:24 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-19 21:24 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-19 21:36 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-19 22:14 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-20 01:22 ` Re: [HACKERS] Point in Time Recovery Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-19 07:35 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-20 15:36 ` Re: [HACKERS] Point in Time Recovery markw@osdl.org
2004-07-20 16:29 ` Re: [HACKERS] Point in Time Recovery Klaus Naumann <kn@mgnet.de>
2004-07-20 17:27 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-21 05:49 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-21 07:49 ` Re: [HACKERS] Point in Time Recovery Klaus Naumann <kn@mgnet.de>
2004-07-21 14:53 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-21 20:00 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-28 16:21 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-28 16:58 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-29 06:10 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-29 17:07 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-29 17:16 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-29 17:33 ` Re: [HACKERS] Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-08-14 00:50 ` Re: [HACKERS] Point in Time Recovery Simon@2ndquadrant.com <simon@2ndquadrant.com>
2004-08-14 02:29 ` Re: [HACKERS] Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-20 22:09 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-21 01:41 ` Re: [HACKERS] Point in Time Recovery Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-15 01:43 ` Re: [HACKERS] Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-15 07:15 ` Re: [HACKERS] Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-15 09:47 ` Re: Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-15 19:35 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-15 23:13 ` Re: Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-15 23:46 ` Re: Point in Time Recovery Mark Kirkwood <markir@coretech.co.nz>
2004-07-16 00:02 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-15 12:16 ` Re: Point in Time Recovery HISADAMasaki <hisada.masaki@lab.ntt.co.jp>
2004-07-15 22:44 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-15 23:01 ` Re: Point in Time Recovery Alvaro Herrera <alvherre@dcc.uchile.cl>
2004-07-15 23:51 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-16 03:49 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-16 07:40 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 22:56 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-13 23:01 ` Re: Point in Time Recovery Bruce Momjian <pgman@candle.pha.pa.us>
2004-07-13 23:21 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2004-07-13 23:28 ` Re: Point in Time Recovery Tom Lane <tgl@sss.pgh.pa.us>
2004-07-13 23:54 ` Re: Point in Time Recovery Simon Riggs <simon@2ndquadrant.com>
2011-11-23 12:39 Point in time recovery Karuna Karpe <karuna.karpe@os3infotech.com>
2011-11-23 14:30 ` Re: Point in time recovery Simon Riggs <simon@2ndQuadrant.com>
2011-11-23 15:51 ` Re: Point in time recovery Ian Lea <ian.lea@gmail.com>
2011-11-24 08:55 ` Re: Point in time recovery white.heron white <white.heron@yahoo.com>
2011-11-24 01:21 ` Re: Point in time recovery Craig Ringer <ringerc@ringerc.id.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox