I'd like to take a crack at implementing point in time recovery.  My
plan is to do this as gently as possible in a number of small
self-contained steps.  I'd appreciate lots of critcial feedback.  

Alternatively, if someone else is looking into this please let me know
so I can either back off or help out.

Stage 1

Add hooks for begin_backup and end_backup at a data file level.  Between
the calls begin_backup(myfile) and end_backup(myfile), writes to myfile
will be disabled allowing the file to be safely copied.

Unfortunately, it seems that this approach is fundamentally in conflict
with the principle of a checkpoint, which, as I understand it, is
supposed to flush all unwritten changes to disk.

My solution is to allow the checkpoint to complete without flushing
myfiles buffers, and then on end_backup(myfile) either perform another
checkpoint or just flush the buffers for myfile.  As long as you cannot
shut down the database untile end_backup has completed its checkpoint I
think all should be well.

So, have I missed something?  Would it instead be better to block the
checkpoint until end_backup(myfile) is complete?  Any other ideas?

Stage 2

Add a move_file function.  This will, use the begin/end_backup hooks,
make a copy of a datafile, create a symlink for it where the original
file used to be, and close and re-open the file without altering any
buffers.

There have been a few mutterings on the mailing lists recently that
suggest this would be useful, and it's a relatively easy test case for
the begin/end_backup hooks.

Stage 3

Add a pgtar executable that will create a tar of all data files using
the begin/end_backup hooks.

Stage 4

Provide some sort of archiving mechanism for WAL files.

Stage 5

Provide some control over the recovery process.  This has to deal with
recovery using both up to date control files, and control files from a
backup set.

Sorry for cross posting this to pgsql-general but my last post to
hackers never made it.


-- 
Marc		marc@bloodnok.com

21 Feb 2002 19:35:42 -0800
Marc Munro <marc@bloodnok.com>
point in time recovery and moving datafiles online
[ pg-general removed from cc: list, as this is off topic for it ]

Marc Munro <marc@bloodnok.com> writes:
> Add hooks for begin_backup and end_backup at a data file level.  Between
> the calls begin_backup(myfile) and end_backup(myfile), writes to myfile
> will be disabled allowing the file to be safely copied.

And the writes are going to go where instead?  If you intend to just
hold the dirty pages in memory, the system will grind to a halt in no
time, ie as soon as it runs out of spare buffers.  This strikes me as
only marginally better than "shut down the database while you copy the
files".

Perhaps more to the point, I'm not following how this helps achieve
point-in-time recovery.  I suppose what you are after is to get an
instantaneous snapshot of the data files that could be used as a
starting point for replaying the WAL --- but AFAICS you'd need a
snapshot that's instantaneous across the *whole* database, ie,
all the data files are in the state corresponding to the chosen
starting point for the WAL.  Locking and copying one file at a time
doesn't get you there.

It seems to me that you can get the desired results without any
locking.  Assume that you start archiving the WAL just after a
checkpoint record.  Also, start copying data files to your backup
medium.  Some not inconsiderable time later, you are done copying
data files.  You continue copying off and archiving WAL entries.
You cannot say that the copied data files correspond to any particular
point in the WAL, or that they form a consistent set of data at all
--- but if you were to reload them and replay the WAL into them
starting from the checkpoint, then you *would* have a consistent set
of files once you reached the point in the WAL corresponding to the
end-time of the data file backup.  You could stop there, or continue
WAL replay to any later point in time.

			regards, tom lane

Thu, 21 Feb 2002 22:52:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 
Tom,
Many thanks for your reply.  This is exactly the sort of feedback I
need.

On Thu, 2002-02-21 at 19:52, Tom Lane wrote:
> [ pg-general removed from cc: list, as this is off topic for it ]
> 
> Marc Munro <marc@bloodnok.com> writes:
> > Add hooks for begin_backup and end_backup at a data file level.  Between
> > the calls begin_backup(myfile) and end_backup(myfile), writes to myfile
> > will be disabled allowing the file to be safely copied.
> 
> And the writes are going to go where instead?  If you intend to just
> hold the dirty pages in memory, the system will grind to a halt in no
> time, ie as soon as it runs out of spare buffers.  This strikes me as
> only marginally better than "shut down the database while you copy the
> files".

The intention is to lock only one file at a time, take a clean snapshot
of it, and only after allowing writes to its buffers to continue, go on
to the next file.  I would hope that the time it would take to copy a
single 1G data file would be considerably less than the time needed to
dirty all in-memory buffers but maybe that is just wild optimism.
 
> Perhaps more to the point, I'm not following how this helps achieve
> point-in-time recovery.  I suppose what you are after is to get an
> instantaneous snapshot of the data files that could be used as a
> starting point for replaying the WAL --- but AFAICS you'd need a
> snapshot that's instantaneous across the *whole* database, ie,
> all the data files are in the state corresponding to the chosen
> starting point for the WAL.  Locking and copying one file at a time
> doesn't get you there.

Actually I'm not trying to get a consistent snapshot.  I just want each
file to be internally consistent.  Then, on recovery, we should be able
to replay the WAL to reach; first a consistent state, then any point in
time from that consistent state to the end of the WAL entries.

I guess I should have explained that ;-)

> It seems to me that you can get the desired results without any
> locking.  Assume that you start archiving the WAL just after a
> checkpoint record.  Also, start copying data files to your backup
> medium.  Some not inconsiderable time later, you are done copying
> data files.  You continue copying off and archiving WAL entries.
> You cannot say that the copied data files correspond to any particular
> point in the WAL, or that they form a consistent set of data at all
> --- but if you were to reload them and replay the WAL into them
> starting from the checkpoint, then you *would* have a consistent set
> of files once you reached the point in the WAL corresponding to the
> end-time of the data file backup.  You could stop there, or continue
> WAL replay to any later point in time.

If I understand you correctly this is exactly what I was thinking, based
on Oracle recovery.  But we must still prevent writes to each data file
as we back it up, so that it remains internally consistent.  This is the
point of the begin/end_backup hooks.  Managing the archiving of the WAL
files is down on my list for doing later (one baby step at a time).

-- 
Marc		marc@bloodnok.com

21 Feb 2002 21:07:27 -0800
Marc Munro <marc@bloodnok.com>
Re: point in time recovery and moving datafiles online
Marc Munro <marc@bloodnok.com> writes:
> On Thu, 2002-02-21 at 19:52, Tom Lane wrote:
>> It seems to me that you can get the desired results without any
>> locking.  Assume that you start archiving the WAL just after a
>> checkpoint record.  Also, start copying data files to your backup
>> medium.  Some not inconsiderable time later, you are done copying
>> data files.  You continue copying off and archiving WAL entries.
>> You cannot say that the copied data files correspond to any particular
>> point in the WAL, or that they form a consistent set of data at all
>> --- but if you were to reload them and replay the WAL into them
>> starting from the checkpoint, then you *would* have a consistent set
>> of files once you reached the point in the WAL corresponding to the
>> end-time of the data file backup.  You could stop there, or continue
>> WAL replay to any later point in time.

> If I understand you correctly this is exactly what I was thinking, based
> on Oracle recovery.  But we must still prevent writes to each data file
> as we back it up, so that it remains internally consistent.

No, you're missing my point.  You don't need intra-file consistency any
more than you need cross-file consistency.  You merely need to be sure
that you have captured all the state of pages that are not updated
anywhere in the series of WAL entries that you have.

I had originally started to compose email suggesting that locking on a
per-disk-page basis (not a per-file basis) would be better, but I do
not believe you need even that, for two reasons:

1. PG will always write changes to data files in page-size write
operations.  The Unix kernel guarantees that these writes appear atomic
from the point of view of other processes.  So the data-file-backup
process will see pagewise consistent data in any case.

2. Even if the backup process managed to acquire an inconsistent
(partially updated) copy of a page due to a concurrent write by a
Postgres backend, we do not care.  The WAL activity is designed to
ensure recovery from partial-page disk writes, and backing up such an
inconsistent page copy would be isomorphic to a system failure after a
partial page write.  Replay of the WAL will ensure that the page will be
fully written from the WAL data.

In short, all you need is a mechanism for archiving off the WAL data and
locating a checkpoint record in the WAL as a starting point for replay.
Your data-file backup mechanism can be plain ol' tar or cp -r.  No
interlocks needed or wanted.

			regards, tom lane

Fri, 22 Feb 2002 00:27:56 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 
Tom,
Again, many thanks for the reply.

