Re: How to determine a database is intact?

Believe it or not, we haven't gotten many requests for this feature,
partly because such corruption is so rare.  Also, any checker isn't
going to find a change from "Baker" to "Faker" in a text field.

---------------------------------------------------------------------------

Wes wrote:
> On 9/4/04 5:28 PM, "Tino Wildenhain" <tino@wildenhain.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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073