Catch-22

Started by Gauthier, Daveabout 18 years ago7 messagesgeneral

dave.gauthier@intel.com

about 18 years ago

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

Tom Lane

tgl@sss.pgh.pa.us

about 18 years ago

In reply to: Gauthier, Dave (#1)

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

about 18 years ago

In reply to: Gauthier, Dave (#1)

Re: Catch-22

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

Erik Jones

erik@myemma.com

about 18 years ago

In reply to: Gauthier, Dave (#3)

Re: 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®
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

about 18 years ago

In reply to: Gauthier, Dave (#1)

Re: Catch-22

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

hubert depesz lubaczewski

depesz@depesz.com

about 18 years ago

In reply to: Gauthier, Dave (#3)

Re: Catch-22

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)

Tom Lane

tgl@sss.pgh.pa.us

about 18 years ago

In reply to: Gauthier, Dave (#5)

Re: Catch-22

"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