On Thu, 2002-02-21 at 21:27, Tom Lane wrote:
> 
> No, you're missing my point.  You don't need intra-file consistency any
> more than you need cross-file consistency.  You merely need to be sure
> that you have captured all the state of pages that are not updated
> anywhere in the series of WAL entries that you have.
> 
> I had originally started to compose email suggesting that locking on a
> per-disk-page basis (not a per-file basis) would be better, but I do
> not believe you need even that, for two reasons:
> 
> 1. PG will always write changes to data files in page-size write
> operations.  The Unix kernel guarantees that these writes appear atomic
> from the point of view of other processes.  So the data-file-backup
> process will see pagewise consistent data in any case.
> 
> 2. Even if the backup process managed to acquire an inconsistent
> (partially updated) copy of a page due to a concurrent write by a
> Postgres backend, we do not care.  The WAL activity is designed to
> ensure recovery from partial-page disk writes, and backing up such an
> inconsistent page copy would be isomorphic to a system failure after a
> partial page write.  Replay of the WAL will ensure that the page will be
> fully written from the WAL data.

Wow.  I hadn't appreciated that.  This is way cooler than I realised.

> In short, all you need is a mechanism for archiving off the WAL data and
> locating a checkpoint record in the WAL as a starting point for replay.
> Your data-file backup mechanism can be plain ol' tar or cp -r.  No
> interlocks needed or wanted.

So, the whole job is much easier than I thought.  This is a good thing.

I will rethink my strategy.  It looks like the tasks now are to manage
the archival of WAL files, provide an interface to manage the recovery
process, produce some guidelines/scripts for managing hot backups, and
write the documentation.


-- 
Marc		marc@bloodnok.com

22 Feb 2002 09:03:57 -0800
Marc Munro <marc@bloodnok.com>
Re: point in time recovery and moving datafiles online
> In short, all you need is a mechanism for archiving off the WAL data and
> locating a checkpoint record in the WAL as a starting point for replay.
> Your data-file backup mechanism can be plain ol' tar or cp -r.  No
> interlocks needed or wanted.

What would happen if a table is dropped or truncated while doing tar
on it? I think we do not perform any logging while doing DROP TABLE or
TRUCATE TABLE. Logging entire table would solve the problem, but that
seems to be impossible for huge tables.

Maybe we need to have some interlock mechanism to prevent DROP
TABLE/TRUNCATE/DROP DATABSE etc. while doing a backup.
--
Tatsuo Ishii




Fri, 08 Mar 2002 09:53:02 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: point in time recovery and moving datafiles online 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> What would happen if a table is dropped or truncated while doing tar
> on it?

Nothing bad, unless tar itself got confused.  (Which could perhaps
happen in the TRUNCATE case; I dunno if tar is happy when it can't read
as much from the file as it originally expected.)  As far as DROP goes,
any standard Unix system will postpone the physical file delete until
the last open file descriptor closes.

> I think we do not perform any logging while doing DROP TABLE or
> TRUCATE TABLE.

TRUNCATE isn't considered rollback-able anyway (indeed that's its whole
point) so I'm not too excited about whether or not it plays by the WAL
rules.  CREATE/DROP DATABASE the same.

As for DROP TABLE, what should happen is that at commit time we log the
IDs of the tables being deleted in WAL; then on replay we can re-delete
them as needed.  If you look in xact.c you will see comments where this
is supposed to happen --- but evidently Vadim never did get around to
that.  Other file creation and deletion actions need to be logged in WAL
as well.

			regards, tom lane

Thu, 07 Mar 2002 20:03:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > What would happen if a table is dropped or truncated while doing tar
> > on it?
> 
> Nothing bad, unless tar itself got confused.  (Which could perhaps
> happen in the TRUNCATE case; I dunno if tar is happy when it can't read
> as much from the file as it originally expected.)  As far as DROP goes,
> any standard Unix system will postpone the physical file delete until
> the last open file descriptor closes.

My concern is after DROP TABLE is committed. In my understanding when
the backup process starts,it records a LSN somewhere (maybe
pg_control?) to remember the place where the archive log recovery
could start later.

Consider a scenario:

(1) backup process starts (and records LSN)
(2) DROP TABLE t1 starts
(3) DROP TABLE t1 commits
(4) backup process ends

I think the database status should be able to go back to (1) using
the archive log recovery. No?
--
Tatsuo Ishii

