Обсуждение: Catch-22
Hi:
After running intoa problem last week where I cannot rename an existing DB because it’s reported to not exist (I can attach to it and query OK, but rename gives... “database foo does not exist”), I’ve done a full dump of the DB and want to delete/recreate it with the backup. But I can’t “dropdb” because “database foo does not exist”.
What’s the course I should take at this point? I could “rm –r” the DB root (the piece after the “-D” in pg_ctl commands), but that may screw things up even worse.
Again, I have a backup and want to delete the DB, but can’t because the “dropdb” tool says the DB does not exist.
v8.2.0 on Linux (and yes, I’ve requested an upgrade to 8.2.6)
-dave
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> After running intoa problem last week where I cannot rename an existing
> DB because it's reported to not exist (I can attach to it and query OK,
> but rename gives... "database foo does not exist"),
Would you show the results of
select oid,ctid,xmin,xmax,datname from pg_database
as well as the contents of $PGDATA/global/pg_database and a directory
listing of $PGDATA/base?
regards, tom lane
stdb=# select oid,ctid,xmin,xmax,datname from pg_database
stdb-# ;
oid | ctid | xmin | xmax | datname
--------+--------+---------+------+--------------
10819 | (0,1) | 592 | 0 | postgres
1 | (0,6) | 585 | 0 | template1
10818 | (0,7) | 586 | 0 | template0
823888 | (0,13) | 761678 | 0 | cells_dev
19810 | (0,49) | 497579 | 0 | stdb2
597974 | (3,2) | 2346578 | 0 | stdb
19882 | (3,3) | 2346580 | 0 | stdb_standby
16384 | (3,4) | 2364457 | 0 | cells
(8 rows)
mmdcc228> dropdb stdb
ERROR: database "stdb" does not exist
STATEMENT: DROP DATABASE stdb;
dropdb: database removal failed: ERROR: database "stdb" does not exist
mmdcc228>
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, March 17, 2008 11:33 AM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Catch-22
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> After running intoa problem last week where I cannot rename an
existing
> DB because it's reported to not exist (I can attach to it and query
OK,
> but rename gives... "database foo does not exist"),
Would you show the results of
select oid,ctid,xmin,xmax,datname from pg_database
as well as the contents of $PGDATA/global/pg_database and a directory
listing of $PGDATA/base?
regards, tom lane
> "Gauthier, Dave" <dave.gauthier@intel.com> writes: >> After running intoa problem last week where I cannot rename an > existing >> DB because it's reported to not exist (I can attach to it and query > OK, >> but rename gives... "database foo does not exist"), > > Would you show the results of > > select oid,ctid,xmin,xmax,datname from pg_database > > as well as the contents of $PGDATA/global/pg_database and a directory > listing of $PGDATA/base? > > regards, tom lane On Mar 17, 2008, at 10:45 AM, Gauthier, Dave wrote: > stdb=# select oid,ctid,xmin,xmax,datname from pg_database > stdb-# ; > oid | ctid | xmin | xmax | datname > --------+--------+---------+------+-------------- > 10819 | (0,1) | 592 | 0 | postgres > 1 | (0,6) | 585 | 0 | template1 > 10818 | (0,7) | 586 | 0 | template0 > 823888 | (0,13) | 761678 | 0 | cells_dev > 19810 | (0,49) | 497579 | 0 | stdb2 > 597974 | (3,2) | 2346578 | 0 | stdb > 19882 | (3,3) | 2346580 | 0 | stdb_standby > 16384 | (3,4) | 2364457 | 0 | cells > (8 rows) > > mmdcc228> dropdb stdb > ERROR: database "stdb" does not exist > STATEMENT: DROP DATABASE stdb; > > dropdb: database removal failed: ERROR: database "stdb" does not > exist > mmdcc228> You left out the directory listings Tom asked for. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
On Mon, Mar 17, 2008 at 08:45:59AM -0700, Gauthier, Dave wrote: > stdb=# select oid,ctid,xmin,xmax,datname from pg_database > stdb-# ; > oid | ctid | xmin | xmax | datname > --------+--------+---------+------+-------------- > 10819 | (0,1) | 592 | 0 | postgres > 1 | (0,6) | 585 | 0 | template1 > 10818 | (0,7) | 586 | 0 | template0 > 823888 | (0,13) | 761678 | 0 | cells_dev > 19810 | (0,49) | 497579 | 0 | stdb2 > 597974 | (3,2) | 2346578 | 0 | stdb > 19882 | (3,3) | 2346580 | 0 | stdb_standby > 16384 | (3,4) | 2364457 | 0 | cells > (8 rows) > > mmdcc228> dropdb stdb > ERROR: database "stdb" does not exist > STATEMENT: DROP DATABASE stdb; > dropdb: database removal failed: ERROR: database "stdb" does not exist > mmdcc228> try this: while connected to stdb (in psql): \connect template1 drop database stdb; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
Woops, sorry.... mmdcc228> more global/pg_database "postgres" 10819 1663 524 "template1" 1 1663 524 "template0" 10818 1663 524 "cells_dev" 823888 1663 524 "stdb2" 19810 1663 524 "stdb" 597974 1663 524 "stdb_standby" 19882 1663 524 "cells" 16384 1663 524 mmdcc228> ls base/ 1 10818 10819 16384 16460 19810 19882 597974 823888 -----Original Message----- From: Erik Jones [mailto:erik@myemma.com] Sent: Monday, March 17, 2008 12:20 PM To: Gauthier, Dave Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] Catch-22 > "Gauthier, Dave" <dave.gauthier@intel.com> writes: >> After running intoa problem last week where I cannot rename an > existing >> DB because it's reported to not exist (I can attach to it and query > OK, >> but rename gives... "database foo does not exist"), > > Would you show the results of > > select oid,ctid,xmin,xmax,datname from pg_database > > as well as the contents of $PGDATA/global/pg_database and a directory > listing of $PGDATA/base? > > regards, tom lane On Mar 17, 2008, at 10:45 AM, Gauthier, Dave wrote: > stdb=# select oid,ctid,xmin,xmax,datname from pg_database > stdb-# ; > oid | ctid | xmin | xmax | datname > --------+--------+---------+------+-------------- > 10819 | (0,1) | 592 | 0 | postgres > 1 | (0,6) | 585 | 0 | template1 > 10818 | (0,7) | 586 | 0 | template0 > 823888 | (0,13) | 761678 | 0 | cells_dev > 19810 | (0,49) | 497579 | 0 | stdb2 > 597974 | (3,2) | 2346578 | 0 | stdb > 19882 | (3,3) | 2346580 | 0 | stdb_standby > 16384 | (3,4) | 2364457 | 0 | cells > (8 rows) > > mmdcc228> dropdb stdb > ERROR: database "stdb" does not exist > STATEMENT: DROP DATABASE stdb; > > dropdb: database removal failed: ERROR: database "stdb" does not > exist > mmdcc228> You left out the directory listings Tom asked for. Erik Jones DBA | Emma(r) erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> Woops, sorry....
> mmdcc228> more global/pg_database
> "postgres" 10819 1663 524
> "template1" 1 1663 524
> "template0" 10818 1663 524
> "cells_dev" 823888 1663 524
> "stdb2" 19810 1663 524
> "stdb" 597974 1663 524
> "stdb_standby" 19882 1663 524
> "cells" 16384 1663 524
> mmdcc228> ls base/
> 1 10818 10819 16384 16460 19810 19882 597974 823888
Huh. That matches up with the OID shown in pg_database, so it's
sure not clear what the problem is.
Could you grab a copy of the appropriate version of pg_filedump from
http://sources.redhat.com/rhdb/
and dump out pg_database with it? The best results would be from
pg_filedump -i -f $PGDATA/global/1262
regards, tom lane