Re: Checking data checksums...

I use this:

create extension pageinspect;

SELECT count(*) AS pages_read
FROM (
SELECT c.oid::regclass::text AS rel,
f.fork,
ser.i AS blocknr,
page_header(get_raw_page(c.oid::regclass::text,
f.fork,
ser.i))
FROM pg_class c
CROSS JOIN (values ('main'::text),
('fsm'::text),
('vm'::text)) f(fork)
CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
WHERE sz.sz>0
) t1;

The idea is to read just everything. Since a select works only inside one
database, this works only for that database. If you have multiple databases
in a cluster, you need to run it in every one of them.

Note this only works if your page size is the usual 8k. If you have
compiled your postgres otherwise then change 8192 to whatever it is.

Also, PG verifies the checksum when it reads a page from storage. So, this
will miss pages that are present in shared_buffers. But assuming that they
came there from storage in the first place, that should be good enough.

Alternatives are something like pg_dumpall >/dev/null. This reads all data
files but won't probably detect problems in indexes. Still it's a good idea
to do once in a while to check toasted data for instance.

On Fri, Dec 16, 2016 at 11:07 AM, <marin(at)kset(dot)org> wrote:

> Hi,
>
> I enabled data checksums (initdb --data-checksums) on a new instance and
> was wandering is there a command in the psql console, or from the linux
> console, to force a checksum check on the entire cluster and get error
> reports if it finds some corrupted pages.
>
> Regards,
> Mladen Marinović
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>