pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_class / missing tables
8+ messages / 5 participants
[nested] [flat]

* pg_class / missing tables
@ 2004-09-14 02:30  Wim Kerkhoff <wim@nyetwork.org>
  0 siblings, 3 replies; 8+ messages in thread

From: Wim Kerkhoff @ 2004-09-14 02:30 UTC (permalink / raw)
  To: pgsql-admin

I'm trying to recover a database where some tables 'went missing'...

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.

The missing tables aren't listed in \d or in the pg_class table, but 
according to the sizes of the numbered files in 
/var/lib/postgres/data/base/142772, the raw files must still be there. 
What's interested is that if I try:

CREATE TABLE missing_table_name (foo int);

It does complain that the table already exists...

What's happening here?

Many thanks,

Wim




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 04:40  Chester Kustarz <chester@arbor.net>
  parent: Wim Kerkhoff <wim@nyetwork.org>
  2 siblings, 0 replies; 8+ messages in thread

From: Chester Kustarz @ 2004-09-14 04:40 UTC (permalink / raw)
  To: Wim Kerkhoff <wim@nyetwork.org>; +Cc: pgsql-admin

On Mon, 13 Sep 2004, Wim Kerkhoff wrote:
> 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.

Perhaps you have IDE disks that do not fsync properly. I have seen
a box that had a bad disk system get into the same condition as well.
Perhaps the transaction commit bug fixed in 7.4.4 got you. Perhaps you
turned fsync off.

In any event, some of your tables and/or indexes are corrupted. You should
test your hardware to eliminate that as a concern and upgrade your postgres
version.

You have backups, right?





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 06:17  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Wim Kerkhoff <wim@nyetwork.org>
  2 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2004-09-14 06:17 UTC (permalink / raw)
  To: Wim Kerkhoff <wim@nyetwork.org>; +Cc: pgsql-admin

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';

Also, let's see the error log from when you tried to restart the server
after the crash.

> 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?

> 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.

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.  But there are enough
gotchas in this idea that "restore from backup" is probably a better
answer.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 13:27  Amin <achavarria@ilusion.com.mx>
  parent: Wim Kerkhoff <wim@nyetwork.org>
  2 siblings, 1 reply; 8+ messages in thread

From: Amin @ 2004-09-14 13:27 UTC (permalink / raw)
  To: Wim Kerkhoff <wim@nyetwork.org>; pgsql-admin

ya no quiere pertenecer a la lista como de desuscribo...?



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 15:46  Lee Wu <Lwu@mxlogic.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Lee Wu @ 2004-09-14 15:46 UTC (permalink / raw)
  To: Wim Kerkhoff <wim@nyetwork.org>; pgsql-admin

I had similar problem before. I was able to select from
the_missig_table.
What I did to fix my problem was to vacuum the whole database by login
psql:
vacuum;

Hope it helps!

-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Wim Kerkhoff
Sent: Monday, September 13, 2004 8:31 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] pg_class / missing tables

I'm trying to recover a database where some tables 'went missing'...

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.

The missing tables aren't listed in \d or in the pg_class table, but 
according to the sizes of the numbered files in 
/var/lib/postgres/data/base/142772, the raw files must still be there. 
What's interested is that if I try:

CREATE TABLE missing_table_name (foo int);

It does complain that the table already exists...

What's happening here?

Many thanks,

Wim


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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 18:30  Wim Kerkhoff <wim@nyetwork.org>
  parent: Lee Wu <Lwu@mxlogic.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Wim Kerkhoff @ 2004-09-14 18:30 UTC (permalink / raw)
  To: pgsql-admin

Hi Lee,

Thanks for the ideas...

Lee Wu wrote:

>I had similar problem before. I was able to select from
>the_missig_table.
>  
>
pmacct=# SELECT * from missing_table;
ERROR:  relation "missing" does not exist
pmacct=# select * from missing_table;
ERROR:  relation "missing_table" does not exist
pmacct=# \d missing_table
Did not find any relation named "missing_table".

pmacct=# select count(*) from pg_class where relname='missing_table';
 count
-------
     0
(1 row)
pmacct=# create table missing_table(foo int);
ERROR:  type "missing_table" already exists

pmacct=#

>What I did to fix my problem was to vacuum the whole database by login
>psql:
>vacuum;
>  
>
Didn't make any difference... :-(

>Hope it helps!
>  
>
Nope :-(

Thanks,

Wim




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 18:33  Wim Kerkhoff <wim@nyetwork.org>
  parent: Amin <achavarria@ilusion.com.mx>
  0 siblings, 0 replies; 8+ messages in thread

From: Wim Kerkhoff @ 2004-09-14 18:33 UTC (permalink / raw)
  To: pgsql-admin

Amin wrote:

>ya no quiere pertenecer a la lista como de desuscribo...?
>  
>
Sorry, I have no idea what you're saying...

Wim




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: pg_class / missing tables
@ 2004-09-14 19:01  Wim Kerkhoff <wim@nyetwork.org>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Wim Kerkhoff @ 2004-09-14 19:01 UTC (permalink / raw)
  To: pgsql-admin

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2004-09-14 19:01 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-14 02:30 pg_class / missing tables Wim Kerkhoff <wim@nyetwork.org>
2004-09-14 04:40 ` Chester Kustarz <chester@arbor.net>
2004-09-14 06:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-14 19:01   ` Wim Kerkhoff <wim@nyetwork.org>
2004-09-14 13:27 ` Amin <achavarria@ilusion.com.mx>
2004-09-14 18:33   ` Wim Kerkhoff <wim@nyetwork.org>
2004-09-14 15:46 Re: pg_class / missing tables Lee Wu <Lwu@mxlogic.com>
2004-09-14 18:30 ` Wim Kerkhoff <wim@nyetwork.org>

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