ALTER DATABASE

Description

Modifies a database, including its name, owner, object isolation, and connection limitation.

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 are 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 or system administrator and a member of the new owner role, with the CREATEDB permission.
  • To modify the default tablespace of the database, the user must have the CREATE permission on the 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.

  1 2	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.

Parameters

• 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: an integer ranging from –1 to 2³¹ – 1. You are advised to set this parameter to an integer ranging 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

  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 disables the setting.

  

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

  • 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 disables the setting.

    Value range: a string.

    • DEFAULT
    • OFF
    • RESET
    • User-specified value: The value must meet the restriction of the modified parameter.
  • FROM CURRENT

    Uses the value of configuration_parameter of the current session.

• time_zone

  Sets the time zone of the database specified by database_name. You must have the permission on the corresponding database.

  Value range: a string.

  • Time zones supported by the system and their abbreviations.
  • -15:59~+15:00
• 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.

• group_name

  Specifies the name of the node group bound to the hash bucket table. Common users can only bind the table to the installation node group but cannot modify it.

• If you modify the default tablespace of a database, the tables and indexes in the old tablespace are moved 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.

Examples

• Rename the database.

  -- Create the database testdb.
  gaussdb=# CREATE DATABASE testdb;
  
  -- Rename testdb to test_db1.
  gaussdb=# ALTER DATABASE testdb RENAME TO test_db1;

• Change the maximum number of database connections.

  -- Change the maximum number of connections to test_db1 to 100.
  gaussdb=# ALTER DATABASE test_db1 WITH CONNECTION LIMIT 100;
  
  -- View the test_db1 information.
  gaussdb=# SELECT datname,datconnlimit FROM pg_database WHERE datname = 'test_db1';
   datname  | datconnlimit 
  ----------+--------------
   test_db1 |          100
  (1 row)

• Change the database owner.

  -- Create user scott.
  gaussdb=# CREATE USER scott PASSWORD '********';
  
  -- Change the owner of test_db1 to scott.
  gaussdb=# ALTER DATABASE test_db1 OWNER TO scott;
  
  -- View the test_db1 information.
  gaussdb=# SELECT t1.datname, t2.usename 
            FROM pg_database t1, pg_user t2 
            WHERE t1.datname='test_db1' AND t1.datdba=t2.usesysid;
   datname  | usename 
  ----------+---------
   test_db1 | scott
  (1 row)

• Change the default tablespace of the database.

  -- Create a tablespace.
  gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
  
  -- Modify the default tablespace of test_db1.
  gaussdb=# ALTER DATABASE test_db1 SET TABLESPACE tbs_data1;
  
  -- View the test_db1 information.
  gaussdb=# SELECT t1.datname AS database, t2.spcname AS tablespace 
            FROM pg_database t1, pg_tablespace t2 
            WHERE t1.dattablespace = t2.oid AND 
                  t1.datname = 'test_db1';
   database | tablespace 
  ----------+------------
   test_db1 | tbs_data1
  (1 row)

• Modify the object isolation attribute of the database.

  -- Create user jack.
  gaussdb=# CREATE USER jack PASSWORD '********';
  
  -- Create the test_tbl1 table in test_db1.
  gaussdb=# \c test_db1
  test_db1=# CREATE TABLE test_tbl1(c1 int,c2 int);
  
  -- Switch to user jack and view pg_tables.
  test_db1=# SET ROLE jack PASSWORD '********';
  test_db1=> SELECT tablename FROM pg_tables WHERE tablename = 'test_tbl1';
   tablename 
  -----------
   test_tbl1
  (1 row)
  
  -- Modify the object isolation attribute.
  test_db1=> SET ROLE scott PASSWORD '********';              
  test_db1=> ALTER DATABASE test_db1 ENABLE PRIVATE OBJECT;
  
  -- Switch to user jack and view pg_tables.
  test_db1=> SET ROLE jack PASSWORD '********';
  
  -- Due to the isolation attribute, 0 data records can be queried.
  test_db1=> SELECT tablename FROM pg_tables WHERE tablename = 'test_tbl1';
   tablename 
  -----------
  (0 rows)
  
  -- Switch to the default user and perform the deletion.
  test_db1=> RESET ROLE;
  test_db1=# DROP TABLE public.test_tbl1;
  
  -- Switch to the default database. Change the database name based on the actual situation.
  test_db1=# \c postgres
  gaussdb=# DROP DATABASE test_db1;
  gaussdb=# DROP TABLESPACE tbs_data1;
  gaussdb=# DROP USER jack;
  gaussdb=# DROP USER scott;

Helpful Links

CREATE DATABASE and DROP DATABASE