Checking data checksums...

On 12/16/16 5:07 AM, marin@kset.org wrote:

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.

The newest release of pgBackRest (http://www.pgbackrest.org) will
validate all data checksums whenever doing a backup. Invalid checksums
are reported in the backup log and recorded in the backup manifest.

The functionality requires a companion library that has not made it into
the community Debian/RHEL packages yet, but should be available soon.

--
-David
david@pgmasters.net

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@kset.org> wrote: