Re: pg_class / missing tables

Wim Kerkhoff <wim(at)nyetwork(dot)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