Re: Point in Time Recovery

> 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

Unfortunately this only works if pg_control was the first file to be
backed up (or by chance no checkpoint happened after backup start and
pg_control backup)

Other db's have commands for:
start/end external backup

Maybe we should add those two commands that would initially only do
the following:

start external backup:
- (checkpoint as an option)
- make a copy of pg_control
end external backup:
- record WAL position (helps choose an allowed minimum PIT)

Those commands would actually not be obligatory but recommended, and would
only help with the restore process.

Restore would then eighter take the existing pg_control backup, or ask
the dba where rollforward should start and create a corresponding pg_control.
A helper utility could list possible checkpoints in a given xlog.

Andreas

On Wed, 2004-07-14 at 10:57, Zeugswetter Andreas SB SD wrote:
> > 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
>
> Unfortunately this only works if pg_control was the first file to be
> backed up (or by chance no checkpoint happened after backup start and
> pg_control backup)
>
> Other db's have commands for:
> start/end external backup
>

OK...this idea has come up a few times. Here's my take:

- OS and hardware facilities exist now to make instant copies of sets of
files. Some of these are open source, others not. If you use these, you
have no requirement for this functionality....but these alone are no
replacement for archive recovery.... I accept that some people may not
wish to go to the expense or effort to use those options, but in my mind
these are the people that will not be using archive_mode anyway.

- all we would really need to do is to stop the bgwriter from doing
anything during backup. pgcontrol is only updated at checkpoint. The
current xlog is updated constantly, but this need not be copied because
we are already archiving it as soon as its full. That leaves the
bgwriter, which is now responsible for both lazy writing and
checkpoints.
So, put a switch into bgwriter to halt for a period, then turn it back
on at the end. Could be a SIGHUP GUC...or...

...and with my greatest respects....

- please could somebody else code that?... my time is limited

Best regards, Simon Riggs

I talked to Tom on the phone today and and I think we have a procedure
for doing backup/restore in a fairly foolproof way.

As outlined below, we need to record the start/stop and checkpoint WAL
file names and offsets, and somehow pass those on to restore. I think
any system that requires users to link those values together is going
to cause confusion and be error-prone.

My idea is to do much of this automatically. First, create a
server-side function called pitr_backup_start() which creates a file in
the /data directory which contains the WAL filename/offsets for
last checkpoint and start. Then do the backup of the data directory.
Then call pitr_backup_stop() which adds the stop filename/offsets to the
file, and archive that file in the same place as the WAL files.

To restore, you untar the backup of /data. Then the recover backend
reads the file created by pitr_backup_start() to find the name of the
backup parameter file. It then recovers that file from the archive
location and uses the start/stop/checkpoint filename/offset information
to the restore.

The advantage of this is that the tar backup contains everything needed
to find the proper parameter file for restore. Ideally we could get all
the parameters into the tar backup, but that isn't possible because we
can't push the stop counters into the backup after the backup has
completed.

I recommend the pitr_backup_start() file be named for the current WAL
filename/offset, perhaps 000000000000032c.3da390.backup or something
like that. The file would be a simple text file in
pg_xlog/archive_status:

# start 2004-07-14 21:35:22.324579-04
wal_checkpoint = 0000000000000319.021233
wal_start = 000000000000032c.92a9cb
...added after backup completes...
wal_stop = 000000000000034a.3db030
# stop 2004-07-14 21:32:22.0923213-04

The timestamps are for documentation only. These files give admins
looking in the archive directory information on backup times.

(As an idea, there is no need for the user to specify a recovery mode.
If the postmaster starts and sees the pitr_backup_start() file in /data,
it can go into recovery mode automatically. If the archiver can't find
the file in the archive location, it can assume that it is just being
started from power failure mode. However if it finds the file in the
archive location, it can assume it is to enter recovery mode. There is
a race condition that a crash during copy of the file to the archive
location would be a problem. The solution would be to create a special
flag file before copying the file to archive, and then archive it and
remove the flag file. If the postmaster starts up and sees the
pitr_backup_start() file in /data and in the archive location, and it
doesn't see the flag file, it then knows it is doing a restore because
the flag file would never appear in a backup. Anyway, this is just an
idea.)

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Wed, 2004-07-14 at 10:57, Zeugswetter Andreas SB SD wrote:
> > > 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
> >
> > Unfortunately this only works if pg_control was the first file to be
> > backed up (or by chance no checkpoint happened after backup start and
> > pg_control backup)
> >
> > Other db's have commands for:
> > start/end external backup
> >
>
> OK...this idea has come up a few times. Here's my take:
>
> - OS and hardware facilities exist now to make instant copies of sets of
> files. Some of these are open source, others not. If you use these, you
> have no requirement for this functionality....but these alone are no
> replacement for archive recovery.... I accept that some people may not
> wish to go to the expense or effort to use those options, but in my mind
> these are the people that will not be using archive_mode anyway.
>
> - all we would really need to do is to stop the bgwriter from doing
> anything during backup. pgcontrol is only updated at checkpoint. The
> current xlog is updated constantly, but this need not be copied because
> we are already archiving it as soon as its full. That leaves the
> bgwriter, which is now responsible for both lazy writing and
> checkpoints.
> So, put a switch into bgwriter to halt for a period, then turn it back
> on at the end. Could be a SIGHUP GUC...or...
>
> ...and with my greatest respects....
>
> - please could somebody else code that?... my time is limited
>
> Best regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 2004-07-15 at 03:02, Bruce Momjian wrote:
> I talked to Tom on the phone today and and I think we have a procedure
> for doing backup/restore in a fairly foolproof way.
>
> As outlined below, we need to record the start/stop and checkpoint WAL
> file names and offsets, and somehow pass those on to restore. I think
> any system that requires users to link those values together is going
> to cause confusion and be error-prone.
>

Unfortunately, it seems clear that many of my posts have not been read,
nor has anyone here actually tried to use the patch. Everybody's views
on what constitutes error-prone might well differ then.

Speculation about additional requirements is just great, but please
don't assume that I have infinite resources to apply to these problems.
Documentation has still to be written.

For a long time now, I've been adding "one last feature" to what is
there, but we're still no nearer to anybody inspecting the patch or
committing it.

There is building consensus on other threads that PITR should not even
be included in the release (3 tentative votes). This latest request
feels more like the necessary excuse to take the decision to pull PITR.
I would much rather that we took the brave decision and pull it NOW,
rather than have me work like crazy to chase this release.

:(

Best Regards, Simon Riggs

Simon Riggs wrote:
> On Thu, 2004-07-15 at 03:02, Bruce Momjian wrote:
> > I talked to Tom on the phone today and and I think we have a procedure
> > for doing backup/restore in a fairly foolproof way.
> >
> > As outlined below, we need to record the start/stop and checkpoint WAL
> > file names and offsets, and somehow pass those on to restore. I think
> > any system that requires users to link those values together is going
> > to cause confusion and be error-prone.
> >
>
> Unfortunately, it seems clear that many of my posts have not been read,
> nor has anyone here actually tried to use the patch. Everybody's views
> on what constitutes error-prone might well differ then.
>
> Speculation about additional requirements is just great, but please
> don't assume that I have infinite resources to apply to these problems.
> Documentation has still to be written.
>
> For a long time now, I've been adding "one last feature" to what is
> there, but we're still no nearer to anybody inspecting the patch or
> committing it.

I totally understand your feeling this, and I would be feeling the exact
same way (but would probably have complained much earlier :-) ).
Anyway, the problem is that Tom and I are serializing application of the
major features in the pipeline. We decided to focus on nested
transactions (NT) first (it is a larger patch), and that is why PITR has
gotten so little attention from us. However, there is no sense that
you had anything to do with it being places behind NT in the queue, and
therefore there is no feeling on our part that PITR is less important or
deserves less time than NT. Certainly any system that made you less
likely to be applied would be unfair and something we will not do.

My explanation about the file format was an attempt to address the
method of passing the wal filename/offset to the recover process. If
that isn't needed, I am sorry.

> There is building consensus on other threads that PITR should not even
> be included in the release (3 tentative votes). This latest request
> feels more like the necessary excuse to take the decision to pull PITR.
> I would much rather that we took the brave decision and pull it NOW,
> rather than have me work like crazy to chase this release.

Those three individuals are not representative of the group. Sorry it
might seem there there is lack of enthusiasm for PITR, but it isn't true
from our end. You might have noticed that the patch queue has shrunk
dramatically, and now we are focused on NT and PITR almost exclusively.

We will get there --- it just seems dark at this time.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Simon Riggs wrote:
> On Wed, 2004-07-14 at 10:57, Zeugswetter Andreas SB SD wrote:
> > > 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
> >
> > Unfortunately this only works if pg_control was the first file to be
> > backed up (or by chance no checkpoint happened after backup start and
> > pg_control backup)
> >
> > Other db's have commands for:
> > start/end external backup
> >
>
> OK...this idea has come up a few times. Here's my take:
>
> - OS and hardware facilities exist now to make instant copies of sets of
> files. Some of these are open source, others not. If you use these, you
> have no requirement for this functionality....but these alone are no
> replacement for archive recovery.... I accept that some people may not
> wish to go to the expense or effort to use those options, but in my mind
> these are the people that will not be using archive_mode anyway.
>
> - all we would really need to do is to stop the bgwriter from doing
> anything during backup. pgcontrol is only updated at checkpoint. The
> current xlog is updated constantly, but this need not be copied because
> we are already archiving it as soon as its full. That leaves the
> bgwriter, which is now responsible for both lazy writing and
> checkpoints.
> So, put a switch into bgwriter to halt for a period, then turn it back
> on at the end. Could be a SIGHUP GUC...or...

