Thread: renaming a db

I have a feeling I can't do it, but is there a way to rename a dB?

I am doing major scheme alterations and recreating the DB from the data
origionally used as well as some data from the origional DB....I would
like to be able to just plug it in without having to alter the dbname in
all of the scripts that use it....I know I can dump the db, drop them
both and recreate...but renaming would be easier :P

just dump the databse, create a new one and insert the info back into a
new one. you might even be able to string the commands together with
pipes.

--
john
There is no operating system but linux and linus is its kernel maintainer.

Sometime in August jik@foxinternet.net assaulted keyboard and produced...

|I have a feeling I can't do it, but is there a way to rename a dB?
|
|I am doing major scheme alterations and recreating the DB from the data
|origionally used as well as some data from the origional DB....I would
|like to be able to just plug it in without having to alter the dbname in
|all of the scripts that use it....I know I can dump the db, drop them
|both and recreate...but renaming would be easier :P
|
|---------------------------(end of broadcast)---------------------------
|TIP 2: you can get off all lists at once with the unregister command
|    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
|

jik@foxinternet.net writes:
> I have a feeling I can't do it, but is there a way to rename a dB?

In 7.1 or later I think it would work to just UPDATE the db's row in
pg_database.  But don't blame me if this fails to work ... make a
backup before you try it.

            regards, tom lane

Tom Lane wrote:
>
> jik@foxinternet.net writes:
> > I have a feeling I can't do it, but is there a way to rename a dB?
>
> In 7.1 or later I think it would work to just UPDATE the db's row in
> pg_database.  But don't blame me if this fails to work ... make a
> backup before you try it.
>
>                         regards, tom lane

Doesn't look like that works....you ruined my database, I bet they fire
me :P

jik-=# UPDATE pg_database SET datname='test34' WHERE datname='test2';
UPDATE 1
jik-=# \c test34
FATAL 1:  Database "test34" does not exist in the system catalog.
Previous connection kept

jik@foxinternet.net writes:
> Tom Lane wrote:
>>> I have a feeling I can't do it, but is there a way to rename a dB?
>>
>> In 7.1 or later I think it would work to just UPDATE the db's row in
>> pg_database.  But don't blame me if this fails to work ... make a
>> backup before you try it.

> Doesn't look like that works....you ruined my database, I bet they fire
> me :P

> jik-=# UPDATE pg_database SET datname='test34' WHERE datname='test2';
> UPDATE 1
> jik-=# \c test34
> FATAL 1:  Database "test34" does not exist in the system catalog.
> Previous connection kept

I find that it starts working after you do a CHECKPOINT ... or wait for
one to happen automatically.  (A would-be backend won't see the
pg_database change until the table update is flushed to disk.)

foo=# update pg_database set datname = 'bar' where datname='foo';
UPDATE 1
foo=# checkpoint;
CHECKPOINT
-- now I can connect to 'bar'

            regards, tom lane