PostgreSQL Rename Database

Summary: in this tutorial, you will learn step by step how to rename a PostgreSQL database using the ALTER DATABASE RENAME TO statement.

PostgreSQL rename database steps

To rename a PostgreSQL database, you use the following steps:

1. Disconnect from the database that you want to rename and connect to a different database.
2. Check and terminate all active connections to the database that you want to rename.
3. Use the ALTER DATABASE statement to rename the database to the new one.

Let’s take a look at an example of renaming a database.

The following statement creates a new database called db:

CREATE DATABASE db;
Code language: SQL (Structured Query Language) (sql)

To rename the db database to newdb, you follow these steps:

First, disconnect from the database that you want to rename and connect to another database e.g., postgres. If you use psql tool, you can use the following command to connect to the postgres database:

db=# \connect postgres
Code language: SQL (Structured Query Language) (sql)

By connecting to the postgres database, you are automatically disconnected from the db database.

Next, check the all active connections to the db database by using the following query:

SELECT  *
FROM pg_stat_activity
WHERE datname = 'db';
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returned the following output:

-[ RECORD 1 ]----+------------------------------
datid            | 35918
datname          | db
pid              | 6904
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | ::1
client_hostname  |
client_port      | 56412
backend_start    | 2017-02-21 08:25:05.083705+07
xact_start       |
query_start      |
state_change     | 2017-02-21 08:25:05.092168+07
waiting          | f
state            | idle
backend_xid      |
backend_xmin     |
query            |
Code language: Shell Session (shell)

As you can see clearly from the output, there is only one connection to the db database.

You may find that the database, which you want to rename, has many active connections. In this case, you need to inform the respective users as well as the application owners before terminating the connections to avoid data loss.

Then, terminate all the connections to the db database by using the following statement:

SELECT
    pg_terminate_backend (pid)
FROM
    pg_stat_activity
WHERE
    datname = 'db';
Code language: SQL (Structured Query Language) (sql)

After that, rename the db database to newdb using the ALTER DATABASE RENAME TO statement as follows:

ALTER DATABASE db RENAME TO newdb;
Code language: SQL (Structured Query Language) (sql)

Last but not least, if your database is being used by applications, you should modify the connection strings.

In this tutorial, you have learned how to rename a PostgreSQL database to a new one by using the ALTER DATABASE RENAME TO statement.