PostgreSQL教程（3）数据库参数说明与配置

一、PostgreSQL 配置文件介绍

在PGSQL中如果要对参数进行修改，可以通过修改静态参数文件（postgresql.conf）、动态参数文件（postgresql.auto.conf）与自定义参数文件（postgresql.conf.user）三种方式来完成，优先级从低到高依次为 postgresql.conf --> postgresql.auto.conf --> postgresql.conf.user

二、PostgreSQL 参数区别

1、查看参数和值

#查看某个配置项
show $parameter  
show work_mem

在PGSQL中，参数分为了多个种类，不同的参数项生效的要求不同，通过pg_settings表context字段可以查询到具体参数的生效条件

· sighup：超级管理员才可以修改，需要reload才能生效

· superuser：超级管理员可以为普通用户、数据库进行修改

· postmaster：超级管理员才可以修改，需要重启才能生效

· user：普通用户可以修改，立即生效

select name,setting,context from pg_settings;   #如果context为postmaster代表需要重启服务才能生效

2、重新加载配置

· 使用pg_ctl reload，不用登录数据库就可以重新加载配置

/usr/local/postgresql/bin/pg_ctl -D /data/postgresql/data/ reload

· 使用pg_reload_conf()函数加载配置文件

postgres=# select pg_reload_conf();

三、PostgreSQL 主要参数与修改方法介绍

1、静态配置

通过vi直接对postgresql.conf进行修改，修改后需要reload才会生效

listen_addresses = '192.168.0.103'    #监听地址，默认为localhost仅允许本机连接，通常配置为0.0.0.0
port = 5432              #监听端口
max_connections = 1000         #允许的最大连接数
superuser_reserved_connections = 3   #为管理员保留的连接数，防止最大连接达到后管理员也无法登录的情况

#日志参数
logging_collector = on            #打开日志，会记录各种error信息、慢查询、服务启停等信息
log_destination = 'csvlog'    #日志格式，默认是stderr标准输出,可配置为csvlog、syslog、eventlog
logging_collector = on              #开启日志收集器，此时会启动一个logger进程
log_directory = 'logs'      #日志存放相对路径，在postgresql/data目录下
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  #日志命名格式
log_rotation_age = 1d            #每天生成一个文件
# log_rotation_size = 500M      #也可以按照日志大小来切割文件
log_truncate_on_rotation = off   #日志是否覆盖，进行循环使用
max_wal_size = 1GB
min_wal_size = 80MB  #wal日志最小占用的硬盘空间，只要不超过这个值，WAL日志都不会被删除而是复写。推荐设置为shared buffer的一半

#审计日志
log_statement = 'ddl'  #控制记录语句的类型，可配置为all（包括了管理语句）、ddl、mod（ddl+dml）、none（也可以写为off）

#归档日志
archive_mode = on    #开启归档日志
#archive_command='/usr/bin/true'    #归档时需要额外执行的命令

#慢日志
log_min_duration_statement=1000ms  #慢查询阈值，超出阈值的查询语句将被记录到 PostgreSQL 的日志文件中

#autovacuum日志
autovacuum = on #开启autovacuum守护进程
log_autovacuum_min_duration = 1000  #当autovacuum达到多少毫秒时记录到日志中    

#内存参数
shared_buffers = 512MB  #PGSQ共享内存缓存空间，建议系统内存的25%左右即可
work_mem =4M     #为每个客户端分配的一块内存空间，用于group by、order by等排序操作
maintenance_work_mem = 64MB  #对数据库进行维护性操作时可用内存，分配高一点可以提高如alter table、CREATE INDEX、VACUUM等操作的效率

#磁盘IO
effective_io_concurrency=100  #磁盘I/O操作的并发度，范围是1-1000，建议根据磁盘负载情况进行调整

#进程相关
max_worker_processes=8   #数据库最大进程数，可设置和cpu数一致。从库在进行该参数的设置时，其值不能超过主库
max_paraller_workers=8      #数据库最大并发数，可设置和cpu数一致。

#插件相关
# shared_preload_libaries = 'auth_delay,passwordcheck'   #可以指定需要加载的插件，这里的2个插件用于密码强度相关

#索引相关
effective_cache_size = 32G  #告诉查询计划预计可以使用的系统内存量，以便它能够更有效地选择最佳的执行计划。

2、动态配置

通过ALTER DATABASE SET语句进行参数修改，当语句成功执行后会创建一个postgresql.auto.conf文件并覆盖原有参数，修改后同样需要reload

#修改配置语法
#ALTER SYSTEM SET configparameter { TO | = } { value | ‘value’ | DEFAULT }  

#修改work_mem为1024MB
postgres=# ALTER SYSTEM SET work_mem=1024MB;

#查看配置是否生效
postgres=# show work_mem;

#设置database级别的配置
ALTER DATABASE dbname SET configparameter { TO | = } { value | DEFAULT }
ALTER DATABASE dbname SET configparameter FROM CURRENT
ALTER DATABASE dbname RESET configparameter
ALTER DATABASE dbname RESET ALL
 
#设置user级别的配置：
ALTER USER|ROLE {username | ALL } [ IN DATABASE dbname ] SET configparameter { TO | = } { value | DEFAULT }
ALTER USER|ROLE {username | ALL } [ IN DATABASE dbname ] SET configparameter FROM CURRENT
ALTER USER|ROLE {username | ALL } [ IN DATABASE dbname ] RESET configparameter
ALTER USER|ROLE {username | ALL } [ IN DATABASE dbname ] RESET ALL
ALTER USER tanglu set work_mem='1024MB'
 
#设置session级别的配置
SET [ SESSION | LOCAL ] TIME ZONE { 时区 | LOCAL | DEFAULT }

通过ALTER SYSTEM RESET 语句可以对动态配置还原，该操作会清空postgresql.auto.conf中的配置，以配置文件postgresql.conf为准 

ALTER SYSTEM RESET work_mem = default;
ALTER SYSTEM RESET work_mem to default;
ALTER SYSTEM RESET work_mem ;
ALTER SYSTEM RESET all;

#重新加载配置文件
postgres=# select pg_reload_conf(); 

#查看配置，会以postgresql.conf中的为准 
postgres=# show work_mem;