Postgres supports an ALTER DATABASE statement that assists us in modifying the already existing databases. For instance, the ALTER DATABASE command allows Postgres users to rename a database, modify the database attributes, change the database ownership, reset configuration parameters, etc.
This Postgres guide presents a detailed overview of the ALTER DATABASE command using suitable examples.
How to Rename a Database in Postgres?
Utilize the below-provided syntax to rename an already existing database via the ALTER DATABASE command:
ALTER DATABASE db_name RENAME TO modified_name;
To rename a database, you must be a superuser or database owner with CREATEDB privileges. Moreover, the current database can’t be renamed in PostgreSQL. To accomplish this task, you need to establish a connection with some other database.
Example: Renaming a Database Via ALTER DATABASE Command
Firstly, execute the “\l” command to list the available databases:
\l
Suppose we want to rename a database named “postgres_copy” to “postgres_db”. For this purpose, the ALTER DATABASE command will be executed as follows:
ALTER DATABASE postgres_copy RENAME TO postgres_db;
Let’s verify the database modification using the “\l” command:
\l
The output clarifies that the selected database has been renamed to “postgres_db”.
How to Alter Database Attributes in Postgres?
Utilize the below syntax to modify the attributes of an already existing database via the ALTER DATABASE command:
ALTER DATABASE db_name WITH option;
Where the “option” parameter can be replaced with one of the following:
- IS_TEMPLATE: the value of the stated parameter can be either true or false. “true” indicates that the selected database can be cloned/copied by any user having CREATEDB rights. While specifying “false” means only superusers or the database owner can clone the selected database.
- ALLOW_CONNECTIONS: If the "false" value is specified, establishing a connection with the selected database will not be possible.
- CONNECTION LIMIT: It determines how many concurrent connections can be established with a particular database. Specifying -1 indicates no connection limit.
To modify database attributes, you must be a superuser or database owner.
Example 1: Modifying Database Attributes
The following example demonstrates how to modify a database attribute in Postgres:
ALTER DATABASE postgres_db ALLOW_CONNECTIONS = FALSE;
The above query specifies a “false” value for the “ALLOW_CONNECTIONS” parameter so that no one can establish the connection with the “postgres_db” database:
Let’s execute the “\c” command followed by the respective database name to verify the working of the “ALLOW_CONNECTIONS” parameter:
\c postgres_db;
The output clearly states that you can’t establish a connection with the “postgres_db”.
Note: Similarly, the IS_TEMPLATE and CONNECTION LIMIT parameters can be used to change the database attributes.
How to Alter Database Owners in Postgres?
Use the ALTER DATABASE command with the OWNER TO clause to alter the database owner:
ALTER DATABASE db_name OWNER TO new_db_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER;
The superuser and database owner with CREATEDB privileges can alter the database owner.
Example: Changing the Database Owner in Postgres
As shown in the following snippet, "postgres" owns the "postgres_db" database:
To change the owner of “postgres_db”, execute the below-provided command:
ALTER DATABASE postgres_db OWNER TO sample_user;
Execute the “\l” command to verify the owner of the “postgres_db”:
\l
The owner of the selected database has been changed successfully.
How to Alter Default Tablespace in Postgres?
The “tablespace” represents a directory/location where Postgres saves the data files. To alter a tablespace of a particular database, the “ALTER DATABASE” command is used with the “SET TABLESPACE” clause:
ALTER DATABASE db_name SET TABLESPACE new_tablespace;
To alter the database tablespace, you must be a superuser or database owner.
Example: Changing Databse’s Tablespace
We have already created a tablespace named “sample_tablespace”. In the following example, we will execute the ALTER DATABASE command to change the default tablespace of the “postgres_db” database:
ALTER DATABASE postgres_db SET TABLESPACE sample_tablespace;
The tablespace has been successfully changed.
How to Alter Defaults Runtime Configuration Parameters in Postgres?
By default, PostgreSQL loads the configuration parameters from the "postgresql.conf" file when it establishes a connection with a database. However, the ALTER DATABASE command assists us in overriding or altering these settings for a specific database:
ALTER DATABASE db_name SET configuration_parameter = parameter_value;
Only the superusers and database owners can alter the database’s default run-time configuration parameters.
Example: Changing Defaults Runtime Configuration Parameters/Variables
The following example addresses the “escape_string_warning” parameter for a database named “postgres_db”:
ALTER DATABASE postgres_db SET escape_string_warning = off;
The database named “postgres_db” has been successfully altered.
Conclusion
Postgres’ ALTER DATABASE command modifies the already existing databases, such as renaming a database, modifying the database attributes, changing the database ownership, setting the configuration parameters, etc. Only superusers or database owners can modify the existing databases. This post presented an in-depth understanding of the ALTER DATABASE statement using numerous examples.