I don't think we can turn off all file system writes during a backup.
Imagine writing to a tape. Preventing file system writes would make the
system useless.

> - please could somebody else code that?... my time is limited

Yes, I think someone else could code this.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 2004-07-15 at 15:57, Bruce Momjian wrote:

> We will get there --- it just seems dark at this time.

Thanks for that. My comments were heartfelt, but not useful right now.

I'm badly overdrawn already on my time budget, though that is my concern
alone. There is more to do than I have time for. Pragmatically, if we
aren't going to get there then I need to stop now, so I can progress
other outstanding issues. All help is appreciated.

I'm aiming for the minimum feature set - which means we do need to take
care over whether that set is insufficient and also to pull any part
that doesn't stand up to close scrutiny over the next few days.

Overall, my primary goal is increased robustness and availability for
PostgreSQL...and then to have a rest!

Best Regards, Simon Riggs

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Simon,

On Thu, 15 Jul 2004, Simon Riggs wrote:

> > We will get there --- it just seems dark at this time.
>
> Thanks for that. My comments were heartfelt, but not useful right now.
>
> I'm badly overdrawn already on my time budget, though that is my concern
> alone. There is more to do than I have time for. Pragmatically, if we
> aren't going to get there then I need to stop now, so I can progress
> other outstanding issues. All help is appreciated.

Personally, as a PostgreSQL Advocate, I believe that PITR is one of the
most important missing features in PostgreSQL. I've been keeping 'all' of
you e-mails about PITR and I'm really excited with that feature.

Please do not stop working on PITR. I'm pretty sure that most of the
'silent' people in the lists are waiting for PITR for an {Oracle, DB2, ...}-killer
database. In my country (Turkey), too many people spend a lot of
money for proprietary databases, just for some missing features in
PostgreSQL. If you finish your work on PITR (and other guys on NT, Win32
port, etc), then we'll feel more concentrated on PostgreSQL Advocation, so
that PostgreSQL will be used more and more. (Oh, we also need native
clustering...)

Maybe I should send this e-mail offlist, but I wanted everyone to learn my
feelings.

Regards and best wishes,
- --
Devrim GUNDUZ
devrim~gunduz.org devrim.gunduz~linux.org.tr
http://www.tdmsoft.com
http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFA9wKxtl86P3SPfQ4RAms+AJ95RfFi0lVwMD7u7zQ/DzLFEBC8MACgvRzd
HRqAjVqI3hekwImPpqelj9U=
=l445
-----END PGP SIGNATURE-----

> On Thu, 2004-07-15 at 23:18, Devrim GUNDUZ wrote:

Thanks for the vote of confidence, on or off list.

> too many people spend a lot of
> money for proprietary databases, just for some missing features in
> PostgreSQL

Agreed - PITR isn't aimed at existing users of PostgreSQL. If you use it
already, even though it doesn't have it, then you are quite likely to be
able to keep going without it.

Most commercial users won't touch anything that doesn't have PITR.

> (Oh, we also need native
> clustering...)

Next week, OK? :)

Best Regards, Simon

Simon Riggs wrote:
> > On Thu, 2004-07-15 at 23:18, Devrim GUNDUZ wrote:
>
> Thanks for the vote of confidence, on or off list.
>
> > too many people spend a lot of
> > money for proprietary databases, just for some missing features in
> > PostgreSQL
>
> Agreed - PITR isn't aimed at existing users of PostgreSQL. If you use it
> already, even though it doesn't have it, then you are quite likely to be
> able to keep going without it.
>
> Most commercial users won't touch anything that doesn't have PITR.

Agreed. I am surprised at how few requests we have gotten for PITR. I
assume people are either using replication or not considering us.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Simon Riggs wrote:
> On Thu, 2004-07-15 at 15:57, Bruce Momjian wrote:
>
> > We will get there --- it just seems dark at this time.
>
> Thanks for that. My comments were heartfelt, but not useful right now.
>
> I'm badly overdrawn already on my time budget, though that is my concern
> alone. There is more to do than I have time for. Pragmatically, if we
> aren't going to get there then I need to stop now, so I can progress
> other outstanding issues. All help is appreciated.
>
> I'm aiming for the minimum feature set - which means we do need to take
> care over whether that set is insufficient and also to pull any part
> that doesn't stand up to close scrutiny over the next few days.

