PostgreSQL – ALTER DATABASE
PostgreSQL has an ALTER DATABASE statement that is used for modifying an existing database. The features of a database, once created can be changed using the ALTER DATABASE statement.
Syntax: ALTER DATABASE target_database action;
As the above syntax depicts, the database where modifications are to be done are mentioned after the ALTER DATABASE statement followed by the action that is to be performed on the database.
Below is the list of actions that PostgreSQL allows:
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;
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.
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.
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;
It is important to note that only the superuser or the database owner can change the default session variables for a database.
Now, let’s look into an example of the implementation of the ALTER DATABASE statement.
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;
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;
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';
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';
Now set escape_string_warning configuration variable to off using the below commands:
ALTER DATABASE my_renamed_db SET escape_string_warning TO off;