Problem:
You've created a database but made an embarrassing typo in the name or for whatever reason you don't like it. How do you rename this database?
Solution:
If you are using PgAdmin III, you will not see this option. Just one of the ways PgAdmin III lets us down. However there is a simple way of doing it with a PostgreSQL command which has been in existence even in the 7.4 days of PostgreSQL which is documented in PostgreSQL official docs on ALTER DATABASE. In order to do it, you need to first make sure everyone is out of the database (including yourself) otherwise you'll get an annoying database is being accessed by other users or current database may not be renamed error.
- Connect to some other database other than the one you are trying to rename such as say the postgres db.
- Kick everyone out of the database you are trying to rename - to figure out users, you can run
SELECT *
FROM pg_stat_activity
WHERE datname = 'myolddbname_goes_here'
- Now just run this command -
ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here