Re: check database integrity

On 20/07/14 16:02, Andrew Sullivan wrote:
>> Then I could also use it in production. But currently I
>> > need it only to verify a backup.

> If you need to verify a backup, why isn't pg_dump acceptable? Or is
> it that you are somehow trying to prove that what you have on the
> target (backup) machine is in fact production-ready? I guess I don't
> really understand what you are trying to do.

Sorry, for kind-of misusing the word backup. What I am doing is this. I
took a base backup and replayed a few xlogs. This is what I meant with
"backup".

What I want to verify is whether all pages in all files match their
checksums. So, I have to make postgres read all pages at least once.
Pg_dump does this for normal tables and toast. But it does not read
index relations as far as I know. A

select count(*)

from all tables would also do the job, again without indexes.

The sentence about the backup was only to point out that I don't really
care if the query locks the database for concurrent transactions. But
better if it would not acquire an exclusive lock on all tables.

Torsten