PostgreSQL教程（3）数据库常用参数与配置方法

一、PostgreSQL 配置文件介绍

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

· 静态参数文件：在PGSQL中静态参数由postgresql.conf文件控制，它也是PGSQL的主配置文件，可以配置服务端口、日志、内存等核心参数。网上有专门的配置生成器（如https://pgtune.leopard.in.ua）用于快速配置PGSQL的关键参数。该配置文件中的参数如果进行了修改，需要重新加载才会生效。 

· 动态参数文件：在PGSQL中动态参数由postgresql.auto.conf文件控制，该文件是通过 ALTER SYSTEM SET 语句进行参数调整后自动生成。该文件里的配置项会覆盖静态参数文件postgresql.conf中的配置。 

· 用户自定义参数文件：在PGSQL中用户自定义参数文件由postgresql.conf.user文件控制，该文件不是必须的，如果需要开启这个文件的话需要现在静态参数文件中声明。 

二、PostgreSQL 参数管理

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

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

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

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

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

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

1、查看参数

在PGSQL中通过show语句可以打印出指定参数和其值

#语法格式
show $parameter
  
#查看work_mem参数
show work_mem

2、修改参数

· 静态配置

通过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      #是否打开日志收集器，只有开启才会记录数据库错误信息、慢查询信息到日志文件
log_destination = 'csvlog'    #日志输出格式，默认为stderr标准输出,可配置为csvlog、syslog、eventlog
log_directory = 'logs'      #日志存放目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  #日志文件命名格式
log_rotation_age = 1d       #日志切割时间，这里表示每天生成一个日志文件
log_rotation_size = 1GB      #日志文件大小，当达到该值时进行切割并生成新的日志文件
log_truncate_on_rotation = off   #日志是否覆盖，进行循环使用
log_min_duration_statement = 2000   #定义慢查询语句的阈值，超过该值的语句会被记录到日志，单位为毫秒

#预写日志
wal_level = replica  #WAL日志分为minamal、logical、replica三个级别，默认replica支持归档和复制，生产环境建议至少设置为replica
max_wal_size = 50GB  #所有日志文件大小之和可占用的最大磁盘空间，每个文件大小默认为16M，超出该值才会触发日志轮询覆盖
min_wal_size = 512MB  #wal日志最小占用的硬盘空间，只要不超过这个值，WAL日志都不会被删除而是复写

#审计日志
log_statement = 'ddl'  #是否在日志中记录SQL，默认为none（不记录），支持为all（所有语句）、ddl、mod（ddl+dml）、none

#归档日志
archive_mode = on    #开启归档日志，可选值为on / off / always
#archive_command='/usr/bin/true'    #归档时需要额外执行的命令

#自动清理进程设置
autovacuum = on    #开启autovacuum守护进程，默认开启
autovacuum_vacuum_scale_factor = 0.3      #表中产生了多少比例的 dead tuples会触发 autovacuum，默认0.2表示20%
autovacuum_analyze_scale_factor = 0.2    #表中有多少数据行执行了DML操作会触发 autovacuum，默认0.1表示10%
log_autovacuum_min_duration = 1000  #当autovacuum达到多少毫秒时记录到日志中
autovacuum_max_workers =  2    #自动清理最大进程数
autovacuum_naptime = 1min    #自动清理时间间隔    

#内存参数
shared_buffers = 4096MB    #PGSQL共享内存空间，类似MySQL中的innodb_buffer_pool_size
work_mem = 4MB    #为每个会话独立分配的内存空间，用于分组、排序、聚合操作，超过该内存空间后将发生写入临时磁盘
maintenance_work_mem = 64MB    #对数据库进行维护性操作时可用内存，分配高一点可以提高如ALTER TABLE、CREATE INDEX、VACUUM等操作的效率
wal_buffer = 32MB        #存储预写日志的内存空间
effective_cache_size = 32G  #存放优化器相关数据的空间，该值用于告诉优化器额外还可使用的内存大小，便于有效选择最佳的执行计划

#磁盘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个插件用于密码强度

· 动态配置

通过SET语句可以进行session级别的参数修改，通过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;

3、重新加载配置

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

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

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

postgres=# select pg_reload_conf();

数据库postgresqlpgsql