Can't rename an existnig DB because it doesn't exist???

On Wed, Mar 12, 2008 at 10:03 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:

V8.2.0 on Linux

Look into updating, there were some serious bugs fixed between 8.2.0
and 8.2.6 It's a pretty simple thing, since you don't need to dump /
reload for it.

Can't rename a db, complains that it doesn't exist. Yet psql –l shows that
it does and I can connect to it ???

mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to stdb_tmp"

ERROR: database "stdb" does not exist

mmdcc228_SETUP(121)% psql -l

List of databases

Name | Owner | Encoding

--------------+----------+----------
stdb | dfgauthi | UTF8

stdb2 | dfgauthi | UTF8

stdb_standby | dfgauthi | UTF8

Very strange. maybe the name has a space in it?

Try running this query:
select '|'||datname||'|' from pg_database ;
and see if you have a space or something in there. can you run the
alter database rename from the command line and just not from psql?

You might want to keep this one on the list. I'm out of my league
with this one I think

On Wed, Mar 12, 2008 at 11:24 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:

On Wed, 12 Mar 2008 21:30:57 -0700
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:

You might want to keep this one on the list. I'm out of my league
with this one I think

Can you pg_dump the database? What about vacuum? What does:

SELECT * FROM pg_database; Return?

J

On Wed, Mar 12, 2008 at 11:24 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:

stdb2=# select '|'||datname||'|' from pg_database ;
?column?
----------------
|postgres|
|template1|
|template0|
|cells_dev|
|stdb2|
|stdb|
|stdb_standby|
|cells|
(8 rows)

-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: Wednesday, March 12, 2008 1:22 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Can't rename an existnig DB because it
doesn't exist???

On Wed, Mar 12, 2008 at 10:03 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:

V8.2.0 on Linux

Look into updating, there were some serious bugs fixed between
8.2.0 and 8.2.6 It's a pretty simple thing, since you don't need
to dump / reload for it.

Can't rename a db, complains that it doesn't exist. Yet psql -l
shows

that

it does and I can connect to it ???

mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to

stdb_tmp"

ERROR: database "stdb" does not exist

mmdcc228_SETUP(121)% psql -l

List of databases

Name | Owner | Encoding

--------------+----------+----------
stdb | dfgauthi | UTF8

stdb2 | dfgauthi | UTF8

stdb_standby | dfgauthi | UTF8

Very strange. maybe the name has a space in it?

Try running this query:
select '|'||datname||'|' from pg_database ;
and see if you have a space or something in there. can you run the
alter database rename from the command line and just not from psql?

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit

On 2008-03-12 21:30, Scott Marlowe wrote:

...

Can't rename a db, complains that it doesn't exist. Yet psql -l shows that it does and I can connect to it ???

mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to

stdb_tmp"

ERROR: database "stdb" does not exist
mmdcc228_SETUP(121)% psql -l

List of databases

Name | Owner | Encoding
--------------+----------+----------
stdb | dfgauthi | UTF8
stdb2 | dfgauthi | UTF8
stdb_standby | dfgauthi | UTF8

Very strange. maybe the name has a space in it?

Thoughts:

1. What SCHEMAs are these DBs in? Perhaps a search_path issue (I
haven't followed all of this thread, so perhaps this has been mentioned).

2. What are the current Linux command-line encoding (value of $LANG) and
the psql client_encoding? Perhaps there is a non-displayable character
in the name.

3. You can probably delete it by:
a. Creating a new schema "zzz".
b. Moving everything else in the schema where stdb is, to the new schema.
c. Drop the existing schema (cascade).
d. Rename schema "zzz" to the name of the previous schema.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

No schemas per-se. After the initdb, I jump right in with "createdb
foo", "createdb foo2", etc... .

I don't think it's an encoding propbem. The problem popped up after
running script that ran successfully for many months. Somethign changed
(as it were).

Ya, I'm thinking of dumping all the problem DBs, deleting them,
recreating and reloading.

-dave

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dean Gibson (DB
Administrator)
Sent: Thursday, March 13, 2008 12:45 PM
To: pgsql-general
Subject: Re: [GENERAL] Can't rename an existnig DB because it doesn't
exist???

On 2008-03-12 21:30, Scott Marlowe wrote:

...

Can't rename a db, complains that it doesn't exist. Yet psql -l

shows that it does and I can connect to it ???

mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to

stdb_tmp"

ERROR: database "stdb" does not exist
mmdcc228_SETUP(121)% psql -l

List of databases

Name | Owner | Encoding
--------------+----------+----------
stdb | dfgauthi | UTF8
stdb2 | dfgauthi | UTF8
stdb_standby | dfgauthi | UTF8

Very strange. maybe the name has a space in it?

Thoughts:

1. What SCHEMAs are these DBs in? Perhaps a search_path issue (I
haven't followed all of this thread, so perhaps this has been
mentioned).

2. What are the current Linux command-line encoding (value of $LANG) and

the psql client_encoding? Perhaps there is a non-displayable character
in the name.

3. You can probably delete it by:
a. Creating a new schema "zzz".
b. Moving everything else in the schema where stdb is, to the new
schema.
c. Drop the existing schema (cascade).
d. Rename schema "zzz" to the name of the previous schema.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Gauthier, Dave" <dave.gauthier@intel.com> writes:

Can't rename a db, complains that it doesn't exist. Yet psql -l
shows that it does and I can connect to it ???

mmdcc228_SETUP(120)% psql stdb2 -c "alter database stdb rename to
stdb_tmp"

ERROR: database "stdb" does not exist

Hmm, I wonder if this could be a corrupt-index problem. If you
try "select * from pg_database where datname = 'stdb'", do you
get a row? Does "REINDEX pg_database" help?

regards, tom lane

On 2008-03-13 10:10, Gauthier, Dave wrote:

Ya, I'm thinking of dumping all the problem DBs, deleting them, recreating and reloading.

Last thought: have you tried uninstalling and reinstalling PostgreSQL?
If something is corrupted on the disk, it's either the data or the
software. An uninstall/reinstall is cheap to do, and probably takes
less time that the dump/restores.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.

On Thu, Mar 13, 2008 at 10:45 AM, Dean Gibson (DB Administrator) >

1. What SCHEMAs are these DBs in? Perhaps a search_path issue (I
haven't followed all of this thread, so perhaps this has been mentioned).

Tis the other way round I'm afriad. Schemas live in dbs, not the
other way around. Maybe you were thinking tablespaces?

On 2008-03-13 23:14, Scott Marlowe wrote:

Tis the other way round I'm afriad. Schemas live in dbs, not the other way around. Maybe you were thinking tablespaces?

You're right; I was thinking of tables, which I routinely move around
from schema to schema.

That also means he should ignore my suggestion about moving stuff around.

--
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.