数据库教程FGMT54‑PostgreSQL逻辑备份与恢复实战

## 前言与内容大纲
逻辑备份是PostgreSQL运维体系中不可缺少的核心能力，区别于直接拷贝数据文件的物理备份，逻辑备份通过导出数据库对象定义与业务数据，生成可移植的归档或SQL脚本，具备跨大版本迁移、细粒度对象恢复的能力，广泛用于数据迁移、环境克隆、误操作数据找回、开发测试环境数据刷新等场景。风哥教程本文面向DBA、运维工程师、数据库开发人员，完整讲解PostgreSQL逻辑备份底层原理、工具参数、备份格式选型、备份策略、故障风险，并且基于两套主机`fgedu‑net‑cn1`生产测试主机、`fgedu‑net‑cn2`异地恢复演练主机完成全套实战演练。本套风哥教程硬件统一规格为**64G内存8CPU服务器**，全部文件路径替换为`/fgedudb`；数据库实例名、业务数据库名统一`fgedudb`，业务用户名`fgedu`。风哥教程本文全部实战操作建议在隔离测试环境完成验证，禁止未经过测试直接在生产业务实例执行。

风哥教程本文整体知识大纲：
1. PostgreSQL逻辑备份基础原理，逻辑备份与物理备份差异对比
2. pg_dump、pg_dumpall、pg_restore工具核心理论，四种备份输出格式详解
3. 逻辑备份一致性快照原理，备份权限要求，全局对象与单库对象区别
4. 逻辑备份策略设计、备份集校验、备份保留周期、异地备份传输理论
5. 逻辑备份典型故障与风险点，大库备份性能优化理论
6. 实战一：pg_dump四种输出格式完整备份实操
7. 实战二：pg_dump细粒度备份，schema级别、单表、部分表导出实战
8. 实战三：pg_dumpall集群全局对象备份，角色、表空间导出实操
9. 实战四：plain文本格式使用psql工具恢复完整演练
10. 实战五：custom自定义格式pg_restore完整恢复、选择性恢复单schema、单表实战
11. 实战六：directory目录格式并行备份与并行恢复大库实战
12. 实战七：备份脚本编写、定时crontab任务、md5校验、过期备份自动清理
13. 实战八：异地主机fgedu‑net‑cn2完整备份集恢复演练
14. 实战九：模拟误删除表，依靠逻辑备份做数据找回实战
15. 实战十：逻辑备份迁移跨主机、跨版本迁移完整流程
16. 逻辑备份运维最佳实践与常见踩坑总结

> 网上搜索风哥教程可以学习全套数据库教程

## 一、PostgreSQL逻辑备份理论知识
### 1.1 逻辑备份基本原理
逻辑备份本质是数据库客户端工具通过数据库连接，读取数据库元数据与业务数据，将对象定义、数据内容输出为SQL脚本或者归档格式文件。pg_dump属于客户端程序，运行在客户端，通过快照机制获取备份启动时刻的一致性数据快照，备份过程不会阻塞业务读写操作，不会锁表，在线业务库可以直接执行逻辑备份。

逻辑备份与物理备份核心差异：
– 逻辑备份：导出SQL/归档，跨版本、跨操作系统兼容性优秀；支持单库、单schema、单表选择性备份恢复；大库场景备份恢复耗时更长，消耗CPU、内存；不包含WAL日志，无法直接做时间点PITR。
– 物理备份：直接拷贝PGDATA物理文件，备份恢复速度快；必须版本严格一致；只能整体实例恢复，无法单独恢复某一张表。

逻辑备份适合：环境迁移、小库日常备份、单对象误删除找回、跨大版本升级迁移；TB级别超大数据库，单纯依靠逻辑备份会面临耗时过长的问题，需要结合目录格式并行备份优化。

> 风哥 itpux‑com

### 1.2 pg_dump、pg_dumpall、pg_restore工具理论
1. **pg_dump**：只备份**单个指定数据库**，可以导出库内表、索引、视图、函数、序列、触发器等对象；**不会导出集群全局对象**，例如数据库角色role、表空间tablespace定义。pg_dump可以远程连接数据库执行备份，不需要运行在数据库服务器本地。
2. **pg_dumpall**：对整个数据库集群做逻辑转储，会导出集群全部数据库、角色用户、表空间等全局对象；pg_dumpall底层内部会多次调用pg_dump，pg_dumpall仅支持plain纯文本输出格式。
3. **pg_restore**：专门用来恢复非plain格式的归档备份（custom、directory、tar），支持选择性恢复部分对象、并行恢复、调整对象恢复顺序，plain格式备份不能使用pg_restore恢复，只能通过psql执行SQL脚本恢复。

> 风哥教程 113257174

### 1.4 一致性快照原理
pg_dump启动时会开启一个可重复读事务，获取数据库一致性快照，整个备份全程基于该快照读取数据，备份得到的所有对象都对应备份启动瞬间的数据库状态，备份过程中业务新增、变更的数据不会进入本次备份集。备份不会锁表，业务DML可以正常运行。
注意：逻辑备份快照只代表备份开始时刻，备份持续时间很长的超大库，备份结束时刻数据和业务最新状态不一致，逻辑备份本身不能实现时间点恢复。

### 1.5 备份权限理论
执行pg_dump需要数据库读取权限：
1. 备份完整数据库，建议使用数据库超级用户；
2. 普通业务账号备份，需要对所有表、视图拥有SELECT权限；序列需要USAGE权限；函数需要EXECUTE权限；
3. pg_dumpall导出角色用户，必须使用超级用户执行；
4. 备份得到的归档，恢复的时候需要拥有创建对象的权限，恢复前必须提前创建对应角色，否则恢复报对象属主报错。

### 1.6 逻辑备份策略理论
一套完整逻辑备份策略不能只简单定时执行pg_dump命令，需要包含如下要素：
1. 备份格式选型：中小库优先‑Fc自定义格式；几十GB以上大库使用‑Fd目录格式开启并行；
2. 备份周期：每日全量逻辑备份，或者结合业务变更量确定；
3. 备份文件本地保留时长，过期备份自动清理；
4. 备份集md5完整性校验；
5. 备份集异地传输，将备份文件拷贝到`fgedu‑net‑cn2`异地主机；
6. **定期恢复演练**：把备份集在测试主机完整恢复，验证备份集可用；没有经过恢复演练的备份等同于无效备份。

逻辑备份3‑2‑1原则：至少3份备份副本，2种存储介质，至少1份存放在异地主机。

> 风哥数据库教程 itpux‑com

### 1.7 逻辑备份常见故障与风险
1. 磁盘空间耗尽：备份过程磁盘写满，备份文件截断损坏，脚本返回码不一定报错；必须校验备份文件大小与md5。
2. 缺少全局对象：pg_dump单库备份不导出role角色，恢复环境没有对应账号，对象属主报错。
3. 序列数值不同步：恢复完成序列current值落后业务表主键最大值，插入数据主键冲突。
4. 大库备份内存、CPU消耗过高，业务实例负载升高。
5. 备份脚本密码明文写在命令行，存在安全风险，推荐使用.pgpass密码文件。
6. plain格式备份文件过大，操作系统单文件大小限制，需要split分割大备份文件。
7. 恢复之后缺少统计信息，查询执行计划异常，恢复完成必须手动执行ANALYZE更新统计信息。

### 1.8 逻辑备份与物理备份适用场景对比
逻辑备份优势：细粒度恢复、跨版本迁移、归档文件体积小；缺点：大库耗时久，无法做PITR时间点恢复。
物理备份pg_basebackup优势：备份恢复速度快，支持WAL归档做时间点恢复；缺点：版本必须严格一致，不能单独恢复单张表。
生产建议组合使用：大实例物理备份作为主力，逻辑备份作为补充，用于单对象误删除找回。

> 上51CTO搜索风哥可以学习全套数据库教程

## 二、实战环境准备
### 2.1主机规划
– **fgedu‑net‑cn1：源数据库主机，64G内存8CPU，NVMe SSD；数据库根目录`/fgedudb/pgdata`；业务数据库`fgedudb`，业务schema为biz，业务账号`fgedu`，端口5432**
– **fgedu‑net‑cn2：异地备份恢复演练主机，硬件规格与fgedu‑net‑cn1保持一致，用于存放备份集、执行全部恢复演练操作**

### 2.2目录与密码文件准备（fgedu‑net‑cn1主机）
创建备份存放目录、脚本目录：
“`bash
mkdir -p /fgedudb/pgbackup/{logic,scripts,md5}
chown -R postgres:postgres /fgedudb/pgbackup
chmod 700 /fgedudb/pgbackup
“`
配置postgres用户的`.pgpass`密码文件，避免命令行明文密码，`/home/postgres/.pgpass`：
“`
127.0.0.1:5432:fgedudb:fgedu:Fgedu@2026
127.0.0.1:5432:*:postgres:Post@2026
“`
修改权限，pgpass权限必须0600，否则不生效：
“`bash
chmod 600 /home/postgres/.pgpass
chown postgres:postgres /home/postgres/.pgpass
“`
业务库fgedudb已经存在，biz业务schema，预先构造一批测试业务数据，用于后续备份恢复验证。登录psql执行：
“`sql
\c fgedudb
SET search_path TO biz,public;
CREATE TABLE t_order(order_id bigint GENERATED ALWAYS AS IDENTITY,user_id bigint,order_no text,amount numeric(12,2),create_time timestamp);
INSERT INTO t_order(user_id,order_no,amount,create_time) SELECT generate_series(1,10000),’ORD’||generate_series(1,10000),100.00,now();
“`

## 三、pg_dump四种格式备份完整实战（fgedu‑net‑cn1主机，postgres用户执行）
### 3.1 plain纯文本格式（‑Fp）备份实战
plain输出完整SQL脚本，使用psql进行恢复。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fp \
-f /fgedudb/pgbackup/logic/fgedudb_plain_${BACKUP_DATE}.sql \
fgedudb
#生成md5校验
md5sum /fgedudb/pgbackup/logic/fgedudb_plain_${BACKUP_DATE}.sql > /fgedudb/pgbackup/md5/fgedudb_plain_${BACKUP_DATE}.sql.md5
“`

### 3.2 custom自定义格式（‑Fc）备份实战（生产推荐格式）
‑Fc自定义格式，默认开启压缩，支持pg_restore选择性恢复。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fc -Z6 \
-f /fgedudb/pgbackup/logic/fgedudb_custom_${BACKUP_DATE}.dump \
fgedudb
md5sum /fgedudb/pgbackup/logic/fgedudb_custom_${BACKUP_DATE}.dump > /fgedudb/pgbackup/md5/fgedudb_custom_${BACKUP_DATE}.dump.md5
“`
参数说明：‑Z6代表压缩级别，范围0‑9，6为平衡CPU与压缩比常用值。

### 3.3 directory目录格式（‑Fd）并行备份实战（适合大数据库）
目录格式是唯一支持并行备份的格式，使用‑j指定并行线程数量，64G‑8CPU环境设置‑j 8。备份输出为一个目录，而不是单一文件。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fd -j 8 \
-f /fgedudb/pgbackup/logic/fgedudb_dir_${BACKUP_DATE} \
fgedudb
#对整个目录生成md5校验
cd /fgedudb/pgbackup/logic
find fgedudb_dir_${BACKUP_DATE} -type f -print0 | xargs -0 md5sum > /fgedudb/pgbackup/md5/fgedudb_dir_${BACKUP_DATE}.md5
“`

### 3.4 tar格式（‑Ft）备份实战
tar格式不压缩，生产很少使用，仅作为演示：
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Ft \
-f /fgedudb/pgbackup/logic/fgedudb_tar_${BACKUP_DATE}.tar \
fgedudb
md5sum /fgedudb/pgbackup/logic/fgedudb_tar_${BACKUP_DATE}.tar > /fgedudb/pgbackup/md5/fgedudb_tar_${BACKUP_DATE}.tar.md5
“`

> 网上搜索风哥教程可以学习全套数据库教程

## 四、pg_dump细粒度备份实战
### 4.1 只备份指定schema（‑n参数）
只备份biz业务schema，排除public等其他schema对象。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fc -n biz \
-f /fgedudb/pgbackup/logic/fgedudb_schema_biz.dump \
fgedudb
“`
多个schema使用多个‑n参数；排除schema使用‑N参数。

### 4.2 只备份单张业务表（‑t参数）
仅备份biz.t_order单张业务表，用于单表误删除应急备份。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fc -t biz.t_order \
-f /fgedudb/pgbackup/logic/fgedudb_table_torder.dump \
fgedudb
“`
多个表可以写多个‑t；排除表使用‑T参数。

### 4.3 只导出对象定义（‑s参数，仅schema，不导出数据）
只导出DDL表、索引、函数定义，不导出业务数据，用于模型归档。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fc -s \
-f /fgedudb/pgbackup/logic/fgedudb_schema_only.dump \
fgedudb
“`

### 4.4 只导出数据，不导出对象定义（‑a参数）
只导出表内业务数据，不导出建表语句。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_dump \
-h 127.0.0.1 -p 5432 -U fgedu \
-Fc -a \
-f /fgedudb/pgbackup/logic/fgedudb_data_only.dump \
fgedudb
“`

> 风哥 itpux‑com

## 五、pg_dumpall集群全局对象备份实战
pg_dumpall用来导出整个集群全局对象：角色role、表空间tablespace、所有数据库。日常运维，很多场景不需要导出全部业务数据，只导出全局对象，使用‑g（‑‑globals‑only）参数，仅导出角色、表空间，**不导出业务数据库数据**，迁移环境前优先导出全局对象，恢复到目标实例，避免恢复业务库时报属主不存在报错。

### 5.1 仅导出集群全局对象（推荐迁移使用）
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
${PGHOME}/bin/pg_dumpall \
-h 127.0.0.1 -p 5432 -U postgres \
‑‑globals‑only \
-f /fgedudb/pgbackup/logic/cluster_global_${BACKUP_DATE}.sql
md5sum /fgedudb/pgbackup/logic/cluster_global_${BACKUP_DATE}.sql > /fgedudb/pgbackup/md5/cluster_global_${BACKUP_DATE}.sql.md5
“`

### 5.2 pg_dumpall导出整个集群全部数据库完整数据
pg_dumpall完整导出集群所有库+全局对象，输出plain文本：
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_dumpall \
-h 127.0.0.1 -p 5432 -U postgres \
-f /fgedudb/pgbackup/logic/cluster_all_$(date +%Y%m%d).sql
“`
> 注意pg_dumpall没有‑Fc/‑Fd归档格式，只能输出plain文本SQL脚本。

> 风哥教程 113257174

## 六、plain文本格式psql恢复实战（在fgedu‑net‑cn2异地演练主机操作）
plain格式的sql备份文件，不可以使用pg_restore，只能使用psql工具执行SQL脚本恢复。
恢复前置步骤：
1. 将fgedu‑net‑cn1备份文件传输到fgedu‑net‑cn2；
2. 在目标演练主机**先执行global全局对象脚本**，创建角色fgedu；
3. 使用template0模板创建空数据库fgedudb，不要使用template1。

### 6.1 传输备份集到异地主机
在`fgedu‑net‑cn1`执行scp传输：
“`bash
scp /fgedudb/pgbackup/logic/*.sql fgedu‑net‑cn2:/fgedudb/pgbackup/logic/
scp /fgedudb/pgbackup/md5/*.md5 fgedu‑net‑cn2:/fgedudb/pgbackup/md5/
“`

### 6.2 fgedu‑net‑cn2校验备份md5完整性
“`bash
su – postgres
cd /fgedudb/pgbackup/logic
md5sum -c /fgedudb/pgbackup/md5/fgedudb_plain_20260916_100000.sql.md5
“`

### 6.3 先导入全局对象，创建业务数据库
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
#导入全局角色、表空间定义
${PGHOME}/bin/psql -h 127.0.0.1 -p 5432 -U postgres -f /fgedudb/pgbackup/logic/cluster_global_20260916.sql
#使用template0模板创建空数据库fgedudb
${PGHOME}/bin/createdb -T template0 -O fgedu fgedudb
“`

### 6.4 psql执行plain sql备份脚本恢复
参数‑X代表不加载psqlrc配置，避免客户端配置干扰恢复过程。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/psql -X -h 127.0.0.1 -p 5432 -U fgedu -d fgedudb -f /fgedudb/pgbackup/logic/fgedudb_plain_20260916_100000.sql
“`

### 6.5 恢复完成校验数据
登录psql，核对表行数、对象是否完整：
“`sql
\c fgedudb
SET search_path TO biz;
SELECT count(*) FROM t_order;
“`
恢复完成必须手动执行ANALYZE，更新统计信息：
“`sql
ANALYZE;
“`

> 风哥数据库教程 itpux‑com

## 七、custom自定义格式pg_restore恢复实战（fgedu‑net‑cn2主机）
‑Fc自定义dump归档文件，使用pg_restore工具恢复，支持完整恢复，也支持选择性恢复单schema、单张表。

### 7.1 将custom备份文件传输至fgedu‑net‑cn2
“`bash
#fgedu‑net‑cn1执行
scp /fgedudb/pgbackup/logic/*.dump fgedu‑net‑cn2:/fgedudb/pgbackup/logic/
“`
在fgedu‑net‑cn2校验md5，导入global全局对象，createdb创建空fgedudb数据库，步骤同上。

### 7.2 pg_restore完整全库恢复
‑j设置并行恢复线程，8CPU机器‑j 8，提升恢复速度。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_restore \
-h 127.0.0.1 -p 5432 -U fgedu \
‑‑dbname=fgedudb \
‑‑jobs=8 \
/fgedudb/pgbackup/logic/fgedudb_custom_20260916_100000.dump
“`

### 7.3 pg_restore只恢复biz单schema（选择性恢复）
业务场景：误删除整个biz schema，不需要恢复整个数据库，仅恢复biz业务schema。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_restore \
-h 127.0.0.1 -p 5432 -U fgedu \
‑‑dbname=fgedudb \
‑‑schema=biz \
/fgedudb/pgbackup/logic/fgedudb_custom_20260916_100000.dump
“`

### 7.4 pg_restore仅恢复单张表biz.t_order
模拟误删除t_order表，只恢复这一张表，其他对象不动。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_restore \
-h 127.0.0.1 -p 5432 -U fgedu \
‑‑dbname=fgedudb \
‑‑table=biz.t_order \
/fgedudb/pgbackup/logic/fgedudb_custom_20260916_100000.dump
“`

### 7.5 只导出归档的toc目录清单，查看归档内部对象列表
pg_restore ‑l参数列出备份归档内部全部对象清单，可以用来确认备份集包含哪些对象。
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_restore -l /fgedudb/pgbackup/logic/fgedudb_custom_20260916_100000.dump
“`

> 上51CTO搜索风哥可以学习全套数据库教程

## 八、directory目录格式备份集并行恢复实战（fgedu‑net‑cn2）
目录格式备份是一个完整文件夹，传输的时候需要传输整个目录。
1. fgedu‑net‑cn1把整个目录拷贝到fgedu‑net‑cn2；
“`bash
scp -r /fgedudb/pgbackup/logic/fgedudb_dir_20260916_100000 fgedu‑net‑cn2:/fgedudb/pgbackup/logic/
“`
2. fgedu‑net‑cn2导入global对象，创建空数据库fgedudb；
3. pg_restore指定‑j开启并行恢复，目录格式恢复同样支持选择性恢复schema/表。

完整并行恢复命令：
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
${PGHOME}/bin/pg_restore \
-h 127.0.0.1 -p 5432 -U fgedu \
‑‑dbname=fgedudb \
‑‑jobs=8 \
/fgedudb/pgbackup/logic/fgedudb_dir_20260916_100000
“`

## 九、编写生产逻辑备份shell脚本与crontab定时任务（fgedu‑net‑cn1）
编写生产可用脚本`/fgedudb/pgbackup/scripts/pg_dump_logic_backup.sh`，完成：pg_dump自定义格式备份、md5生成、日志输出、7天过期备份清理、自动scp传输备份集到异地主机fgedu‑net‑cn2。

“`bash
#!/bin/bash
#PostgreSQL逻辑备份脚本风哥教程
PGHOME=/fgedudb/pgdata
PG_HOST=127.0.0.1
PG_PORT=5432
PG_DB=fgedudb
PG_USER=fgedu
BACKUP_BASE=/fgedudb/pgbackup
BACKUP_DIR=${BACKUP_BASE}/logic
MD5_DIR=${BACKUP_BASE}/md5
LOG_DIR=${BACKUP_BASE}/logs
REMOTE_HOST=”fgedu‑net‑cn2″
REMOTE_PATH=”/fgedudb/pgbackup/logic”
DATE_NOW=$(date +%Y%m%d_%H%M%S)

