PostgreSQL Rename Database

Summary: In this tutorial, you’ll learn the exact steps to rename a PostgreSQL database safely and effectively.

Sometimes, you want to rename a PostgreSQL database due to:

• Standardizing name conventions.
• Correcting an existing name.

PostgreSQL provides the ALTER DATABASE statement to make it simple to rename a database.

Prerequisites #

Before you start renaming a PostgreSQL database, ensure the following:

1. You have a superuser or the owner of the database.
2. There are no active connections to the database you want to rename.
3. You have a backup of the database you want to rename in case something goes wrong.

Renaming a Database Using the ALTER DATABASE Statement #

Here are the steps for renaming a database:

Step 1: Connect to PostgreSQL #

Open your terminal and connect to the database you want to rename:

psql -U postgres -d target_dbCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 2: Disconnect Active Connections #

Terminate all connections to the database by running the following statement:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'target_db' AND pid <> pg_backend_pid();Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace target_db with the actual name of the database you want to rename.

Step 3: Rename the Database #

Run the following ALTER DATABASE command to rename the database:

ALTER DATABASE target_db
RENAME TO new_db;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace target_db with the current name and new_db with the desired name.

Step 4: Verify the Change #

Show the new database to confirm the changes:

\l new_dbCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Renaming a Database Example #

We’ll create a database called acc and rename it to accounting:

First, connect to the local PostgreSQL server using psql:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, create a new database called acc:

CREATE DATABASE acc;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, switch the current database to acc:

\c accCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fourth, open a new session and connect to the postgres database:

psql -U postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Fifth, rename the acc database to accounting:

ALTER DATABASE acc
RENAME TO accounting;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL issued the following error:

ERROR: current database cannot be renamedCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The acc database may have active connections. Run the following command to check:

SELECT datname, pid, application_name, client_addr, client_port
FROM pg_stat_activity WHERE datname = 'acc';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It shows one active connection.

Sixth, terminate the active connection:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'acc';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Seventh, rerun the ALTER DATABASE statement to rename the acc database:

ALTER DATABASE acc
RENAME TO accounting;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Finally, verify the changes:

\l accountingCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the ALTER DATABASE ... RENAME TO statement to rename a database. After renaming the database, you should update all application configurations referencing the old name.