Re: Is there a tool for checking database integrity

Hi,

On 6.5.2012 20:47, Clemens Eisserer wrote:
> Hi,
>
> Is there a tool for checking a postgresql database for integrity, so I
> would be notified immediatly if something went wrong e.g. with memory
> and can go back to the last good backup?
> Such a tool would guarantee me I am not sitting on a ticking time bomb
> (corrupted database) without even noticing it.

No, at least in the current version. The next version (9.2) will support
checksums, but it's meant mostly as a protection against failures at the
I/O level. It might catch some memory issues, but it certainly won't be
100% protection.

There are unofficial tools (e.g. pg_check @ github, written by me) that
perform some checking when requested, but it's not (and never will be)
automatic.

Moreover, in many cases it's impossible to identify hw-level corruption,
unless you take the mainframe approach (running the task on multiple
devices and check if they produce the same result).

The best thing you can do is:

(1) watch the PostgreSQL log for unexpected failures - for example
    memory issues often manifest themselves as "invalid memory alloc"
    crashes etc.

(2) do regular backups and **check them** i.e. check that the backup
    finished OK and restore them somewhere else (a backup of a
    corrupted database usually fails)

(3) use good hw (ECC memory, ...) / test it thoroughly etc.

kind regards
Tomas