Re: Data corruption after SAN snapshot

Terry,

* Terry Schmitt (tschmitt@schmittworks.com) wrote:

So far, executing pg_dumpall
seems to be fairly reliable for finding the corrupt objects after my
initial data load, but unfortunately much of the corruption has been with
indexes which pgdump will not expose.

Shouldn't be too hard to write a script that'll do a query against each
table using an ORDER BY that matches each index, at least for 'simple'
indexes, which'll typically cause an in-order index traversal.

I'd really like a "VERIFY" command for PostgreSQL, though a proper one isn't really possible without block checksums.

I'm currently working on a virtual plug pull tool that uses VMs to simulate abrupt crashes of the machine PostgreSQL is running on. One of the bigger challenges is that Pg doesn't offer any reliable way to detect even simple corruption.

Maybe a pg_read_relation(oid) that simply reads all blocks in an index or table would help. It could live in the `adminpack' module ( http://www.postgresql.org/docs/9.1/static/adminpack.html) or `pageinspect' module ( http://www.postgresql.org/docs/9.1/static/pageinspect.html).


It turns out I can use the pageinspect functions to do a rough kind of verify, but it's pretty slow and inconvenient. Eg:

WITH pages(page) AS (
    SELECT get_raw_page('tablename'::text, pageno)
    FROM generate_series(0, (SELECT relpages FROM pg_class WHERE relname = 'tablename')-1) AS pageno
)
SELECT page_header(page), heap_page_items(page) FROM pages;

takes 90ms when a 'SELECT * FROM tablename' takes 6.2ms . On a bigger table, the query takes 3939.912 vs 125.135ms for a table scan.

Of course, pageinspect is mostly interesting for indexes, where I'd do:

create or replace function scan_index(indexname text) returns setof record as $$
SELECT page_header(get_raw_page($1, 0));
WITH pagenumbers(pageno) AS (
    SELECT generate_series(1, (SELECT relpages FROM pg_class WHERE relname = $1)-1)
)
SELECT bt_page_items($1, pageno) FROM pagenumbers;
$$ language sql volatile;

SELECT scan_index('some_idx');

... but that's getting really quite slow and still hasn't touched the free space map or visibility map.

Of course, these checks prove nothing about subtle corruption or incorrect contents, they only make sure Pg can read them and they look vaguely sane. It doesn't do any kind of consistency checking between index and table.

--
Craig Ringer