ALTER DATABASE

功能描述

修改数据库的属性，包括：数据库名称、所有者、连接数限制以及对象隔离属性等。

注意事项

• 仅数据库的所有者或者被授予了数据库ALTER权限的用户可以执行ALTER DATABASE命令，系统管理员默认拥有此权限。针对所要修改属性的不同，存在以下权限约束：
  • 修改数据库名称：必须拥有CREATEDB权限。
  • 修改数据库所有者：当前用户必须是该数据库的所有者或者系统管理员，必须拥有CREATEDB权限，且该用户是新所有者角色的成员。
  • 修改数据库默认表空间：该用户必须拥有新表空间的CREATE权限。进行该操作会从物理上将一个数据库原来缺省表空间上的表和索引移至新的表空间。注意不在缺省表空间的表和索引不受此影响。
• 不能直接对当前使用的数据库进行重命名，如果需要重新命名，请连接至其他数据库之后对原数据库进行重命名。
• 不允许将其他模式重命名为templatem，不允许将templatem模式重命名为其他名称，不允许将templatea模板重命名为其他名称。

语法格式

• 修改数据库名称。

  1 2	ALTER DATABASE database_name RENAME TO new_name;

  

• 修改数据库所有者。

  1 2	ALTER DATABASE database_name OWNER TO new_owner;

  

• 修改数据库的最大连接数。

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

  

• 修改数据库默认表空间。

  1 2	ALTER DATABASE database_name SET TABLESPACE new_tablespace;

  

  

  如果数据库中的某些表或对象已经创建在new_tablespace下，则无法将该数据库的默认表空间修改为new_tablespace，执行会报错。

参数说明

• database_name

  需要修改属性的数据库名称。

  取值范围：字符串，要符合标识符命名规范。

• new_name

  数据库的新名称。

  取值范围：字符串，要符合标识符命名规范且最大长度不超过63个字符。

  • 当数据库名称超过63个字符时，数据库会截断并保留前63个字符当作数据库名称。
  • 当数据库名称中包含大写字母时，数据库会自动转换为小写字母。
  • 如果需要创建包含大写字母的数据库名称，则需要使用双引号进行包裹。
• new_owner

  数据库的新所有者。

  取值范围：字符串，有效的用户名。

• connlimit

  数据库可以接收的最大并发连接数（管理员用户连接除外）。

  取值范围：[-1, 2³¹-1]的整数。默认值为-1，表示不限制数据库的最大并发连接数，建议设置为1~50的整数。

• new_tablespace

  数据库新的默认表空间，该表空间为数据库中已经存在的表空间。

  取值范围：字符串，有效的表空间名。默认表空间为pg_default。

  

  修改数据库默认表空间，会将旧表空间中的所有表和索引转移到迁移至新表空间中，该操作不会影响其他非默认表空间中的表和索引。

• time_zone

  设置数据库的时区值，需要拥有数据库的操作权限。

  取值范围：字符串，当前支持设置的时区值为：

  • 系统支持的时区和其相应的缩写。
  • -15:59~+15:00。
  

  该参数仅在连接数据库为ORA兼容模式数据库且已设置a_format_version='10c'以及a_format_dev_version='s2'时支持指定。

• group_name

  hashbucket表绑定的node group名称，普通用户只支持绑定到installation node group上且不支持修改。

示例

• 修改数据库名称

  --创建数据库testdb。
  gaussdb=# CREATE DATABASE testdb;
  
  --将testdb重命名为test_db1。
  gaussdb=# ALTER DATABASE testdb RENAME TO test_db1;

• 修改数据库最大连接数

  --修改test_db1最大连接数为100。
  gaussdb=# ALTER DATABASE test_db1 WITH CONNECTION LIMIT 100;
  
  --查看test_db1信息。
  gaussdb=# SELECT datname,datconnlimit FROM pg_database WHERE datname = 'test_db1';
   datname  | datconnlimit 
  ----------+--------------
   test_db1 |          100
  (1 row)

• 修改数据库所有者

  --创建用户scott。
  gaussdb=# CREATE USER scott PASSWORD '********';
  
  --将test_db1的所有者修改为scott。
  gaussdb=# ALTER DATABASE test_db1 OWNER TO scott;
  
  --查看test_db1信息。
  gaussdb=# SELECT t1.datname, t2.usename 
            FROM pg_database t1, gs_user t2 
            WHERE t1.datname='test_db1' AND t1.datdba=t2.usesysid;
   datname  | usename 
  ----------+---------
   test_db1 | scott
  (1 row)

• 修改数据库默认表空间

  --创建表空间。
  gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
  
  --修改test_db1默认表空间。
  gaussdb=# ALTER DATABASE test_db1 SET TABLESPACE tbs_data1;
  
  --查看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)

• 修改数据库时区
  前提条件：当前连接数据库为ORA兼容模式数据库且已设置a_format_version='10c'以及a_format_dev_version='s2'。

  --创建ORA兼容性数据库，默认为PRC时区。
  gaussdb=# CREATE DATABASE testdb2 DBCOMPATIBILITY = 'ORA';
  
  --查看数据库testdb2时区。
  gaussdb=# SELECT datname,datcompatibility,dattimezone FROM pg_database WHERE datname = 'testdb2';
   datname | datcompatibility | dattimezone 
  ---------+------------------+-------------
   testdb2 | ORA              | PRC
  (1 row)
  
  --切换至数据库testdb2，修改数据库时区为'+08:00'。
  gaussdb=# \c testdb2
  testdb2=# SET a_format_version='10c';
  testdb2=# SET a_format_dev_version='s2';
  testdb2=# ALTER DATABASE testdb2 SET DBTIMEZONE = '+08:00';
  
  --查看数据库时区。
  testdb2=# SELECT datname,datcompatibility,dattimezone FROM pg_database WHERE datname = 'testdb2';
   datname | datcompatibility | dattimezone 
  ---------+------------------+-------------
   testdb2 | ORA              | +08:00
  (1 row)

• 修改数据库对象隔离属性

  --创建用户jack。
  gaussdb=# CREATE USER jack PASSWORD '********';
  
  --在test_db1创建表test_tbl1。
  gaussdb=# \c test_db1
  test_db1=# CREATE TABLE test_tbl1(c1 int,c2 int);
  
  --切换至jack用户查看pg_tables。
  test_db1=# SET ROLE jack PASSWORD '********';
  test_db1=> SELECT tablename FROM pg_tables WHERE tablename = 'test_tbl1';
   tablename 
  -----------
   test_tbl1
  (1 row)
  
  --修改对象隔离属性。
  test_db1=> SET ROLE scott PASSWORD '********';              
  test_db1=> ALTER DATABASE test_db1 ENABLE PRIVATE OBJECT;
  
  --切换至jack用户查看pg_tables。
  test_db1=> SET ROLE jack PASSWORD '********';
  
  --由于隔离属性的原因，该查询只能查出0条数据。
  test_db1=> SELECT tablename FROM pg_tables WHERE tablename = 'test_tbl1';
   tablename 
  -----------
  (0 rows)
  
  --切换至默认用户删除。
  test_db1=> RESET ROLE;
  test_db1=# DROP TABLE public.test_tbl1;
  
  --切换至默认数据库，请根据实际情况修改数据库名字。
  test_db1=# \c postgres
  gaussdb=# DROP DATABASE test_db1;
  gaussdb=# DROP DATABASE testdb2;
  gaussdb=# DROP TABLESPACE tbs_data1;
  gaussdb=# DROP USER jack;
  gaussdb=# DROP USER scott;

相关链接

CREATE DATABASE，DROP DATABASE