PostgreSQL 配置文件 postgresql.conf 及 postgresql.auto.conf

本文我们学习 PostgreSQL 配置文件 ，了解配置文件为什么要有两个、如何修改配置等内容以及修改配置文件缺省位置。

两个配置文件

我们对 postgresql.conf 不陌生，但PostgreSQL在 9.4及以上版本还提供了 postgresql.auto.conf 配置文件。那么为什么要两个配置文件呢？

当我们打开 postgresql.conf 查看内容，会看到非常多的参数。其他大多数被注释了，意味着自动被赋予缺省值。我们可以去掉注释编辑特定值，但这样比较难以跟踪变化。

为此，PostgreSQL提供了类似Oracle的命令 ALTER SYSTEM 修改服务器参数并被持久化，这样最新的修改即使在下次重启也会生效。

postgresql.conf位置迁移

首先通过下面命令查找配置文件位置：

$ psql -c "show config_file"
config_file
----------------------------------------
/var/lib/pgsql/13/data/postgresql.conf

默认配置文件和数据文件位置一致，但在不同操作系统上有差异。有时我们希望修改默认位置，可能是因为下面几个原因：

• 保持目录的一致性（相对于不同OS或环境）
• 团队约定
• 配置文件或脚本为了方便备份

下面介绍如何修改配置文件的位置，但要提醒的是：配置文件要和数据文件一起备份。

1. 停止PostgreSQL服务

   pg_ctl -D $PGDATA stop -mf
   

2. 创建新的配置文件目录并赋权限

$ sudo mkdir -p /pgconfigs
$ sudo chown postgres:postgres /pgconfigs
$ sudo chmod -R 700 /pgconfigs

3. 移动配置文件

mv $PGDATA/postgresql.conf /pgconfigs/postgresql.conf

4. 使用新的配置启动数据库

$ pg_ctl -D $PGDATA -o '--config-file=/pgconfigs/postgresql.conf' start

5. 验证配置文件

$ psql -c "show config_file"
config_file
----------------------------
/pgconfigs/postgresql.conf
(1 row)

postgresql.auto.conf

该文件保存最新的配置，当数据库服务重启时，postgresql.auto.conf 配置优先。当手动修改 postgresql.conf 文件时要非常小心，因为当在两个文件中都存在相同参数，前者修改无效。

当执行 ALTER SYSTEM … 命令时，改变会被自动写入 postgresql.auto.conf 文件，而不是 postgresql.conf文件。通过这种方法，即使几个月或几年之后，也能看到参数修改变化。

下面示例修改 work_mem 并加载：

$ psql -c "ALTER SYSTEM SET work_mem TO '8MB'"
$ pg_ctl -D $PGDATA reload
-- 或者
$ psql -c "select pg_reload_conf()"

为了判断参数修改后是否要重启服务，请看下面示例：

修改参数：

$ psql -c "ALTER SYSTEM SET shared_buffers TO '512MB'"

加载参数：

$ pg_ctl -D $PGDATA reload

查询 pg_setting 判断是否要重启:

$ psql -c "select name, setting, pending_restart from pg_settings
where name = 'shared_buffers'"
name | setting | pending_restart
----------------+---------+-----------------
shared_buffers | 16384 | t
(1 row)

我们看到 shared_buffers 参数正在等待重启，这意味着执行修改需要修改。最后执行重启命令：

$ pg_ctl -D $PGDATA restart -mf

总结

本文我们学习了 PostgreSQL 两个配置文件 postgresql.conf 及 postgresql.auto.conf ，一般修改参数尽可能通过postgresql.auto.conf 文件进行修改。