PostgreSQL ALTER DATABASE
The PostgreSQL ALTER DATABASE statement is a powerful tool used for modifying an existing database. The features of a database, once created can be changed using the ALTER DATABASE statement.
Let’s explore the syntax of PostgreSQL ALTER DATABASE, the various actions that can be performed, and practical examples for efficiently managing your PostgreSQL databases.
Syntax
ALTER DATABASE target_database action;
As the above syntax depicts, the database where modifications are to be done is mentioned after the ALTER DATABASE statement followed by the action that is to be performed on the database.
Available Actions with ALTER DATABASE
Below is the list of actions that PostgreSQL allows:
1. Rename a database:
The ‘ALTER DATABASE RENAME TO’ statement is used to rename a database as follows:
ALTER DATABASE target_database RENAME TO new_database;
2. Change owner of the database:
The ‘ALTER DATABASE OWNER TO’ statement is used to change the owner of a database as follows:
ALTER DATABASE target_database OWNER TO new_owner;
It is important to note that only the superuser or the owner of the database can perform this action.
3. Change tablespace of the database:
The ‘ALTER DATABASE SET TABLESPACE’ statement is used to change the default tablespace of a database as follows:
ALTER DATABASE target_database SET TABLESPACE new_tablespace;
The statement moves tables and indexes from the legacy tablespace to the new one.
4. Change session defaults for run-time configuration variables:
By, default, PostgreSQL loads the configuration variable from the ‘postgresql.conf’ file. This file contains information regarding the database roles and their respective authentication hashing types. These settings or information can be edited using the ‘ALTER DATABASE SET’ statement as shown below:
ALTER DATABASE target_database SET config_data = value;
Note: It is important to note that only the superuser or the database owner can change the default session variables for a database.
PostgreSQL ALTER DATABASE Example
Now, let’s look into an example of the implementation of the ALTER DATABASE statement.
1. Create a Sample Database
Let’s log in as the Postgres user and create a sample database (say, ‘my_test_db’) using the below command:
CREATE DATABASE my_test_db;
2. Rename the Database
Now use the below command to rename the database from “my_test_db” to “my_renamed_db“:
ALTER DATABASE my_test_db RENAME TO my_renamed_db;
3. Change the Owner of the Database
Now execute the following statement to change the owner of the “my_renamed_db” database from “postgres” to “geeks“, with the assumption that the “geeks” role already exists.
ALTER DATABASE my_renamed_db OWNER TO geeks;
If the “geeks” role does not exist, create it by using the following statement:
CREATE ROLE geeks
VALID UNTIL 'infinity';
4. Change the Default Tablespace
Now change the default ‘tablespace’ of the ‘my_renamed_db’ from ‘pg_default’ to ‘geeks_default’, with the assumption that the ‘geeks_defaulttablespace’ already exists.
ALTER DATABASE my_renamed_db
SET TABLESPACE geeks_default;
If the ‘geeks_defaulttablespace’ does not exist, you can create it by using the following statement:
CREATE TABLESPACE geeks_default
OWNER geeks
LOCATION E'C:\\pgdata\\geeks';
5. Change Session Defaults
Now set ‘escape_string_warning’ configuration variable to off using the below commands:
ALTER DATABASE my_renamed_db SET escape_string_warning TO off;
Important Points About PostgreSQL ALTER DATABASE
- Before performing certain
ALTER DATABASEactions, such as renaming or changing the tablespace, you need to disconnect all users from the database.- You can set a database to read-only mode using the
ALTER DATABASEstatement for maintenance tasks or when you want to prevent write operations.- PostgreSQL allows you to set custom configuration parameters for a database.
- The ‘
LC_COLLATE'and ‘LC_CTYPE'settings, which control the database collation and character classification, cannot be changed usingALTER DATABASE.
