Thread: 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