pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtesting database consistency
6+ messages / 5 participants
[nested] [flat]
* testing database consistency
@ 2013-10-24 07:18 Raphael Bauduin <rblists@gmail.com>
0 siblings, 3 replies; 6+ messages in thread
From: Raphael Bauduin @ 2013-10-24 07:18 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: testing database consistency
@ 2013-10-24 13:06 Rene Romero Benavides <rene.romero.b@gmail.com>
parent: Raphael Bauduin <rblists@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Rene Romero Benavides @ 2013-10-24 13:06 UTC (permalink / raw)
To: Raphael Bauduin <rblists@gmail.com>; +Cc: pgsql-admin
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: testing database consistency
@ 2013-10-25 09:00 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Raphael Bauduin <rblists@gmail.com>
2 siblings, 1 reply; 6+ messages in thread
From: Albe Laurenz @ 2013-10-25 09:00 UTC (permalink / raw)
To: Raphael Bauduin *EXTERN* <rblists@gmail.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: testing database consistency
@ 2013-10-25 13:37 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 1 reply; 6+ messages in thread
From: Scott Ribe @ 2013-10-25 13:37 UTC (permalink / raw)
To: Albe Laurenz <laurenz.albe@wien.gv.at>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: testing database consistency
@ 2013-11-04 13:56 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 6+ messages in thread
From: Albe Laurenz @ 2013-11-04 13:56 UTC (permalink / raw)
To: Scott Ribe *EXTERN* <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: testing database consistency
@ 2013-11-04 19:16 Alejandro Brust <alejandrob@pasteleros.org.ar>
parent: Raphael Bauduin <rblists@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Alejandro Brust @ 2013-11-04 19:16 UTC (permalink / raw)
To: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2013-11-04 19:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-10-24 07:18 testing database consistency Raphael Bauduin <rblists@gmail.com>
2013-10-24 13:06 ` Rene Romero Benavides <rene.romero.b@gmail.com>
2013-10-25 09:00 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-10-25 13:37 ` Scott Ribe <scott_ribe@elevated-dev.com>
2013-11-04 13:56 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2013-11-04 19:16 ` Alejandro Brust <alejandrob@pasteleros.org.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox