Re: pg_class / missing tables

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