3.7 PostgreSQL 数据库安装与管理

预计阅读时间：17 分钟

学习目标

学完本章后，你将能够：

• 安装并配置 PostgreSQL 数据库服务器
• 使用 psql 元命令和 SQL 管理数据库与角色
• 创建表、插入和查询数据
• 使用 pg_dump/pg_restore 备份恢复
• 理解 VACUUM、shared_buffers 等维护与优化要点

核心知识

• PostgreSQL——功能最丰富的开源关系型数据库，以 SQL 标准兼容性、扩展性和事务完整性著称
• psql——PostgreSQL 的命令行交互工具，以 \ 开头的是元命令（如 \l 列出数据库）
• 角色（Role）——PostgreSQL 的权限管理单元，可作"用户"或"用户组"使用
• 模式（Schema）——数据库内部的命名空间，用于组织表、视图等对象
• VACUUM——回收已删除行占用的存储空间并更新查询统计信息
• MVCC（多版本并发控制）——PostgreSQL 的事务隔离机制，读不阻塞写、写不阻塞读
• pg_stat_statements——跟踪 SQL 执行统计的扩展，用于定位慢查询
• WAL（预写日志）——保证数据持久性和崩溃恢复的关键机制

知识关联

• 前置知识：1.8：软件包管理 软件包管理、基本 SQL 概念
• 对比技术：3.6：MySQL 数据库 MySQL/MariaDB（另一主流关系型数据库，各有侧重）
• 后续影响：PostgreSQL 是 3.14：LAMP/LEMP 环境 LAMP/LEMP 环境中可选的数据库层

原理讲解

PostgreSQL vs MySQL

PG vs MySQL 选型对比（扩展）

MVCC 与 VACUUM

PostgreSQL 通过 MVCC 实现高并发：当一行数据被更新时，旧版本仍保留在表中（供旧事务读取），新版本被创建。这避免了读写冲突，但代价是"死元组"会累积，导致表膨胀和性能下降。VACUUM 回收死元组占用的空间，ANALYZE 更新查询优化器的统计信息。

PostgreSQL 为什么选择 MVCC 而不是锁？

传统数据库用锁来保证事务隔离：读操作加共享锁，写操作加排他锁，读写互斥。这意味着一个长时间运行的查询会阻塞所有写入，反之亦然。PostgreSQL 的 MVCC 完全不同——读操作不加任何锁，它通过事务快照看到数据在某个时间点的一致视图，写操作创建新版本而不修改旧版本。这实现了"读不阻塞写、写不阻塞读"。

代价是：PostgreSQL 需要维护多个版本的数据（旧版本保留在表中直到被 VACUUM 清理），这会导致表膨胀。MySQL 的 InnoDB 也用 MVCC，但它的回滚段（undo log）存储旧版本，不在主表中，所以表膨胀问题较轻。这是两种数据库在架构上的根本差异——PostgreSQL 追求 SQL 标准兼容性和功能丰富性，MySQL 追求简单高效。

为什么 PostgreSQL 需要 VACUUM 而 MySQL 不需要？

MySQL InnoDB 的旧版本存储在 undo log 中，会自动清理。PostgreSQL 把旧版本直接留在表文件中（称为"死元组"），由 autovacuum 进程定期回收。这不是设计缺陷，而是权衡的结果：PostgreSQL 的 MVCC 实现更简单、更符合 SQL 标准，代价是需要额外的维护工作。autovacuum 默认开启且通常不需要手动干预，但在高写入负载下可能需要调整参数（如 autovacuum_vacuum_scale_factor）。

WAL 与崩溃恢复

PostgreSQL 的每次数据修改都会先写入 WAL（Write-Ahead Log，预写日志），再落盘数据页。如果服务器崩溃，重启时数据库会回放 WAL 把数据恢复到一致状态。WAL 也是流复制和 PITR（时间点恢复）的基础——从节点通过持续接收主节点的 WAL 记录保持同步，备份机通过应用 WAL 可以把数据库恢复到任意时间点。

# 查看 WAL 状态（需切换为超级用户）
SELECT pg_current_wal_lsn();      -- 当前 WAL 写入位置
SELECT * FROM pg_stat_wal;        -- WAL 写入统计
SHOW wal_level;                   -- 应配置为 replica 或 logical（默认 replica）
SHOW max_wal_size;                -- WAL 段文件总大小上限（默认 1GB）

