PostgreSQL数据库配置

修改配置参数

这里演示在postgresql.conf文件里修改log_directory参数改变日志的默认存储位置。

1. 用 vim 打开 postgresql.conf
2. 在命令行模式输入\log_directory命令查找该参数，然后进入i模式修改位置,**然后删除注释符#**，保存退出
3. 然后重载配置，执行pg_ctl reload 或者使用

   SELECT pg_reload_conf();

4. 可以使用命令

   SHOW log_directory;

   来查询是否已经更改。

⚠️log_directory 这个参数的作用是：
指定日志收集器（logging_collector）将日志文件保存到哪里。
但是前提是：logging_collector = on
若 collector 关闭（off），该参数不会生效。
日志流会直接送到 stderr 或由 systemd 捕获。

因此还要将logging_collector设置为 on,步骤和上面相似，不同的是设置完之后需要重启而不是重载。因此执行sudo systemctl restart postgresql重启。
原因如下：

有些参数支持“reload”（重新加载），有些则需要“restart”。

查看参数是否支持 reload：

SELECT name, context 
FROM pg_settings 
WHERE name = 'log_directory' OR name = 'logging_collector';

可以看到如下结果：

name        |  context   
-------------------+------------
 log_directory     | sighup
 logging_collector | postmaster
(2 rows)

• sighup 表示只需 重载 就能生效。
• postmaster 则表示这个参数需要 重启数据库服务 才能生效。

---

错误报告与日志系统（Error Reporting & Logging）

日志系统作用：

功能	示例
错误与警告报告	查询语法错误、权限不足、约束失败
安全与访问审计	登录失败、拒绝连接
性能监控	慢查询日志、自动VACUUM信息
调试与开发	显示执行计划、函数报错
系统运维	记录后台进程事件（如 checkpoint、autovacuum）

常用日志相关参数(控制日志系统能记录什么)：

参数名	说明	示例/建议
log_destination	决定 PostgreSQL 日志输出的目标	默认值通常是：stderr。可选csvlog以CSV格式保存日志文件
logging_collector	是否启用日志收集	on
log_directory	日志存放目录（相对数据目录）	log 或 /var/log/postgresql
log_filename	日志文件命名规则	postgresql-%Y-%m-%d_%H%M%S.log
log_rotation_age	按时间轮转日志	1d
log_rotation_size	按文件大小轮转	10MB
log_truncate_on_rotation	是否覆盖旧日志	on（避免日志无限增长）
log_min_messages	最低记录的消息级别	warning（默认）
log_min_error_statement	记录出错 SQL 语句	error

有趣的是：logging_collector 默认是off状态，但是如果你在运行数据库程序时使用pg_ctl -l /usr/local/pgsql/data/logfile start命令手动指定了日志的输出位置，即使 logging_collector是关闭状态，仍然能记录日志。

日志的类型

日志类型	主要内容	典型触发场景	主要控制参数
错误日志（Error Logs）	错误、警告、提示	SQL语法错误、约束冲突、权限问题	log_min_messages, log_min_error_statement
连接与认证日志（Connection Logs）	客户端连接与断开、认证失败	登录、退出、密码错误	log_connections, log_disconnections, log_hostname
查询日志（Statement Logs）	记录执行的 SQL 语句	调试、审计、跟踪执行	log_statement, log_duration, log_min_duration_statement
慢查询日志（Slow Query Logs）	执行时间超过阈值的 SQL	性能瓶颈分析	log_min_duration_statement
检查点与自动维护日志（Checkpoint/Vacuum Logs）	系统后台任务	checkpoint、autovacuum、bgwriter	log_checkpoints, log_autovacuum_min_duration
后台进程日志（Background Worker Logs）	系统进程活动	walwriter、archiver、stats collector 等	自动输出
WAL 与归档日志（WAL/Archive Logs）	WAL文件生成与归档	crash recovery, replication	archive_mode, archive_command
调试日志（Debug Logs）	低层系统行为	调试或开发	client_min_messages, debug_print_parse

---

预写式日志(WAL)

预写式日志（Write-Ahead Logging，简称 WAL），这是 PostgreSQL 保证数据可靠性与 事务 ACID 中“持久性（Durability）” 的核心机制之一。是 PostgreSQL “崩溃不丢数据” 的根本原因。

介绍

在对数据文件进行任何修改之前，必须先把这些修改的记录写入日志（WAL 日志）中。

也就是说：先写日志，再写数据,日志中记录了“要修改什么”和“如何修改”的信息

WAL 的简化执行过程：

步骤	操作	说明
1	事务开始	记录事务开始的标记
2	产生修改	当修改表页、索引页等数据页时，会生成对应的 WAL 记录
3	将 WAL 记录写入 WAL 缓冲区	所有修改先写入 pg_wal/ 目录下的日志文件（顺序写入，速度快）
4	数据页写入缓冲区	修改后的页在内存中缓存，不立即写入磁盘（延迟写）
5	事务提交时 WAL 强制刷新到磁盘（fsync）	PostgreSQL 确保 WAL 写入磁盘后，才认为事务提交成功
6	后台进程异步将脏页写入磁盘	真正的数据文件更新可以延后进行（异步写）
7	崩溃恢复	若崩溃，则根据 WAL 日志“重放”提交事务，撤销未提交事务

检查点和后台写入器

PostgreSQL在修改数据页前，必须先把对应的 WAL 日志写入磁盘。
但有一个问题——如果每次修改都立刻写入磁盘，会极大降低性能。于是 PostgreSQL 引入了两个关键机制：

机制	作用
Background Writer（后台写入器）	定期把脏页（被修改但未写回磁盘的数据页）写回磁盘，减少后续检查点的工作量。
Checkpoint（检查点）	定期把所有脏页写入磁盘，并记录一个“安全点”，保证从此之后 WAL 日志可以安全地截断。

Checkpoint（检查点）

检查点是数据库系统的** 一致性快照**。
在检查点时，PostgreSQL 会：
将所有脏页写入磁盘,然后写入一条特殊的 WAL 记录（checkpoint record）,更新控制文件（pg_control），最后记录最新的 checkpoint 位置。

换句话说，检查点是数据库恢复时的“起点”。
崩溃恢复时只需要从最后一个 checkpoint 开始重放 WAL，而不是从系统启动时开始。

检查点的触发方式: 定时触发、日志量超出触发、手动触发、关闭数据库时触发

后台写入器

后台写入器（Background Writer）是一个后台进程，负责平滑地将脏页写入磁盘。
在不影响前端查询的情况下，提前刷新部分脏页，避免 checkpoint 时一次性大量写盘导致的 I/O 峰值。

工作机制：
后台写入器周期性地扫描共享缓冲区（shared_buffers），选择部分脏页写入磁盘，写入的页数量受配置参数控制。

---

资源消耗(Resource Usage)

资源消耗（Resource Usage） 参数位于 postgresql.conf 文件中，
用于控制 PostgreSQL 在运行时如何使用 内存、CPU、磁盘 I/O 以及后台任务资源。

这些参数直接影响：

• 查询执行速度（尤其是复杂的排序、哈希、连接操作）；

• 系统负载与并发性能；

• 写入延迟与缓存命中率；

• 以及数据库整体的吞吐量。

核心参数介绍：

内存相关参数

1. shared_buffers

   作用是控制数据库用于缓存数据页的共享内存大小。

   PostgreSQL 会优先从这个缓冲区读取数据，而不是直接访问磁盘。

   推荐值通常为物理内存的 25% 左右，太小会导致频繁磁盘 I/O，太大会与操作系统缓存冲突。
   shared_buffers = 512MB

2. work_mem

   控制 每个排序（sort）或哈希（hash）操作 可使用的内存。
   用于 ORDER BY、DISTINCT、JOIN、聚合等操作。

   每个并行操作、每个连接、每个排序都独立占用一份 work_mem。
   例如 50 个连接、每个查询有两个排序操作 → 可能消耗 50×2×4MB = 400MB。

   work_mem = 4MB

3. maintenance_work_mem

   用于维护任务，如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY。

   通常设置为 work_mem 的 10–20 倍，
   在执行索引重建或批量导入时非常关键。
   maintenance_work_mem = 256MB

CPU 并行相关参数

1. max_worker_processes

   控制可启动的后台工作进程总数。
   max_worker_processes = 8

2. max_parallel_workers_per_gather

   控制单个并行查询可使用的最大 worker 数。
   适合在多核 CPU 上开启并行执行，提高大查询性能。
   max_parallel_workers_per_gather = 4

I/O 与磁盘写入

1. wal_buffers
   用于存储尚未写入WAL 预写日志文件的缓冲区。
   如果设置为 -1，PostgreSQL 会自动按 shared_buffers 大小动态分配。
   wal_buffers = 16MB

2. effective_io_concurrency

   控制并发 I/O 操作数量，适用于 SSD/RAID 等高并发存储。
   HDD 一般设置 1–4，SSD 可设为 100–300。
   effective_io_concurrency = 200

3. temp_file_limit

   限制临时文件大小，防止查询使用过多磁盘。
   temp_file_limit = 10GB

---

数据库物理存储和文件布局

PGDATA的一个常见位置是/var/lib/pgsql/data。由不同数据库实例所管理的多个集簇可以在同一台机器上共存。

数据库与表的物理文件结构

**1. 每个数据库一个目录 **
对于集簇里的每个数据库，在PGDATA/base里都有一个子目录对应， 子目录的名字为该数据库在 pg_database里的 OID。这个子目录是该数据库文件的缺省位置；值得一提的是，该数据库的系统目录存储在此。

📁目录结构总览

data/
├── base/                 # 每个数据库的数据文件（真正存放表数据的地方）
│   ├── 1/                # template1 数据库
│   ├── 16384/            # 其他数据库（OID 形式命名）
│   └── ...
├── global/               # 全局系统表（如pg_database、pg_authid）
├── pg_wal/               # WAL（Write-Ahead Log）日志
├── pg_xact/              # 事务提交状态文件
├── pg_multixact/         # 多事务共享锁信息
├── pg_stat/              # 统计信息文件
├── pg_tblspc/            # 表空间（存放符号链接）
├── pg_subtrans/          # 子事务状态
├── pg_logical/           # 逻辑复制相关
├── pg_dynshmem/          # 动态共享内存
├── pg_notify/            # NOTIFY/LISTEN 消息队列
├── postgresql.conf       # 主配置文件
├── pg_hba.conf           # 认证与访问控制配置
├── pg_ident.conf         # 用户映射配置
└── postmaster.pid        # 运行进程信息

索引和表类似也有自己独立的物理文件。
2. 每张表对应一个或多个物理文件
进入某个数据库目录后，例如 base/16384/，你会看到很多类似这样的文件：

base/16384/
├── 2619        # pg_class 系统表
├── 1259        # 你的某个用户表
├── 1259.1      # 同一表的第二个段文件（超出1GB后拆分）
└── ...

这些文件名（数字）是 pg_class.relfilenode，即该表在文件系统中的“物理节点号”。

表的文件组成

每个普通表通常包含以下几类文件：

文件	含义
relfilenode	表的主数据文件（堆文件）
relfilenode_fsm	Free Space Map（空闲空间映射）
relfilenode_vm	Visibility Map（可见性映射）
relfilenode_init	未初始化副本（用于 UNLOGGED 表）

页结构

PostgreSQL 的表数据以 Page（页） 为单位存储，
每个页大小默认 8KB，可以通过 BLCKSZ 编译参数调整。
一个表文件就是一系列连续的数据页：

+---------+---------+---------+---------+ ...
| Page 0  | Page 1  | Page 2  | Page 3  | ...
+---------+---------+---------+---------+

页内结构：

+----------------------+
| Page Header          | ← 存储页级元数据（LSN、校验、空闲空间指针等）
+----------------------+
| Tuple Data (Heap)    | ← 存放真正的行记录（tuple）
+----------------------+
| Free Space           |
+----------------------+
| Line Pointer Array   | ← 反向索引记录，每个记录对应一个元组
+----------------------+

WAL日志与数据文件的关系

PostgreSQL 的数据写入遵循 WAL（Write Ahead Logging） 原则：所有对数据文件的修改，必须先写入 WAL 日志（pg_wal 目录）。
流程：事务修改内存缓冲区（shared buffer），然后将修改写入 pg_wal/，最后checkpoint 时将数据真正写回表文件（base/ 下）。

表空间

表空间允许你把不同表或索引存储在不同磁盘上：

• 默认表空间：pg_default → 对应 base/

• 全局表空间：pg_global → 存放系统表

• 自定义表空间：pg_tblspc/ 目录下以符号链接形式存在
  详见数据库管理#表空间

---

系统目录（System Catalog）

系统目录（System Catalog） 就是数据库的“元数据库（Metadata Database）”。它存储了 数据库中所有对象的定义信息，包括：数据库、表、索引、视图、函数、类型、角色、权限等 。

postgreSQL 系统目录是一组表和视图，用于存储数据库对象的元数据，为数据库管理和查询提供关键信息。关键的系统目录表包括pg_database（数据库信息）、pg_tables（表的详细信息）、pg_indexes（索引信息）、pg_class（表、索引和序列的常规信息）、pg_attribute（每个表的列详细信息）以及pg_roles（用户和角色信息）。这些目录使数据库引擎和用户能够有效地管理模式、安全性和查询优化，从而确保高效的数据库操作和维护。

---

为每个用户/数据库配置

在 PostgreSQL 中，每个用户和每个数据库的设置允许管理员为特定用户或数据库自定义配置，从而增强性能和管理。这些设置使用 ALTER ROLE 和ALTER DATABASE命令进行管理。
这些命令将设置存储在系统目录中，并在用户连接数据库或访问数据库时应用这些设置。常见的自定义参数包括 search_path、work_mem 和 maintenance_work_mem，允许根据特定需求对查询性能和资源使用情况进行精细控制。

配置的优先级

在 PostgreSQL 中，大部分参数都可以通过以下三种层次进行设置（优先级从低到高），当多个层次同时定义同一参数时，后者 （更具体的层级）覆盖前者。例如，ALTER ROLE 中定义的参数会覆盖全局配置文件中的同名参数。

优先级	设置层次	说明
1️⃣	postgresql.conf	全局配置，适用于整个实例
2️⃣	ALTER DATABASE	针对特定数据库的配置
3️⃣	ALTER ROLE	针对特定用户（角色）的配置
4️⃣	SET / SET LOCAL	针对当前会话或事务的临时设置

为每个数据库配置(ALTER DATABASE)

通过 ALTER DATABASE 命令，可以为某个数据库设置特定参数：

ALTER DATABASE mydb SET work_mem = '64MB';
ALTER DATABASE mydb SET search_path = 'myschema, public';

当客户端连接该数据库时，这些参数自动生效。这些参数会被记录在系统目录 pg_db_role_setting 中（字段 setdatabase 指向该数据库的 OID）。

SELECT * 
FROM pg_db_role_setting 
WHERE setdatabase = (SELECT oid FROM pg_database WHERE datname = 'mydb');

每个角色的设置(ALTER ROLE)

同理，可以使用 ALTER ROLE 为某个用户定义参数：

ALTER ROLE alice SET work_mem = '32MB';
ALTER ROLE bob SET maintenance_work_mem = '512MB';

这些设置同样保存在 pg_db_role_setting 表中（字段 setrole 指向角色 OID）。用户登录时，这些设置会自动加载。
你也可以同时为某个用户和数据库定义参数（双重限定设置）：

## 当用户 alice 连接数据库 mydb 时，将自动禁用顺序扫描。
ALTER ROLE alice IN DATABASE mydb SET enable_seqscan = off;

常见参数

参数	说明	建议设置场景
search_path	默认 schema 搜索路径	每个项目独立 schema
work_mem	单个查询操作可用内存	查询较大的排序或 join
maintenance_work_mem	VACUUM、CREATE INDEX 时可用内存	维护操作频繁的库
log_statement	控制 SQL 日志记录级别	调试或审计
statement_timeout	查询超时时间	限制低效长查询

---

存储参数

存储参数是 PostgreSQL 提供的一组表级或索引级的可调节配置项。它们定义了数据库对象（表、索引、物化视图等）的存储策略，如：

• 自动清理行为 (autovacuum_*)

• TOAST 压缩与存储策略 (toast.*)

• I/O 缓冲策略 (fillfactor, buffering)

• 索引维护参数 (fastupdate, deduplicate_items)

• 表分区或数据压缩策略等

这些参数不会影响逻辑结构（如列或约束），而是影响底层物理存储和性能。

设置方式

可以在创建或修改表/索引时设置存储参数：

-- 创建表时设置
CREATE TABLE orders (
    id serial PRIMARY KEY,
    details text
) WITH (fillfactor = 80, autovacuum_vacuum_scale_factor = 0.1);

-- 修改已存在的表
ALTER TABLE orders SET (autovacuum_enabled = true);

-- 设置索引参数
CREATE INDEX idx_orders_date ON orders(order_date) WITH (fillfactor = 70);
ALTER INDEX idx_orders_date SET (fastupdate = off);

常见存储参数及作用分类

1. 填充与空间利用

参数	作用	说明
fillfactor	页填充比例	控制每个表页或索引页填充的百分比（默认 100%）。留出空间可减少更新引发的页分裂。
parallel_workers	并行工作者数	指定在并行扫描或操作中可用的工作线程数。

2. 自动清理（Autovacuum）控制类

参数	含义	默认值	说明
autovacuum_enabled	是否启用自动清理	true	控制是否对该表启用 autovacuum
autovacuum_vacuum_threshold	触发清理的最小更新/删除行数	50	当更新/删除数超过阈值时触发
autovacuum_vacuum_scale_factor	清理触发比例	0.2	与阈值共同决定何时触发
autovacuum_analyze_threshold	触发 ANALYZE 的最小行变化数	50	用于更新统计信息
autovacuum_analyze_scale_factor	ANALYZE 触发比例	0.1	同上
autovacuum_freeze_min_age	冻结行龄阈值	50M	防止 XID 溢出

3. TOAST 存储与压缩类
   TOAST用于存储超大字段。（text / bytea / jsonb等）

参数	说明
toast_tuple_target	TOAST 行大小目标（默认 2KB）
toast.autovacuum_enabled	是否为 TOAST 表启用 autovacuum
toast.autovacuum_vacuum_scale_factor	TOAST 表清理比例
toast.autovacuum_vacuum_threshold	TOAST 表清理阈值

4. 索引类参数

参数	作用	说明
fastupdate	GiST/Gin 索引是否使用缓冲区	true 时可加速插入，但稍后需要合并。
deduplicate_items	B-Tree 索引是否去重重复项	默认 on，节省空间。
buffering	控制 GiST 索引构建时是否使用缓冲构建	提升大索引创建性能。

5. 分区、存储表空间相关参数

参数	作用
tablespace	指定表或索引的物理存储位置
parallel_workers	为表扫描指定并行线程
user_catalog_table	声明此表为系统目录表（供扩展使用）