PostgreSQL - ALTER DATABASE

PostgreSQL - ALTER DATABASE

The ALTER DATABASE command in PostgreSQL is used to modify the characteristics of a database. This is a powerful administrative command typically used to rename a database, change its owner, or adjust its session-level configuration parameters.

Key Concepts and Syntax

The general syntax for altering a database involves specifying the database name and the action to perform. Note that most ALTER DATABASE operations require superuser privileges or ownership of the database.

1. Rename a Database

To change the name of a database, you can use the RENAME TO clause. You must be the database owner or a superuser to perform this action.

ALTER DATABASE current_name RENAME TO new_name;

Important: You cannot be connected to the target database while renaming it. Connect to a different database (like postgres or template1) before running this command.

2. Change the Database Owner

To transfer ownership of a database to a new role (user), use the OWNER TO clause. The new owner must be an existing role.

ALTER DATABASE database_name OWNER TO new_owner_role;

3. Set Connection Limit

You can limit the number of concurrent connections to a specific database. This is useful for resource management. A value of -1 (the default) means no limit.

ALTER DATABASE database_name CONNECTION LIMIT 25;

Practical Example

Let’s walk through a complete example of creating a database and then altering its properties.

Step 1: Create a Database

First, we’ll create a new database to work with.

CREATE DATABASE project_alpha;

The command completes, and the database project_alpha is created.

Step 2: Rename the Database

Now, let’s rename project_alpha to project_omega. Remember to connect to a different database first.

-- Make sure you are connected to a database other than 'project_alpha'
ALTER DATABASE project_alpha RENAME TO project_omega;

The database is now named project_omega.

Step 3: Transfer Ownership

To change the owner, we first need to create a new role. Then, we can alter the database owner.

-- First, create a new role (user) with login capabilities
CREATE ROLE dev_lead WITH LOGIN PASSWORD 'a_secure_password';

-- Now, transfer ownership of the database
ALTER DATABASE project_omega OWNER TO dev_lead;

The role dev_lead is now the owner of the project_omega database.

Security and Best Practices

• Privilege Control: ALTER DATABASE is a privileged command. In a production environment, only database administrators should have the rights to execute it.
• Disconnection for Renaming: Always remember the disconnection requirement for renaming to avoid errors and service interruptions.
• Role Management: Before changing ownership, ensure the new owner role exists and has the appropriate permissions. It’s a best practice to assign ownership to roles representing applications or teams rather than individual user accounts.