Обсуждение: testing database consistency

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

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

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

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