ALTER DATABASE

Function

This command is used to modify the attributes of a database, including the database name, owner, maximum number of connections, and object isolation attribute.

Important Notes

• Only the owner of a database or a system administrator has the permission to run the ALTER DATABASE statement. Users other than system administrators may have the following permission constraints depending on the attributes to be modified:
  • To modify the database name, you must have the CREATEDB permission.
  • To modify a database owner, you must be a database owner and a member of the new owner, and have the CREATEDB permission.
  • To change the default tablespace, you must be a database owner or a system administrator, and must have the CREATE permission on the new tablespace. This syntax physically migrates tables and indexes in a default tablespace to a new tablespace. Note that tables and indexes outside the default tablespace are not affected.
  • Only a database owner or a system administrator can modify GUC parameters for the database.
  • Only database owners and system administrators can modify the object isolation attribute of a database.
• You are not allowed to rename a database in use. To rename it, connect to another database.
• The compatibility mode of an existing database cannot be changed. It can only be specified during creation of the database. For details, see CREATE DATABASE.

Syntax

• Modify the maximum number of connections of the database.

  1 2	ALTER DATABASE database_name [ [ WITH ] CONNECTION LIMIT connlimit ];

• Rename the database.

  1 2	ALTER DATABASE database_name RENAME TO new_name;

  

  If the database contains OBS multi-temperature tables, the database name cannot be changed.

• Change the database owner.

  1 2	ALTER DATABASE database_name OWNER TO new_owner;

• Change the default tablespace of the database.

  1 2	ALTER DATABASE database_name SET TABLESPACE new_tablespace;

  

  The current tablespaces cannot be changed to OBS tablespaces.

• Modify the session parameter value of the database.

  1 2	ALTER DATABASE database_name SET configuration_parameter { { TO | = } { value | DEFAULT } | FROM CURRENT };

• Reset the database configuration parameter.

  1 2	ALTER DATABASE database_name RESET { configuration_parameter | ALL };

Parameter Description

• database_name

  Specifies the name of the database whose attributes are to be modified.

  Value range: a string. It must comply with the naming convention.

• connlimit

  Specifies the maximum number of concurrent connections that can be made to this database (excluding administrators' connections).

  Value range: The value must be an integer, preferably between 1 and 50. The default value -1 indicates no restrictions.

• new_name

  Specifies the new name of a database.

  Value range: a string. It must comply with the naming convention.

• new_owner

  Specifies the new owner of a database.

  Value range: a string indicating a valid user name

• configuration_parameter

  value

  Sets a specified database session parameter. If the value is DEFAULT or RESET, the default setting is used in the new session. OFF closes the setting.

  Value range: a string. It can be set to:

  • DEFAULT
  • OFF
  • RESET
• FROM CURRENT

  Sets the value based on the database connected to the current session.

• RESET configuration_parameter

  Resets the specified database session parameter.

• RESET ALL

  Resets all database session parameters.

• Modifies the default tablespace of a database by moving all the tables or indexes from the old tablespace to the new one. This operation does not affect the tables or indexes in other non-default tablespaces.
• The modified database session parameter values will take effect in the next session.

Example:

Create the example database testdb and user tom.

1

CREATE DATABASE testdb ENCODING 'UTF8' template = template0;

1
2

DROP USER IF EXISTS tom;
CREATE USER tom PASSWORD '{Password}';

Set the maximum number of connections to database testdb to 10:

1

ALTER DATABASE testdb CONNECTION LIMIT= 10;

Change the database name from testdbto testdb_1.

1

ALTER DATABASE testdb RENAME TO testdb_1;

Change the owner of database testdb_1 to tom:

1

ALTER DATABASE testdb_1 OWNER TO tom;

Set the tablespace of database music1 to PG_DEFAULT:

1

ALTER DATABASE testdb_1 SET TABLESPACE PG_DEFAULT;

Disable the default index scan on the testdb_1 database.

1

ALTER DATABASE testdb_1 SET enable_indexscan TO off;

Reset parameter enable_indexscan:

1

ALTER DATABASE testdb_1 RESET enable_indexscan;

Links

CREATE DATABASE, DROP DATABASE