Thread: Rename database?

I have looked (briefly) through the general, sql, and hackers archives,and
could not find anything the addressed the ability to rename a database.
Most of the posts resorted to using pg_dump to rebuild the database under a
new name.

I appreciate that (AFAIK) SQL does not have 'alter schema rename', but it
is something I would find very useful: I have a web site that is 99.9%
read-only, where bulk updates are sent periodically and can take a lot of
time to run, so what I do is apply the changes (or rebuild the db) under
another name. After this is done, I would like to rename the current DB,
and put the new one in it's place. Currently this is fine with the 100,000
or so records it contains, but the projections are for 5M+ records within
four years.

I strongly suspect that renaming the directory will not work, and besides,
I have a strong aversion to making structural changes to files that are
logically part of the database.

So the questions are: 

1) is is there a place for a pg_rename utility? 

2) would it be a difficult thing to write?

3) is this better handled by psql.

I suspect the answer to (3) is 'NO', since moving databases around seems to
be something best done without a backend attached.

Any and all (polite) comments welcome.

P.S. Before anybody suggests it, I currently build the new db elsewhere,
then do a pg_dump, delete the current DB, and load from the dump file. This
is fine for the relatively small amount of data currently in the db.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: +61-03-5367 7422            |                 _________  \
Fax: +61-03-5367 7430            |                 ___________ |
Http://www.rhyme.com.au          |                /           \|                                |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Philip Warner <pjw@rhyme.com.au> writes:
> I have looked (briefly) through the general, sql, and hackers archives,and
> could not find anything the addressed the ability to rename a database.

Nope, we haven't got it.

As long as there are no backends running in the DB, I think it'd just
be a matter of renaming the subdirectory of data/base/ and updating the
pg_database row with the new name.  You could do that manually if you
are comfortable with assuming that no one is connected to the DB while
you do it.

> 1) is is there a place for a pg_rename utility? 

It could not be a standalone utility because it'd have no way to
interlock against running backends.  It'd have to be implemented as
an SQL statement and use the same interlock method that DROP DATABASE
does.

> 2) would it be a difficult thing to write?

Probably not too tough if you used DROP DATABASE as a model.

Bear in mind though that the whole issue might go away, depending on
what happens with the tablespace/schema/physical-file-name-conventions
discussions.  Might want to see how that plays out before expending much
work on it.
        regards, tom lane