Re: How to determine a database is intact?

On 9/4/04 5:28 PM, "Tino Wildenhain" <tino(at)wildenhain(dot)de> wrote:

> Well, with such a huge database you probably should consider
> different backup strategies, a filesystem with snapshot
> support (XFS?) could help where you can copy a state of the database
> at any time - so you can backup the database cluster without
> stopping the postmaster. Also replication via slony could be
> an option.

Yes, we are looking into using file system snapshots. We are currently
using primarily file system backups (shut down the DB, back up the file
system). The problem we ran into was that we didn't have a specific point
in time where we knew with absolute certainty the backed up database was
good - snapshots would not help here.

I ended up starting with a recent backup, and working backwards until I
found one that wouldn't crash postmaster on a pg_dumpall. Rather than trust
that there was no corruption in that version (data blocks might be good, but
pg_dumpall doesn't test indexes), I did a pg_dumpall and reload.

> The best tool to verify the backup is probably the postmaster
> itself. I really doubt any other program would be smaller and
> faster :)

Not really... Postmaster won't tell you if a structure is bad until it
stumbles on it and crashes (or politely reports an error). Just because
postmaster comes up doesn't mean your database is good. As far as I know,
there is no "verify database" command option on postmaster - postmaster
won't fsck your database.

Wes