pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)

Message-ID: <41473FF1.5080805@nyetwork.org>
Permalink:  ../41473FF1.5080805@nyetwork.org/
Also on:    postgresql.org/message-id/41473FF1.5080805@nyetwork.org

 · 

reply

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