PostgreSQL Alter Databases

PostgreSQL allows you to modify an existing database using the ALTER DATABASE statement.

PostgreSQL `ALTER DATABASE` syntax

PostgreSQL ALTER DATABASE can modify a database, including name, attributes, owner, tablespace, etc.

To rename a database, use the following syntax:
```
ALTER DATABASE name RENAME TO new_name;
```

To change the owner of a database, use the following syntax:

ALTER DATABASE name OWNER TO
{ new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER };

To modify options for a database, use the following syntax:
```
ALTER DATABASE name [WITH] ALLOW_CONNECTIONS { true | false};
ALTER DATABASE name [WITH] CONNECTION LIMIT connlimit;
ALTER DATABASE name [WITH] IS_TEMPLATE { true | false};
```
Explanation:
- The ALLOW_CONNECTIONS indicates whether to allow connections to this database.
- The CONNECTION LIMIT indicates How many concurrent connections can be made to this database.
- The IS_TEMPLATE indicates whether it is a template database
To modify a database tablespace, use the following syntax:
```
ALTER DATABASE name SET TABLESPACE new_tablespace;
```
To modify configuration parameters of the database, use the following syntax:
```
ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT };
ALTER DATABASE name SET configuration_parameter FROM CURRENT;
```
New values for configuration parameters will be written to the postgresql.conf file to be loaded for use in new sessions.
To reset the value of a configuration parameter of a database, use the following syntax:
```
ALTER DATABASE name RESET configuration_parameter;
```
To reset the values of all configuration parameters of the database, use the following syntax:
```
ALTER DATABASE name RESET ALL;
```

Note: To modify the information of the database, you must be the superuser or the owner of the database.

PostgreSQL `ALTER DATABASE` instance

We will use ALTER DATABASE in the psql tool to modify a database.

[~] psql -U postgres
psql (14.4)
Type "help" for help.

Create a database named test_db for demonstration:

CREATE DATABASE test_db;

Rename the database

To rename the test_db database to test_new_db, use the following statement:

ALTER DATABASE test_db
RENAME TO test_new_db;

You cannot rename the current database.

PostgreSQL will give an error if another user is using the database to rename.

Modify database tablespaces

The default tablespace of the database is pg_default, the following describes how to modify the tablespace of the test_new_db database to test_tablespace.

First, create the tablespace using the following statement:

CREATE TABLESPACE test_tablespace
OWNER postgres
LOCATION 'D:\\data\\pg_tablespaces\\test_tablespace';

Then, use the following statement to modify the database tablespace:

ALTER DATABASE test_new_db
SET TABLESPACE test_tablespace;

Modify whether the database is allowed to connect

To set the database to not allow connections, use the following statement:

ALTER DATABASE test_new_db ALLOW_CONNECTIONS false;

To set the database to allow connections, use the following statement:

ALTER DATABASE test_new_db ALLOW_CONNECTIONS true;

Modify the max number of connections to the database

To limit the number of connections to this database to 10, use the following statement:

ALTER DATABASE test_new_db CONNECTION LIMIT 10;

Modify whether the database is a template database

To set the database to be the template database, use the following statement:

ALTER DATABASE test_new_db IS_TEMPLATE true;

To set the database to not be a template database, use the following statement:

ALTER DATABASE test_new_db IS_TEMPLATE false;

Change the owner of the database

The owner of the test_new_db database is postgres, and here’s how to change its owner to test.

First, create a test user:

CREATE USER test PASSWORD '123456';

Then, use the following statement to change the owner of the database to test:

ALTER DATABASE test_new_db OWNER TO test;

In this tutorial, you learned how to use PostgreSQL ALTER DATABASE statements.