Thread: Invalid Page Header
I get an "Invalid page header in block 23 of storetransaction_tbl" error on my table when running any kind of select. I do not get the error on other tables. I get the error from CGI apps as well as from psql command line. Any help would be appreciated. Thank You, Christopher A. Goodfellow Corporate Director Tealuxe, Inc. Phone: 508-520-7887 Fax: 508-528-8999 Tea For All
On Wed, Mar 08, 2006 at 10:39:37AM -0500, Christopher A. Goodfellow wrote: > I get an "Invalid page header in block 23 of storetransaction_tbl" error on > my table when running any kind of select. Something has corrupted part of your table. If you search the list archives for "Invalid page header" you'll find suggestions on how to find the bad block and view its contents as a hex or ASCII dump (which might or might not be interesting) and how to zero that block so the database can access the rest of the table without errors (a destructive process, so don't do it lightly). http://archives.postgresql.org/ -- Michael Fuhr
Have a look at this post: http://archives.postgresql.org/pgsql-general/2006-03/msg00002.php I have the same problem and we are in the process of fix it. Cheers Noel Christopher A. Goodfellow wrote: > I get an "Invalid page header in block 23 of storetransaction_tbl" error on > my table when running any kind of select. I do not get the error on other > tables. I get the error from CGI apps as well as from psql command line. > Any help would be appreciated. > > Thank You, > Christopher A. Goodfellow > Corporate Director > Tealuxe, Inc. > Phone: 508-520-7887 > Fax: 508-528-8999 > Tea For All > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Attachment
On Thu, Mar 09, 2006 at 11:38:40AM +1100, Noel Faux wrote: > Have a look at this post: > http://archives.postgresql.org/pgsql-general/2006-03/msg00002.php > > I have the same problem and we are in the process of fix it. But we should point out that we're not "fixing" it in the sense of recovering data. We're wiping out bad blocks; whatever data was there has been corrupted so we're telling the database to forget about it. I haven't tried it, but I wonder how the database would respond to pulling an old, good copy of the block from a filesystem-level backup and plugging it into the data file where the bad block is. I'm sure that could cause problems, but if the data hadn't changed since before it went bad then I wonder if that might work. -- Michael Fuhr
Michael Fuhr wrote:
That's a very good idea, but one really needs to have setup a robust / regular backup of the database. Has it been tried before?On Thu, Mar 09, 2006 at 11:38:40AM +1100, Noel Faux wrote:Have a look at this post: http://archives.postgresql.org/pgsql-general/2006-03/msg00002.php I have the same problem and we are in the process of fix it.But we should point out that we're not "fixing" it in the sense of recovering data. We're wiping out bad blocks; whatever data was there has been corrupted so we're telling the database to forget about it. I haven't tried it, but I wonder how the database would respond to pulling an old, good copy of the block from a filesystem-level backup and plugging it into the data file where the bad block is. I'm sure that could cause problems, but if the data hadn't changed since before it went bad then I wonder if that might work.
Attachment
I have read quite a bit in the archives and it seems the best way is to zero_damaged_pages. I did a search for zero_damaged_pages. Is it as simple as adding zero_damaged_pages to postgresql.conf and restarting the postmaster? Thank You, Christopher A. Goodfellow Corporate Director Tealuxe, Inc. Phone: 508-520-7887 Fax: 508-528-8999 Tea For All -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: Wednesday, March 08, 2006 6:18 PM To: Christopher A. Goodfellow Cc: Pgsql-Novice (E-mail) Subject: Re: [NOVICE] Invalid Page Header On Wed, Mar 08, 2006 at 10:39:37AM -0500, Christopher A. Goodfellow wrote: > I get an "Invalid page header in block 23 of storetransaction_tbl" error on > my table when running any kind of select. Something has corrupted part of your table. If you search the list archives for "Invalid page header" you'll find suggestions on how to find the bad block and view its contents as a hex or ASCII dump (which might or might not be interesting) and how to zero that block so the database can access the rest of the table without errors (a destructive process, so don't do it lightly). http://archives.postgresql.org/ -- Michael Fuhr
On Thu, Mar 09, 2006 at 01:48:40PM -0500, Christopher A. Goodfellow wrote: > I have read quite a bit in the archives and it seems the best way is to > zero_damaged_pages. I did a search for zero_damaged_pages. Is it as simple > as adding zero_damaged_pages to postgresql.conf and restarting the > postmaster? To be honest, I'd forgotten about zero_damaged_pages (and I must have overlooked the recent messages in -hackers that mention it). I'd prefer to set it in a particular session rather than in postgresql.conf so it happened only when and where I want. Here's an example: test=# select count(*) from foo; ERROR: invalid page header in block 10 of relation "foo" test=# set zero_damaged_pages to on; SET test=# select count(*) from foo; WARNING: invalid page header in block 10 of relation "foo"; zeroing out page WARNING: invalid page header in block 20 of relation "foo"; zeroing out page WARNING: invalid page header in block 30 of relation "foo"; zeroing out page count ------- 9445 (1 row) test=# set zero_damaged_pages to off; SET -- Michael Fuhr
"Christopher A. Goodfellow" <cgoodfellow@tealuxe.com> writes:
> I have read quite a bit in the archives and it seems the best way is to
> zero_damaged_pages. I did a search for zero_damaged_pages. Is it as simple
> as adding zero_damaged_pages to postgresql.conf and restarting the
> postmaster?
Since zero_damaged_pages is a pretty dangerous thing to have on, I
wouldn't recommend turning it on in postgresql.conf. Instead, turn it
on within a single session using SET, and then scan the tables that you
want to clean up (a VACUUM or SELECT COUNT(*) will do).
regards, tom lane
Thank You. I set zero_damaged_pages to on using the owner user for the
database and did a select count(). The response was fixing. After setting
zero_damaged_pages to off, I still received the Invalid Page Header error.
I then set zero back to on and did a vacuum on the table. This solved the
problem. I did loose one row of data but I expected that.
Thank You,
Christopher A. Goodfellow
Corporate Director
Tealuxe, Inc.
Phone: 508-520-7887
Fax: 508-528-8999
Tea For All
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Thursday, March 09, 2006 2:20 PM
To: Christopher A. Goodfellow
Cc: 'Michael Fuhr'; 'Pgsql-Novice (E-mail)'
Subject: Re: [NOVICE] Invalid Page Header
"Christopher A. Goodfellow" <cgoodfellow@tealuxe.com> writes:
> I have read quite a bit in the archives and it seems the best way is to
> zero_damaged_pages. I did a search for zero_damaged_pages. Is it as
simple
> as adding zero_damaged_pages to postgresql.conf and restarting the
> postmaster?
Since zero_damaged_pages is a pretty dangerous thing to have on, I
wouldn't recommend turning it on in postgresql.conf. Instead, turn it
on within a single session using SET, and then scan the tables that you
want to clean up (a VACUUM or SELECT COUNT(*) will do).
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly