本文主要介绍了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 |
参数修改的方式（全局）
编辑配置文件
启动时设置（不推荐）
psql -c [参数]=[值]
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 | 单个会话用户可以在任意时间做修改，只会影响该会话 |
配置生效的方式
重启生效
pg_ctl restart
重载
pg_ctl reload
由超级用户运行SQL
SELECT pg_reload_conf()
配置的查看方式
show命令
show all; #查看所有数据库参数的值
show shared_buffers; #查看某个参数的当前值(可查看当前会话值)
select current_setting('shared_buffers');
通过查询 pg_settings
SELECT name,setting FROM pg_settings where name ~ ‘xxx’;
SELECT current_setting(name);
