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, 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 the “RENAME TO” clause from that particular database to rename the targeted database.

This blog post will explain how to rename a database in PostgreSQL using the following context:

1. How to Rename Databases in PostgreSQL Using psql?
2. How to Rename Databases in PostgreSQL Using pgAdmin?

So, let’s start.

How to Rename Databases in PostgreSQL Using psql

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

• 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 the RENAME TO clause to rename the database.

Syntax:

To rename a database in PostgreSQL, specify an ALTER DATABASE command, followed by the database's old name, after that specify the RENAME TO clause followed by the database's 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 Name

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”.

How to Rename a Database in PostgreSQL Using pgAdmin

If you are a GUI lover and want to rename a database without executing any query, then opt for the pgAdmin(GUI-based development platform for Postgres). Using pgAdmin you can rename a database either by using GUI (manually) or by executing queries (using its query tool).

To rename a Postgres database manually using pgAdmin walk through the following steps:

Step 1: Select the Database to Rename

Open the pgAdmin, and navigate to the "Object Explorer" Pane > expand the "Servers" tree > click the "Databases" option to expand it > select the database to rename and right-click on it, as follows:

Click on the "Properties..." option to open the database properties.

Step 2: Rename the Database

In the properties windows, you can modify database attributes/properties like name, owner, default privileges, tablespace, etc. To rename a database, stay on the "General" tab, navigate to the "Database" field, and rename it to any valid name of your choice:

Hit the "Save" button to update the database name.

Step 3: Rename the Database

Now navigate back to the "Databases" section in the left pane, and you will witness that the selected database has been renamed successfully:

That's all about renaming a database in Postgres using psql and pgAdmin.

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 renaming a PostgreSQL database.