本文主要介绍了pgsql数据库的参数配置及生效方式.

---

概述

pgSQL数据库的服务具有以下特点：

• 多数操作系统上，pgSQL是被作为一种服务（或叫守护进程）来安装的
• 多个pgSQL服务可以运行在同一台服务器上
• 不同的pgSQL服务之间侦听的端口不能重复，也不能共享同一个数据存储目录
• 多数情况下，在一台服务器上只会安装一个pgSQL服务
• 一个pgSQL服务可以包含多个独立的database

pg_ctl

start:启动

stop:关闭

​ -m 参数，可以用来指定断开的方式。

​ smart: 等到所有客户端断开后再关闭，且期间不允许新的用户连接,同时也会等待正在进行的备份完成。

​ fase: 默认方式，立即关闭所有连接，并对未断开的连接进行回滚操作,如果服务器处于备份状态,则终止此次备份。

​ immediate:强制模式，立即中止服务的所有进程，因此重启时会导致服务会使用wal进行恢复。

reload:重新加载

restart: 重启

statu: 查看服务的状态

服务配置

pgSQL常见的配置文件及作用(位于cluster的目录下):

文件名	主要作用
pg_hba.conf	客户端访问认证文件
postgresql.auto.conf	保存ALTER SYSTEM修改后的参数不要手动修改它优先级较高
postgresql.conf	主要配置文件
recovery.conf	基于wal日志恢复的配置文件
pg_ident.conf	配置哪些操作系统用户可以映射为数据库用户

pg_hba.conf

# TYPE	DATABASE	USER	ADDRESS	METHOD
local	database	user		auth-method [auth-options]
host	database	user	address	auth-method [auth-options]
hostssl	database	user	address	auth-method [auth-options]
hostnossl	database	user	address	auth-method [auth-options]
host	database	user	IP-address IP-mask	auth-method [auth-options]
hostssl	database	user	IP-address IP-mask	auth-method [auth-options]
hostnossl	database	user	IP-address IP-mask	auth-method [auth-options]

type

• local:本地用户,将使用unix套接字连接
• host:远程用户,tcp/ip连接,ssl无论是否开启都可以
• hostssl:远程用户,tcp/ip连接,且开启ssl
• hostnosll:远程用户,tcp/ip连接,且不开启ssl

database:可以是数据库名称或者replication(流复制)

ADDRESS:

​ 例如: 192.168.1.10/32 代表只允许 192.168.1.10这台机器访问

​ 192.168.1.0/24 代表只允许 192.168.1.x 的机器访问

method:代表认证的方式,常见的认证方式包括:

• trust:无条件连接,不需要口令
• reject:无条件拒绝连接
• MD5:密码通过md5加密
• password:明文口令
• 其他.....ident,peer,cert等

postgresql.conf

配置	介绍
listen_addresses = '*'	默认为localhost,只允许本地连接<br/>0.0.0.0 代表所有ipv4地址<br/>:: 代表所有ipv6地址
port=5432	端口,默认为5432
max_connections=100	最大连接数(建议一个cpu核心50个链接)
shared_buffers	数据缓冲区的内存大小,默认为128M,,建议为内存的1/4,如果超过内存的40%可能会因为文件系统的缓存导致双缓存过多,从而影响性能
wal_buffers	还未写入磁盘的 WAL 数据的共享内存量,默认为-1,代表为shared_buffers的1/32
work_mem	写到临时磁盘文件之前用于内部排序操作和哈希表的内存量(单会话所占用),如果超过该量,则系统会使用临时文件.work_mem = (输入内存数量- shared_buffers)/(连接数 3） 1024 (单位是 KB);
maintenance_work_mem	维护数据库时(VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作),占用的内存大小. 设置的较大会提高VACUUM数据库和从dump文件中恢复数据库的速度,且此类操作很少执行,一般建议设置大店,但如果设置了 auto vacuum ,每次触发都会分配内存,因此也不建议设置太大. 最好是在每次进行操作时,使用set 命令临时调整该参数的大小.

​

参数的级别

参数设置级别	参数存储位置
cluster	postgresql.conf or postgresql.auto.conf
db	pg_db_role_setting
role	pg_db_role_setting
db 和 role的组合	pg_db_role_setting

参数修改的方式（全局）

1. 编辑配置文件
2. 启动时设置（不推荐）
3. psql -c [参数]=[值]

4. ALTER SYSTEM 语句（修改的是postgresql.auto.conf）

   ALTER SYSTEM SET 配置参数 { TO | = } { 值 | '值' | DEFAULT }
   ALTER SYSTEM RESET 配置参数
   ALTER SYSTEM RESET ALL 

配置修改的方式（非全局）

配置生效级别	修改方式
Database	ALTER DATABASE name SET configparameter { TO \	= } { value \	DEFAULT }ALTER DATABASE name RESET configuration
Session	通过SET命令设置当前Session的配置SET configparameter { TO \	= } { value \	'value' \	DEFAULT } SET configparameter TO DEFAULT; 更新pg_settings视图UPDATE pg_settings SET setting = new_value WHERE name = 'configparameter'; 使用set_config函数更新会话配置 SELECT set_config('configparameter',new_value,false);
user/Role	ALTER ROLE name [ IN DATABASE database_name ] SET configparameter { TO \	= } { value \	DEFAULT } 重置/取消这些参数的设置ALTER ROLE name [ IN DATABASE database_name ] RESET configparameter ALTER ROLE name [ IN DATABASE database_name ] RESET ALL;

参数的类型

参数主要包括以下6种

Context（上下文）	生效方式
sihup	给服务器发送HUP信号会使服务器重新加载postgresql.conf配置，可以立即生效
postmaster	只有服务重启才能生效
internal	编译期间的设置，只有重新编译才能生效。
backend	与sighup类似，但是不影响正在运行的会话，只在新会话中生效
superuser	使用superuser(如postgres)才能更改，不用重新加载所有配置即可生效
user	单个会话用户可以在任意时间做修改，只会影响该会话

配置生效的方式

1. 重启生效

   pg_ctl restart

2. 重载

   pg_ctl reload

3. 由超级用户运行SQL

   SELECT pg_reload_conf()

配置的查看方式

1. show命令

   show all;                       #查看所有数据库参数的值
   show shared_buffers;   #查看某个参数的当前值(可查看当前会话值)
   select current_setting('shared_buffers');

2. 通过查询 pg_settings

   SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
   SELECT current_setting(name);