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.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C