Re: PG-8.2 backup strategies

On Mon, Jan 21, 2008 at 04:05:13PM +0100, Henrik wrote:
> Hello list,
>
> I know backup of pg is a well discussed topic and ranges in solution
> from simple pg_dump to more advanced PITR with Slony.
>
> Even though I've studied most of them I can't really decide the best
> solution for a new situation and would be grateful for any inputs on
> this.
>
> The situation is as follow.
>
> We want to do a daily backups from many installations to a remote
> location and also want easy restores when disaster strikes. Preferable
> the backup site would only need an ftp server to store the files on.
>
> My optimal solution would be differential pg_dumps but that is not
> possible as far as I know. Doing pg_dumps every day is a little to
> heavy even though the db's are not huge. I like the fact that I have
> one big SQL file which is really simple to restore with.

Heavy where? If it's just heavy on the transfer, it might be possible to
do the dump locally and then rsync the file off to the server. (dumping
without compression is likely to make rsync a lot more efficient here, but
I don't have any numbers to back up that guess)

> The next best solution would probably be weekly pg_dumps with daily
> wal shipping. But how would this handle tables with columns that get
> tiny updates several times per second? Do I get huge WALs or?

First, you don't combine pg_dump with WAL shipping. If you do WAL shipping
with PITR, you do filesystem base backups and shipping. Not pg_dump.

Second, yes, they might be huge - depending on your defition. If you have
udpated a row a million times, there will be a million updates recorded in
the WAL log, whereas a pg_dump will only show the latest version.

//Magnus