As you can see, we are still chewing on NT. What PITR features are
missing? I assume because we can't stop file system writes during
backup that we will need a backup parameter file like I described. Is
there anything else that PITR needs?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> Simon Riggs wrote:
> > > On Thu, 2004-07-15 at 23:18, Devrim GUNDUZ wrote:
> >
> > Thanks for the vote of confidence, on or off list.
> >
> > > too many people spend a lot of
> > > money for proprietary databases, just for some missing features in
> > > PostgreSQL
> >
> > Agreed - PITR isn't aimed at existing users of PostgreSQL. If you use it
> > already, even though it doesn't have it, then you are quite likely to be
> > able to keep going without it.
> >
> > Most commercial users won't touch anything that doesn't have PITR.
>
> Agreed. I am surprised at how few requests we have gotten for PITR. I
> assume people are either using replication or not considering us.

Don't forget that there are (must be) lots of us that know it's coming and
are just waiting until it's available. I haven't requested per se, but
believe me, I'm waiting for it :-)

Couldn't agree more. Maybe we should have made more noise :-)

Glen Parker wrote:

>>Simon Riggs wrote:
>>
>>
>>>>On Thu, 2004-07-15 at 23:18, Devrim GUNDUZ wrote:
>>>>
>>>>
>>>Thanks for the vote of confidence, on or off list.
>>>
>>>
>>>
>>>> too many people spend a lot of
>>>>money for proprietary databases, just for some missing features in
>>>>PostgreSQL
>>>>
>>>>
>>>Agreed - PITR isn't aimed at existing users of PostgreSQL. If you use it
>>>already, even though it doesn't have it, then you are quite likely to be
>>>able to keep going without it.
>>>
>>>Most commercial users won't touch anything that doesn't have PITR.
>>>
>>>
>>Agreed. I am surprised at how few requests we have gotten for PITR. I
>>assume people are either using replication or not considering us.
>>
>>
>
>Don't forget that there are (must be) lots of us that know it's coming and
>are just waiting until it's available. I haven't requested per se, but
>believe me, I'm waiting for it :-)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
>

> Thanks for that. My comments were heartfelt, but not useful right now.

Hi Simon, I'm sorry if I gave the impression that I thought your work
wasn't worthwhile, it is :(

> I'm badly overdrawn already on my time budget, though that is my concern
> alone. There is more to do than I have time for. Pragmatically, if we
> aren't going to get there then I need to stop now, so I can progress
> other outstanding issues. All help is appreciated.

I've got your patch applied (but having some compilation problem), but
I'm really not sure what to test really. I don't really understand the
whole thing fully :/

> I'm aiming for the minimum feature set - which means we do need to take
> care over whether that set is insufficient and also to pull any part
> that doesn't stand up to close scrutiny over the next few days.
>
> Overall, my primary goal is increased robustness and availability for
> PostgreSQL...and then to have a rest!

Definitely!

Chris

Let me address you concerns about PITR getting into 7.5. I think a few
people spoke last week expressing concern about our release process and
wanting to take drastic action. However, looking at the release status
report I am about to post, you will see we are on track for an August 1
beta.

PITR has been neglected only because it has been moving along so well we
haven't needed to get deeply involved. Simon has been able to address
concerns as we raised them and make adjustments quickly with little
guidance.

Now, we certainly don't want to skip adding PITR by not giving it our
full attention to get into 7.5. Once Tom completes the cursor issues
with NT in the next day or so, I think that removes the last big NT
stumbling block, and we will start to focus on PITR. Unless there is
some major thing we are missing, we fully expect to get PITR in 7.5. We
don't have a crystal ball to know for sure, but our intent is clear.

I know Simon is going away July 26 so we want to get him feedback as
soon as possible. If we wait until after July 26, we will have to make
all the adjustments without Simon's guidance, which will be difficult.

As far as the importance of PITR, it is a _key_ enterprise feature, even
more key than NT. PITR is going to be one of the crowning jewels of the
7.5 release, and I don't want to go into beta without it unless we can't
help it.

So, I know with the deadline looming, and everyone it getting nervous,
but keep the faith. I can see the light at the end of the tunnel. I
know this is a tighter schedule than we would like, but I know we can do
it, and I expect we will do it.

---------------------------------------------------------------------------

Simon Riggs wrote:
> On Thu, 2004-07-15 at 15:57, Bruce Momjian wrote:
>
> > We will get there --- it just seems dark at this time.
>
> Thanks for that. My comments were heartfelt, but not useful right now.
>
> I'm badly overdrawn already on my time budget, though that is my concern
> alone. There is more to do than I have time for. Pragmatically, if we
> aren't going to get there then I need to stop now, so I can progress
> other outstanding issues. All help is appreciated.
>
> I'm aiming for the minimum feature set - which means we do need to take
> care over whether that set is insufficient and also to pull any part
> that doesn't stand up to close scrutiny over the next few days.
>
> Overall, my primary goal is increased robustness and availability for
> PostgreSQL...and then to have a rest!
>
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073