Search Postgresql Archives

Re: Data corruption zero a file - help!!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Mar 09, 2006 at 03:57:46PM +1100, Noel Faux wrote:
> Given that this seems problem has occurred a number of times for a 
> number I've written a small step by step procedure to address this 
> issue.  Is there any other comments you which to add.  I was thinking 
> that this should be added to the FAQ / troubleshooting in the docs.
> 
> How to repair corrupted data due to "ERROR: invalid page header in block 
> X of relation "Y":

The word "repair" might be misleading.  The operation repairs the
table in a sense, but as the following caution points out it does
so by completing the destruction that something else began.

> CAUTION this will permanently remove the data defined in the bad block
> 
>   1. To identify which file(s) the relation is in:
>         1. <THE SELECT STATEMENTS TO COLLECT THIS DATA> I can't 
> remember how I did it, but will keep hunting for my notes :)

Hint: pg_class.relfilenode

http://www.postgresql.org/docs/8.1/interactive/catalog-pg-class.html

>         2. so the file(s) are $PGDATA/<databaseDIR>/<relFile>.* The * 
> is any number which is defined below

You might want to show how to determine <databaseDIR>.  The actual
location might not be under $PGDATA -- 8.0 and later have tablespaces
and earlier versions support alternative locations, so instructions
should account for that.  Also, relations smaller than 1G won't have
any .N files.

http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html
http://www.postgresql.org/docs/8.1/interactive/storage.html
http://www.postgresql.org/docs/7.4/interactive/manage-ag-alternate-locs.html

>   2. To calculate the * value:
>         1. SELECT <block> / 131072 AS filenum, <block> % 131072 AS blocknum;
>            filenum    |  blocknum
>         -----------+----------------
>          <filenum> | <blocknum>
>               1. 131072 comes from "each database file is 1G, or 131072 
> * 8k blocks"

The 1G figure obviously applies only to tables that require that
much space.  If <filenum> comes back zero then you'd use the file
without any .N suffix.  If the bad block is less than 131072 (or
however many other-than-8k blocks fit in 1G) then you needn't bother
with the calculation.

>   3. Now you need to re-zero this block using the following command:
>         1. dd bs=8k seek=<blocknum> conv=notrunc count=1 if=/dev/zero 
> of=$PGDATA/base/<databaseDIR>/<relFile>.<filenum>

I'd recommend testing the command on a throwaway file before working
with real data -- "measure twice, cut once" as it were.  To gain
confidence in what you're doing you could create a test table,
populate it with data, corrupt its data file, then zero its bad
blocks until you can select all of the remaining data.  Playing
around in a production database is probably a bad idea; a safer way
would be to initdb a test cluster and run a separate postmaster
(listening on a different port if you're on the same machine as the
real database).

It's probably best to shut down the postmaster while you're mucking
around with the data files.

>               1. Before you do this it is best to backup the block:  
> "dd bs=8k skip=<blocknum> count=1 if=/path/file | hd"

This command doesn't back up the block, it pipes the block into a
command that on some systems will display a hex and ASCII dump of
the data (some systems will require a command other than hd).  You
could back up the block by redirecting the dd output to a file
instead of piping it into another command.

Incidentally, I was looking at your web site and your project
might make an interesting case study for the PostgreSQL web site
(Community -> In The Real World -> Case studies).

http://www.postgresql.org/about/casestudies/

Some users and potential users might be interested in reading about
how you're using PostgreSQL with a 100G+ database.  Post a message
to pgsql-www if you'd be interested in providing a write-up.

-- 
Michael Fuhr


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux