ALTER DATABASE

功能描述

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

注意事项

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

语法格式

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

  ALTER DATABASE database_name 
      [ WITH ] CONNECTION LIMIT connlimit;

• 修改数据库名称。

  ALTER DATABASE database_name 
      RENAME TO new_name;

  

  禁止重命名templatea。

• 修改数据库所有者。

  ALTER DATABASE database_name 
      OWNER TO new_owner;

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

  ALTER DATABASE database_name 
      SET TABLESPACE new_tablespace;

  

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

• 修改数据库指定会话参数值。

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

• 数据库配置参数重置。

  ALTER DATABASE database_name RESET 
      { configuration_parameter | ALL };

参数说明

• database_name

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

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

• connlimit

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

  取值范围：[-1, 2^31-1]的整数，建议填写1~50的整数。-1（缺省）表示没有限制。

• new_name

  数据库的新名称。

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

• new_owner

  数据库的新所有者。

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

• new_tablespace

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

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

• configuration_parameter
  • value

    把指定的数据库会话参数值设置为给定的值。如果value是DEFAULT或者RESET，则在新的会话中使用系统的缺省设置。OFF关闭设置。

    取值范围：字符串。

    • DEFAULT
    • OFF
    • RESET
    • 用户指定的值：需要满足修改参数的取值限制。
  • FROM CURRENT

    取当前会话中的值设置为configuration_parameter的值。

• time_zone

  设置database_name的数据库的时区值，需要有对应的数据库的权限。

  取值范围：字符串。

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

  重置指定的数据库会话参数值。

• RESET ALL

  重置全部的数据库会话参数值。

• 修改数据库默认表空间，会将旧表空间中的所有表和索引转移到新表空间中，该操作不会影响其他非默认表空间中的表和索引。
• 修改的数据库会话参数值，将在下一次会话中生效。

示例

• 修改数据库名称。

  --创建数据库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, pg_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)

• 修改数据库时区。前提条件：当前连接数据库为A兼容数据库、a_format_version='10c'、a_format_dev_version='s2'。

  --查看数据库test_db1时区。
  gaussdb=# SELECT datname,datcompatibility,dattimezone FROM pg_database WHERE datname = 'test_db1';
   datname  | datcompatibility | dattimezone 
  ----------+------------------+-------------
   test_db1 | A            | PRC
  
  --设置参数后修改数据库时区为'+08:00'。
  gaussdb=# SET a_format_version='10c';
  gaussdb=# SET a_format_dev_version='s2';
  gaussdb=# ALTER DATABASE test_db1 SET DBTIMEZONE = '+08:00';
  
  --查看数据库时区。
  gaussdb=# SELECT datname,datcompatibility,dattimezone FROM pg_database WHERE datname = 'test_db1';
   datname | datcompatibility | dattimezone 
  ---------+------------------+-------------
   test_db1| A              | +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 TABLESPACE tbs_data1;
  gaussdb=# DROP USER jack;
  gaussdb=# DROP USER scott;

相关链接

CREATE DATABASE，DROP DATABASE。