Fri, 08 Mar 2002 10:42:19 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: point in time recovery and moving datafiles online 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Consider a scenario:

> (1) backup process starts (and records LSN)
> (2) DROP TABLE t1 starts
> (3) DROP TABLE t1 commits
> (4) backup process ends

> I think the database status should be able to go back to (1) using
> the archive log recovery. No?

No.  It is not reasonable to expect the backup to allow you to recreate
any state occurring before the *end* of the backup process.  After the
backup is complete, you can use the backup and the WAL to duplicate the
state of any later instant.

			regards, tom lane

Thu, 07 Mar 2002 20:49:31 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 
> > (1) backup process starts (and records LSN)
> > (2) DROP TABLE t1 starts
> > (3) DROP TABLE t1 commits
> > (4) backup process ends
> 
> > I think the database status should be able to go back to (1) using
> > the archive log recovery. No?
> 
> No.  It is not reasonable to expect the backup to allow you to recreate
> any state occurring before the *end* of the backup process.  After the
> backup is complete, you can use the backup and the WAL to duplicate the
> state of any later instant.

I see your point. But is it reasonable? We cannot know when the backup
process completes beforehand and that makes the archive log recovery
less usefull in my opinion.

I guess Oracle and other commercial DBMSs declare the start of backup
process explicitly and that would be the point where the archive log
recovery could go back. I'm interested in how Oracle accomplishes this
(I know DROP TABLE is not rollbackable in Orale).
--
Tatsuo Ishii

Fri, 08 Mar 2002 11:00:33 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: point in time recovery and moving datafiles online 
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> No.  It is not reasonable to expect the backup to allow you to recreate
>> any state occurring before the *end* of the backup process.

> I see your point. But is it reasonable? We cannot know when the backup
> process completes beforehand and that makes the archive log recovery
> less usefull in my opinion.

So?  The backup will not let you recover states occurring before it
starts, either.  I don't see the issue.  Anyone using this in a
production situation will *always* have a prior backup and a continuous
WAL trail since the start of the prior backup.  Whether the
synchronization point is the start time or end time of each new backup
isn't a hot issue for them --- at least it shouldn't be if they are
following appropriate procedures.

The only way I can see to make it work the other way is to dump the
entire contents of any to-be-deleted table into the WAL log before
deleting it.  That is way too much overhead to be a reasonable tradeoff
for letting people think of the start time rather than end time of their
backups as the cutover point.

			regards, tom lane

Thu, 07 Mar 2002 21:13:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: point in time recovery and moving datafiles online 
On Thu, 2002-03-07 at 18:00, Tatsuo Ishii wrote:
> > > (1) backup process starts (and records LSN)
> > > (2) DROP TABLE t1 starts
> > > (3) DROP TABLE t1 commits
> > > (4) backup process ends
> > 
> > > I think the database status should be able to go back to (1) using
> > > the archive log recovery. No?
> > 
> > No.  It is not reasonable to expect the backup to allow you to recreate
> > any state occurring before the *end* of the backup process.  After the
> > backup is complete, you can use the backup and the WAL to duplicate the
> > state of any later instant.

> I guess Oracle and other commercial DBMSs declare the start of backup
> process explicitly and that would be the point where the archive log
> recovery could go back. I'm interested in how Oracle accomplishes this
> (I know DROP TABLE is not rollbackable in Orale).

Actually it is possible to get back to state 1 but for this we need a
backup from earlier.  To restate the scenario:

0) Backup A completes
1) Database activity happens
2) Backup B begins
3) Drop table t1 starts
4) Drop table t1 commits
5) Backup B completes
6) More database activity

We can recover any database activity for which we have WALs from backups
prior to that event.  If we want to restore to any point in time prior
to step 5, we must use backup A.  For points after step 5 we can use
backup B.

This is exactly the way that we would recover databases using Oracle.  I
have known DBAs attempt to restore a backup only to discover media
problems, and then revert to backup N-1, N-2 and so on.  Once a good
backup is finally found, recovery (roll-forward) is performed.  It takes
longer from an older backup and there is more risk of encountering a
lost log file but it works just fine.


-- 
Marc		marc@bloodnok.com

08 Mar 2002 08:06:33 -0800
Marc Munro <marc@bloodnok.com>
Re: point in time recovery and moving datafiles online