Is renaming a database easy or dangerous
Hello,
I'd like to rename one of my databases. Is it is simple as changing
the datname field in the pg_databases table?
If so, is it done through ALTER database or a SQL update?
Thank You.
--
Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328
hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.
Hadley Willan <hadley.willan@deeperdesign.co.nz> writes:
I'd like to rename one of my databases. Is it is simple as changing
the datname field in the pg_databases table?
If so, is it done through ALTER database or a SQL update?
There is no ALTER DATABASE command for this. I believe that since 7.1
you can get away with an UPDATE on the pg_database row, with a couple of
caveats:
* It's unwise to do this while there are any backends connected to the
target database. I'm not sure whether they'd get seriously confused
because their database changed name, but you don't want to find out the
hard way.
* You will have to issue a CHECKPOINT (or wait for one to occur
automatically) before the change will be visible to incoming new
connections. A "vacuum pg_database" command before checkpointing might
be helpful as well, though I think it's probably not necessary in most
cases.
regards, tom lane
On 27 Feb 2003, Hadley Willan wrote:
Hello,
I'd like to rename one of my databases. Is it is simple as changing
the datname field in the pg_databases table?If so, is it done through ALTER database or a SQL update?
The way I do it is:
# createdb newdb
# pg_dump olddb|psql -e newdb
... check to make sure it's all there working, then...
# dropdb olddb
scott.marlowe wrote:
On 27 Feb 2003, Hadley Willan wrote:
Hello,
I'd like to rename one of my databases. Is it is simple as changing
the datname field in the pg_databases table?If so, is it done through ALTER database or a SQL update?
The way I do it is:
# createdb newdb
# pg_dump olddb|psql -e newdb
... check to make sure it's all there working, then...
# dropdb olddb
The coolest way I have seen it done is to do CREATE DATABASE with
TEMPLATE old_dbname, then drop the old database.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073