PostgreSQL Rename Databases

This article describes the detailed steps to rename a database in PostgreSQL.

PostgreSQL allows you to use the ALTER DATABASE ... RENAME statement to rename a database.

Notes on Renaming Databases

Renaming a database is a simple action, but it can cause some other problems. If you do not synchronously modify code that uses this database, they may not work correctly.

When you plan to rename a database, you need to evaluate it as a whole first, and then decide whether to rename the table. Once you’ve decided to rename a database, you need to organize the changes that need to be synchronized.

PostgreSQL Rename Databases syntax

To rename a database, use the ALTER DATABASE ... RENAME statement:

ALTER DATABASE name RENAME TO new_name;

Explanation:

  • The name is the name of the database to be renamed.
  • The new_name is the new name of the database.

You cannot rename a database that still has active connections, or PostgreSQL will give an error.

  • PostgreSQL will give the following error if you try to rename the database you are currently connectting to:

    ERROR:  current database cannot be renamed。

  • If there are still active connections to the database you are renaming, PostgreSQL will give the following error:

    ERROR:  database "testdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

PostgreSQL Rename Databases Examples

In this example, we will use the psql tool to connect to the PostgreSQL server, create a database named test_db and rename it to test_new_db.

  1. First, log in to the PostgreSQL server as a user with privileges:

    [~] psql -U postgres
psql (14.4)
Type "help" for help.

  2. Second, create the test_db database using the following statement:

    CREATE DATABASE test_db;

    CREATE DATABASE

    If you enter an already existing database name, PostgreSQL will return an error: ERROR: database "test_db" already exists.

  3. Then, connect to the test_db database you just created with the \c command:

    \c test_db;

    You are now connected to database "test_db" as user "postgres".

  4. Then, try renaming the test_db database to test_new_db:

    ALTER DATABASE test_db RENAME TO test_new_db;

    ERROR:  current database cannot be renamed

    That means you cannot rename a currently open database.

  5. Keeping the previous session, open a new session and log in, then try renaming the test_db database:

    ALTER DATABASE test_db RENAME TO test_new_db;

    ERROR:  database "test_db" is being accessed by other users
DETAIL:  There is 1 other session using the database.

    Here, PostgreSQL reports an error: The database test_db is being accessed by another user.

  6. Close the previous session, and come back to this session and try renaming again:

    ALTER DATABASE test_db RENAME TO test_new_db;

    DROP DATABASE

    At this point, the test_db database has been renamed to test_new_db.

Show and terminate active connections to the database

Sometimes, it is not yourself to connect to the database to rename. You can query active connections to the database from the pg_stat_activity view as follows:

SELECT
  pid,
  usename,
  application_name
FROM
  pg_stat_activity
WHERE
  datname = 'test_db';

 pid  | usename  | application_name
-------+----------+------------------
37771 | postgres | psql
(1 row)

Then, use the pg_terminate_backend() function to terminate the active connection with pid 37771:

SELECT pg_terminate_backend(37771);

pg_terminate_backend
----------------------
t
(1 row)

Summarize

The PostgreSQL ALTER DATABASE ... RENAME statement is used to rename databases. You cannot rename a database with active connections.