文章总结： 本文系统介绍了PostgreSQL全栈优化方法，涵盖架构、参数调优、autovacuum、索引、SQL优化等核心内容。文章指出PG调优需从参数配置、死元组清理、连接池管理等多方面入手，并提供了具体配置建议如sharedbuffers设为内存25%、workmem需谨慎设置等。主要结论是PG调优是系统工程，关键发现包括多进程模型和MVCC机制对性能的影响，可操作建议是优先调整内存和autovacuum参数。 综合评分： 86 文章分类： 实战经验

---

全栈优化 PostgreSQL：从配置参数落地到 SQL 精细化调优

点击关注 👉 点击关注 👉

马哥Linux运维

2026年6月7日 14:00 广东

在小说阅读器读本章

去阅读

问题背景

PostgreSQL 是被低估的 OLTP 数据库。功能强、稳定性好、SQL 标准兼容度高、扩展能力一流。但生产里还是能看到这些问题：

• 装上就用，从来不调参数，TPS 跑不到预期。
• 表越来越大，autovacuum 不工作，查询越来越慢。
• 慢查询几个小时没结果，DBA 也找不到原因。
• 锁等待 / 死锁一出现，CPU 100% 持续几十分钟。
• 连接数暴涨，应用报错 too many connections。
• 升级到 14/15/16 后，磁盘 IO 反而变差。
• 主备延迟大，备库查不到数据。

PostgreSQL 调优是个系统工程：参数、autovacuum、索引、SQL、连接池、监控、HA 都要做。这篇文章从架构讲起，覆盖参数调优、autovacuum、索引、SQL 优化、连接池、监控、实战案例。看完之后能独立完成 PostgreSQL 的全链路调优。

适用读者

• 维护 PostgreSQL 集群的 DBA、运维工程师。
• 想从 MySQL 迁移到 PG 的工程师。
• 遇到性能瓶颈想调优的同学。
• 准备做 PG 12/13/14/15/16 升级的团队。

适用场景

• OLTP 业务（订单、支付、用户）。
• 复杂分析查询（数据仓库、报表）。
• GIS、时序、全文搜索（PG 扩展）。
• 中大规模（GB ~ TB 数据）。
• 单实例 / 主备 / 流复制集群。

核心知识点

PG 架构

进程模型

PG 是”多进程”模型，每个连接一个 backend 进程：

postmaster (主进程)
  ├─ backend 1 (连接 1)
  ├─ backend 2 (连接 2)
  ├─ bgwriter (后台写进程)
  ├─ walwriter (WAL 写进程)
  ├─ autovacuum launcher (autovacuum 启动器)
  │   ├─ autovacuum worker 1
  │   └─ autovacuum worker 2
  ├─ checkpointer (检查点)
  ├─ stats collector (统计信息)
  └─ logger (日志)

vs MySQL（多线程）。PG 多进程的好处是稳定，一个连接崩了不影响其他连接；缺点是连接数多了内存开销大（每进程 ~5~10MB）。

MVCC

PG 用 MVCC 实现读不阻塞写、写不阻塞读。每次 UPDATE/DELETE 实际上不立即删除老版本，而是标记为”死元组”（dead tuple），autovacuum 负责清理。

旧版本（xmin=100）→ 新版本（xmax=100, xmin=101）

读事务看的是 xmin ≤ 当前事务的快照，xmax > 当前事务的快照。死元组对活事务还可见。

MVCC 优点：一致性读不阻塞写。缺点：需要 vacuum 清理死元组；表会膨胀。

WAL

PG 用 WAL（Write-Ahead Log）保证持久性。事务提交前，WAL 写盘（fsync），数据文件后写。

事务 → WAL buffer → WAL file (fsync)
                  ↓
              checkpointer → data file

崩溃恢复时 replay WAL 重建数据。

共享缓冲（shared_buffers）

PG 用 OS mmap 把数据文件映射到 shared_buffers 区域。读数据先查 shared_buffers，没命中再走 OS page cache、最后读盘。shared_buffers 越大，命中越高，但过大会拖慢 checkpoint。

TOAST

大字段（超过 2KB）自动 TOAST 存储。TOAST 是行外的存储方式，分四种策略：PLAIN、EXTENDED、EXTERNAL、MAIN。默认 EXTENDED 会尝试压缩 + 行外存储。

版本说明

• PG 12：流复制增强、分区表改进、JSONPath
• PG 13：B-tree 优化、并行 vacuum、增量排序
• PG 14：连接逻辑预读、扩展统计信息、TOAST 优化
• PG 15：MERGE、JSON 表函数、性能改进
• PG 16：逻辑复制增强、参数 bulk insert

本篇文章配置以 PG 14/15 为主。版本差异部分会在附录列出。

工具集

• psql：CLI 客户端
• pg_dump / pg_restore：逻辑备份
• pg_basebackup：物理备份
• pg_isready：检查集群状态
• pg_stat_statements：SQL 统计
• auto_explain：慢查询 EXPLAIN
• pgBadger：日志分析
• pgAdmin：GUI 工具
• pganalyze：商业 APM

实战一：基础参数调优

内存相关

shared_buffers

# postgresql.conf
shared_buffers = 8GB

PG 推荐设为物理内存的 25%（不含 OS），大内存机器可以提到 25~40%。

-- 8GB 内存：2GB
-- 16GB 内存：4GB
-- 32GB 内存：8GB
-- 64GB 内存：16GB
-- 128GB 内存：32GB

重启生效：

sudo systemctl restart postgresql

work_mem

work_mem = 64MB

每个 sort、hash join、hash aggregate 操作的内存上限。复杂查询会分配多次 work_mem（多个 sort）。

经验值：

• 小查询：16~32MB
• 中查询（OLAP）：128~256MB
• 大查询：512MB+（要看总连接数）

风险提示：连接数 × work_mem = 最大内存占用。1000 连接 × 64MB = 64GB。要谨慎。

-- 查看当前使用
SHOW work_mem;

-- 单查询覆盖
SET work_mem = '256MB';
SELECT ...

maintenance_work_mem

maintenance_work_mem = 2GB

autovacuum、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY 等维护操作内存。可以设大些，因为并发维护操作不多。

经验值：物理内存的 5%~10%。

effective_cache_size

effective_cache_size = 24GB

不是分配内存，是告诉 PG 期望 OS 缓存多大。PG 用这个值决定是否走索引。推荐设为物理内存的 50%~75%。

-- 32GB 内存：16~24GB
-- 64GB 内存：32~48GB

huge_pages

huge_pages = try

启用大页。需要在 OS 上配置 vm.nr_hugepages。

# 计算大页数
grep HugePages_Total /proc/meminfo
# 需要 = (shared_buffers + 几GB) / 2MB (大页大小)
# 例如 8GB shared_buffers 需要 4100 个 2MB 大页

echo 4100 > /proc/sys/vm/nr_hugepages
sudo sysctl -w vm.nr_hugepages=4100

wal_buffers

wal_buffers = 64MB

WAL 缓冲区大小。默认 16MB，写多的场景可以提到 64MB。重启生效。

temp_buffers

temp_buffers = 32MB

临时表缓冲区。每个 session 占用。默认 8MB，可以提到 32~64MB。

WAL 与 Checkpoint

wal_level

wal_level = replica

WAL 记录详细程度：

• minimal：只记重建需要的最小信息
• replica：默认，支持流复制
• logical：支持逻辑复制

流复制用 replica，逻辑复制用 logical。

fsync

fsync = on

强制 WAL fsync。生产必须 on。关掉 fsync 能提性能但崩溃时丢数据。

synchronous_commit

synchronous_commit = on

事务提交是否等待 WAL 落盘。on 最安全，off 性能好但可能丢最近几个事务。

流复制下：

synchronous_commit = on  # 默认
# remote_apply：备库 apply 后才 commit，最严格
# on：备库 fsync 后 commit
# remote_write：备库 write 后 commit
# off：不等待备库

风险提示：synchronous_commit = off 在主库挂时可能丢数据。生产慎用。

checkpoint 相关

# 检查点目标完成时间（秒）
checkpoint_timeout = 15min

# WAL 量触发检查点
max_wal_size = 4GB
min_wal_size = 1GB

# 检查点完成度目标（0.0-1.0）
# 0.9 表示 90% 时开始 ramp up
checkpoint_completion_target = 0.9

checkpoint_completion_target = 0.9 让 checkpoint 慢一点完成，避免突发 IO。

连接相关

max_connections

max_connections = 200

最大连接数。PG 多进程模型下，每个连接 5~10MB。1000 连接 = 5~10GB 内存给连接。

生产建议：

• 200~300：直接连 PG
• 500+：上连接池（pgbouncer）

superuser_reserved_connections

superuser_reserved_connections = 5

为超级用户预留连接，防止管理员被锁在外面。

成本相关

# 优化器成本估算
seq_page_cost = 1.0
random_page_cost = 1.1  # SSD 推荐 1.1，机械盘 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
parallel_tuple_cost = 0.01
parallel_setup_cost = 1000.0
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB
effective_parallel_workers_per_gather = 2
max_parallel_workers = 8
max_parallel_workers_per_gather = 2

random_page_cost SSD 推荐 1.1，机械盘 4.0。如果 PG 还走错索引，可能就是这个值没调对。

default_statistics_target

default_statistics_target = 100

ANALYZE 时收集的统计信息粒度。100 是默认，复杂表可以提到 500~1000。

-- 单表覆盖
ALTER TABLE mytable ALTER COLUMN mycol SET STATISTICS 1000;

autovacuum 相关

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
autovacuum_vacuum_cost_delay = 20
autovacuum_vacuum_cost_limit = 200

详见后面的 autovacuum 章节。

日志相关

logging_collector = on
log_destination = 'stderr'
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000  # 记录 1s 以上的慢查询

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on  # 锁等待超 deadlock_timeout 时记录
log_temp_files = 0  # 记录所有临时文件
log_autovacuum_min_duration = 0  # 记录所有 autovacuum
log_line_prefix = '%m [%p] %q%u@%d/%a from %h '

log_line_prefix 一定要配好，否则日志里只有时间没进程号、用户、库，排查很麻烦。

实战二：autovacuum 调优

原理

autovacuum 是 PG 的后台清理进程，负责：

1. VACUUM：删除死元组，标记空间可复用。
2. ANALYZE：收集表的统计信息。
3. autovacuum：自动跑上面两个。

死元组来自 UPDATE/DELETE。MVCC 让 UPDATE 实际上 INSERT 新行 + 标记老行死，DELETE 标记行为死。

-- 手动 VACUUM
VACUUM (VERBOSE, ANALYZE) mytable;

-- 手动 VACUUM FULL（锁表，回收空间）
-- 风险提示：VACUUM FULL 锁表，大表慎用
VACUUM FULL mytable;

调优

参数

# 触发 vacuum 的死元组数阈值
# 公式: vacuum_threshold + vacuum_scale_factor * 表行数
# 默认 50 + 0.2 * 行数
# 大表 (1亿行) 触发: 50 + 2000万 = 2000万死元组
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

# 触发 analyze 的变更行数阈值
# 默认 50 + 0.1 * 行数
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1

# 控制 vacuum 速度
# cost_limit 是每个 worker 一次循环的 cost 上限
# cost_delay 是 IO 操作间隔
autovacuum_vacuum_cost_limit = 200
autovacuum_vacuum_cost_delay = 20ms

单表覆盖

-- 写多的表（订单、日志）调高频率
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 1000
);

-- 静态表（配置）调低频率
ALTER TABLE config SET (
  autovacuum_vacuum_scale_factor = 0.5,
  autovacuum_analyze_scale_factor = 0.2
);

监控

-- 看表的死元组占比
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDERBY dead_pct DESC
LIMIT20;

dead_pct > 20% 表示 autovacuum 没跟上。

-- 看 vacuum 进度
SELECT * FROM pg_stat_progress_vacuum;

-- 看正在跑 vacuum 的 worker
SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  ROUND(heap_blks_scanned::numeric / GREATEST(heap_blks_total, 1) * 100, 2) AS scan_pct
FROM pg_stat_progress_vacuum;

实战

# 一次性大表清理
vacuumdb -d mydb -t orders --analyze --verbose

# 紧急清理（高 IO 风险）
vacuumdb -d mydb -t log_table --full --verbose
# 风险提示：VACUUM FULL 锁表，大表可能锁几小时

pg_repack

PG 原生 VACUUM FULL 锁表。pg_repack 是社区方案，能在线重建表：

# 安装
apt install postgresql-14-repack

# 在线重建（不锁表）
pg_repack -d mydb -t orders

pg_repack 流程：

1. 创建影子表 + 触发器。
2. 复制数据到影子表。
3. 切换表名。
4. 删除旧表。

整个过程读写不阻塞。

实战三：索引调优

索引类型

| 类型 | 适用 | | — | — | | B-Tree | 等值、范围、排序（默认） | | Hash | 等值（PG 10+ 改进） | | GiST | 几何、全文、范围 | | GIN | JSONB、数组、全文 | | BRIN | 顺序数据（时间序、地理位置） | | SP-GiST | 空间分区 |

-- B-Tree
CREATEINDEX idx_orders_user_id ON orders(user_id);

-- Hash
CREATEINDEX idx_users_email ONusersUSINGhash(email);

-- 复合索引
CREATEINDEX idx_orders_user_status ON orders(user_id, status);

-- 部分索引
CREATEINDEX idx_orders_pending ON orders(user_id) WHEREstatus = 'pending';

-- 表达式索引
CREATEINDEX idx_users_lower_email ONusers(LOWER(email));

-- GIN（JSONB）
CREATEINDEX idx_users_data ONusersUSING gin(data);

-- GIN（全文）
CREATEINDEX idx_articles_body ON articles USING gin(to_tsvector('english', body));

-- BRIN（时序）
CREATEINDEX idx_logs_created_at ONlogsUSING brin(created_at);

索引选择

-- 看一个查询走哪个索引
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;

Index Scan using idx_orders_user_id on orders  (cost=0.43..50.12 rows=100 width=120) (actual time=0.025..0.312 rows=100 loops=1)
  Index Cond: (user_id = 123)
  Buffers: shared hit=45
Planning Time: 0.123 ms
Execution Time: 0.456 ms

关键字段：

• cost：估算成本
• rows：估算返回行数
• actual time：实际耗时
• Buffers: shared hit=N：命中 shared_buffers 次数
• Buffers: shared read=N：从磁盘读次数

Seq Scan 是顺序扫描，Index Scan 是索引扫描，Index Only Scan 是仅索引扫描（覆盖索引）。大表上避免 Seq Scan。

索引维护

-- 看索引使用情况
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

idx_scan = 0 的索引可能是无用索引。

-- 看索引大小
SELECT
  schemaname,
  relname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 重建索引
REINDEX INDEX idx_orders_user_id;
REINDEX TABLE orders;

-- 在线重建（PG 12+，不锁写）
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

风险提示：REINDEX 会锁表。生产大表用 REINDEX CONCURRENTLY（PG 12+）或者用 pg_repack。

索引陷阱

函数索引

-- 错：直接走索引失败
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- 因为 email 上建的是普通 B-Tree 索引，LOWER(email) 走不到

-- 对：建表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

数据类型不匹配

-- 错：user_id 是 integer，传了 string
SELECT * FROM orders WHERE user_id = '123';
-- PG 隐式转换，可能走不了索引

-- 对：保持类型一致
SELECT * FROM orders WHERE user_id = 123;

复合索引列顺序

CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 走索引
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 123;  -- 也走

-- 不走索引
SELECT * FROM orders WHERE status = 'paid';  -- 不走，因为 user_id 不在

PG 复合索引遵循最左前缀。建索引时把等值列放前面、范围列放后面。

实战四：执行计划分析

EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) SELECT ...

参数说明：

• ANALYZE：实际执行并计时
• BUFFERS：显示 buffer 命中/读
• VERBOSE：显示每个节点的输出列
• COSTS：显示成本估算（默认开）
• FORMAT JSON / YAML / TEXT

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 100;

关键节点

Seq Scan

Seq Scan on orders  (cost=0.00..5000.00 rows=10000 width=120)
  Filter: (user_id = 123)

顺序扫描整张表。可能原因：

• 表小（几百行）。
• 缺少索引。
• 优化器判断全表扫比索引扫快。

Index Scan

Index Scan using idx_orders_user_id on orders  (cost=0.43..50.12 rows=100 width=120)
  Index Cond: (user_id = 123)

走索引，先查索引拿到 TID，再去表读数据。

Index Only Scan

Index Only Scan using idx_orders_user_id on orders  (cost=0.43..30.00 rows=100 width=20)
  Index Cond: (user_id = 123)

索引里就有所需列，不用回表。需要 visibility map 配合。

Bitmap Heap Scan / Bitmap Index Scan

Bitmap Heap Scan on orders  (cost=100.00..2000.00 rows=5000 width=120)
  Recheck Cond: (user_id = 123)
  ->  Bitmap Index Scan on idx_orders_user_id
        Index Cond: (user_id = 123)

PG 先用索引拿到所有 TID 列表（Bitmap），再按物理顺序读表。多个索引 OR 时常用。

Nested Loop

Nested Loop  (cost=0.43..500.00 rows=100 width=200)
  ->  Index Scan on users u
        Index Cond: (id = 123)
  ->  Index Scan on orders o
        Index Cond: (user_id = 123)

外层每行匹配内层。适合小结果集。

Hash Join

Hash Join  (cost=100.00..5000.00 rows=10000 width=200)
  Hash Cond: (o.user_id = u.id)
  ->  Seq Scan on orders o
  ->  Hash
        ->  Seq Scan on users u

构建 hash 表，匹配连接。大表连接常用。

Merge Join

Merge Join  (cost=500.00..5000.00 rows=10000 width=200)
  Merge Cond: (o.user_id = u.id)
  ->  Index Scan on orders o
  ->  Index Scan on users u

两边都排序后归并。适合已经排序的数据。

Sort / Aggregate / Limit

Sort  (cost=5000.00..5500.00 rows=10000 width=120)
  Sort Key: created_at DESC
  ->  Seq Scan on orders
Limit  (cost=0.00..1.50 rows=100 width=120)

Sort 看是用内存还是磁盘（Sort Method: external merge Disk: 50MB 是落盘了）。

慢查询分析

-- 开启 pg_stat_statements
-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top

-- 重启
sudo systemctl restart postgresql

-- 创建扩展
CREATE EXTENSION pg_stat_statements;

-- 查最慢的 SQL
SELECT
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  calls,
ROUND(total_exec_time::numeric, 2) AS total_ms,
query
FROM pg_stat_statements
ORDERBY mean_exec_time DESC
LIMIT20;

-- 查总耗时最多的 SQL
SELECT
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  calls,
  ROUND(mean_exec_time::numeric, 2) AS mean_ms,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查最占 IO 的 SQL
SELECT
  ROUND((shared_blks_hit + shared_blks_read)::numeric, 0) AS total_blocks,
  calls,
  query
FROM pg_stat_statements
ORDER BY total_blocks DESC
LIMIT 20;

auto_explain

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '1s'  # 1s 以上的慢查询自动 EXPLAIN
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on

sudo systemctl restart postgresql

auto_explain 自动记录慢查询的执行计划，比 pg_stat_statements 信息更全。

实战五：SQL 优化技巧

慢 SQL 案例

案例 1：SELECT *

-- 错：SELECT * 读所有列
SELECT * FROM users WHERE id = 1;

-- 对：只读需要的列
SELECT id, name, email FROM users WHERE id = 1;

案例 2：隐式类型转换

-- 错：phone 是 varchar，传了 integer
SELECT * FROM users WHERE phone = 13800138000;
-- 隐式转换可能导致 Seq Scan

-- 对：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

案例 3：NOT IN 子查询

-- 错：NOT IN 子查询会扫所有行
SELECT * FROMusersWHEREidNOTIN (SELECT user_id FROM blocked);

-- 对：NOT EXISTS 用相关子查询
SELECT * FROMusers u
WHERENOTEXISTS (
SELECT1FROM blocked b WHERE b.user_id = u.id
);

-- 或 LEFT JOIN
SELECT u.*
FROMusers u
LEFTJOIN blocked b ON u.id = b.user_id
WHERE b.user_id ISNULL;

案例 4：OR 条件

-- 错：OR 可能走全表
SELECT * FROMusersWHEREname = 'John'OR email = '[email protected]';

-- 对：UNION ALL
SELECT * FROMusersWHEREname = 'John'
UNIONALL
SELECT * FROMusersWHERE email = '[email protected]'ANDname != 'John';

案例 5：分页

-- 错：大 offset 慢
SELECT * FROM orders ORDERBYidLIMIT20OFFSET1000000;

-- 对：游标分页
SELECT * FROM orders WHEREid > 1000000ORDERBYidLIMIT20;

案例 6：IN 大列表

-- 错：IN 10000 个值
SELECT * FROM users WHERE id IN (1, 2, 3, ..., 10000);

-- 对：临时表 + JOIN
CREATE TEMP TABLE tmp_ids (id int);
INSERT INTO tmp_ids VALUES (1), (2), ...;
SELECT u.* FROM users u JOIN tmp_ids t ON u.id = t.id;

批量操作

-- 错：一次 UPDATE 100 万行
UPDATEusersSETstatus&nbsp;=&nbsp;'inactive'WHERE&nbsp;last_login <&nbsp;'2023-01-01';

-- 对：分批
DO&nbsp;$$
DECLARE
&nbsp; batch_size&nbsp;INT&nbsp;:=&nbsp;10000;
&nbsp; affected INT;
BEGIN
LOOP
&nbsp; &nbsp;&nbsp;UPDATEusers
&nbsp; &nbsp;&nbsp;SETstatus&nbsp;=&nbsp;'inactive'
&nbsp; &nbsp;&nbsp;WHEREidIN&nbsp;(
&nbsp; &nbsp; &nbsp;&nbsp;SELECTidFROMusers
&nbsp; &nbsp; &nbsp;&nbsp;WHERE&nbsp;last_login <&nbsp;'2023-01-01'ANDstatus&nbsp;=&nbsp;'active'
&nbsp; &nbsp; &nbsp;&nbsp;LIMIT&nbsp;batch_size
&nbsp; &nbsp; &nbsp;&nbsp;FORUPDATESKIPLOCKED
&nbsp; &nbsp; );
&nbsp; &nbsp; GET DIAGNOSTICS affected = ROW_COUNT;
&nbsp; &nbsp; RAISE NOTICE 'Updated % rows', affected;
&nbsp; &nbsp; EXIT WHEN affected = 0;
&nbsp; &nbsp; PERFORM pg_sleep(0.1);
ENDLOOP;
END&nbsp;$$;

SKIP LOCKED（PG 9.5+）跳过被锁的行，避免阻塞。

实战六：连接池

为什么用 pgbouncer

PG 多进程模型下，连接数过多会拖慢数据库：

• 每个连接 5~10MB 内存。
• 1000 连接 = 5~10GB 内存。
• fork 进程开销大。
• 缓存命中率下降。

pgbouncer 是 PG 官方推荐的连接池，支持三种模式：

• session：客户端断开时归还连接（最稳定）
• transaction：事务结束后归还连接（性能最好）
• statement：SQL 执行完归还（最激进，部分功能不可用）

安装配置

# 安装
sudo apt install pgbouncer

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
mydb_ro = host=10.0.0.3 port=5432 dbname=mydb pool_mode=transaction

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# 后端连接池大小
default_pool_size = 20
min_pool_size = 5
max_client_conn = 1000
reserve_pool_size = 5
reserve_pool_timeout = 3

# 客户端连接超时
client_idle_timeout = 600
client_login_timeout = 60

# 后端连接超时
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15

# 池模式
pool_mode = transaction

# /etc/pgbouncer/userlist.txt
"appuser" "md5hashpassword"
"readonly" "md5hashpassword"

sudo systemctl restart pgbouncer

验证

# 连 pgbouncer
psql -h 127.0.0.1 -p 6432 -U appuser -d mydb

# pgbouncer 管理
psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

# 看连接池状态
SHOW POOLS;
SHOW STATS;
SHOW SERVERS;

-- 输出形如：
-- database | user | cl_active | cl_waiting | sv_active | sv_idle | pool_mode
-- mydb | appuser | 50 | 0 | 8 | 12 | transaction
-- mydb_ro | readonly | 30 | 0 | 5 | 15 | transaction

cl_active 客户端活跃连接，sv_active 后端活跃连接，比例是放大倍数。

transaction 模式注意事项

• 不支持 prepared statements。
• 不支持 SET 命令（session 级别）。
• 不支持 advisory locks（pg_advisory_lock）。
• 不支持 LISTEN / NOTIFY。

业务层用 JDBC 连接池（druid / HikariCP）+ pgbouncer 双重连接池。JDBC 端 pool 50~200，pgbouncer 端 pool 20~50。

实战七：监控

pg_stat 视图

-- 数据库级别
SELECT
  datname,
  blks_read,
  blks_hit,
ROUND(blks_hit::numeric / GREATEST(blks_read + blks_hit, 1) * 100, 2) AS hit_pct,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted,
  conflicts,
  temp_files,
  temp_bytes,
  deadlocks,
  blk_read_time,
  blk_write_time
FROM pg_stat_database
WHERE datname NOTIN ('template0', 'template1', 'postgres');

-- 表级别
SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  idx_tup_fetch,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDERBY n_live_tup DESC
LIMIT20;

-- 索引级别
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

-- 当前活跃查询
SELECT
  pid,
  datname,
  usename,
  application_name,
  client_addr,
  state,
  query_start,
  state_change,
  wait_event_type,
  wait_event,
LEFT(query, 100) ASquery
FROM pg_stat_activity
WHERE state != 'idle'
ORDERBY query_start;

-- 锁等待
SELECT
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement,
  blocked_activity.application_name AS blocked_application
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.database ISNOTDISTINCTFROM blocked_locks.database
AND blocking_locks.relation ISNOTDISTINCTFROM blocked_locks.relation
AND blocking_locks.page ISNOTDISTINCTFROM blocked_locks.page
AND blocking_locks.tuple ISNOTDISTINCTFROM blocked_locks.tuple
AND blocking_locks.virtualxid ISNOTDISTINCTFROM blocked_locks.virtualxid
AND blocking_locks.transactionid ISNOTDISTINCTFROM blocked_locks.transactionid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
ON blocking_activity.pid = blocking_locks.pid
WHERENOT blocked_locks.granted;

-- 杀进程
SELECT&nbsp;pg_cancel_backend(<pid>); &nbsp;-- 取消当前 query
SELECT&nbsp;pg_terminate_backend(<pid>); &nbsp;-- 强制断开连接

Prometheus 监控

用 postgres_exporter 抓取指标：

# 安装
docker run -d --name pg-exporter \
  -p 9187:9187 \
  -e DATA_SOURCE_NAME="postgresql://monitor:[email protected]:5432/postgres?sslmode=disable" \
  prometheuscommunity/postgres-exporter

# prometheus-servicemonitor.yaml
apiVersion:monitoring.coreos.com/v1
kind:ServiceMonitor
metadata:
name:postgres
namespace:monitoring
spec:
selector:
    matchLabels:
      app:postgres-exporter
endpoints:
-port:metrics
    interval:30s

# prometheus-rule-postgres.yaml
apiVersion:monitoring.coreos.com/v1
kind:PrometheusRule
metadata:
name:postgres-alerts
namespace:monitoring
spec:
groups:
-name:postgres
    rules:
    -alert:PostgresTooManyConnections
      expr:pg_stat_activity_count>80
      for:5m
      labels:
        severity:warning
      annotations:
        summary:"PostgreSQL 连接数 {{ $value }} 接近上限"
    -alert:PostgresReplicationLag
      expr:pg_replication_lag_seconds>60
      for:5m
      labels:
        severity:warning
      annotations:
        summary:"PostgreSQL 备库延迟 {{ $value }} 秒"
    -alert:PostgresDeadTuples
      expr:pg_stat_user_tables_n_dead_tup/pg_stat_user_tables_n_live_tup>0.2
      for:30m
      labels:
        severity:warning
      annotations:
        summary:"PostgreSQL 表 {{ $labels.relname }} 死元组超过 20%"
    -alert:PostgresLongTransaction
      expr:pg_stat_activity_max_tx_duration>600
      for:1m
      labels:
        severity:warning
      annotations:
        summary:"PostgreSQL 存在运行超过 {{ $value }} 秒的事务"

Grafana 官方 PostgreSQL dashboard ID 9628。

实战八：流复制与 HA

主备部署

# 主库
postgresql.conf:
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'

pg_hba.conf:
host replication replicator 10.0.0.0/24 md5

# 创建复制用户
psql -c "CREATE USER replicator REPLICATION PASSWORD 'xxx';"

# 备库
pg_basebackup -h 10.0.0.1 -D /var/lib/pgsql/14/data \
  -U replicator -P -Xs -R

# 启动备库
sudo systemctl start postgresql

-R 自动写 standby.signal 和 postgresql.auto.conf。

切换

# 主库停止
sudo systemctl stop postgresql

# 备库提升
sudo pg_ctlcluster 14 main promote
# 或
sudo -u postgres /usr/lib/postgresql/14/bin/pg_ctl promote -D /var/lib/pgsql/14/data

同步复制

# 主库
synchronous_standby_names = 'standby01'

# 备库
synchronous_commit = on

# 多副本
synchronous_standby_names = 'ANY 2 (standby01, standby02, standby03)'

同步复制保证主库 commit 前备库已经 apply。代价是延迟增加。

Repmgr / Patroni

社区主流的 PG HA 工具：

• repmgr：轻量，自动 failover。
• Patroni：完整方案（基于 Zookeeper / etcd / Consul 选主）。

# Repmgr 配置
cat > /etc/repmgr.conf <<EOF
node_id=1
node_name=node1
conninfo='host=10.0.0.1 user=repmgr dbname=repmgr'
data_directory='/var/lib/pgsql/14/data'
pg_bindir='/usr/lib/postgresql/14/bin'
EOF

# 注册主节点
repmgr primary register

# 注册备节点（在备库上）
repmgr standby register

# 看状态
repmgr cluster show

实战九：升级路径

小版本升级

# 14.5 → 14.10
sudo apt install postgresql-14
# 自动替换二进制，保留数据
sudo systemctl restart postgresql

大版本升级（pg_upgrade）

# 14 → 15
# 1. 备份
pg_dumpall > full_backup.sql

# 2. 装新版本
sudo apt install postgresql-15

# 3. 停老实例
sudo systemctl stop postgresql@14

# 4. 跑 pg_upgrade
sudo -u postgres /usr/lib/postgresql/15/bin/pg_upgrade \
  -b /usr/lib/postgresql/14/bin \
  -B /usr/lib/postgresql/15/bin \
  -d /var/lib/pgsql/14/data \
  -D /var/lib/pgsql/15/data \
  --link

# 5. 启动新实例
sudo systemctl start postgresql@15

# 6. 跑 analyze
sudo -u postgres vacuumdb -a -z

--link 用硬链接不拷数据，快。生产推荐。

风险提示：升级前必须全量备份。pg_upgrade 不支持降级。升级失败用备份恢复。

逻辑复制升级

大库或者跨大版本（12 → 15）推荐用逻辑复制，零停机：

-- 发布
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- 订阅（在目标库）
CREATE SUBSCRIPTION sub_src
CONNECTION 'host=10.0.0.1 port=5432 dbname=mydb user=repl password=xxx'
PUBLICATION pub_all;

-- 监控
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_subscription;

逻辑复制可以跨大版本。零停机升级流程：

1. 老库 14 持续运行。
2. 部署 15 库，建逻辑复制订阅老库。
3. 等 15 库追上 14 库。
4. 切换应用连接 15 库。
5. 取消订阅。
6. 老库停服。

实战十：实战案例

案例 1：TPS 从 800 提升到 6500

现象：电商订单库，原来 TPS 800，调优后 6500。

根因分析：

-- 1. 慢查询
SELECT * FROM pg_stat_statements
ORDERBY mean_exec_time DESCLIMIT10;
-- 看到一条 SELECT 1.2s 平均

-- 2. EXPLAIN 分析
EXPLAINANALYZE
SELECT * FROM orders WHERE user_id = 123ANDstatus = 'paid';
-- Seq Scan on orders
-- 因为 user_id + status 复合索引缺失

优化：

1. 加复合索引：

CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);

2. 调参：

shared_buffers = 16GB  # 之前 4GB
effective_cache_size = 48GB
work_mem = 32MB
random_page_cost = 1.1  # SSD

3. 调 autovacuum：

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

4. pgbouncer 池化：

pool_mode = transaction
default_pool_size = 50

5. 应用层优化：把单条 SQL 拆成多个 batch。

效果：TPS 800 → 6500，P99 延迟 1.2s → 80ms。

案例 2：autovacuum 跟不上导致表膨胀

现象：订单表 50GB 实际数据，磁盘占用 200GB。

SELECT
  relname,
  pg_size_pretty(pg_relation_size(oid)) AS table_size,
  n_live_tup,
  n_dead_tup,
  ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'orders';

输出：table_size 200GB，n_dead_tup 占 70%。

根因：autovacuum 太慢，跟不上更新速度。

修复：

1. 提高 autovacuum 速度：

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 2000
);

2. 检查 worker 数：

SHOW autovacuum_max_workers;
-- 默认 3，调到 6

3. 用 pg_repack 重建表：

pg_repack -d mydb -t orders -j 4

4. 调 work_mem：

work_mem = 64MB
autovacuum_work_mem = 1GB

效果：表从 200GB 缩到 60GB（死元组清理后 + 索引大小），查询速度提升 3x。

案例 3：慢 SQL 拖垮整库

现象：业务反馈数据库卡，CPU 100%。

排查：

SELECT pid, query_start, state, wait_event, query
FROM pg_stat_activity
WHERE state != 'idle' ORDER BY query_start;

找到一条跑了几小时的 SQL：

SELECT * FROM huge_table t1, huge_table t2 WHERE t1.id = t2.related_id;

根因：笛卡尔积级别的 join。

修复：

-- 1. 立即取消
SELECT&nbsp;pg_cancel_backend(<pid>);

-- 2. 跟业务沟通，修 SQL（加条件、改 JOIN、加 LIMIT）
SELECT&nbsp;t1.id, t2.related_id
FROM&nbsp;huge_table t1
JOIN&nbsp;huge_table t2&nbsp;ON&nbsp;t1.id = t2.related_id
WHERE&nbsp;t1.created_at >&nbsp;NOW() -&nbsp;INTERVAL&nbsp;'7 days'
LIMIT&nbsp;1000;

复盘：

• 慢查询监控 + log_min_duration_statement + auto_explain。
• 应用上线前 review SQL。
• 业务层 pg_advisory_lock 避免重复跑。

实战十一：常见问题 FAQ

Q1：PG 升级从 12 到 15 怎么升？

A：两种方式：(1) pg_upgrade --link 物理升级，停服几分钟；(2) 逻辑复制，零停机。大库推荐逻辑复制。

Q2：PG 怎么实现类似 MySQL Group Replication 的多主？

A：PG 12+ 有 BDR（双向复制）扩展。或者用 Pgpool-II 做多主。原生 PG 不支持多主写入，需要业务层处理冲突。

Q3：PG 索引为什么不被使用？

A：可能原因：(1) 表太小（< 几百行），全表扫更快；(2) 索引列函数转换；(3) 数据类型不匹配；(4) 统计信息过时，跑 ANALYZE；(5) random_page_cost 不对（机械盘 4.0，SSD 1.1）。

Q4：PG 表膨胀怎么处理？

A：(1) 调 autovacuum；(2) pg_repack 在线重建；(3) 紧急时 VACUUM FULL（锁表）。

Q5：pgbouncer transaction 模式有什么限制？

A：不支持 prepared statements、session 级 SET、advisory lock、LISTEN/NOTIFY。生产前测试兼容性。

Q6：PG 怎么监控锁等待？

A：pg_stat_activity 配合 pg_locks。pg_locks 看锁等待关系，pg_stat_activity 看具体 SQL。

Q7：PG 流复制延迟大怎么排查？

A：检查 pg_stat_replication 的 replay_lag 字段。延迟大可能：(1) 主库大事务；(2) 备库 IO 慢；(3) max_wal_senders 不够；(4) hot_standby_feedback 配置。

Q8：PG 怎么从 MySQL 迁移？

A：用 pgloader 自动转换。或者 mysqldump + pg_dump 互转。结构和数据要逐一适配。

Q9：PostgreSQL 16 有什么新特性？

A：逻辑复制增强、参数 bulk insert、JSON 表函数、SQL/JSON 标准改进。

Q10：PG JSONB 性能怎么样？

A：JSONB 字段 + GIN 索引能高效查询嵌套结构。比 MySQL JSON 性能好。

Q11：PG 怎么处理时序数据？

A：用 TimescaleDB 扩展，自动分区 + 压缩 + 保留策略。或者用 BRIN 索引 + 分区表。

Q12：PG 怎么加密？

A：(1) 字段级 pgcrypto；(2) 全盘加密 LUKS；(3) 透明加密（OpenSSL）；(4) 连接 SSL/TLS。

Q13：PG 与 MySQL 性能对比？

A：同硬件下 PG 在 OLAP、复杂查询、JSON、GIS 优于 MySQL。MySQL 在 OLTP 简单查询略胜。但 PG 的稳定性、扩展性、功能性更优。

Q14：PG 主备延迟多少算大？

A：取决于业务。1 秒内：优秀。1~10 秒：观察。10 秒+：介入。流复制 + 同步复制时延迟通常 < 100ms。

总结

PG 调优是一个系统工程：

• 参数调优：shared_buffers、work_mem、effective_cache_size、autovacuum 参数。
• autovacuum 调优：写多的表调高频率，监控 dead tuple。
• 索引调优：B-Tree / GIN / BRIN 选择，复合索引最左前缀，表达式索引。
• SQL 调优：EXPLAIN ANALYZE，pg_stat_statements 找慢查询，auto_explain 自动记录。
• 连接池：pgbouncer transaction 模式池化。
• 监控：Prometheus + postgres_exporter + Grafana。
• HA：流复制 + repmgr / Patroni。

核心心法：

• 默认配置能跑但性能差，必须按硬件调参。
• autovacuum 是命门，调不好表膨胀、查询变慢。
• 索引不是越多越好，无用索引影响写入。
• 慢查询闭环：监控 → 抓 SQL → EXPLAIN → 索引/SQL → 验证。
• pgbouncer 池化减少连接数。
• 流复制 + repmgr 是 HA 标配。
• 升级前全量备份、演练，pg_upgrade –link 物理升级最快。

把这套做扎实，PG 性能能上一个台阶。

附录：常用命令速查

-- 查看版本
SELECTversion();

-- 查看参数
SHOW shared_buffers;
SHOWall;

-- 修改参数
SET work_mem = '256MB';
ALTERSYSTEMSET work_mem = '256MB';
SELECT pg_reload_conf();

-- VACUUM
VACUUM (VERBOSE, ANALYZE) mytable;
VACUUM FULL mytable;  -- 锁表

-- ANALYZE
ANALYZE mytable;
ANALYZE VERBOSE mytable;

-- 重新统计
ALTERTABLE mytable ALTERCOLUMN mycol SETSTATISTICS1000;

-- EXPLAIN
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

-- 索引
CREATEINDEX idx_name ONtable(col);
CREATEINDEX CONCURRENTLY idx_name ONtable(col);
REINDEX INDEX CONCURRENTLY idx_name;
DROPINDEX CONCURRENTLY idx_name;

-- 看进程
SELECT pid, query, state FROM pg_stat_activity;
SELECT&nbsp;pg_cancel_backend(<pid>);
SELECT&nbsp;pg_terminate_backend(<pid>);

-- 表大小
SELECT
&nbsp; relname,
&nbsp; pg_size_pretty(pg_total_relation_size(oid))&nbsp;AS&nbsp;total_size
FROM&nbsp;pg_class
WHERE&nbsp;relkind =&nbsp;'r'AND&nbsp;relnamespace =&nbsp;'public'::regnamespace
ORDERBY&nbsp;pg_total_relation_size(oid)&nbsp;DESC;

-- 慢查询
SELECT&nbsp;*&nbsp;FROM&nbsp;pg_stat_statements&nbsp;ORDERBY&nbsp;mean_exec_time&nbsp;DESCLIMIT10;

-- 锁
SELECT&nbsp;*&nbsp;FROM&nbsp;pg_locks&nbsp;WHERENOT&nbsp;granted;

-- 复制状态
SELECT&nbsp;*&nbsp;FROM&nbsp;pg_stat_replication;
SELECT&nbsp;*&nbsp;FROM&nbsp;pg_stat_wal_receiver;

-- 备份
pg_dump -Fc -d mydb > mydb.dump
pg_restore -d mydb mydb.dump

pg_dumpall > all.sql
psql -f all.sql

pg_basebackup -h 10.0.0.1 -D /var/lib/pgsql/data -U repl -P -Xs -R

-- 升级
pg_upgrade -b <old_bin> -B <new_bin> -d <old_data> -D <new_data>&nbsp;--link

附录：关键参数速查

| 参数 | 默认值 | 推荐值 | 说明 | | — | — | — | — | | shared_buffers | 128MB | 物理内存 25% | 共享缓冲 | | work_mem | 4MB | 32~256MB | sort/hash 内存 | | maintenance_work_mem | 64MB | 1~2GB | 维护操作内存 | | effective_cache_size | 4GB | 物理内存 75% | OS 缓存期望 | | wal_buffers | 16MB | 64MB | WAL 缓冲 | | huge_pages | try | try | 大页 | | wal_level | replica | replica / logical | WAL 详细度 | | fsync | on | on | 必须开 | | synchronous_commit | on | on | 同步提交 | | max_connections | 100 | 200~300 | 最大连接 | | max_wal_size | 1GB | 4GB | WAL 上限 | | min_wal_size | 80MB | 1GB | WAL 下限 | | checkpoint_timeout | 5min | 15~30min | 检查点超时 | | checkpoint_completion_target | 0.5 | 0.9 | checkpoint ramp | | random_page_cost | 4.0 | 1.1 (SSD) | 随机 IO 成本 | | default_statistics_target | 100 | 100~500 | 统计粒度 | | log_min_duration_statement | -1 | 1000ms | 慢查询阈值 | | autovacuum_max_workers | 3 | 3~6 | autovacuum worker | | autovacuum_vacuum_scale_factor | 0.2 | 0.05~0.1 | vacuum 阈值因子 |

不同版本默认值可能略有差异，以实际版本为准。

附录：PG 12~16 主要差异

| 版本 | 主要新特性 | | — | — | | 12 | 流复制增强、分区表改进、JSONPath | | 13 | B-tree 去重、并行 vacuum、增量排序 | | 14 | 连接逻辑预读、扩展统计、TOAST 优化 | | 15 | MERGE、JSON 表函数、性能改进 | | 16 | 逻辑复制增强、bulk insert 参数 |

附录：pg_hba.conf 示例

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    mydb            appuser         10.0.0.0/24             md5
host    replication     replicator      10.0.0.0/24             md5
host    all             all             0.0.0.0/0               reject

附录：备份策略

# 全量 + WAL 归档（推荐）
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
wal_keep_size = 2GB

# 物理备份
pg_basebackup -h 10.0.0.1 -D /backup/base -U repl -P -Xs

# 定时备份
0 2 * * * pg_dumpall | gzip > /backup/full_$(date +\%Y\%m\%d).sql.gz

# 保留策略
find /backup -name "full_*.sql.gz" -mtime +30 -delete

附录：常见错误处理

错误 1：too many connections

# postgresql.conf
max_connections = 300
superuser_reserved_connections = 5

或者用 pgbouncer 池化。

错误 2：disk full

-- 找大表
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relkind = 'r'
ORDERBY pg_total_relation_size(oid) DESC
LIMIT10;

-- 找大索引
SELECT
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDERBY pg_relation_size(indexrelid) DESC
LIMIT10;

错误 3：deadlock detected

-- 看 deadlock 日志
grep "deadlock detected" /var/log/postgresql/*.log

-- 分析锁
SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

log_lock_waits = on + deadlock_timeout 默认 1s。

错误 4：checkpoint too frequent

max_wal_size = 8GB
checkpoint_timeout = 30min

错误 5：could not write to file

临时目录满。改 temp_tablespaces 到独立目录。

附录：参考资源

• PG 官方文档：https://www.postgresql.org/docs/
• PG 中文社区：https://www.pgfans.com/
• pgBadger：https://pgbadger.darold.net/
• pg_repack：https://reorg.github.io/pg_repack/
• repmgr：https://www.repmgr.org/
• Patroni：https://patroni.readthedocs.io/
• pg_stat_statements 文档：https://www.postgresql.org/docs/current/pgstatstatements.html
• pgbouncer 文档：https://www.pgbouncer.org/config.html
• EXPLAIN 详解：https://www.postgresql.org/docs/current/using-explain.html

---

免责声明：

本文所载程序、技术方法仅面向合法合规的安全研究与教学场景，旨在提升网络安全防护能力，具有明确的技术研究属性。

任何单位或个人未经授权，将本文内容用于攻击、破坏等非法用途的，由此引发的全部法律责任、民事赔偿及连带责任，均由行为人独立承担，本站不承担任何连带责任。

本站内容均为技术交流与知识分享目的发布，若存在版权侵权或其他异议，请通过邮件联系处理，具体联系方式可点击页面上方的联系我。

本文转载自：马哥Linux运维 点击关注 👉 点击关注 👉《全栈优化 PostgreSQL：从配置参数落地到 SQL 精细化调优》