Thread: Can't rename an existnig DB because it doesn't exist???
V8.2.0 on Linux
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
--------------+----------+----------
cells | dfgauthi | UTF8
cells_dev | dfgauthi | UTF8
postgres | dfgauthi | UTF8
stdb | dfgauthi | UTF8
stdb2 | dfgauthi | UTF8
stdb_standby | dfgauthi | UTF8
template0 | dfgauthi | UTF8
template1 | dfgauthi | UTF8
(8 rows)
mmdcc228_SETUP(122)% psql stdb
Welcome to psql 8.2.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
stdb=#
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: > 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? >
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
Attachment
Re: Can't rename an existnig DB because it doesn't exist???
From
"Dean Gibson (DB Administrator)"
Date:
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
Re: Can't rename an existnig DB because it doesn't exist???
From
"Dean Gibson (DB Administrator)"
Date:
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?
Re: Can't rename an existnig DB because it doesn't exist???
From
"Dean Gibson (DB Administrator)"
Date:
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.