Hi! In this article i will show you how to rename a database in PostgreSQL. For those who does not have time to read our whole detailed article command template we use is:

ALTER DATABASE database_name RENAME TO new_database_name;

It’s not uncommon to encounter situations where you need to rename a database in PostgreSQL. Perhaps you’ve changed project details, need to consolidate your databases, or simply made a typo when you originally created the database. Thankfully, PostgreSQL makes renaming a database straightforward. Let’s walk you through the process!

Prerequisites – Things to Know Before You Start

• Permissions: Don’t attempt this if you’re not the database owner or a superuser. Renaming databases requires specific privileges.
• Active Connections: It’s essential to close all active connections to the database you want to rename. Trying to rename it while there are open connections can lead to errors.
• Downtime: While usually quick, renaming can introduce brief downtime. If your database is in active use, it’s best to do this during low-traffic times.

The Essential Command: ALTER DATABASE

The key to renaming a database in PostgreSQL is the ALTER DATABASE command. Here’s the basic syntax:

SQL

ALTER DATABASE old_database_name RENAME TO new_database_name;

Let’s break this down:

• ALTER DATABASE: This tells PostgreSQL we want to make changes to a database.
• old_database_name: Replace this with the existing name of your database.
• RENAME TO: This keyword indicates we’re specifically renaming the database.
• new_database_name: Put the desired new name of your database here.

Step-by-Step Example

Say you have a database called “project_alpha” and want to rename it “project_final”. Here’s how you’d do it:

1. Disconnect: Close any connections to the “project_alpha” database (including yours).
2. Connect to a Different Database: Open a connection to a PostgreSQL database that is not “project_alpha”. The default “postgres” database is suitable.
3. The Command: Execute this: ALTER DATABASE project_alpha RENAME TO project_final;
4. Verify: You should see a confirmation message saying “ALTER DATABASE”. You can check the available databases for verification.

Important Considerations

• Case Sensitivity: Database names in PostgreSQL can be case-sensitive depending on how they were created. If in doubt, use double-quotes around the names ("old_database_name") to ensure accuracy.
• Objects Within the Database: Renaming the database itself doesn’t change the names of tables, schemas, or other objects inside it. If you need those updated too, you’ll have to rename them individually.

Renaming a Tablespace with Connections

As you can imagine, you can not rename a database in PostgreSQL while connections are running on the database.

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

And That’s How It’s Done!

Renaming databases in PostgreSQL is a simple operation once you understand the basics. It’s an important administration task you might encounter as you manage your PostgreSQL setup. Always test these kinds of changes in a non-production environment if you have one available.

Got more PostgreSQL questions? Let me know, and I’ll do my best to guide you!