Re: Backup method

On Wed, 2006-04-05 at 15:42 -0400, Bob Powell wrote:

> I have a systems admin that is backing up our Linux computers
> (postgres) by backing up the directory structure. This of course
> includes all the files that pertain to my postgres databases. I
> maintain that using pgdump and creating a file of SQL commands for
> restore is a better method by allowing the restore on any linux box that
> is running postgress as opposed to having to reconstruct the directory
> on another server.
>
> Does anyone have any thoughts on this matter. Is one way better than
> the other? Thanks in advance.

If you want to do this quickly then you should use PITR. The base backup
is faster, plus you're covered if you crash between backups.

Archivelogmode is standard for Oracle/DB2 etc installations; PITR should
be your standard if you run PostgreSQL too. Here's why:

pg_dump produces portable backups, but that won't help you if you took
the backup at 04:00 and your server crashes at 14:15 - you'll still lose
*all* the transactions your business performed in the last 10+ hours.
You'll also have to explain that away to your boss and remember she/he's
the one handing out the raises at the end of the year...

PITR takes more thought, but then is the purpose of a backup to make
your life easier or to recover the data for the person paying you?

Best Regards, Simon Riggs