Re: dumping tables from badly damaged db

Brian Ristuccia <bristucc(at)sw(dot)starentnetworks(dot)com> writes:
> Recently I had a problem where a system crash scribbed on some directories,
> which landed a bunch of files, including a few of the system table files for
> one of my databases, in lost+found along with a zillion other files.

Ugh.

> I might be able to find the file for this table/index in lost+found, but how
> do I know what name to give it in /var/lib/postgres/...?

I can't think of any reasonably simple way to identify the files by
content (this might be something to try to fix in future, but for now
you're stuck). Best idea I can think of is to examine "od -c" dumps
and try to intuit which file is which.

> Currently, I can't connect to the database with the following error:
> psql: FATAL 1: cannot open pg_class_relname_index: No such file or directory

You might be able to get past this by starting a standalone postgres
with the -P command-line option (ignore system indexes). If so, try
"select relname, relfilenode from pg_class". With luck that will give
you a list of which file name is needed for each table. I'd not counsel
trying to do more than that in the standalone backend until you've
gotten at least the tables put back together.

You do not need to try very hard to recreate the indexes --- you can use
REINDEX to rebuild them.

Good luck!

regards, tom lane