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

On Mon, May 07, 2012 at 12:37:47AM +0200, Tomas Vondra wrote:

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.

Postgres 9.2 will _not_ support checksums.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 7.5.2012 00:56, Bruce Momjian wrote:

On Mon, May 07, 2012 at 12:37:47AM +0200, Tomas Vondra wrote:

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.

Postgres 9.2 will _not_ support checksums.

Oh, I see - it was bumped to 9.3 and I've missed that.

Sorry for confusion.

Tomas

HI Tomas,

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.

Oh, I see - it was bumped to 9.3 and I've missed that.

Glad to see there is work going on in the integrity area.

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).

Sure, but checksumming in combination with a structural integrity
check should give at least some confidence everything is ok.

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

Thats the problem - because of cost constraints I have to deploy
postgresql on non-ECC boxes.
So I am looking forward to the checksum feature and hope no bit will toogle ;)

Thanks again, Clemens