mkdir -p ${BACKUP_DIR} ${MD5_DIR} ${LOG_DIR}
LOG_FILE=${LOG_DIR}/backup_${DATE_NOW}.log

echo “=====开始逻辑备份 ${DATE_NOW} =====” >> ${LOG_FILE}

#执行pg_dump custom格式备份
${PGHOME}/bin/pg_dump -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -Fc -Z6 \
‑f ${BACKUP_DIR}/${PG_DB}_custom_${DATE_NOW}.dump ${PG_DB} >>${LOG_FILE} 2>&1
RET_DUMP=$?

if [ ${RET_DUMP} -eq 0 ];then
echo “pg_dump备份成功” >> ${LOG_FILE}
md5sum ${BACKUP_DIR}/${PG_DB}_custom_${DATE_NOW}.dump > ${MD5_DIR}/${PG_DB}_custom_${DATE_NOW}.dump.md5
#传输备份到异地主机
scp ${BACKUP_DIR}/${PG_DB}_custom_${DATE_NOW}.dump ${MD5_DIR}/${PG_DB}_custom_${DATE_NOW}.dump.md5 ${REMOTE_HOST}:${REMOTE_PATH}/ >>${LOG_FILE} 2>&1
#清理本地7天前旧备份
find ${BACKUP_DIR} -type f -mtime +7 -name “*.dump” -exec rm -f {} \;
find ${MD5_DIR} -type f -mtime +7 -name “*.md5” -exec rm -f {} \;
else
echo “pg_dump备份失败 return code:${RET_DUMP}” >> ${LOG_FILE}
exit 1
fi

