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. By default, system administrators have the permission. 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.

  ALTER DATABASE database_name 
      [ WITH ] CONNECTION LIMIT connlimit;

• Rename the database.

  ALTER DATABASE database_name 
      RENAME TO new_name;

• Change the database owner.

  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 session parameter value of the database.

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

• Reset the database configuration parameter.

  ALTER DATABASE database_name RESET 
      { configuration_parameter | ALL };

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
  • 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.

• RESET ALL

  Resets all database session parameters.

• 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.

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 information about test_db1.
  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 information about test_db1.
  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 information about test_db1.
  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 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