Re: Backup of live database

Brian Modra wrote:
> Sorry to be hammering this point, but I want to be totally sure its
> OK, rather than 5 months down the line attempt to recover, and it fails...
>
> Are you absolutely certain that the tar backup of the file that
> changed, is OK? (And that even if that file is huge, tar has managed
> to save the file as it was before it was changed - otherwise I'm
> afraid that the first part of the file is saved to tar, and then the
> file is modified, and the last part of the file is saved to tar from
> the point it was modified - and so therefore not consistent with the
> first part... And therefore the file has lost its integrity, so even a
> WAL restore won't help because the base files themselves are corrupt
> in the tar file?
Not sure if the answers you got answered your question or not. Here's
my take:

1) If the database is not running, tar works fine.

2) If the database is running, you can ONLY use tar if you also use WAL
archiving since the database will not only need the tar files, which
will be inconsistent, but also the WAL files (in your $PGDATA/pg_xlog)
in order to recover from those inconsistencies. I find this is best if
you are creating a warm standby that is keeping a backup database in
sync with a primary.

3) If the database is running, use pg_dump to create a consistent backup.

4) No matter what, as previously mentioned, you should test your backup
procedures to ensure you can reliably restore.

Good luck,
David