Message-ID: <5021D13C.40809@ringerc.id.au>
Date: Wed, 08 Aug 2012 10:38:52 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Stephen Frost <sfrost@snowman.net>
CC: Terry Schmitt <tschmitt@schmittworks.com>,
 pgsql-admin@postgresql.org
Subject: Re: Data corruption after SAN snapshot
References: 
 <CAOOcyswLYBfJDuvNBPMkiNCGNKgK=SiexUuTVHCh2O+Y1T-sLw@mail.gmail.com>
	<5021BCA6.7030000@ringerc.id.au>
	<CAOOcysxktC6qkc0j4cruhtA5Ec5BLDb9Q0hX9YWkb8G7F1_JdA@mail.gmail.com>
	<20120808013931.GO1267@tamriel.snowman.net>
In-Reply-To: <20120808013931.GO1267@tamriel.snowman.net>
Content-Type: multipart/alternative;
	boundary="------------070307030300040309040205"

This is a multi-part message in MIME format.
--------------070307030300040309040205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 08/08/2012 09:39 AM, Stephen Frost wrote:
> 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

--------------070307030300040309040205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 08/08/2012 09:39 AM, Stephen Frost
      wrote:<br>
    </div>
    <blockquote cite="mid:20120808013931.GO1267@tamriel.snowman.net"
      type="cite">
      <pre wrap="">Terry,

* Terry Schmitt (<a class="moz-txt-link-abbreviated" href="mailto:tschmitt@schmittworks.com">tschmitt@schmittworks.com</a>) wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="">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.
</pre>
      </blockquote>
      <pre wrap="">
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.

</pre>
    </blockquote>
    I'd really like a "VERIFY" command for PostgreSQL, though a proper
    one isn't really possible without block checksums. <br>
    <br>
    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.<br>
    <br>
    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 (
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    <a href="http://www.postgresql.org/docs/9.1/static/adminpack.html">http://www.postgresql.org/docs/9.1/static/adminpack.html</a>)
    or `pageinspect' module (
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    <a href="http://www.postgresql.org/docs/9.1/static/pageinspect.html">http://www.postgresql.org/docs/9.1/static/pageinspect.html</a>).<br>
    <br>
    <br>
    It turns out I can use the pageinspect functions to do a rough kind
    of verify, but it's pretty slow and inconvenient. Eg:<br>
    <br>
    <blockquote>WITH pages(page) AS (<br>
      &nbsp;&nbsp;&nbsp; SELECT get_raw_page('tablename'::text, pageno)<br>
      &nbsp;&nbsp;&nbsp; FROM generate_series(0, (SELECT relpages FROM pg_class WHERE
      relname = 'tablename')-1) AS pageno<br>
      )<br>
      SELECT page_header(page), heap_page_items(page) FROM pages;<br>
    </blockquote>
    <br>
    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.<br>
    <br>
    Of course, pageinspect is mostly interesting for indexes, where I'd
    do:<br>
    <br>
    <blockquote>create or replace function scan_index(indexname text)
      returns setof record as $$<br>
      SELECT page_header(get_raw_page($1, 0));<br>
      WITH pagenumbers(pageno) AS (<br>
      &nbsp;&nbsp;&nbsp; SELECT generate_series(1, (SELECT relpages FROM pg_class WHERE
      relname = $1)-1)<br>
      )<br>
      SELECT bt_page_items($1, pageno) FROM pagenumbers;<br>
      $$ language sql volatile;<br>
      <br>
      SELECT scan_index('some_idx');<br>
    </blockquote>
    <meta http-equiv="content-type" content="text/html;
      charset=ISO-8859-1">
    <br>
    ... but that's getting really quite slow and still hasn't touched
    the free space map or visibility map.<br>
    <br>
    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. <br>
    <br>
    --<br>
    Craig Ringer<br>
  </body>
</html>

--------------070307030300040309040205--