本文的大部分内容摘录自<PostgreSQL即学即用>这本书籍.

1.配置文件

postgresql.conf: 该文件包含一些通用设置,比如内存分配,新建数据库的存储位置,postgreSQL服务器的IP地址,日志位置以及许多其他的全局配置.9.4版本引入了auto.conf文件,任何时候执行ALTER SYSTEM SQL命令,都会创建或者重写改文件,该文件的设置会替代postgresql.conf文件的设置.

pg_hba.conf: 该文件用于访问控制的安全性,管理客户端对PostgreSQL服务器的访问权限.包括:允许哪些用户连接到哪个数据库,允许哪些Ip或者哪个网段的IP连接到本服务器,以及连接制定使用的身份验证模式.

pgident.conf: 如果pghba.conf的权限控制信息中的身份验证模式如果指定为ident方式,则用户连接时系统会尝试访问pg_ident文件,如果该文件存在,则系统会基于文件内容将当前登录操作的操作系统用户映射为一个postgreSQL数据库内部用户身份来登录.

查询配置文件的位置:

SELECT name,setting from pg_settings where category='File Locations'

通过查询pg_settings视图可以很方便检查当前设置.

查询一些关键的设置:

SELECT name,context,unit,setting,boot_val,reset_val FROM pg_settings where name in
('listen_address','max_connections','shared_buffers','effevtive_cache_size','work_mem') 
order by context,name

如果将context设置为postmaster,那么更改后此形参可能需要重启postgreSQL生效.如果将其设置为user,那么只需要执行一次重新加载即可全局生效.重启数据库服务会终止活动连接,但是重新加载不会.

setting是指当前设置;bootval是指默认设置,rest_val是指重新启动或者加载设置后的新设置.

shared_buffer:此设置定义了用于缓存最近访问过的数据页的内存区大小.所有用户的session均共享此缓冲区.此设置对查询速度有重大的影响,一般来说越大越好,至少应该达到系统总内存的25%,但不宜超过8G,因为超过后会出现边际效益递减的效应.

effective_cache_size:此设置表示一个查询执行过程中可以使用的最大内存.

work_mem:此设置制定了用户执行排序,哈希关联,表扫描等操作的最大内存量.要得到此设置的最优值要考虑到以下一些因素:数据库的使用方式,要预留多少内存给除数据库系统以外的程序,以及服务器是否专用于运行postgreSQL等问题.此设置的修改可以动态生效,执行重新加载即可.一篇文章值得一读:

上述设置可以再库级,用户级以及函数级设置.如果有一个精通sql的用户在库上执行非常复杂的sql语句,那么可以单独为此用户设置更大的work_mem值.

postgreSQL 9.4版本中引入了对新的ALTER SYSTEM SQL语句的支持,使用此命令可以更改设置.比如全局设置work_mem:

ALTER SYSTEM set work_mem=8192;

实现重新加载:

SELECT pg_reload_conf();

pg_hba.conf文件示例

host all all 127.0.0.1/32 ident
host all all ::1/128 trust

身份验证模式:ident,trust,md5以及password

2.连接管理

查询活动连接列表以及进程id

SELECT * FROM pg_stat_activity;

取消连接上的活动查询:

SELECT pg_cancel_backend(procid)

终止该连接:

SELECT pg_terminate_backend(procid);

3.角色

创建具备登录权限的角色:

CREATE ROLE broad LOGIN PASSWORD 'demo' CREATE VALID UNTIL 'infinity'

创建具备超级管理员用户权限的角色:

CREATE ROLE king LOGIN PASSWORD 'king' SUPERUSER VALID UNTIL '2020-01-01 00:00:00'

创建组角色:

CREATE ROLE royalty INHERIT;

注意术语INHERIT的用法,它表示组角色royalty 的任何一个成员角色都将自动继承除超级管理员外的所有权限.处于安全考虑,postgreSQL不允许超级用户权限通过继承的方式传递.

以下语句可以将组角色的权限授予其成员角色:

GRANT royalty TO broad;

从组角色继承权限

postgreSQL有一个很奇葩的功能,就是禁止组角色将其权限授予其成员角色,该功能通过NOINHERIT关键字控制.因此创建组角色时请务必显示指定INHERIT或者NOINHERIT关键字.