invalid page header in block 52979 of relation "pg_attribu te"

[Steve Crawford]

This morning I got bitten by the "SELECT INTO" / "CREATE TABLE AS"
from tables without OIDs bug in 7.4.1.

Postmaster killed all the backends and restarted - pg was down for 2
seconds.

This happened two times within a few minute period.

Now I am getting 'invalid page header in block 52979 of relation
"pg_attribu te"' errors in the postgresql log file. I cannot reindex
or view the table at all even as user postgres.

I have not tried a manual restart of PG.

Although hardware could be an issue I tend to doubt it. This server
passed an extensive memory test before going into service and I see
no occurrences of this error prior to the crash but several since
then.

This is a 24x7x365 system. I do have a dump from this morning (I do
two per day) but I would prefer to fix the problem with no downtime
or data loss.

What serious problem is this likely to cause/be causing? What are my
options for fixing it?

Naturally, quick replies are appreciated - this is making me very
nervous.

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Steve Crawford <sc*******@pinp ointresearch.co m> writes:

This morning I got bitten by the "SELECT INTO" / "CREATE TABLE AS"
from tables without OIDs bug in 7.4.1.
On a production server, you really ought to track bug-fix releases a
bit more enthusiasticall y than that :-(. However, I don't see anything
in the CVS history that looks related to your problem.
Now I am getting 'invalid page header in block 52979 of relation
"pg_attribu te"' errors in the postgresql log file.

Could you get a hex dump of that page?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Steve Crawford]

On Thursday 11 November 2004 3:14 pm, Tom Lane wrote:

Steve Crawford <sc*******@pinp ointresearch.co m> writes:

This morning I got bitten by the "SELECT INTO" / "CREATE TABLE
AS" from tables without OIDs bug in 7.4.1.

On a production server, you really ought to track bug-fix releases
a bit more enthusiasticall y than that :-(. However, I don't see
anything in the CVS history that looks related to your problem.

True. I hadn't come up with a good time to get past that 7.4.1 ->
7.4.2 initdb requirement. I guess I'll have to go with the manual
method.

Now I am getting 'invalid page header in block 52979 of relation
"pg_attribu te"' errors in the postgresql log file.

Could you get a hex dump of that page?

What is the best method to do this? Also, can I safely drop that table
and recreate it from a dump file?

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Steve Crawford <sc*******@pinp ointresearch.co m> writes:

Could you get a hex dump of that page?

What is the best method to do this?
There's always "od -x" ... however, if you prefer you can use
pg_filedump from http://sources.redhat.com/rhdb/.
Also, can I safely drop that table

Not unless you want to drop the whole database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Martijn van Oosterhout]

On Thu, Nov 11, 2004 at 04:29:38PM -0700, Steve Crawford wrote:

True. I hadn't come up with a good time to get past that 7.4.1 ->
7.4.2 initdb requirement. I guess I'll have to go with the manual
method.
IIRC, the initdb is recommended, but not required. It can be done
without an initdb too. Check the release notes for more details. You
may as well jump to the most recent...
What is the best method to do this? Also, can I safely drop that table
and recreate it from a dump file?
Errr, pg_attribute is what holds the information about column names,
types and other such details. Most of your schema really.

It would need a full restore if you dropped it...

--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBlNyZY5T wig3Ge+YRAgScAJ 9tvf0GeCcZZogIp dIITtON6TPqXgCe NwoX
QLgYM+MaTfKU/XP6pAGVJm4=
=Wgc5
-----END PGP SIGNATURE-----

[Steve Crawford]

On Friday 12 November 2004 7:54 am, Martijn van Oosterhout wrote:

On Thu, Nov 11, 2004 at 04:29:38PM -0700, Steve Crawford wrote:

True. I hadn't come up with a good time to get past that 7.4.1 ->
7.4.2 initdb requirement. I guess I'll have to go with the manual
method.

IIRC, the initdb is recommended, but not required. It can be done
without an initdb too. Check the release notes for more details.
You may as well jump to the most recent...

Already downloaded - the upgrade is on the todo list.

What is the best method to do this? Also, can I safely drop that
table and recreate it from a dump file?

Errr, pg_attribute is what holds the information about column
names, types and other such details. Most of your schema really.

It would need a full restore if you dropped it...

I successfully tested then ran a process to dump the db, drop it, then
restore it. All is fine, now. I think the bad block wasn't used by
anything important as the server continued handling all its work
without problem. The only time this error showed up in the log was
when I tried to query the table or when autovacuum tried to analyze
it.

Fortunately this database dump is only ~300MB so I only had to shut
down for 5 minutes to do the dump/drop/create/restore.

Cheers,
Steve

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend