PostgreSQL 生产环境运维指南（持续更新）

前言

PostgreSQL 是什么？

想象一下：你是一家超级图书馆的管理员。这家图书馆特别厉害：

不仅能存书（存数据）

还能存地图、视频、3D模型（支持复杂数据类型）

查书特别快（有强大的索引）

支持多人同时查书（高并发）

PostgreSQL 就是这样的"超级图书馆"！ 它是世界上最强大的开源关系型数据库，功能强大、稳定可靠，被无数企业用于核心业务系统。

PostgreSQL（简称 PG）是功能最强大的开源关系型数据库。相比 MySQL，它支持：

更复杂的数据类型（JSON、数组、地理信息等）
更强大的事务支持
更完善的权限管理
更丰富的索引类型

作为运维工程师，我们的职责是：让数据安全、查询快、系统稳。

本文档会告诉你：

PostgreSQL 核心概念
怎么部署和配置
生产环境要看哪些指标
出问题了怎么排查

快速部署 PostgreSQL

Docker 方式部署

Docker 是什么？

就像集装箱把货物标准化一样，Docker 把应用"标准化"了。一次打包，到处运行。

docker run -d \
  --name postgres-demo \
  -e POSTGRES_PASSWORD=postgres123 \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  postgres:16

命令解释：

参数	通俗解释
`--name postgres-demo`	给容器起个名字
`POSTGRES_PASSWORD=postgres123`	设置 root 密码
`POSTGRES_DB=testdb`	创建一个测试数据库
`-p 5432:5432`	端口映射
`postgres:16`	使用 PostgreSQL 16 版本

验证 PostgreSQL 是否正常

# 👀 连接测试
docker exec -it postgres-demo psql -U postgres -c "SELECT version();"

# 或者使用客户端连接
# psql -h localhost -U postgres -p 5432

生产环境推荐配置

docker run -d \
  --name postgres-prod \
  -e POSTGRES_PASSWORD=your_strong_password \
  -e POSTGRES_DB=production_db \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=app_password \
  -p 5432:5432 \
  -v /data/postgres:/var/lib/postgresql/data \
  postgres:16 \
  -c shared_buffers=1GB \
  -c max_connections=500 \
  -c effective_cache_size=3GB \
  -c maintenance_work_mem=256MB \
  -c checkpoint_completion_target=0.9 \
  -c wal_buffers=16MB \
  -c default_statistics_target=100

核心概念详解

1. 数据库（Database）

什么是数据库？

就像图书馆有很多个房间（数据库），每个房间放不同类型的书。

-- 👀 查看所有数据库
SELECT datname FROM pg_database;

-- 创建数据库
CREATE DATABASE myapp;

-- 删除数据库
DROP DATABASE myapp;

2. 表（Table）

什么是表？

就像图书馆每个房间里有书架（表），书架上放着同类的书。

-- 👀 查看所有表
SELECT tablename FROM pg_tables WHERE schemaname = 'public';

-- 创建表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,           -- 自增主键
    name VARCHAR(50) NOT NULL,      -- 姓名
    email VARCHAR(100) UNIQUE,       -- 邮箱（唯一）
    age INTEGER,                     -- 年龄
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 创建时间
);

-- 查看表结构
\d users

3. 索引（Index）

什么是索引？

就像书的目录。如果没有目录，你要找某本书需要从头翻到尾；有目录就能直接找到。

-- 👀 查看索引
\d users

-- 创建索引
CREATE INDEX idx_users_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_users_name_age ON users(name, age);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

4. 视图（View）

什么是视图？

就像图书馆的"热门借阅榜单"。它不是真正的书架，而是从真实书架中统计出来的结果。

-- 创建视图
CREATE VIEW user_stats AS
SELECT 
    name,
    COUNT(*) as order_count
FROM users
GROUP BY name;

5. 存储过程（Stored Procedure）

什么是存储过程？

就像图书馆的"自动化流程"。比如：还书流程 = 检查图书损坏 + 更新库存 + 记录还书时间。存储过程把这些步骤打包成一个"自动化任务"。

-- 创建函数
CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
BEGIN
    RETURN (SELECT COUNT(*) FROM users);
END;
$$ LANGUAGE plpgsql;

-- 调用函数
SELECT get_user_count();

6. 事务（Transaction）

什么是事务？

就像银行转账：

从 A 账户扣钱

向 B 账户加钱

这两步必须一起成功或一起失败！

-- 开始事务
BEGIN;

-- 操作1
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

-- 操作2
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交
COMMIT;

-- 或者回滚
-- ROLLBACK;

常用命令

连接 PostgreSQL

# 连接本地数据库
psql -U postgres

# 连接远程数据库
psql -h 192.168.1.10 -p 5432 -U postgres

# 连接指定数据库
psql -U postgres -d myapp

数据库操作

-- 👀 查看所有数据库
\l

-- 👀 查看当前数据库
SELECT current_database();

-- 🗑️ 删除数据库
DROP DATABASE myapp;

表操作

-- 👀 查看当前数据库的所有表
\d

-- 👀 查看表结构
\d users

-- 🗑️ 删除表
DROP TABLE users;

数据操作（CRUD）

-- 👀 查 - 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;

-- ➕ 增 - 插入数据
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- ✏️ 改 - 更新数据
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- 🗑️ 删 - 删除数据
DELETE FROM users WHERE id = 1;

用户和权限

-- 👀 查看所有用户
SELECT usename FROM pg_user;

-- 创建用户
CREATE USER appuser WITH PASSWORD 'password123';

-- 授权
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- 给用户授权访问表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;

-- 🗑️ 删除用户
DROP USER appuser;

备份和恢复

# 👀 备份数据库
docker exec postgres-demo pg_dump -U postgres myapp > backup.sql

# 🗑️ 恢复数据库
docker exec -i postgres-demo psql -U postgres myapp < backup.sql

运维监控

关键指标

1. 连接数

-- 👀 当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 👀 最大连接数
SHOW max_connections;

-- 👀 按用户查看连接数
SELECT usename, count(*) FROM pg_stat_activity GROUP BY usename;

指标	通俗解释
当前连接数	现在有多少人连接
空闲连接	有多少连接在等着
最大连接数	最多能连多少人

2. 数据库大小

-- 👀 查看数据库大小
SELECT pg_database_size('myapp');

-- 👀 查看表大小
SELECT pg_size_pretty(pg_total_relation_size('users'));

3. 查询统计

-- 👀 查看慢查询
SELECT 
    query, 
    calls, 
    mean_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

需要先开启 pg_stat_statements 扩展：

CREATE EXTENSION pg_stat_statements;

4. 表的统计信息

-- 👀 查看表统计信息
SELECT 
    schemaname,
    relname,
    n_tup_ins,    -- 插入行数
    n_tup_upd,    -- 更新行数
    n_tup_del,    -- 删除行数
    n_live_tup,   -- 存活行数
    n_dead_tup    -- 死亡行数
FROM pg_stat_user_tables;

监控脚本示例

#!/bin/bash

echo "=========================================="
echo "      PostgreSQL 监控报告"
echo "=========================================="
echo "时间: $(date)"
echo ""

echo "🔌 【连接状态】"
docker exec postgres-demo psql -U postgres -t -c "SELECT count(*) FROM pg_stat_activity;" 2>/dev/null
echo ""

echo "💾 【数据库大小】"
docker exec postgres-demo psql -U postgres -t -c "SELECT pg_database_size(current_database());" 2>/dev/null
echo ""

echo "📊 【表统计】"
docker exec postgres-demo psql -U postgres -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 5;" 2>/dev/null
echo "=========================================="

生产环境最佳实践

1. 内存配置

# PostgreSQL 配置参数建议

# 共享内存（建议为系统内存的 25%）
shared_buffers = 1GB

# 有效缓存（建议为系统内存的 75%）
effective_cache_size = 3GB

# 维护操作内存（如 VACUUM、CREATE INDEX）
maintenance_work_mem = 256MB

# 排序操作内存
work_mem = 64MB

2. 连接数配置

-- 查看最大连接数
SHOW max_connections;

-- 修改最大连接数（需要重启）
-- 在 postgresql.conf 中设置
max_connections = 500

3. 日志配置

-- 开启慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 超过1秒记录

-- 查看日志
SELECT * FROM pg_log;

4. 定期维护

-- 手动 VACUUM（清理垃圾数据）
VACUUM ANALYZE users;

-- 只 VACUUM（不更新统计信息）
VACUUM users;

-- 重建表（减少碎片）
REINDEX TABLE users;

5. 安全配置

-- 修改密码
ALTER USER postgres PASSWORD 'new_strong_password';

-- 禁止远程 root 登录
ALTER USER postgres WITH NOSUPERUSER;

-- 限制连接来源
-- 在 pg_hba.conf 中配置
# host all all 0.0.0.0/0 md5  # 允许所有IP密码登录（生产环境不建议）
# host all all 192.168.1.0/24 md5  # 只允许内网

常见问题排查

问题 1：连接数过多

现象： 连接不上数据库，提示 “too many clients”

通俗解释： 就像餐厅满客了，新客人进不来。

排查步骤：

-- 1. 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 2. 查看最耗时的查询
SELECT * FROM pg_stat_activity ORDER BY query_start DESC LIMIT 10;

-- 3. 查看空闲连接
SELECT count(*) FROM pg_stat_activity WHERE state = 'idle';

解决方案：

增加最大连接数

-- 修改配置文件 postgresql.conf
max_connections = 1000

杀掉空闲连接

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
AND query_start < now() - interval '1 hour';

问题 2：查询很慢

现象： 一个简单的 SELECT 查询要好几秒

通俗解释： 就像去图书馆找书，找了半天才找到。

排查步骤：

-- 1. 查看查询计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 2. 查看是否使用索引
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

解决方案：

添加索引

CREATE INDEX idx_users_email ON users(email);

分析表（更新统计信息）

ANALYZE users;

**避免 SELECT *** - 只查询需要的列

-- ❌ 慢
SELECT * FROM users WHERE email = 'test@example.com';

-- ✅ 快
SELECT id, name, email FROM users WHERE email = 'test@example.com';

问题 3：表膨胀

现象： 表占用空间很大，但实际数据不多

通俗解释： 就像图书馆有很多破损的书架占地方。

排查步骤：

-- 查看表大小
SELECT 
    relname,
    pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

解决方案：

VACUUM 清理

VACUUM FULL users;

重建索引

REINDEX TABLE users;

问题 4：锁等待

现象： 一个查询卡住不动

通俗解释： 两个人同时借同一本书，都等着对方放手。

排查步骤：

-- 查看锁
SELECT 
    pg_blocking_pids(pid) as blocked_by,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle';

解决方案：

查看并杀掉阻塞进程

-- 查看锁详情
SELECT * FROM pg_locks WHERE relation = 'users'::regclass;

-- 杀掉阻塞进程
SELECT pg_terminate_backend(pid);

问题 5：磁盘空间不足

现象： 数据库无法写入

通俗解释： 图书馆书架满了，没地方放新书了。

排查步骤：

# 查看磁盘使用
df -h

# 查看 PostgreSQL 数据目录大小
docker exec postgres-demo du -sh /var/lib/postgresql/data

解决方案：

清理日志

