PG入门（2）postgreSQL重要配置文件及参数设置

pg_hba.conf

作用

哪些主机可以连接数据库实例
哪个数据库用户可以使用它
允许这个用户使用哪些数据库
客户端使用什么连接方式和认证方式

postgresql.conf

listen_addresses = '*'  #（关联配置文件pg_hba.conf）
#指定服务器在哪些 TCP/IP 地址上监听客户端连接。
值的形式是一个逗号分隔的主机名和/或数字 IP 地址列表。 
特殊项*对应监听所有可用 IP 接口
0.0.0.0允许监听所有 IPv4 地址
并且::允许监听所有 IPv6 地址
如果列表为空，服务器将根本不会监听任何 IP 接口，在这种情况中只能使用 Unix 域套接字来连接它。
默认值是localhost，它只允许建立本地 TCP/IP "环回"连接。
这能帮助在不安全网络接口上阻止重复的恶意连接请求。
这个参数只能在服务器启动时设置。 

port = 5866
#服务器监听的 TCP 端口；默认是 5866 。

max_connections = 100
#决定数据库的最大并发连接数。
这个参数只能在服务器启动时设置。（cpu 1核 50个链接）

shared_buffers
#它表示数据缓冲区中的数据块的个数，每个数据块的大小是8KB。
数据缓冲区位于数据库的共享内存中，它越大越好，不能小于128KB。
这个参数只有在启动数据库时，才能被设置。
默认值是128MB。
推荐值：1/4 主机物理内存  

wal_buffers 
#用于还未写入磁盘的 WAL 数据的共享内存量。
默认值 -1 表示将该参数值设置为 shared_buffers 的 1/32 的大小 （ 大约 3%），
但是不小于64kB 也不大于一个WAL段的大小（通常为 16MB）。 
如果自动的选择太大或太小可以手工设置该值，但是任何小于 32kB 的正值都将被当作 32kB。 
这个参数只能在服务器启动时设置。
事务日志缓冲区位于数据库的共享内存中。
推荐值：min( 2047MB, shared_buffers/32 ) = 512MB

work_mem
#指定在写到临时磁盘文件之前用于内部排序操作和哈希表的内存量。
ORDER BY， DISTINCT 和合并连接（ merge joins） 都会用到排序操作。 
默认值为 4 兆字节（ 4MB）。
推荐值：work_mem = (输入内存数量- shared_buffers)/(连接数 * 3）* 1024 (单位是 KB);

maintenance_work_mem
#它决定数据库的维护操作使用的内存空间的大小。
数据库的维护操作包括VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY等操作。 
值如果比较大，通常可以缩短VACUUM数据库和从dump文件中恢复数据库需要的时间。
maintenance_work_mem存放在每个数据库进程的私有内存中，而不是存放在数据库的共享内存中。这个参数可以在任何时候被设置。

开启日志采集
logging_collector = on      

日志目录
log_directory = 'log'            

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  日志文件名字
log_truncate_on_rotation = off    是否覆盖
log_rotation_age = 1d             每天生成
log_rotation_size = 10MB          每个日志大小

优化建议

andom_page_cost = 2.5
规划器对一次非顺序获取磁盘页面的代价估计。 默认值是 4.0。 高端存储或者ssd可以适当调小该参数。

autovacuum_max_workers = 10
指定能同时运行的autovacuum进程的最大数量， 适当调大， 避免vacuum不及时导致表膨胀

checkpoint_completion_target = 0.7
增加checkpoint_completion_target来降低检查点的I/O负载， 默认0.5

archive_timeout =1800
强制服务器来周期性地切换到一个新的 WAL 段文件

archive_command = 'test ! -f /paic/dba/pgbackup/${PGNAME}/archlog/%f && pxz -2 < %p >
/paic/dba/pgbackup/${PGNAME}/archlog/%f'
pxz压缩归档日志， 64M的归档， 压缩时间可以在0.5s内， 压缩比一般可在1:3左右

配置参数级别

系统级别 /集群服务级别/实例级别（全局）
用户/角色级别 
用户/角色 + 数据库级别
会话级别

使配置生效的几种方法

使配置生效的几种方法
1）用超级用户运行   
 SELECT pg_reload_conf(); 
2）使用pg_ctl命令触发SIGHUP信号 
 pg_ctl reload
3）用UNIX的kill手动发起HUP信号
ps -ef|grep -i postmaster|grep -v grep|xargs kill -HUP   
4）重启数据库服务

查看配置

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

配置生效级别