Re: backup/restore - another area.

On 9 Oct 2003, Greg Stark wrote:

> I don't quite follow your #2 so I can only comment on the above idea of using
> an LVM snapshot. If you have the hardware and the LVM-fu to be able to do this
> properly I would recommend it.
>
Just to be a bit clearer incase it was my wording:

Method #2 is nearly identical to method #1, except that no logical volume
manager is needed. We cannot just cp $PGDATA because it is (or could be)
changing and we need to take data from a constitant point. So what we do
is write code that understands xids and all that and simply "dumps" out
the pages of data in a raw form that can be quickly reloaded. The key is
that the data be in a somewhat consistant state. Method #2 requires a ton
more work but it would be able to run on platforms without an lvm (or
requiring the use of an lvm). Does that make more sense?

The idea here is to backup & restore as fast as possible, throwing away
some things like inter-version compat and whatnot. Being able to add
"fast backup / restore" is a good thing in the list of enterprise
features.

> Also, I wouldn't consider this a replacement for having a pg_dump export. In a
> crisis when you want to restore everything *exactly* the way things were you
> want the complete filesystem snapshot. But if you just want to load a table
> the way it was the day before to compare, or if you want to load a test box to
> do some performance testing, or whatever, you'll need the logical export.
>

Yeah, a pg_dump now and then would be useful (and safe).
If you wanted to get fancy schmancy you could take the snapshot, archive
it, transfer it and unarchive it on machine B. (We actually used to do
that here until machine B no longer had the capacity to hold all our data
:)

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.jefftrout.com/
http://www.stuarthamm.net/