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 OWNERTO' 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 DATABASE actions, 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 DATABASE statement 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 using ALTER DATABASE.