Skip site navigation (1) Skip section navigation (2)

Re: Data corruption after SAN snapshot

From: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
To: Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Terry Schmitt <tschmitt(at)schmittworks(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Data corruption after SAN snapshot
Date: 2012-08-08 02:38:52
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
On 08/08/2012 09:39 AM, Stephen Frost wrote:
> Terry,
> * Terry Schmitt (tschmitt(at)schmittworks(dot)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 ( or 
`pageinspect' module (

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

In response to

pgsql-admin by date

Next:From: Craig RingerDate: 2012-08-08 06:57:05
Subject: Re: Data migration to sql server 2008
Previous:From: Stephen FrostDate: 2012-08-08 02:21:51
Subject: Re: PostgreSQL 9.0 authentication against LDAP/AD

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group