To rename a database in Postgres, use the ALTER DATABASE statement with the RENAME TO clause. In Postgres, the current database cannot be renamed. So, in order to rename a database, firstly, you must establish a connection with any other database.

Once the connection is established, you can run the “ALTER DATABASE” command with the collaboration of “RENAME TO” clause from that particular database to rename the targeted database.

This blog post explained how to rename a database in PostgreSQL through practical examples. So, let’s start.

How to Rename Databases in PostgreSQL?

Following are the prerequisite steps that must be performed in order to rename a database in Postgres:

• Firstly, terminate/disconnect the database that you want to rename.
• Establish a connection with any other database.
• Close all the connections that are associated with the targeted database.
• Finally, run the ALTER DATABASE statement with the collaboration of RENAME TO clause to rename the database.

Syntax:

To rename a database in PostgreSQL, specify an ALTER DATABASE command, followed by the database old name, after that specify the RENAME TO clause followed by the database new name:

ALTER DATABASE old_dbname RENAME TO new_dbname;

Let’s implement it practically.

Example: Renaming a Database in Postgres

This example will provide stepwise instructions to rename a database in PostgreSQL:

Step 1: Create a New Database

Firstly, create a new database named “example_db” using the following command:

CREATE DATABASE example_db;

The “example_db” database is created successfully.

Step 2: Make a Connection With Database

Connect to any database other than the database to be renamed:

\c commandprompt;

The connection is successfully established with the “commandprompt” database.

Step 3: Check Active Connections

Execute the below statement to check the active connections:

SELECT * FROM pg_stat_activity WHERE datname = ‘example_db’;

On successful execution, you will get the list of active connections.

Step 4: Terminate Active Connections

Run the following statement to sack/close all the active connections:

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

Step 5: Rename Database

Once the active connections are terminated, now, you can rename a database using the ALTER TABLE command as follows:

ALTER DATABASE example_db RENAME TO modified_db;

The output snippet verifies that the selected database has been modified/renamed successfully.

Step 6: Verify Database

Let’s verify that whether the selected database has been renamed or not:

\l;

The output snippet proves that the “example_db” database has been renamed to “modified_db”.

Conclusion

To rename a database in Postgres, use the ALTER DATABASE statement with the RENAME TO clause. To do so, specify an ALTER DATABASE command, followed by the database’s old name, and after that, specify the RENAME TO clause followed by the database’s new/modified name. This blog post demonstrates a thorough guide on how to rename a database in PostgreSQL.