PostgreSQL - Rename Database

PostgreSQL - Renaming a Database

Renaming a database is an administrative task that changes its identifier. After renaming, the database is only accessible via its new name. This operation requires superuser privileges and careful handling to avoid disrupting active services.

Syntax

The SQL command to rename a database is straightforward:

ALTER DATABASE old_database_name RENAME TO new_database_name;

• old_database_name: The current name of the database.
• new_database_name: The desired new name for the database.

Prerequisites and Safety

Before you can rename a database, you must ensure a critical condition is met:

• No Active Connections: The database cannot be renamed while there are any active connections to it (including your own session).
• Privileges: You must have superuser privileges or be the owner of the database.

How to Rename a Database: A Step-by-Step Guide

The following steps demonstrate the safe and correct procedure for renaming a database using the psql command-line tool.

Step 1: Connect to a different database. You cannot be connected to the database you intend to rename. The default maintenance database, postgres, is a safe choice.

# Connect to the 'postgres' database as the postgres user
psql -U postgres -d postgres

Step 2: List existing databases to confirm the current name. Let’s assume we want to rename test_app_db to production_app_db.

postgres=# \l

The output will show a list of your databases, including test_app_db.

Step 3: Execute the ALTER DATABASE command. If there are active connections, this command will fail.

postgres=# ALTER DATABASE test_app_db RENAME TO production_app_db;
ALTER DATABASE

Step 4: Verify the change. List the databases again to see the new name in effect.

postgres=# \l

The list will now show production_app_db instead of test_app_db. All tables, data, and permissions within the database remain intact.

Handling Active Connections (Advanced)

In a live environment, you might encounter an error because other users or applications are connected. The error will look like this:

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

The best approach is to gracefully shut down the application services connected to the database. As a last resort, a database administrator can forcefully terminate connections. This is a disruptive action and should be used with extreme caution.

-- This command forcefully disconnects all users from 'test_app_db'.
-- WARNING: This can cause data loss for in-progress transactions.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'test_app_db';

Best Practices

• Schedule Downtime: Renaming a production database should be a planned maintenance activity.
• Update Connection Strings: After renaming, all applications and services must be updated with the new database name in their connection strings.
• Use Consistent Naming: Follow a clear and consistent naming convention for your databases (e.g., project_dev, project_staging, project_prod).