PostgreSQL（九）数据库集簇管理

目录

一、数据库结构

1、数据库集簇逻辑结构

2、数据库属主

二、数据库级权限管理

1、DB权限及语法说明

2、回收默认的数据库连接权限

三、数据库级参数配置

四、数据库级属性修改

---

一、数据库结构

1、数据库集簇逻辑结构

每个db存储的对象（表、索引、视图…）是独立、私有的，每个数据库类似房间，放房间中取东西就需要到房间里；同理，要访问某个数据库中的对象，就需要登录到指定的数据库中。

数据库集簇是由 PostgresqL服务器管理的数据库的集合，也名数据库集群（ Postgresql中的“数据库集群”一词并不意味着“一组数据库服务器”，而是 Postgresql服务器在单个主机上运行并管理的单个数据库群集）。

数据库是数据库对象的集合。在关系数据库理论中，数据库对象是用来存储或引用数据的数据结构。堆(heap)表是一个典型的例子，它有很多类似于索引、序列、视图、函数等等。在 Postgresql中，数据库本身也是数据库对象，在逻辑上彼此分离。所有其他数据库对象(如表、索引等)都属于各自的数据库。

2、数据库属主

（1）Postgres中的数据库属主属于创建者，只要有 createdb的权限就可以创建数据库。

（2）数据库属主不一定拥有存放在该数据库中其它用户创建的对象的访问权限，例如：数据库A的属主为用户a，假设用户b在数据库A中创建了对象x，用户a对对象x并不拥有访问权等权限，依然需要授权。

（3）数据库在创建后，允许public角色连接，即允许任何人连接（可以回收，详见权限管理段落）。

（4）数据库在创建后，不允许除了超级用户和 owner之外的任何人在数据库中创建schema。

（5）数据库在创建后，会自动创建名为public的 schema，这个 schema的all权限已经赋予给了public角色，即允许任何人在里面创建对象，但对己存在的其它用户的表不具有任何权限。

二、数据库级权限管理

1、DB权限及语法说明

CREATE：可以在指定数据库创建 schema的权限
CONNECT：可以连接到指定数据库的权限
TEMPORARY：可以创建临时表的权限
ALL：指定数据库所有的权限

//语法
GRANT { { CREATE | CONNECT I TEMPORARY I TEMP} […] I ALL PRIVILEGES ] }
ON DATABASE $dbz-name [,…]
TO $role_specification [,…] [ WITH GRANT OPTION ];

2、回收默认的数据库连接权限

由于数据库在创建后允许public角色连接，即允许任何人连接。所以，如果要取消某个用户对指定数据库连接的权限，需要先取消 public的连接权限，再取消该用户的连接权限。

//授权用户连接数据库的权限
grant connect on database $db_name to $user_name;

//撤销用户连接数据库的权限
revoke connect on database $db_name from public;
revoke connect on database $db_name from $user_name;

//查看哪些用户有某个数据库的connect权限
select datname,datacl from pg_database where datname=’$db_name’;

三、数据库级参数配置

Postgresql是参数设置分为实例级、数据库级、用户级和会话级，而有些参数可以在所有级别中设置;

优先级顺序为：会话级>用户级>数据库级>实例级。

数据库参数配置语法：

//设置参数内容
ALTER DATABASE $db_name SET $parameter_name  {TO | = }{值 | DEFAULT };
ALTER DATABASE $db_name SET $parameter_name FROM CURRENT;

//重置参数为默认值
ALTER DATABASE $db_name RESET $parameter_name;
ALTER DATABASE $db_name RESET ALL;



e.g.:
//设置数据库搜索路径
alter database postgres set search_path to"$user",public, schema_name;
//配置连接某个库时可使用的工作内存
alter database postgres set work_mem = ‘8MB’;
//配置连接某个库时可使用的维护内存
alter database postgres set maintenance_work_mem to ‘256MB’

//配置连接某个库后使用的时区
alter database postgres set TimeZone to cet;
alter database postgres set Date Style to SQL,DMY;      (重新登录生效)
//配置连接某个库后执行语句最多时长(执行1秒超时)
alter database postgres set statement timeout =1000
//配置连接某个库后默认的客户端编码,配置客户端编码为gbk,适用于数据库编码为ut8,应用程序编码为gk的应用
alter database postgres set client encoding to gbk;

//配置某个库使用日志记录级别(none：对这个数据库的访问不记录日志)
alter database postgres set log_statement=none;
//配置连接某个库后的wal日志写盘级别(local：该库的更新操作只要求本地提交)
alter database postgres set synchronous_commit to local;
//配置连接某个库后禁用某个规划器(禁用 indexonlyscan扫描)
alter database postgres set enable_indexonlyscan to off;

//配置连接某个库后执行出错时中断连接(对新会话生效)
alter database postgres set exit_on_error to on;
--重新连接后
select pg_backend_pid();
--执行错误会导致连接中断
select * from d1;

//查看所有个性化配置
\drds
//查询数据库的连接数限制,只能查看数据字典表
select datname,datconnlimit from pg_database;
//设置某个个性化设置为默认值
ALTER DATABASE postgres reset exit_on_error;
//设置所有个性化设置为默认值
ALTER DATABASE postgres reset ALL;

四、数据库级属性修改

数据库的属性我们可以进行修改,修改范围是数据库名字、属主、表空间，语法如下：

//改库名
ALTER DATABASE $db_name RENAME TO $new_db_name;
//改属主
ALTER DATABASE $db_name OWNER TO { $new_owner | CURRENT_USER | SESSION_USER }; 
//改表空间
ALTER DATABASE $db_name SET TABLESPACE $new_tablespace;