更新时间：2023 年 9 月
版本：PostgreSQL 16
参考：PostgreSQL: Documentation: 16: Chapter 20. Server Configuration
目录
数据目录结构
$ ls -lF /data/pgsql/16/
total 68
drwx------. 5 postgres postgres 33 Sep 16 23:25 base/
-rw-------. 1 postgres postgres 30 Sep 16 23:28 current_logfiles
drwx------. 2 postgres postgres 4096 Sep 16 23:30 global/
drwx------. 2 postgres postgres 32 Sep 16 23:28 log/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_commit_ts/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_dynshmem/
-rw-------. 1 postgres postgres 5499 Sep 16 23:25 pg_hba.conf
-rw-------. 1 postgres postgres 2640 Sep 16 23:25 pg_ident.conf
drwx------. 4 postgres postgres 68 Sep 16 23:25 pg_logical/
drwx------. 4 postgres postgres 36 Sep 16 23:25 pg_multixact/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_notify/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_replslot/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_serial/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_snapshots/
drwx------. 2 postgres postgres 6 Sep 16 23:28 pg_stat/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_stat_tmp/
drwx------. 2 postgres postgres 18 Sep 16 23:25 pg_subtrans/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_tblspc/
drwx------. 2 postgres postgres 6 Sep 16 23:25 pg_twophase/
-rw-------. 1 postgres postgres 3 Sep 16 23:25 PG_VERSION
drwx------. 3 postgres postgres 60 Sep 16 23:25 pg_wal/
drwx------. 2 postgres postgres 18 Sep 16 23:25 pg_xact/
-rw-------. 1 postgres postgres 88 Sep 16 23:25 postgresql.auto.conf
-rw-------. 1 postgres postgres 29689 Sep 16 23:26 postgresql.conf
-rw-------. 1 postgres postgres 49 Sep 16 23:28 postmaster.opts
-rw-------. 1 postgres postgres 88 Sep 16 23:28 postmaster.pid
各个目录作用
-
base：存储用户创建的数据库文件，以及属于用户数据库的所有关系，比如表、索引等 -
global：存储pg_control及数据库集群维度的数据库及其关系，非用户维度的数据。目录内的文件结构和base一致 -
log：日志存放的默认路径，实际取决于配置 -
pg_commit_ts：存储事务提交时间戳 -
pg_dynshmem：存储动态共享内存的状态 -
pg_logical：存储逻辑复制的信息 -
pg_multixact：用于存储多事务（MultiXact）数据的地方。当一个事务需要锁定多个行，并且这些行已经被其他事务锁定时，就会涉及到多事务（MultiXact） -
pg_notify：存储数据库中异步通知队列的信息。这个目录是LISTEN/NOTIFY机制的一部分 -
pg_replslot：存储逻辑或物理复制槽的信息。复制槽是一个持久的数据结构，用于跟踪 PostgreSQL 主数据库（发布者）与其复制节点（订阅者）之间的复制进度 -
pg_serial：存储序列化冲突的信息。这种冲突主要发生在使用SERIALIZABLE事务隔离级别时（较少使用） -
pg_snapshots：存储数据库的某个时间点快照信息，比如并行查询执行或某些类型的备份操作。由数据库系统自己管理和使用 -
pg_stat：存储统计信息 -
pg_stat_tmp：存储临时统计数据 -
pg_subtrans：存储子事务（也称为嵌套事务或保存点）的信息。这个目录主要用于管理和解决涉及子事务的复杂事务场景 -
pg_tblspc：用于管理表空间（Tablespaces）的符号链接。表空间是用于存储数据库对象（如表和索引）的物理位置 -
pg_twophase：用于存储两阶段提交（Two-Phase Commit, 2PC）的事务信息。两阶段提交是一个分布式事务管理的机制，用于确保多个数据库或系统间的事务能够同步地提交或回滚 -
pg_wal：WAL 机制中的 wal 日志存储目录 -
pg_xact：事务提交日志（Commit Log）的存储目录。事务提交日志默认 256 KB
各个文件的作用
current_logfiles：记录当前日志文件的信息pg_hba.conf：认证配置文件，配置了认证方式等信息pg_ident.conf：ident认证方式的用户映射文件PG_VERSION：数据库版本号信息postgresql.auto.conf：数据库实例配置文件，作用同postgresql.conf，但优先级高于postgresql.conf，在数据库中通过ALTER SYSTEM命令更改的参数会记录在此文件中。不推荐手动修改该文件postgresql.conf：数据库实例主配置文件，基本上所有的数据库参数配置都在此文件中postmaster.opts：记录数据库实例的启动命令postmaster.pid：进程文件，启动实例时创建，关闭时删除
参数查询与配置
查询参数
PG 提供了很多查询参数的方式，常用的有
- 使用
SHOW命令 - 查询
pg_settings视图 - 使用
current_setting()函数
使用 SHOW 命令
SHOW 命令是最简单的方式，适用于查询会话级别或全局级别的参数
-- 格式
SHOW parameter_name;
-- 示例：查询当前数据库的搜索路径
postgres=# SHOW search_path;
search_path
-----------------
"$user", public
(1 row)
查询 pg_settings 视图
pg_settings 视图包含了索引系统设置的详细信息
-- 格式
SELECT * FROM pg_settings WHERE name = 'parameter_name';
-- 示例：查询 max_connections 参数
postgres=# SELECT name,setting,boot_val,reset_val FROM pg_settings WHERE name = 'max_connections';
name | setting | boot_val | reset_val
-----------------+---------+----------+-----------
max_connections | 500 | 100 | 500
(1 row)
常用字段：
name：参数名称setting：当前的参数值min_val：参数允许的最小值max_val：参数允许的最大值boot_val：参数的引导（启动）默认值reset_val：当参数被重置时，它会回到这个值sourcefile：参数设置所在的配置文件路径pending_restart：如果为true，表示更改此参数需要重新启动数据库实例
使用 current_setting() 函数
current_setting() 函数可以在 SQL 查询中嵌入，使用比较灵活
-- 格式
SELECT current_setting('parameter_name');
-- 示例：查询 work_mem 参数
postgres=# SELECT current_setting('work_mem');
current_setting
-----------------
4MB
(1 row)
修改参数
PG 所有参数名称都不区分大小写，且支持通过多种方式修改实例参数
- 通过配置文件修改参数
- 通过 SQL 修改参数
- 通过 shell 修改参数
通过配置文件修改参数
设置参数最简单的方法就是编辑文件 postgresql.conf，该文件通常保存在数据目录中。以这种方式设置的参数将为集群提供默认值
配置文件参数设置后并不会立即生效，需要手动进行动态加载，加载方式有：
- 向服务器主进程发送 SIGHUP 信号；例：
/bin/kill -HUP $MAINPID - 命令行运行
pg_ctl reload；例：/usr/pgsql-16/bin/pg_ctl reload -D /data/pgsql/16/ - 调用 SQL 函数
pg_reload_conf()；例：SELECT pg_reload_conf();
某些参数只能在服务器启动时设置，这些参数无法动态加载
通过 SQL 修改参数
PostgreSQL 提供了三个 SQL 命令来修改参数，分别对应了三个层面
ALTER SYSTEM：修改全局默认值，实际修改的是postgresql.auto.conf配置文件，需要重新加载配置才会生效ALTER DATABASE database_name：在数据库层面重写全局设置；重新连接会话生效ALTER ROLE role_name：针对用户特定的参数值设置，会覆盖全局设置和数据库设置；重新连接会话生效
注意：
ALTER SYSTEM命令通常需要超级用户权限。ALTER DATABASE和ALTER ROLE仅影响特定数据库或特定用户的新会话；它们不会更改当前活动的会话。- 不是所有参数都可以在所有级别上设置
- 某些参数只能在服务器启动时设置，这些参数无法通过 SQL 进行修改
修改方式
使用 SET 命令修改参数
-- 格式
SET parameter_name TO 'parameter_value'
等同于
-- 格式
UPDATE pg_settings SET setting = 'parameter_value' WHERE name = 'parameter_name';
示例
修改最大连接数（ALTER SYSTEM）
postgres=# SHOW work_mem;
work_mem
----------
4MB
-- 修改
postgres=# ALTER SYSTEM SET work_mem TO '64MB';
ALTER SYSTEM
-- 可以看到配置并未生效，需要重新加载配置
postgres=# SHOW work_mem;
work_mem
----------
4MB
-- 重新加载配置
postgres=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
-- 已生效
postgres=# SHOW work_mem;
work_mem
----------
64MB
其他层级示例
ALTER DATABASE my_db SET work_mem = '16MB';
ALTER ROLE john SET work_mem = '64MB';
通过 shell 修改参数
可以通过 shell 将设置传递给 PostgreSQL，有两种方式
- 在服务器启动时传入参数
- 在连接会话时传入参数
在服务器启动时传入参数
在服务器启动期间，可以通过命令行参数将 -c 参数设置传递给 postgres 命令
$ postgres -c log_connections=yes -c log_destination='syslog'
这种方式提供的设置将覆盖通过设置 postgresql.conf 或 ALTER SYSTEM 的设置，因此如果不重新启动服务器，则无法全局修改这些参数
在连接会话时传入参数
打开会话时，可以使用 PGOPTIONS 环境变量指定参数设置。以这种方式建立的设置将作为会话生存期的默认值，但不会影响其他会话
$ env PGOPTIONS="-c geqo=off -c statement_timeout=5min" psql
postgresql.conf
PostgreSQL 初始化完成后，会在 $PGDATA 变量指定的路径下生成 postgresql.conf 配置文件