Re: Re-nameing a database?

On Mon, 2002-02-11 at 15:50, Naomi Walker wrote:
> Not in postgres, unfortunately. I'd love to be wrong.
>
> At 04:26 PM 2/10/02 -0600, Chris Ruprecht wrote:
>
> >Hi all,
> >
> >is there an easy way to re-name a database?

In 7.1.3, you can do it by updating the row in pg_database and then
restarting the postmaster:

$ psql template1
...

template1=# \l
List of databases
Database | Owner | Encoding
-----------+----------+-----------
junk | olly | SQL_ASCII
...
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(8 rows)

template1=# update pg_database set datname = 'rubbish' where datname =
'junk';
UPDATE 1
template1=# \l
List of databases
Database | Owner | Encoding
-----------+----------+-----------
rubbish | olly | SQL_ASCII
...
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(8 rows)

template1=# \c rubbish
FATAL 1: Database "rubbish" does not exist in the system catalog.
Previous connection kept
template1=# \c junk
FATAL 1: Database "junk", OID 44062648, has disappeared from
pg_database
Previous connection kept
template1=# \q
$ sudo /etc/init.d/postgresql restart
Password:
Restarting PostgreSQL database: postmaster
Stopped /usr/lib/postgresql/bin/postmaster (pid 15397).
Starting PostgreSQL postmaster.
postmaster successfully started

$ psql template1
...
template1=# \c rubbish
You are now connected to database rubbish.
rubbish=# \c junk
FATAL 1: Database "junk" does not exist in the system catalog.
Previous connection kept
rubbish=#

An alternative method would be to dump everything with pg_dumpall and
then edit the dump to change the database name.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C