Re: Is there a tool for checking database integrity - Mailing list pgsql-general

From Tomas Vondra
Subject Re: Is there a tool for checking database integrity
Date
Msg-id 4FA6FD3B.4040708@fuzzy.cz
Whole thread Raw
In response to Is there a tool for checking database integrity  (Clemens Eisserer <linuxhippy@gmail.com>)
Responses Re: Is there a tool for checking database integrity
Re: Is there a tool for checking database integrity
List pgsql-general
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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: how to amend SQL standard to add comments?
Next
From: Bruce Momjian
Date:
Subject: Re: Is there a tool for checking database integrity