Tom Lane wrote:
Wim Kerkhoff <[EMAIL PROTECTED]> 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 server2004-09-13 13:17:55 [1526] LOG: database system was interrupted at 2004-09-13 12:14:08 PDT
after the crash.
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
Ahaha... you're right. No entry in pg_class, but there is an entry in pg_type.
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?
/*
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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