示例代码

1. 安装与登录

# 安装
sudo apt update && sudo apt install postgresql postgresql-contrib -y

# 检查服务状态
sudo systemctl status postgresql
# 输出: ● postgresql.service - PostgreSQL RDBMS
#        Active: active (exited)

# 以 postgres 系统用户登录
sudo -u postgres psql

# 或设置密码后远程连接
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'new_password';"

2. 数据库与角色管理

-- 列出数据库
\l
-- 输出:
--   Name    | Owner | Encoding | Collate   |   Ctype   |
-- ----------+-------+----------+-----------+-----------+
--  postgres | postgres | UTF8  | C.UTF-8 | C.UTF-8   |
--  mydb     | postgres | UTF8  | C.UTF-8 | C.UTF-8   |

-- 创建数据库
CREATE DATABASE myapp ENCODING 'UTF8';

-- 创建角色（相当于用户）
CREATE ROLE appuser WITH LOGIN PASSWORD 'secure_pass';

-- 授权：将数据库的连接权限授予用户
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- 但连接后还需要授予 schema 权限
\c myapp
GRANT ALL ON SCHEMA public TO appuser;

3. 建表与基本操作

-- 创建表（支持 SERIAL 自增）
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 插入数据
INSERT INTO users (name, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com');

INSERT INTO posts (title, content, user_id) VALUES
    ('PostgreSQL 入门', '内容...', 1);

-- 查询（JOIN）
SELECT p.title, u.name, p.created_at
FROM posts p JOIN users u ON p.user_id = u.id
ORDER BY p.created_at DESC;

-- 聚合查询
SELECT u.name, COUNT(p.id) AS post_count
FROM users u LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.name;

4. 备份与恢复

# 备份单个数据库（SQL 格式）
pg_dump -U postgres myapp > myapp_backup.sql

# 自定义格式（支持并行恢复、压缩、选择性恢复）
pg_dump -U postgres -Fc myapp > myapp.dump

# 备份所有数据库
pg_dumpall -U postgres > all.sql

# 恢复 SQL 格式
psql -U postgres -d myapp < myapp_backup.sql

# 恢复自定义格式
pg_restore -U postgres -d myapp myapp.dump

# 压缩备份（推荐）
pg_dump -U postgres myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

5. 性能优化与维护

-- 查看 postgresql.conf 关键参数
SHOW shared_buffers;          -- 建议 25% 可用内存
SHOW effective_cache_size;    -- 建议 75% 可用内存
SHOW work_mem;                -- 每个排序/哈希操作的内存（不宜过大）
SHOW maintenance_work_mem;    -- VACUUM/INDEX 等维护操作的内存

-- 启用 pg_stat_statements（需修改 postgresql.conf 后重启）
-- shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;

-- 查看最耗时的查询
SELECT query, calls, total_exec_time / 1000 AS total_sec,
       mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- VACUUM 维护
VACUUM;                         -- 回收空间（不影响读）
VACUUM ANALYZE;                 -- 回收空间并更新统计
VACUUM VERBOSE;                 -- 显示详细信息
ANALYZE;                        -- 只更新统计信息

-- 查看活动连接
SELECT pid, state, query_start, wait_event, query
FROM pg_stat_activity
WHERE state = 'active';

6. JSONB 与窗口函数

-- JSONB：无 schema 的数据，可索引、可查询
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL
);

INSERT INTO events (payload) VALUES
    ('{"type": "click", "user": "alice", "duration_ms": 120}'),
    ('{"type": "purchase", "user": "bob", "amount": 99.9}');

-- JSONB 查询与索引
CREATE INDEX idx_events_type ON events ((payload->>'type'));
SELECT payload->>'user' AS username, payload->>'amount' AS amount
FROM events WHERE payload @> '{"type": "purchase"}';

-- 窗口函数：计算每个用户文章数的同时保留明细行
SELECT author, title,
       COUNT(*) OVER (PARTITION BY author) AS author_total,
       ROW_NUMBER() OVER (PARTITION BY author ORDER BY created_at DESC) AS rn
FROM posts;

-- 排名函数对比
SELECT title, views,
       RANK()       OVER (ORDER BY views DESC) AS rk,   -- 并列跳号
       DENSE_RANK() OVER (ORDER BY views DESC) AS drk,  -- 并列不跳号
       NTILE(4)     OVER (ORDER BY views DESC) AS quartile
