Hi all:
Is there any document that describes the system tables of Postgres (the
pg_* ones), their function, utility, relations among each other, etc.,
and how can one manipulate them to recover from errors?
I am asking this because I've just experienced one of those awful
database corruption cases, where one connects to an existing database
and gets a "Couldn't find any tables, sequences or indices!" when trying
to list the tables. Okay, let's do a "SELECT * FROM pg_class"... but it
doesn't show any of the existing tables. No problem, we'll just dump the
database and copy it from the "pg_dump" done by the nightly backup
cronjob. "destroydb $dbname; createdb $dbname"... but you can't;
Postgres tells you that the "database already exists". You do a "psql
-l" and a "SELECT * FROM pg_database" while connected to "template1",
and none of them show said database, but you still can't create it, nor
connect to it because then it tells you that the database doesn't
exist... In the end, I had to delete completely the Postgres
installation directory, do an initdb and restore the databases by hand.
All of the above makes me think that, despite what I have mentioned
above about pg_class and pg_database, there's something that I'm missing
about the inner workings of Postgres, and I'd really know what it is. I
have read the FAQ, the user's guide, the administrator's guide (the
place where I expected to find said information) and have searched the
list archives, and have come up with nothing (or at least, nothing
comprehensive; there are mentions scattered here and there to "look at
this pg_foo table, which holds the information about blah blah...").
Sorry for the rant-like nature of this post, but in any case... can
anybody help? And while we're on this, what do people here do in cases
like what I have described above?
Paulo Jan.
DDnet.