Re: pg_dumpall for Postgres Database Daily Backup

On Thursday 13 May 2010 17.50:31 Wang, Mary Y wrote:
> http://www.postgresql.org/docs/8.3/static/backup-dump.html#BACKUP-DUMP-A
> LL (I only found the documentation for 8.3.10), and it looks like
> pg_dumpall > outfile is the best choice.

pg_dumpall or by-database pg_dump ha the advantage that it is uncomplicated
and restore is possible to newer pg versions without problem.

Another strategy is taking snapshots of the data/ directory and archiving
WAL files as described in
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

This has various trade-offs against the pg_dump approach:
* pint in time recovery
* you can get away with taking a full backup only occasionally, as long
as you keep the WAL files
* you need much more space (data/ dir plus WAL)
* it's a bit more complex to set up
* reovery needs to be to a database with the same version (and
architecture? Not sure. Only same minor version, or patchlevel too?)

We decided to use the continuous archiving way becase (since we ship the WAL
files off site) we lose almost no data if a crash happens (hasn't, so far
:-) and transferring the WAL file over the network (on a only light db load)
is quite light. (transferring pg_dump files with rsync should be quite
light as well, though; I guess the ordering of the data is more or less
constant for parts of tables that are not changed. I've not tested this
though.)

cheers
-- vbi

--
this email is protected by a digital signature: http://fortytwo.ch/gpg