PostgreSQL 备份恢复与流式复制（完整版）
---
课程目标
- [ ] 备份策略概述（逻辑备份 vs 物理备份）
- [ ] 逻辑备份与恢复（pg_dump、pg_dumpall、pg_restore）
- [ ] 物理备份与恢复（pg_basebackup 全量备份）
- [ ] 增量备份与恢复（pg_basebackup 增量 + pg_combinebackup 合并）
- [ ] 流式复制（主从配置、状态监控）
---
第一部分：备份与恢复
---
一、备份方案概述
PostgreSQL 提供两种备份方案：
| 备份类型 | 说明 | 工具 | 特点 |
|---|---|---|---|
| 逻辑备份 | 导出 SQL 语句或自定义格式文件 | pg_dump、pg_dumpall | 可跨版本恢复、可选择性恢复、速度较慢 |
| 物理备份 | 直接复制数据目录文件 | pg_basebackup | 速度快、恢复快、不能跨版本、必须整库恢复 |
选择建议：
- 小型数据库、需要跨版本迁移 → 逻辑备份
- 大型数据库、要求快速恢复 → 物理备份
- 生产环境建议：物理备份 + 逻辑备份 结合使用
二、逻辑备份与恢复
2.1 逻辑备份工具
| 工具 | 说明 |
|---|---|
| pg_dump | 备份单个数据库（可选单表、多表） |
| pg_dumpall | 备份全部数据库（含全局对象：角色、表空间） |
2.2 数据准备
在开始备份之前，先准备测试数据：
-- 1. 创建 itheima 数据库
CREATE DATABASE itheima;
-- 2. 切换到 itheima
\c itheima
-- 3. 创建 projects 表
CREATE TABLE projects (
proj_id SERIAL PRIMARY KEY,
proj_name VARCHAR(100) NOT NULL,
start_date DATE
);
INSERT INTO projects (proj_name, start_date) VALUES
('研发A项目', '2026-03-01'),
('市场B项目', '2026-03-01'),
('财务系统升级', '2026-03-15');
-- 4. 创建 users 表
create table users(
id SERIAL PRIMARY KEY,
name varchar(20)
);
insert into users (name) values ('张三'),('李四');
-- 5. 创建 db1 数据库
CREATE DATABASE db1;
-- 6. 切换到 db1
\c db1;
-- 7. 创建 students 表
create table students (
id SERIAL PRIMARY KEY,
name varchar(20)
);
insert into students (name) values ('张三'),('李四');-- 验证 itheima 库数据
itheima=# SELECT * FROM projects;
proj_id | proj_name | start_date
---------+--------------+------------
1 | 研发A项目 | 2026-03-01
2 | 市场B项目 | 2026-03-01
3 | 财务系统升级 | 2026-03-15
(3 行记录)
itheima=# SELECT * FROM users;
id | name
----+------
1 | 张三
2 | 李四
(2 行记录)
-- 验证 db1 库数据
db1=# SELECT * FROM students;
id | name
----+------
1 | 张三
2 | 李四
(2 行记录)2.3 pg_dump 语法与常用选项
# 基本语法
pg_dump [选项] 库名 > db.sql常用选项：
| 选项 | 说明 |
|---|---|
-h host | 指定数据库主机名或 IP |
-p port | 指定端口号 |
-U user | 指定连接使用的用户名 |
-W | 按提示输入密码 |
-F, --format=c|d|t|p | 输出格式：c=自定义(二进制压缩)、d=目录、t=tar包、p=纯文本SQL |
-f, --file | 输出到指定文件 |
-j, --jobs=num | 指定并行导出的并行度 |
--inserts | 使用 INSERT 命令导出（比默认 COPY 慢，但兼容非PG数据库） |
-t | 指定备份的表名 |
格式对比：
| 格式 | 参数 | 说明 | 恢复工具 |
|---|---|---|---|
| 纯文本 SQL | -Fp | 大库不推荐，不可并行恢复 | psql |
| 自定义格式 | -Fc | 二进制压缩，推荐 | pg_restore |
| tar 包 | -Ft | 可用 tar 工具查看 | pg_restore |
| 目录格式 | -Fd | 每个表一个文件，支持并行 | pg_restore |
2.4 备份操作
2.4.1 备份单表
# 切换到 postgres 用户
su - postgres
# 默认使用 pgsql 的 COPY 组织 SQL 语句，效率更高（推荐）
pg_dump itheima -t users > /tmp/users.sql
# 导出标准 SQL 语句（INSERT 形式，兼容性更好）
pg_dump itheima -t users --inserts > /tmp/users.sql# 查看导出文件
cat /tmp/users.sql命令作用：
- 读取并输出
/tmp/users.sql的内容；使用>重定向时，输出会写入目标文件。
执行结果：
- 成功后会输出指定文件内容；文件不存在或权限不足时会报错。
关键参数：
注意事项：
-- --
... 1 张三 2 李四 \. ...
| 分类 | 说明 |
|---|---|
| **优点** | 能转储全局对象（角色、表空间），pg_dump 无法做到；单个命令获取整个集群 |
| **缺点** | 未压缩，文件大；单线程，速度慢；仅支持文本格式；部分恢复困难；内部调用 pg_dump |
关键参数：
注意事项：
**注意事项：**注意事项：
---------+--------------+------------
1 | 研发A项目 | 2026-03-01
2 | 市场B项目 | 2026-03-01
3 | 财务系统升级 | 2026-03-15| 操作 | 工具 | 适用格式 |
|---|---|---|
| 备份单库/单表 | pg_dump | 所有格式 |
| 备份全局对象 | pg_dumpall | 仅纯文本 |
| 恢复二进制格式 | pg_restore | .dump / .tar / 目录格式 |
---
核心特点：
注意事项：
数据库列表 名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限 -----------+----------+----------+-------------+-------------+----------------------- | | | | | postgres=CTc/postgres | | | | | postgres=CTc/postgres
数据恢复成功！
---
注意事项：
**数据准备：**
);---------+--------------+------------
1 | 研发A项目 | 2026-03-01
2 | 市场B项目 | 2026-03-01
3 | 财务系统升级 | 2026-03-15执行增量备份：
**数据准备：**
);
----+------
1 | 张三
2 | 李四
3 | 王五执行增量备份：
空间对比：
| 备份 | 大小 | 说明 |
|---|
/data/pg_incr2
**参数说明：**
**注意事项：**注意事项：
**注意事项：**
注意事项：
注意：在主库执行写操作，在从库执行查询操作！
);
**主库执行结果：**---------+--------------+------------ 1 | 研发A项目 | 2026-03-01 2 | 市场B项目 | 2026-03-01 3 | 财务系统升级 | 2026-03-15
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+--------------------------------+--------------+------------ 1 | 研发A项目 | 2026-03-01 2 | 市场B项目 | 2026-03-01 3 | 财务系统升级 | 2026-03-15
------+----------------+------------------+------------+-----------+------------+----------
73515 | 192.168.50.214 | walreceiver | streaming | 0/3000148 | 0/3000148 |---------+----------------+------------+-----------
注意事项：
------+----------+--------------------------------------------------------------+----------------+------------------------- 73515 | streaming| host=192.168.50.213 port=5432 user=slave ... | 0/3000148 | 2026-06-03 17:05:30+08
----------+----------------+-----------------
**注意事项：**
| 角色 | 关键字段 | 正常值 | 说明 |
|---|---|---|---|
| **主库** | `state` | `streaming` | 主库正在向从库发送 WAL |
| **从库** | `status` | `streaming` | 从库正在接收主库的 WAL |
---