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:
- Disconnect from the database that you want to rename by connecting to a different database.
- Terminate all connections, connected to the database to be renamed.
- 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.
- 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;