FROM posts;

7. CTE 与递归查询

-- WITH 子句（CTE）：把复杂查询拆成可读步骤
WITH recent AS (
    SELECT * FROM posts WHERE created_at > NOW() - INTERVAL '7 days'
), author_stats AS (
    SELECT user_id, COUNT(*) AS cnt FROM recent GROUP BY user_id
)
SELECT u.name, s.cnt
FROM author_stats s JOIN users u ON u.id = s.user_id
ORDER BY s.cnt DESC;

-- 递归 CTE：遍历树形结构（如评论的楼层回复）
WITH RECURSIVE reply_tree AS (
    -- 锚点：顶层评论
    SELECT id, parent_id, content, 1 AS depth
    FROM comments WHERE parent_id IS NULL AND post_id = 1
    UNION ALL
    -- 递归：找到所有子评论
    SELECT c.id, c.parent_id, c.content, t.depth + 1
    FROM comments c
    JOIN reply_tree t ON c.parent_id = t.id
)
SELECT * FROM reply_tree ORDER BY depth, id;

-- 递归限制：PostgreSQL 的 WITH RECURSIVE 没有内置迭代上限（会无限循环直到内存耗尽），
-- 生产环境应加 depth 守卫（WHERE depth <= 50）或用 statement_timeout 兜底

8. 事务与隔离级别

-- PostgreSQL 四种隔离级别（与 SQL 标准一一对应）
SHOW transaction_isolation;
-- 输出: read committed（默认）

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;   -- 或 ROLLBACK;

-- 设置隔离级别
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... 业务逻辑 ...
COMMIT;

-- 演示 MVCC 读不阻塞写：
-- 会话 A: BEGIN; UPDATE accounts SET balance = 0 WHERE id = 1; （不提交）
-- 会话 B: SELECT balance FROM accounts WHERE id = 1;  → 仍读到旧值，立即返回
-- 会话 A: COMMIT;
-- 会话 B 再次 SELECT → 读到新值

-- 查看活动事务与锁
SELECT pid, state, xact_start, wait_event_type, wait_event
FROM pg_stat_activity WHERE state <> 'idle';

-- 强制终止长时间运行的事务
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > INTERVAL '30 minutes';

9. 角色权限与 schema 隔离

-- 角色即用户/用户组（PostgreSQL 没有独立"用户"概念）
CREATE ROLE app_admin WITH LOGIN PASSWORD 'strong_pass';
CREATE ROLE app_readonly WITH LOGIN PASSWORD 'read_pass';

-- 用组角色统一管理权限
CREATE ROLE app_group NOLOGIN;
GRANT app_group TO app_admin;
GRANT app_group TO app_readonly;

-- schema 级隔离：每个业务一个 schema，互不可见
CREATE SCHEMA sales AUTHORIZATION app_admin;
CREATE SCHEMA inventory;

GRANT USAGE ON SCHEMA sales TO app_group;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO app_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
    GRANT SELECT ON TABLES TO app_readonly;   -- 未来新建的表也自动授权

-- 撤销权限
REVOKE DELETE ON ALL TABLES IN SCHEMA sales FROM app_readonly;

-- 查看角色与权限
\du
SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_schema = 'sales';

10. 备份恢复完整流程与 PITR

# 每日全量备份脚本 /usr/local/bin/pg-backup.sh
#!/bin/bash
BACKUP_DIR=/backup/pg
DATE=$(date +%Y%m%d_%H%M)
mkdir -p $BACKUP_DIR

# 自定义格式 + 压缩级别 9（注意：pg_dump 并行导出 -j 需目录格式 -Fd；pg_restore -j 对 -Fc/-Fd 均可）
pg_dump -h localhost -U backup_user -Fc -Z 9 myapp \
    > $BACKUP_DIR/myapp_$DATE.dump

# 保留 7 天
find $BACKUP_DIR -name '*.dump' -mtime +7 -delete

# 恢复：先建库，再 pg_restore
createdb -h localhost -U postgres myapp_restore
pg_restore -h localhost -U postgres -d myapp_restore -j 4 \
    /backup/pg/myapp_latest.dump

# 选择性恢复：只恢复某张表
pg_restore -d myapp --table=posts /backup/pg/myapp_latest.dump

