Updated on 2024-10-14 GMT+08:00

ALTER DATABASE

Function

ALTER DATABASE modifies a database, including its name, owner, connection limitation, and object isolation.

Precautions

  • Only the database owner or a user granted with the ALTER permission can run the ALTER DATABASE command. The system administrator has this permission by default. The following is permission constraints depending on 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 or system administrator and a member of the new owner role, with the CREATEDB permission.
    • To modify the default tablespace of the database, you 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.
  • You are not allowed to rename a database in use. To rename it, connect to another database.

Syntax

  • Modify the maximum number of connections to 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;
    
  • Change the database owner.
    1
    2
    ALTER DATABASE database_name 
        OWNER TO new_owner;
    
  • Change the default tablespace of the database.
    ALTER DATABASE database_name 
        SET TABLESPACE new_tablespace;

    If some tables or objects in the database have been created in new_tablespace, the default tablespace of the database cannot be changed to new_tablespace. An error will be reported during the execution.

  • Modify the object isolation attribute of the database.
    1
    ALTER DATABASE database_name [ WITH ] { ENABLE | DISABLE } PRIVATE OBJECT;
    
    • To modify the object isolation attribute of a database, the database must be connected. Otherwise, the modification will fail.
    • For a new database, the object isolation attribute is disabled by default. After the database object isolation attribute is enabled, the database automatically adds row-level access control policies to the system catalogs PG_CLASS, PG_ATTRIBUTE, PG_PROC, PG_NAMESPACE, PGXC_SLICE and PG_PARTITION. Common users can only view the objects (tables, functions, views, and columns) that they have the permission to access. This attribute does not take effect for administrators. After this attribute is enabled, administrators can still view all database objects.

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 from 1 to 50. The default value -1 indicates that there is no restriction on the number of concurrent connections.

  • 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. It must be a valid username.

  • new_tablespace

    Specifies the new default tablespace of a database. The tablespace exists in the database. The default tablespace is pg_default.

    Value range: a string. It must be a valid tablespace name.

  • configuration_parameter

    value

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

    The current version does not support setting database-level parameters.

    Value range: a string

    • DEFAULT
    • OFF
    • RESET
  • FROM CURRENT

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

  • RESET configuration_parameter

    Resets the specified database session parameter.

    The current version does not support resetting database-level parameters.

  • RESET ALL

    Resets all database session parameters.

    The current version does not support resetting database-level parameters.

  • Modify the default tablespace of a database by moving the table or index in the old tablespace into the new tablespace. 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.
  • After setting the parameters, you need to manually run the CLEAN CONNECTION command to clear the old connections. Otherwise, the parameter values between cluster nodes may be inconsistent.

Example

See Examples in CREATE DATABASE.

Helpful Links

CREATE DATABASE and DROP DATABASE.