Re: Point in Time Recovery

On Fri, 2004-07-16 at 16:47, Tom Lane wrote:
> As far as the business about copying pg_control first goes: there is
> another way to think about it, which is to copy pg_control to another
> place that will be included in your backup. For example the standard
> backup procedure could be
>
> 1. [somewhat optional] Issue CHECKPOINT and wait till it finishes.
>
> 2. cp $PGDATA/global/pg_control $PGDATA/pg_control.dump
>
> 3. tar cf /dev/mt $PGDATA
>
> 4. do something to record ending WAL position
>
> If we standardized on this way, then the tar archive would automatically
> contain the pre-backup checkpoint position in ./pg_control.dump, and
> there is no need for any special assumptions about the order in which
> tar processes things.
>

Sounds good. That would be familiar to Oracle DBAs doing BACKUP
CONTROLFILE. We can document that and off it as a suggested procedure.

> However, once you decide to do things like that, there is no reason why
> the copied file has to be an exact image of pg_control. I claim it
> would be more useful if the copied file were plain text so that you
> could just "cat" it to find out the starting WAL position; that would
> let you determine without any special tools what range of WAL archive
> files you are going to need to bring back from your archives.

I wouldn't be in favour of a manual mechanism. If you want an automated
mechanism, whats wrong with using the one thats already there? You can
use pg_controldata to read the controlfile, again whats wrong with that?

We agreed some time back that an off-line xlog file inspector would be
required to allow us to inspect the logs and make a decision about where
to end recovery. You'd still need that.

It's scary enough having to specify the end point, let alone having to
specify the starting point as well.

At your request, and with Bruce's idea, I designed and built the
recovery system so that you don't need to know what range of xlogs to
bring back. You just run it, it brings back the right files from archive
and does recovery with them, then cleans up - and it works without
running out of disk space on long recoveries.

I've built it now and it works...

> This is pretty much the same chain of reasoning that Bruce and I went
> through yesterday to come up with the idea of putting a label file
> inside the tar backups. We concluded that it'd be worth putting
> both the backup starting time and the checkpoint WAL position into
> the label file --- the starting time isn't needed for restore but
> might be really helpful as documentation, if you needed to verify
> which dump file was which.

...if you are doing tar backups...what will you do if you're not using
that mechanism?

If you are: It's common practice to make up a backup filename from
elements such as systemname, databasename, date and time etc. That gives
you the start time, the file last mod date gives you the end time.

I think its perfectly fine for everybody to do backups any way they
please. There are many licenced variants of PostgreSQL and it might be
appropriate in those to specify particular ways of doing things.

I'll be trusting the management of backup metadata and storage media to
a solution designed for the purpose (open or closed source), just as
I'll be trusting my data to a database solution designed for that
purpose. That for me is one of the good things about PostgreSQL - we use
the filesystem, we don't write our own, we provide language interfaces
not invent our own proprietary server language etc..

Best Regards, Simon Riggs