Обсуждение: how to REINDEX pg_database on
I'm having a problem with Postgres 7.4 on RHEL 4 (Nahant Update 8).
It seems that pg_database disappeared some time ago (I think before
my oldest backups, unfortunately).
I searched and found some mention of REINDEX but I'm not sure if I am
using it correctly since this is a system catalog. I tried this:
postgres -P -D /usr/local/databases/pgsql/data pg_database
FATAL: database "pg_database" does not exist
I also tried connecting to a working database to run the command:
backend> REINDEX DATABASE pg_database
ERROR: can only reindex the currently open database
However, I have at least one working database:
postgres -P -D /var/lib/pgsql/data pg_bmap_repo
POSTGRES backend interactive interface
$Revision: 1.375.2.5 $ $Date: 2005/12/14 17:07:00 $
backend> select * from fMRIStudy;
1: _oid (typeid = 23, len = 4, typmod = -1, byval = t)
2: imaging_study (typeid = 23, len = 4, typmod = -1, byval = t)
3: function (typeid = 1043, len = -1, typmod = 24, byval = f)
4: mrseries (typeid = 23, len = 4, typmod = -1, byval = t)
5: description (typeid = 1043, len = -1, typmod = 104, byval = f)
Thanks for any help!
On Thu, Jul 16, 2009 at 12:18 PM, Joshua Daniel Franklin wrote: > I'm having a problem with Postgres 7.4 on RHEL 4 (Nahant Update 8). > It seems that pg_database disappeared some time ago (I think before > my oldest backups, unfortunately). > > I searched and found some mention of REINDEX but I'm not sure if I am > using it correctly since this is a system catalog. I tried this: > > postgres -P -D /usr/local/databases/pgsql/data pg_database > FATAL: database "pg_database" does not exist So... does that 'FATAL: database "pg_database" does not exist' message mean it's game over ? I've got the data so it's not the end of the world, but it would be nice to recover the user accounts if possible.
Joshua Daniel Franklin escribió: > On Thu, Jul 16, 2009 at 12:18 PM, Joshua Daniel Franklin wrote: > > I'm having a problem with Postgres 7.4 on RHEL 4 (Nahant Update 8). > > It seems that pg_database disappeared some time ago (I think before > > my oldest backups, unfortunately). > > > > I searched and found some mention of REINDEX but I'm not sure if I am > > using it correctly since this is a system catalog. I tried this: > > > > postgres -P -D /usr/local/databases/pgsql/data pg_database > > FATAL: database "pg_database" does not exist > > So... does that 'FATAL: database "pg_database" does not exist' message > mean it's game over ? No, it means you didn't pass the correct parameters. You need to pass the database name, not "pg_database". -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On Mon, Jul 20, 2009 at 8:39 AM, Alvaro Herrera wrote: > Joshua Daniel Franklin escribió: >> > postgres -P -D /usr/local/databases/pgsql/data pg_database >> > FATAL: database "pg_database" does not exist >> >> So... does that 'FATAL: database "pg_database" does not exist' message >> mean it's game over ? > > No, it means you didn't pass the correct parameters. You need to pass > the database name, not "pg_database". I don't understand how to do this. I've read the manual at http://www.postgresql.org/docs/7.4/interactive/sql-reindex.html It mentions that "standalone server must be used to repair" system catalogs, but doesn't give any special instructions to do so. I tried REINDEX for various databases and it says it's doing something, but when I try to list the databases or pg_dumpall it says it is empty. However, as I mentioned earlier if I run a specific SELECT on any of my user tables I see the data. $ postgres -P -D /usr/local/databases/pgsql/data template0 backend> reindex database template0; NOTICE: table "pg_class" was reindexed NOTICE: table "pg_constraint" was reindexed NOTICE: table "pg_description" was reindexed NOTICE: table "pg_proc" was reindexed NOTICE: table "pg_rewrite" was reindexed NOTICE: table "pg_statistic" was reindexed NOTICE: table "pg_shadow" was reindexed NOTICE: table "pg_attrdef" was reindexed NOTICE: table "pg_type" was reindexed NOTICE: table "pg_attribute" was reindexed NOTICE: table "pg_index" was reindexed NOTICE: table "pg_operator" was reindexed NOTICE: table "pg_opclass" was reindexed NOTICE: table "pg_am" was reindexed NOTICE: table "pg_amop" was reindexed NOTICE: table "pg_amproc" was reindexed NOTICE: table "pg_language" was reindexed NOTICE: table "pg_aggregate" was reindexed NOTICE: table "pg_trigger" was reindexed NOTICE: table "pg_cast" was reindexed NOTICE: table "pg_namespace" was reindexed NOTICE: table "pg_conversion" was reindexed NOTICE: table "pg_depend" was reindexed NOTICE: table "pg_database" was reindexed NOTICE: table "pg_group" was reindexed NOTICE: table "pg_inherits" was reindexed NOTICE: table "pg_largeobject" was reindexed .... xbrain_queries=# \d No relations found. xbrain_queries=# \l List of databases Name | Owner | Encoding ------+-------+---------- (0 rows)
Joshua Daniel Franklin escribió: > xbrain_queries=# \d > No relations found. > xbrain_queries=# \l > List of databases > Name | Owner | Encoding > ------+-------+---------- > (0 rows) Hmmm, seems your Xid counter wrapped around. Have you vacuumed the system catalogs in that database? Particularly pg_class, pg_attribute etc ... Since you already have a backup, the easiest way out is to forget the borked installation and restore the backup, then ensure this doesn't happen to you ever again by switching to a more modern release that has specific protections against it. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Joshua Daniel Franklin <joshuadfranklin@gmail.com> writes:
> NOTICE: table "pg_database" was reindexed
So you did the reindex ...
> xbrain_queries=# \l
> List of databases
> Name | Owner | Encoding
> ------+-------+----------
> (0 rows)
... and it didn't help. I think this shows that the indexes weren't the
problem. More likely this is a transaction wraparound issue.
Have you tried "VACUUM pg_database"? Sometimes that can get you out
of wraparound problems.
regards, tom lane
On Mon, Jul 20, 2009 at 11:00 AM, Alvaro Herrera wrote: > Joshua Daniel Franklin escribió: > >> xbrain_queries=# \d >> No relations found. > > Hmmm, seems your Xid counter wrapped around. Have you vacuumed the > system catalogs in that database? Particularly pg_class, pg_attribute > etc ... > > Since you already have a backup, the easiest way out is to forget the > borked installation and restore the backup, then ensure this doesn't > happen to you ever again by switching to a more modern release that has > specific protections against it. That worked! Thanks Alvaro (and Tom). I will definitely take a look at migrating this machine to a newer release. The problem is probably my VACUUM procedure failing somewhere, but I would never have guessed that one my own. You probably already know this, but this mailing list is such a huge community asset due to the excellent answers you all provide. Thanks again, Joshua