# 清理 PostgreSQL 日志
rm -rf /var/lib/postgresql/data/log/*

清理不需要的表

DROP TABLE old_table;

VACUUM 回收空间

VACUUM FULL;

总结

核心概念

概念	通俗解释
Database	图书馆的房间
Table	房间里的书架
Index	书的目录
View	热门榜单（统计结果）
Procedure	自动化流程
Transaction	转账操作

运维黄金法则

监控连接数 - 别让连接数爆了
添加索引 - 让查询飞起来
定期 VACUUM - 清理垃圾数据
配置参数优化 - 内存利用最大化
定期备份 - 数据是命，不能丢

最后一句话总结：
PostgreSQL 就是"超级图书馆"，功能强大但需要用心运维。配置好参数，监控好指标，它就能稳稳地运行！

第二部分：PostgreSQL 高可用集群

PostgreSQL 主从复制

什么是主从复制？

通俗解释：

就像古代的"备份信使"。皇帝下达命令后，会派多个信使同时出发去不同的地方送信。

即使一个信使被抓或遇险，其他信使还能把信送到

PostgreSQL 主从复制就是这个原理：一份数据，多个备份，坏了还能用！

组成角色：

角色	通俗解释	职责
Master（主库）	“Original”（正品）	负责写入数据
Slave（从库）	“Copy”（复制品）	负责读取数据

主从复制原理

┌─────────────┐         WAL日志         ┌─────────────┐
│   主库      │  ──────────────────>  │   从库      │
│  (写入)     │      Stream           │  (读取)     │
└─────────────┘                       └─────────────┘

步骤：
1. 主库执行 SQL，写入数据
2. 主库记录 WAL 日志
3. 从库通过流复制读取 WAL
4. 从库重放 WAL
5. 数据同步完成！

主从复制配置

步骤1：配置主库

# postgresql.conf
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

-- 创建复制用户
CREATE USER replica WITH REPLICATION PASSWORD 'replica_password';

步骤2：配置从库

# postgresql.conf
hot_standby = on

# recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.10 port=5432 user=replica password=replica_password'

步骤3：启动从库

# 从主库拉取基础备份
pg_basebackup -h 192.168.1.10 -U replica -D /var/lib/postgresql/16/main -P -Xs -R

验证主从复制

-- 👀 在从库查看复制状态
SELECT * FROM pg_stat_replication;

PostgreSQL 流复制

什么是流复制？

通俗解释：

就像看直播！主播（主库）一边播（写入），观众（从库）一边看（同步）。延迟极低，几乎实时！

流复制 vs 异步复制

特性	流复制	异步复制
延迟	低（秒级）	可能较高
性能	略低	高
数据安全	高	低

Patroni 高可用集群

什么是 Patroni？

通俗解释：

想象一下：一个军事基地有很多士兵（PostgreSQL），其中有一个是"指挥官"（主节点），其他是"候补"（从节点）。

Patroni 就是"自动化指挥官"！ 它会自动：

监控指挥官是否活着

如果指挥官挂了，自动选新的指挥官

保证数据不丢失

Patroni 架构

┌─────────────────────────────────────────┐
│              HAProxy / VIP              │
│           (负载均衡 + 高可用)             │
└─────────────────┬───────────────────────┘
                  │
    ┌─────────────┼─────────────┐
    │             │             │
┌───▼───┐   ┌───▼───┐   ┌───▼───┐
│节点1  │   │节点2  │   │节点3  │
│(Leader)│   │(Follower)│  │(Follower)│
│ 可读写 │   │ 只读   │   │ 只读   │
└───────┘   └───────┘   └───────┘
         Patroni 管理
         (自动故障切换)

Patroni 配置

patroni.yml 示例：

scope: postgres
namespace: /service/

name: node1

restapi:
  listen: 0.0.0.0:8008
  connect_address: 192.168.1.10:8008

postgresql:
  listen: 0.0.0.0:5432
  connect_address: 192.168.1.10:5432
  data_dir: /data/postgresql
  parameters:
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    hot_standby: on

consul:
  hosts: 192.168.1.10:8500,192.168.1.20:8500

tags:
  nofailover: false
  noloadbalance: false

PostgreSQL 分片集群（Citus）

什么是 Citus？

通俗解释：

想象一下：一家超大型图书馆，书太多了，一个书架放不下怎么办？

分馆策略！

分馆A：存放 A-M 开头的书

分馆B：存放 N-Z 开头的书

Citus 就是这个"分馆系统"！ 它把数据分散存储在多个节点上。

Citus 架构

┌─────────────────────────────────────────┐
│              Coordinator 节点             │
│           (协调节点 - 路由)               │
└─────────────────┬───────────────────────┘
                  │
    ┌─────────────┼─────────────┐
    │             │             │
┌───▼───┐   ┌───▼───┐   ┌───▼───┐
│ Worker│   │ Worker│   │ Worker│
│  节点1│   │  节点2│   │  节点3│
│ (分片1)│   │(分片2)│   │(分片3)│
└───────┘   └───────┘   └───────┘

Citus 使用

-- 1. 开启 Citus 扩展
CREATE EXTENSION citus;

-- 2. 添加 Worker 节点
SELECT * FROM citus_add_node('192.168.1.20', 5432);
SELECT * FROM citus_add_node('192.168.1.30', 5432);

-- 3. 创建分布式表
CREATE TABLE users (
    id BIGSERIAL,
    name TEXT,
    email TEXT
);
SELECT create_distributed_table('users', 'id');

-- 4. 查询自动路由
SELECT * FROM users WHERE id = 1;

三种集群模式对比

模式	架构	优点	缺点	适用场景
主从复制	1主+N从	配置简单	主库单点故障	读写分离
Patroni	主从+自动切换	高可用、自动故障切换	需要额外组件	生产环境
Citus	分布式	水平扩展	配置复杂	大数据量

选择建议

场景	推荐模式
单机测试	单机模式
读写分离	主从复制
生产高可用	Patroni
大数据量	Citus
不想自己管理	使用云服务（RDS）

集群运维监控

监控脚本

#!/bin/bash

echo "=========================================="
echo "   PostgreSQL 主从复制监控报告"
echo "=========================================="
echo "时间: $(date)"
echo ""

echo "🔄 【复制状态】"
docker exec postgres-demo psql -U postgres -c "SELECT * FROM pg_stat_replication;" 2>/dev/null
echo ""

echo "🔌 【连接数】"
docker exec postgres-demo psql -U postgres -c "SELECT count(*) as connections FROM pg_stat_activity;" 2>/dev/null
echo "=========================================="

关键监控指标

指标	含义	告警阈值
pg_stat_replication	复制进程状态	无数据
pg_current_wal_lsn	WAL 位置	主从不一致
replication_slot	复制槽	过多

常见问题排查

问题 1：复制延迟

现象： 从库数据比主库慢

排查：

-- 查看复制延迟
SELECT 
    now() - pg_last_xact_replay_timestamp() AS replication_lag;

解决：

检查网络延迟
调整 wal_sender / wal_receiver 参数

问题 2：复制中断

现象： 复制状态不是 streaming

排查：

-- 查看复制状态
SELECT * FROM pg_stat_replication;

解决：

检查网络连通性
检查复制用户权限
重做复制

问题 3：Patroni 故障切换失败

现象： 主节点挂了，但没有选出新主节点

排查：

# 查看 Patroni 状态
patronictl list

解决：

检查 Consul/Etcd 是否正常
检查网络连通性
查看日志

持续更新中… 如有问题或建议，欢迎交流讨论！

PostgreSQL 生产环境运维指南（持续更新）

前言

目录

快速部署 PostgreSQL

Docker 方式部署

验证 PostgreSQL 是否正常

生产环境推荐配置

核心概念详解

1. 数据库（Database）

2. 表（Table）

3. 索引（Index）

4. 视图（View）

5. 存储过程（Stored Procedure）

6. 事务（Transaction）

常用命令

连接 PostgreSQL

数据库操作

表操作

数据操作（CRUD）

用户和权限

备份和恢复

运维监控

关键指标

1. 连接数

2. 数据库大小

3. 查询统计

4. 表的统计信息

监控脚本示例

生产环境最佳实践

1. 内存配置

2. 连接数配置

3. 日志配置

4. 定期维护

5. 安全配置

常见问题排查

问题 1：连接数过多

问题 2：查询很慢

问题 3：表膨胀

问题 4：锁等待

问题 5：磁盘空间不足

总结

核心概念

运维黄金法则

第二部分：PostgreSQL 高可用集群

PostgreSQL 主从复制

什么是主从复制？

主从复制原理

主从复制配置

验证主从复制

PostgreSQL 流复制

什么是流复制？

流复制 vs 异步复制

Patroni 高可用集群

什么是 Patroni？

Patroni 架构

Patroni 配置

PostgreSQL 分片集群（Citus）

什么是 Citus？

Citus 架构

Citus 使用

三种集群模式对比

选择建议

集群运维监控

监控脚本

关键监控指标

常见问题排查

问题 1：复制延迟

问题 2：复制中断

问题 3：Patroni 故障切换失败

PostgreSQL 生产环境运维指南（持续更新）

分享文章

文章目录