From: Wim Kerkhoff <wim@nyetwork.org>
To: pgsql-admin@postgresql.org
Subject: Re: pg_class / missing tables
Date: Tue, 14 Sep 2004 12:01:05 -0700
Message-ID: <41473FF1.5080805@nyetwork.org> (raw)
In-Reply-To: <3144.1095142657@sss.pgh.pa.us>
References: <414657CA.5040809@nyetwork.org>
<3144.1095142657@sss.pgh.pa.us>
Tom Lane wrote:
>Wim Kerkhoff <wim@nyetwork.org> writes:
>
>
>>Short story: server was rebooted without being shut down properly, upon
>>bootup PostgreSQL (7.4.3) starts fine, but a couple of data tables
>>(along with their associated indexes and sequences) are gone. Other
>>tables are still there.
>>
>>
>
>Hmm. What *exactly* happens when you try
> select * from pg_class where relname = 'missing_table_name';
>
>
No records are returned, just the column headings for that table.
>Also, let's see the error log from when you tried to restart the server
>after the crash.
>
>
2004-09-13 13:17:55 [1526] LOG: database system was interrupted at
2004-09-13 12:14:08 PDT
2004-09-13 13:17:55 [1526] LOG: invalid magic number 0000 in log file
40, segment 199, offset 3670016
2004-09-13 13:17:55 [1526] LOG: invalid primary checkpoint record
2004-09-13 13:17:55 [1526] LOG: using previous checkpoint record at
28/C4720CB8
2004-09-13 13:17:55 [1526] LOG: redo record is at 28/C3009E78; undo
record is at 0/0; shutdown FALSE
2004-09-13 13:17:55 [1526] LOG: next transaction ID: 394565; next OID:
25070992
2004-09-13 13:17:55 [1526] LOG: database system was not properly shut
down; automatic recovery in progress
2004-09-13 13:17:55 [1526] LOG: redo starts at 28/C3009E78
2004-09-13 13:18:13 [1526] LOG: invalid magic number 0000 in log file
40, segment 199, offset 0
2004-09-13 13:18:13 [1526] LOG: redo done at 28/C6FFFF84
2004-09-13 13:18:25 [1526] LOG: recycled transaction log file
"00000028000000C3"
2004-09-13 13:18:25 [1526] LOG: recycled transaction log file
"00000028000000C5"
2004-09-13 13:18:25 [1526] LOG: recycled transaction log file
"00000028000000C4"
2004-09-13 13:18:25 [1526] LOG: database system is ready
>
>
>>What's interested is that if I try:
>>CREATE TABLE missing_table_name (foo int);
>>It does complain that the table already exists...
>>
>>
>
>You sure it's not complaining that the type already exists?
>
>
Ahaha... you're right. No entry in pg_class, but there is an entry in
pg_type.
/*
SELECT typname, typrelid from pg_type where typname like
'missing_table_name';
typname | typrelid
-----------------+----------
missing_table_name | 142777
*/
And, the typrelid matches the filenames in the $PGDATA/base/142772/
directory! Nice.
>>What's happening here?
>>
>>
>
>I'm suspicious that you've got a damaged block of pg_class. It would
>make sense that that would take out several rows created at about the
>same time, which would explain the fact that the lost items seem closely
>related.
>
>
This makes sense - thanks for the explanation.
>If you had *no* other lossage, you might be able to recover by
>recreating the tables with the exact same schemas, and then copying the
>old data files over these tables' data files.
>
Ok. I'm going to give that a try.
>But there are enough
>gotchas in this idea that "restore from backup" is probably a better
>answer.
>
> regards, tom lane
>
>
Yea... Thanks for the help.
Wim
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: wim@nyetwork.org
Subject: Re: pg_class / missing tables
In-Reply-To: <41473FF1.5080805@nyetwork.org>
References: <414657CA.5040809@nyetwork.org> <3144.1095142657@sss.pgh.pa.us> <41473FF1.5080805@nyetwork.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox