Postgres Pro
Downloads
Home   >   mailing lists

Thread: testing database consistency

testing database consistency

From
Raphael Bauduin
Date:
Hi,

I have a postgresql running in a virtual machine (linux, kvm).
I wanted to test that the backup, which takes a copy of the disk image, is valid and usable.

I have taken a copy of the disk image and run it fine on another computer.
However, just starting the server succesfully does not mean there is no corrupted data in the database. This is why I'm looking to a way to validate that all the data stored in the postgresql is ok, ie consistent and usable. Does anyone have advices on how to best check the validity of the whole database?

Thanks in advance!

Raphaël

Re: testing database consistency

From
Rene Romero Benavides
Date:
PostgreSQL 9.3 ships with data corruption detection facilities http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-data-checksums/ but it works on a transactional basis.



2013/10/24 Raphael Bauduin <rblists@gmail.com>
Hi,

I have a postgresql running in a virtual machine (linux, kvm).
I wanted to test that the backup, which takes a copy of the disk image, is valid and usable.

I have taken a copy of the disk image and run it fine on another computer.
However, just starting the server succesfully does not mean there is no corrupted data in the database. This is why I'm looking to a way to validate that all the data stored in the postgresql is ok, ie consistent and usable. Does anyone have advices on how to best check the validity of the whole database?

Thanks in advance!

Raphaël



--
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

Re: testing database consistency

From
Albe Laurenz
Date:
Raphael Bauduin wrote:
> I have a postgresql running in a virtual machine (linux, kvm).
> I wanted to test that the backup, which takes a copy of the disk image, is valid and usable.
> 
> I have taken a copy of the disk image and run it fine on another computer.
> However, just starting the server succesfully does not mean there is no corrupted data in the
> database. This is why I'm looking to a way to validate that all the data stored in the postgresql is
> ok, ie consistent and usable. Does anyone have advices on how to best check the validity of the whole
> database?

There is no safe way (unless you were running on 9.3 with checksums).

The best thing you can do is run a SELECT * from all tables and see
if that succeeds.
Also, rebuild all indexes if there are doubts whether they are valid or not.

Yours,
Laurenz Albe

Re: testing database consistency

From
Scott Ribe
Date:
On Oct 25, 2013, at 3:00 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

> There is no safe way (unless you were running on 9.3 with checksums).

It would be cool though, for those of us doing that, to have some new command to verify the checksum on all pages...

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: testing database consistency

From
Albe Laurenz
Date:
Scott Ribe wrote:
>> There is no safe way (unless you were running on 9.3 with checksums).
> 
> It would be cool though, for those of us doing that, to have some new command to verify the checksum
> on all pages...

How about
pg_dumpall -f /dev/null

Yours,
Laurenz Albe

Re: testing database consistency

From
Alejandro Brust
Date:
Hi,
U could try to perform many task over the running copy
like pg_dump + pg_restore + vacuumdb + reindexdb.... ,etc
If all of them  went ok U could make several select of yours best known
tables and compare them with the original one.


see U



El 24/10/2013 04:18, Raphael Bauduin escribió:
> Hi,
>
> I have a postgresql running in a virtual machine (linux, kvm).
> I wanted to test that the backup, which takes a copy of the disk
> image, is valid and usable.
>
> I have taken a copy of the disk image and run it fine on another computer.
> However, just starting the server succesfully does not mean there is
> no corrupted data in the database. This is why I'm looking to a way to
> validate that all the data stored in the postgresql is ok, ie
> consistent and usable. Does anyone have advices on how to best check
> the validity of the whole database?
>
> Thanks in advance!
>
> Raphaël