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.

Precautions

• 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 statement 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. You can only specify the compatibility mode when creating a 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

Examples

Create the testdb database and the user1 user.

1

CREATE DATABASE testdb ENCODING 'UTF8' template = template0;

1
2

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

Set the number of connections of the testdb database 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 the testdb_1 database to user1.

1

ALTER DATABASE testdb_1 OWNER TO user1;

Set the tablespace of the testdb_1 database 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 and DROP DATABASE