Renaming database in PostgreSQL#
Sometimes you have a system with legacy naming standards, but you really want to switch over to the new standard to keep all the scripting clean without some exceptions no one is going to remember in 12 months. Oracle had the command ALTER DATABASE, but since Oracle 10 you need to take the database offline and do some magic. MySQL got the RENAME DATABASE option with release 5.1.7 and lost the option again with release 5.1.23 as it was eating data.
Luckily PostgreSQL still has the command ALTER DATABASE so let’s rename a database and the owner. Before we start we need the password and then we need to log in as the PostgreSQL superuser postgres or another account with similar privileges. So first we check the database name and owner.
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
--------------+----------+-----------+-------------+-------------+-----------------------
dbu0001 | dbu0001 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Now we rename the database owner as we made a typo and we need to set the password again.
postgres=# alter user dbu0001 rename to dbo0001;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
postgres=# alter user dbo0001 password 'yeaxaureiraeLohsh6deJ2ohngahpu9a';
ALTER ROLE
The second task is to rename the database to the correct name.
postgres=# alter database dbu0001 rename to dbs0001;
ALTER DATABASE
And basically, we are now done as the ownership was already modified when we renamed the account with our first statement, but let’s check what PostgreSQL says it now has.
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
--------------+----------+-----------+-------------+-------------+-----------------------
dbs0001 | dbo0001 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Warning
Don’t forget to update the connection strings for applications using this database and maybe GRANTS that have been set and the pg_hba.conf file.