# ---- PITR 时间点恢复（基于 WAL 归档）----
# 1. postgresql.conf 开启归档:
#    archive_mode = on
#    archive_command = 'cp %p /backup/pg/wal/%f'
# 2. 做基础备份（pg_basebackup 物理备份；注意：pg_dump 是逻辑备份，
#    不能作为 PITR 的基础备份——WAL 重放只对物理备份有效）
pg_basebackup -h localhost -U backup_user -D /backup/pg/base -Ft -z

# 3. 误操作后恢复到指定时间点：
#    将 base 恢复到数据目录，创建 recovery.signal，编辑 postgresql.conf:
#    restore_command = 'cp /backup/pg/wal/%f %p'
#    recovery_target_time = '2026-07-30 10:23:00'
# 4. 启动数据库完成恢复，确认数据后恢复原数据目录

11. 运维管理：autovacuum 与监控视图

-- autovacuum 配置（postgresql.conf，默认开启，一般无需关闭）
SHOW autovacuum;                    -- on
SHOW autovacuum_vacuum_threshold;   -- 50 行（死元组超过阈值+比例才触发）
SHOW autovacuum_naptime;            -- 1min（检查间隔）

-- 手动触发 VACUUM（大表用 VACUUM FULL 但会锁表）
VACUUM ANALYZE VERBOSE posts;
REINDEX TABLE posts;                -- 重建索引，回收膨胀

-- 表膨胀监控：死元组比例
SELECT relname,
       n_dead_tup,
       n_live_tup,
       round(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
ORDER BY dead_pct DESC NULLS LAST
LIMIT 10;

-- 最频繁/最耗时查询
SELECT query, calls, round(mean_exec_time::numeric, 1) AS mean_ms,
       round(total_exec_time::numeric/1000, 1) AS total_sec
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 数据库大小与表大小
SELECT pg_size_pretty(pg_database_size('myapp'));
SELECT pg_size_pretty(pg_total_relation_size('posts'));

-- psql 常用运维元命令补充
\l            -- 列数据库
\c myapp      -- 连接数据库
\dt+          -- 列表 + 大小/描述
\d+ posts     -- 详细表结构（含索引、注释）
\i script.sql -- 执行 SQL 文件
\x            -- 竖排显示（宽表友好）
\timing       -- 显示每条语句耗时
\watch 5      -- 每 5 秒重放上一条查询（监控利器）

常见错误

最佳实践

练习题

1. （概念）PostgreSQL 中的 MVCC 是什么？它如何让"读不阻塞写"成为可能？VACUUM 在其中扮演什么角色？
2. （概念）pg_dump -Fc 和普通 SQL 格式的备份有什么区别？什么场景适合用自定义格式？
3. （实操）安装 PostgreSQL，创建一个名为 blog 的数据库，创建 authors 和 articles 两张表（含外键关联），插入 3 位作者和每人 2 篇文章，使用 JOIN 查询每位作者及其文章数量。
4. （实操）创建两个角色：readonly（只能 SELECT）和 editor（可以 INSERT/UPDATE/DELETE）。为 readonly 用户测试删除操作是否被拒绝。
5. （🔍 挑战）启用 pg_stat_statements，执行 INSERT 10 万条数据和一次复杂 JOIN 查询，然后用 pg_stat_statements 找出最耗时的 SQL。用 EXPLAIN ANALYZE 分析该查询的执行计划，尝试通过创建索引将查询时间降低至少 50%。

学习检查点

学完本章后，请检验自己是否掌握以下内容：

本章总结

PostgreSQL 以其标准的 SQL 兼容性、丰富的扩展能力和强大的并发控制（MVCC）在开源数据库中独树一帜。psql 元命令是日常管理的利器，角色权限体系比 MySQL 更灵活，pg_dump/pg_restore 的多种备份格式适应不同场景。理解 VACUUM 机制和 shared_buffers 等关键参数是性能优化的基础。与 MySQL 的选择没有绝对的好坏——MySQL 更简单易用，PostgreSQL 更适合复杂查询和对数据完整性要求高的场景。

速查表

学习路径建议

• 对比学习：对比 3.6：MySQL 数据库 MySQL 理解两者的设计哲学差异
• 学完本章后建议阅读 db01 SQL 优化与索引调优（EXPLAIN、慢查询、连接池）
• 深入可读《PostgreSQL 实战》或官方文档

延伸阅读