PostgreSQL | Database | Modify a Database (ALTER DATABASE)

Tags:

• PostgreSQL
• Database

This page explains how to modify a created database by using the ALTER DATABASE command.

Change Database Settings

First, look at how to change settings for each database. The syntax is as follows.

ALTER DATABASE name [ [ WITH ] option [ ... ] ]

option : 
    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

This changes the settings of the database (name). The settings that can be changed are the same three settings that can be specified when creating a database: ALLOW_CONNECTIONS, CONNECTION LIMIT, and IS_TEMPLATE.

To change database settings, the role executing the command must be a superuser or the database owner.

–

Now try it in practice. First, create a hamster database with a superuser role. Set CONNECTION LIMIT, which specifies the number of concurrent connections, to 5.

devkuma=# create database hamster connection limit 5;
CREATE DATABASE
devkuma=#

The database has been created. To confirm, query the database list and the number of concurrent connections from pg_database, one of the system catalogs.

devkuma=# select datname, datconnlimit from pg_database;
  datname  | datconnlimit
-----------+--------------
 postgres  |           -1
 template1 |           -1
 template0 |           -1
 sample    |           -1
 devkuma   |           -1
 devkuma2  |           -1
 hamster   |            5
(7개 행)


devkuma=#

You can confirm that the hamster database has been created and that the number of concurrent connections is 5.

Now change the concurrent connection limit for the hamster database to 3 by using the ALTER DATABASE command. Run the following.

devkuma=# alter database hamster connection limit 3;
ALTER DATABASE
devkuma=#

The number of concurrent connections has been changed to 3. To confirm, query the database list and concurrent connection limit from pg_database again.

devkuma=# select datname, datconnlimit from pg_database;
  datname  | datconnlimit
-----------+--------------
 postgres  |           -1
 template1 |           -1
 template0 |           -1
 sample    |           -1
 devkuma   |           -1
 devkuma2  |           -1
 hamster   |            3
(7개 행)


devkuma=#

You can confirm that the concurrent connection limit for the hamster database is now 3.

Rename a Database

Next, look at how to rename a database. The syntax is as follows.

ALTER DATABASE name RENAME TO new_name

This changes the database name (name) to another name (new_name).

To rename a database, the role executing the command must be a superuser or the database owner, and it must have the CREATEDB privilege. You cannot rename the database to which you are currently connected.

–

Now try it in practice. Rename the hamster database created earlier to rabbit. Run the following.

devkuma=# alter database hamster rename to rabbit;
ALTER DATABASE
devkuma=#

The database name has been changed. To confirm, query the database list from pg_database.

devkuma=# select datname, pg_authid.rolname as dbrollname
devkuma-#   from pg_database
devkuma-#   join pg_authid on pg_authid.oid = pg_database.datdba;
  datname  | dbrollname
-----------+------------
 rabbit    | postgres   <<<<< changed from hamster to rabbit
 devkuma2  | postgres
 devkuma   | postgres
 sample    | postgres
 template0 | postgres
 template1 | postgres
 postgres  | postgres
(7개 행)


devkuma=#

You can confirm that the hamster database has been renamed to rabbit.

Change the Database Owner

Next, here is how to change the owner of a database. Use the following syntax.

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

This changes the owner of the database (name) to another role (new_owner).

To change a database owner, the role executing the command must be a superuser or the database owner, be a direct or indirect member of the new owner role, and have the CREATEDB privilege.

–

Now try it in practice. The owner of the created rabbit database is currently the postgres role.

devkuma=# \l rabbit
                               데이터베이스 목록
  이름  |  소유주  | 인코딩 |     Collate      |      Ctype       | 액세스 권한
--------+----------+--------+------------------+------------------+-------------
 rabbit | postgres | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
(1개 행)


devkuma=#

Change the owner of the rabbit database to the orange role. First, create the orange role by running the following.

devkuma=# create role orange with login password 'apple';
CREATE ROLE
devkuma=#

The database owner has been changed. To confirm, run the \l command again.

devkuma=# alter database rabbit owner to orange;
ALTER DATABASE
devkuma=# \l rabbit
                              데이터베이스 목록
  이름  | 소유주 | 인코딩 |     Collate      |      Ctype       | 액세스 권한
--------+--------+--------+------------------+------------------+-------------
 rabbit | orange | UTF8   | Korean_Korea.949 | Korean_Korea.949 |
(1개 행)


devkuma=#

You can confirm that the owner of the rabbit database has changed to the orange role.

Other Forms

The ALTER DATABASE command also provides the following forms.

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

–

This page explained how to modify a database by using the ALTER DATABASE command.