Thread: Is there a tool for checking database integrity

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.

Thank you in advance, Clemens

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