PostgreSQL – Rename Database

In PostgreSQL, the ALTER DATABASE RENAME TO statement is used to rename a database. The below steps need to be followed while renaming a database: 

1. Disconnect from the database that you want to rename by connecting to a different database.
2. Terminate all connections, connected to the database to be renamed.
3. Now you can use the ALTER DATABASE statement to rename the database. 

Now let’s look into the below example to see how to rename a database in PostgreSQL. 

Example:

• Step 1: Create a database named “test_db” using the below commands:

CREATE DATABASE test_db;

• Step 2: Now to rename the “test_db” database, disconnect from that database using the below command and connect to the Postgres database: 

test_db=# \connect postgres;

• Step 3: Use the below query to check all active connections to the “test_db” database: 

SELECT
    *
FROM
    pg_stat_activity
WHERE
    datname = 'test_db';

• Step 4: Use the below query to terminate all the connections to the test_db database: 

SELECT
    pg_terminate_backend (pid)
FROM
    pg_stat_activity
WHERE
    datname = 'test_db';

• Step 5: Now use the ALTER DATABASE RENAME TO statement to rename the database as “new_test_db”(say) as follows: 

ALTER DATABASE test_db RENAME TO new_test_db;