echo “=====备份完成 ${DATE_NOW} =====” >> ${LOG_FILE}
exit 0
“`

赋予脚本执行权限：
“`bash
chmod +x /fgedudb/pgbackup/scripts/pg_dump_logic_backup.sh
chown postgres:postgres /fgedudb/pgbackup/scripts/pg_dump_logic_backup.sh
“`

手动测试脚本运行：
“`bash
su – postgres
/fgedudb/pgbackup/scripts/pg_dump_logic_backup.sh
“`

配置crontab定时任务，postgres用户，每日凌晨02点执行逻辑备份：
“`bash
crontab -u postgres -e
#写入内容
0 2 * * * /fgedudb/pgbackup/scripts/pg_dump_logic_backup.sh >> /fgedudb/pgbackup/logs/crontab_all.log 2>&1
“`

> 网上搜索风哥教程可以学习全套数据库教程

## 十、模拟误删除表故障，使用逻辑备份做数据找回实战
业务故障场景：操作人员误执行`DROP TABLE biz.t_order;`，业务表被删除，需要依靠逻辑备份找回数据。整个恢复**不要在原生产库直接恢复，优先在fgedu‑net‑cn2演练主机恢复，导出需要的数据，再回写生产库**。

1. fgedu‑net‑cn2，使用pg_restore从备份归档只恢复biz.t_order到演练主机；
2. 在演练主机校验t_order表数据行数，确认数据完整；
3. 使用psql或者pg_dump把恢复出来的t_order数据导出；
4. 将正确数据导回fgedu‑net‑cn1生产业务库。

演练主机fgedu‑net‑cn2执行：
“`bash
su – postgres
PGHOME=/fgedudb/pgdata
#仅恢复t_order表
${PGHOME}/bin/pg_restore \
‑‑dbname=fgedudb \
‑‑table=biz.t_order \
‑‑jobs=4 \
/fgedudb/pgbackup/logic/fgedudb_custom_20260916_100000.dump
“`
校验数据，然后导出这张表的数据，传输回源主机：
“`bash
${PGHOME}/bin/pg_dump -h 127.0.0.1 -p 5432 -U fgedu -d fgedudb -Fc -t biz.t_order -f /fgedudb/pgbackup/logic/recover_t_order.dump
scp /fgedudb/pgbackup/logic/recover_t_order.dump fgedu‑net‑cn1:/fgedudb/pgbackup/logic/
“`
在fgedu‑net‑cn1业务库，确认业务停写，导入找回的t_order数据。
> 重要提醒：生产环境严禁直接在故障实例执行pg_restore覆盖对象，优先在隔离演练主机完成数据校验。

## 十一、跨主机逻辑备份迁移完整流程实战
完整跨主机迁移标准步骤，风哥教程本文梳理标准流程：
1. fgedu‑net‑cn1：pg_dumpall ‑‑globals‑only导出全局角色、表空间；
2. fgedu‑net‑cn1：pg_dump ‑Fc导出业务数据库fgedudb；
3. 把global脚本、业务dump归档文件全部scp传输到fgedu‑net‑cn2；
4. fgedu‑net‑cn2：执行global脚本，创建全部角色、表空间；
5. fgedu‑net‑cn2：createdb ‑T template0 创建空业务数据库fgedudb；
6. fgedu‑net‑cn2：pg_restore并行恢复业务dump归档；
7. 恢复完成执行ANALYZE更新统计信息；
8. 执行SQL校验表数量、行数、序列、函数、视图完整性；
9. 业务功能测试验证。

## 十二、逻辑备份运维踩坑说明
1. pg_dump只备份单数据库，不会导出角色，迁移恢复前必须先导入pg_dumpall‑‑globals‑only的全局对象脚本，否则报对象属主不存在错误。
2. 恢复数据库务必使用template0模板创建数据库，不要使用template1，避免template1自定义对象混入恢复库。
3. 大库优先使用‑Fd目录格式开启‑j并行备份；恢复同样开启‑j并行恢复；恢复阶段调大maintenance_work_mem参数加速索引创建。
4. 备份后必须做md5校验；磁盘满会造成备份文件截断，返回码不一定报错，只看脚本返回码并不绝对可靠。
5. 恢复结束一定要执行ANALYZE，否则pg_stat_statements统计信息为空，查询执行计划异常，业务性能变差。
6. .pgpass密码文件权限必须0600，权限过大pg_dump会忽略密码配置，导致备份连接失败。
7. 逻辑备份没有时间点恢复能力，只能恢复备份快照时刻；如果需要PITR时间点恢复，需要搭配物理备份+WAL归档。
8. 序列对象恢复之后，核对序列的nextval值，避免业务插入出现主键冲突。
9. 定期在异地主机完整执行一次恢复演练，没有经过演练的备份不可信。

## 风哥针对本文总结
风哥教程本文完整讲解PostgreSQL逻辑备份与恢复整套知识，理论部分讲解逻辑备份底层原理，对比逻辑备份与物理备份差异；详细拆解pg_dump、pg_dumpall、pg_restore三套工具，四种输出备份格式的优缺点；讲解一致性快照、备份权限、备份策略设计，梳理逻辑备份常见故障风险点。

实战部分使用`fgedu‑net‑cn1`源数据库主机、`fgedu‑net‑cn2`异地恢复演练主机，硬件规格64G内存8CPU，路径统一替换为`/fgedudb`，数据库、账号统一`fgedudb/fgedu`；完整实战包含四种备份格式实操、schema级别、单表细粒度备份；pg_dumpall全局对象导出；plain格式psql恢复；custom自定义格式pg_restore完整恢复与选择性恢复单schema、单表；directory目录格式并行备份恢复；生产备份shell脚本编写，crontab定时任务；模拟误drop表故障的数据找回演练；完整跨主机迁移实战。

风哥教程本文强调几个生产关键点：逻辑备份擅长细粒度对象恢复、跨版本迁移，但不适合TB级超大库作为唯一备份手段，建议与物理备份互相补充；迁移恢复流程必须优先导入全局角色对象，使用template0模板创建目标空库；备份集必须做md5完整性校验，并且定期做异地恢复演练；恢复完成务必执行ANALYZE更新统计信息。

本套风哥教程所有脚本命令，建议先在隔离测试环境完整验证，确认无误之后再落地生产运维。掌握本套教程内容之后，可以进一步学习pgBackRest高级备份工具、物理备份pg_basebackup、WAL归档PITR时间点恢复相关进阶内容。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

数据库教程FGMT54‑PostgreSQL逻辑备份与恢复实战

相关推荐

联系我们