Effortlessly Rename Your PostgreSQL Database in 3 Steps

• Basic understanding of SQL syntax.
• Access to a PostgreSQL server and a user with appropriate permissions (database owner or superuser).

Steps:

1. psql -h <host> -U <username> -d <other_database>
   

2. Terminate active connections: Ensure no other users are currently connected to the database you want to rename. You can use the following command to list active connections and terminate them if necessary:

   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE datname = '<old_database_name>';
   

3. Rename the database: Use the ALTER DATABASE statement with the RENAME TO clause to specify the new name:

   ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;
   

Example:

Let's rename a database named my_old_database to my_new_database:

1. Connect to a different database (e.g., postgres):

   psql -h localhost -U postgres
   

2. Terminate any active connections to my_old_database:

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

3. Rename the database:

   ALTER DATABASE my_old_database RENAME TO my_new_database;
   

Related Issues and Solutions: