如果你管理过一段时间的 PostgreSQL保准会遇上这样的时刻某个深夜一张被误删的表、一次不成功的批量更新或者一台起不来的机器把你从睡梦中叫醒。这时候备份就是你手里唯一可靠的底牌。PostgreSQL 官方给出的备份方案里SQL 转储是最容易上手、也是最能救命的一类——说白一点就是用 pg_dump 把数据库翻译成一份 SQL 文本或者带压缩的归档文件出事之后再配合 psql 或 pg_restore 把这份 SQL 重新执行一遍库就回来了。这篇文章想把我平时用 SQL 转储做备份和恢复的完整流程、参数取舍、真实踩坑按一个能直接照着做的顺序讲清楚。这篇内容适合这几类人刚接触 PostgreSQL 没多久、需要一个可靠兜底方案的运维正在规划数据库迁移升级、想先搞明白逻辑备份边界的技术人以及那些已经会跑 pg_dump 但没认真想过恢复链路是否能跑通的同行。文章不会只给你命令我会把每个关键选择背后的原因也一起说掉。1. 为什么 SQL 转储是一份值得信任的建筑图纸1.1 它备份的不是文件而是重建库所需的全部指令我第一次接触 pg_dump 时有个误解以为它和文件系统快照一样会把某个时刻的数据文件拷一份出来。后来才意识到SQL 转储走的完全是另一条路——它连接数据库读取系统表catalog里记录的表结构、索引、约束、触发器定义再读取每个表的全部数据把这些内容拼装成一条条 DDL 和 DML 语句。也就是说备份出来的是一个数据库的源代码。比如一张 orders 表它在转储文件里大致长这样先来一段 CREATE TABLE中间是一大段 COPY 语句把每一行数据灌进去最后重建主键、外键、索引和触发器。只要有一台能跑 PostgreSQL 的机器拿着这份文件从头到尾执行一遍就能得到一台结构、数据、权限关系都基本一致的数据库。这个机制决定了 SQL 转储的很多特性它跟操作系统、CPU 架构、文件系统都没有关系所以跨平台迁移时特别可靠它也跟数据库文件页面的内部格式无关所以跨大版本恢复时通常比直接搬迁数据目录更稳。1.2 和物理备份、WAL 归档相比它的位置在哪很多人讨论备份方案时会把 SQL 转储和物理备份放在对立面其实它们解决的问题并不完全重叠。物理备份直接拷贝 $PGDATA 数据目录配合 WAL 归档还能做到时间点恢复PITRSQL 转储则是对某个时刻的数据库做逻辑快照。我用一个类比帮助新同学理解物理备份像是给房子拍了一整套全景照片和录像还原的时候直接照着原样重建效率高但照片里的装修风格、管线布局都是固定的SQL 转储更像是把房子的砖、瓦、电路、管道写成一份详细施工图纸还原时从头施工速度慢一些但图纸可以在任何一块空地上用。对比项SQL 转储pg_dump物理备份pg_basebackupWAL 归档PITR备份内容逻辑对象与数据数据目录文件WAL 日志序列恢复粒度可单独恢复表、schema整实例恢复任意时间点恢复跨版本跨平台好差一般要求同版本同架构差和物理备份绑定备份期间业务在线DML 不受影响在线但会复制整个数据目录在线持续归档恢复速度较慢需要重放 SQL/COPY快文件级恢复快但需要重放 WAL主要用途迁移、逻辑备份、单对象恢复整库快速恢复、克隆实例数据库误操作、精确到秒恢复在真实的运维里我通常不会只用其中一种。SQL 转储解决的是逻辑层面还能抢救一下的问题比如某个开发环境里误删了一张表、某次升级前想留一份干净的结构、或者要把数据迁到另一个版本的实例而物理备份和 WAL 归档解决的是机器整体没了的问题。两者互补而不是互相替代。1.3 什么场景该用它什么场景不该硬扛SQL 转储的优势很明确但边界同样清楚。以我的经验下面这些场景选它是对的数据库整体迁移比如从 PostgreSQL 12 迁到 15或者从物理机搬到容器环境只恢复某张表、某个 schema而不是整库需要一份可读的、能被人工检查的备份比如数据审计开发测试环境的日常快照量级不大恢复要求也不苛刻结构变更前的安全带先导一份当前库的状态改坏了能用它兜底。反过来有几个场景我不建议硬用 SQL 转储扛数 TB 级别的大库全量导出可能要跑好几个小时恢复时间更长业务等不起要求恢复到最近几秒的状态SQL 转储最多只能恢复到导出开始时刻的快照这个精度满足不了需要频繁做整库克隆的自动化流水线物理层复制会更划算。能不能用它和该不该用它是两码事。选型之前先想清楚你的恢复目标RTO和数据丢失容忍度RPO比直接背命令重要得多。2. pg_dump 初体验参数选型与一次标准单库备份2.1 四种输出格式先想清楚再下手pg_dump 支持四种输出格式很多人第一次看到就懵了其实只需要搞清楚它们各自适合什么场景。平文本格式-Fp默认值输出一个纯 SQL 脚本文件。它最直白用 psql 就能恢复适合小库、交付脚本、结构备份。缺点是文件没压缩恢复时只能顺序执行灵活性低。自定义格式-Fc输出一个二进制归档默认带压缩。它能被 pg_restore 读取支持只恢复某张表、某个 schema是我日常备份最推荐的格式。目录格式-Fd输出到一个目录目录里每个表对应一个文件。它支持 pg_dump 并行导出-j也支持 pg_restore 定向恢复适合中大库。tar 格式-Ft输出一个 tar 包也能用 pg_restore 恢复但并行能力受限我实际用得很少。一句话总结数据库规模不大用平文本想省心用自定义格式库大需要并行就上目录格式。2.2 一份可以直接抄的备份命令我平时执行单库备份用的命令格式非常固定DATE$(date %Y%m%d_%H%M%S) pg_dump \ -h 127.0.0.1 \ -p 5432 \ -U backup_user \ -d appdb \ -F c \ -Z 9 \ -f /backup/pg_backup/dbs/appdb_${DATE}.dump拆开解释一下-d 指定要备份的库-F c 选择自定义格式-Z 9 是压缩级别0 到 9 可选9 压缩率最高但更费 CPU。如果定时任务大量跑我建议用默认压缩级别或者 6大多数场景下没必要和 CPU 过不去。这里有个细节值得说明为什么要选自定义格式而不是默认的平文本因为自定义格式等于给备份文件加了一个目录索引。恢复的时候你能用 pg_restore -l 查看文件里有哪些对象能按表名只恢复一张表还能用 --clean 在恢复前自动清理同名对象。这些能力平文本格式都没有真到出事故的时候灵活度差别非常大。2.3 常用参数一览表除了上面几个核心参数下面这些选项是我在真实项目中翻来覆去会用的参数作用备注-s / --schema-only只导出结构不导出数据做结构比对、版本升级前常用-a / --data-only只导出数据不导出结构注意恢复时表必须已存在-t 表名只导出指定表可重复如 -t public.users-T 表名排除指定表大日志表常用-n schema名只导出指定 schema多 schema 库比较实用-N schema名排除指定 schema排除临时 schema-C在转储文件中包含 CREATE DATABASE 语句配合 psql 恢复时可以直接建库--clean恢复前先执行 DROP 语句建议和 --if-exists 一起用--if-existsDROP 语句附带 IF EXISTS避免报对象不存在的错--no-owner不导出 ALTER ... OWNER TO 语句跨用户、跨环境恢复时很关键--no-privileges不导出权限相关语句权限依赖角色存在跳过更省事-j 并发数并行转储或恢复pg_dump 并行仅目录格式支持2.4 配一个验证备份的小动作备份完只看到文件生成还不够我每次都会额外做一个快速检查。自定义格式的归档可以直接看它的目录信息pg_restore -l /backup/pg_backup/dbs/appdb_20250115_023000.dump | head -n 30如果这条命令能正常列出表、索引、约束等条目说明归档文件结构完整、可被读取。比起只看文件大小这个小动作能在十几秒内发现很多问题比如文件写了一半、权限不对、格式损坏。当然列表能读出来不代表数据完整。所以我还会在每个月做一次完整恢复演练这个后面专门讲。3. 不要只备份数据全局对象的兜底方案3.1 角色、权限和表空间为什么不跟 pg_dump 走很多同学第一次做迁移时都会踩同一个坑用 pg_dump 把业务库导到新机器上恢复时报了一堆错误最典型的就是 role app_user does not exist。原因很简单——pg_dump 默认只负责导出一个数据库内部的逻辑对象而角色role、表空间tablespace、数据库级别的参数配置这些属于整个 PostgreSQL 集群层面的全局对象它不负责。你可以把数据库集群想象成一栋办公楼数据库是楼里的房间角色是进出楼的门禁卡表空间是楼里的仓库位置。pg_dump 帮你把房间里的家具、文件全部打包运走了但门禁卡、仓库登记表还得单独处理。这意味着如果你的备份方案只包含每个库的 dump 文件换机器恢复时必然会遇到权限、属主对不上的问题。全局对象必须单独备份这块经常被忽略。3.2 pg_dumpall -g 的用法全局对象的备份工具是 pg_dumpall加 -g 参数表示只导出全局对象也就是角色和表空间DATE$(date %Y%m%d_%H%M%S) pg_dumpall -g \ -h old_host \ -p 5432 \ -U postgres \ -f /backup/pg_backup/global/globals_${DATE}.sql恢复也非常直接用 psql 执行这个文件即可psql -h new_host -U postgres -d postgres \ -f /backup/pg_backup/global/globals_20250115_023000.sql注意两点第一恢复全局对象通常需要超级用户权限因为角色和表空间都属于集群级资源第二pg_dumpall -g 导出的表空间语句里包含文件系统路径如果新机器的数据目录布局和旧机器不同恢复 CREATE TABLESPACE 可能会报错需要先确认目标路径存在或者手工修改脚本里的路径。3.3 一个备份多份文件的规范我习惯把备份目录按全局对象和业务库分开管理结构大概是/backup/pg_backup/ ├── global/ │ └── globals_20250115_023000.sql └── dbs/ ├── appdb_20250115_023000.dump └── billing_20250115_023000.dump每个业务库单独导一份而不是把所有库混在同一个文件里。好处是恢复时非常灵活某个库出了问题就恢复某一个不需要为了拿一张表去动整个集群。全局对象因为所有库公用每天保留一份就够了。老生常谈但必须强调如果只备份业务库不备份全局对象从零恢复集群时你会被权限问题折磨到怀疑人生反过来只备份全局对象不备份数据那更是什么都救不回来。这两份缺一不可。4. 恢复的完整链路从备份文件到能用的数据库4.1 平文本文件用 psql 回放平文本格式的转储文件本质就是一条条 SQL 语句恢复方式最直接createdb -h 127.0.0.1 -U postgres -O app_user appdb_restored psql -h 127.0.0.1 -U postgres -d appdb_restored -1 -f /backup/appdb.sql这里有个容易被忽略的点psql 的 -1 参数表示整个文件在单个事务里执行任一步失败都会全部回滚。小库用 -1 很安全不容易留下恢复了一半的烂摊子但大库我不建议用因为单事务意味着所有操作要一起提交过程中占用的资源会更重失败回滚代价也大。如果备份时用了 -C 参数转储文件里已经带了 CREATE DATABASE 语句那就不需要先手动 createdb可以直接执行 psql -f但要注意文件里创建的数据库名会和源库一致。4.2 自定义格式用 pg_restore 定向恢复自定义格式和目录格式用 pg_restore 恢复这是我最常用的方式。全量恢复一条命令createdb -h 127.0.0.1 -U postgres appdb_restored pg_restore \ -h 127.0.0.1 -U postgres \ -d appdb_restored \ --clean --if-exists --no-owner \ /backup/pg_backup/dbs/appdb_20250115_023000.dump--clean 会在恢复前把同名对象先 DROP 掉配合 --if-exists 可以避免重复恢复时报对象已存在。--no-owner 的作用是跳过属主变更语句否则恢复时会尝试把对象所有权改给源库里的旧用户目标库没有这个名字就会报错。如果只需要从备份里捞一张表出来pg_restore 比 pg_dump 灵活得多# 查看归档里有哪些对象 pg_restore -l /backup/pg_backup/dbs/appdb_20250115_023000.dump # 只恢复一张表 pg_restore -h 127.0.0.1 -U postgres \ -d appdb_restored \ -t public.orders \ /backup/pg_backup/dbs/appdb_20250115_023000.dump注意 -t 指向的表名最好写成 schema.table 的形式避免不同 schema 下重名表带来的歧义。单独恢复一张表时pg_restore 不会自动处理这张表的外键依赖关系如果 src_component表数据被其他表引用恢复后可能会有约束报错的提示需要根据实际报错信息再补。4.3 恢复时三句高频报错和对应解法这些年我看着不同的环境、不同的人恢复数据库来来回回就是那几句报错列在这里并附上处理思路。报错内容根本原因解决办法role xxx does not exist全局角色没有先恢复先执行 pg_dumpall -g 导出的文件或恢复时加 --no-ownerdatabase xxx does not exist目标库还没创建手动 createdb或备份时用 -C 让脚本自动建库relation xxx already exists目标库里已有同名对象且没有清理恢复时加 --clean --if-exists或先清空目标库permission denied for schema public当前用户不是该 schema 的属主确认用超级用户恢复或先恢复全局角色并把属主对上每次恢复报错先别急着改备份文件优先确认目标环境是否空库角色是否齐全用的用户有没有权限。这三个检查做完了九成问题都能解决。4.4 动手恢复前我固定检查的四件事恢复不是一个闷头执行命令的动作它在生产环境里属于高危操作。我给自己定了一个恢复前 checklist每条都很朴素但真的救过场目标库有没有业务连接恢复过程中如果业务账号正连着库读写数据会出现中间状态比如表在 COPY 数据和约束重建之间是有一段时间无索引的。恢复大库前我会主动断开应用连接或者先切到维护模式磁盘空间是否充裕备份文件加目标库本身还有索引构建、临时排序的空间至少留出和源库数据同等大小以上的余量否则恢复到一半磁盘满了非常被动目标 PostgreSQL 版本是否合适这一点下面专门展开说恢复用的用户名权限如果是整库恢复直接用超级用户最省事如果有限制确保恢复账号对目标库有建表、写数据、建索引的全部权限。5. 在真实环境里容易翻车的点版本、权限、并发和编码5.1 跨版本恢复方向对了麻烦少一半PostgreSQL 社区支持跨版本迁移SQL 转储是最常用的路径之一。但跨版本不是随便拿一个 pg_dump 就能通的。我的经验是导出尽量用源库对应版本的 pg_dump恢复尽量用目标库对应版本的 pg_restore整体方向建议从低版本导、往高版本恢。新版本通常能兼容旧的 DDL 和数据类型行为反过来高版本导出的脚本里可能含新语法、新默认值低版本可能不认。还有个很容易被忽略的点如果源库装了扩展比如 PostGIS 这类备份文件的 CREATE EXTENSION 语句会跟着走恢复前必须在目标库把对应扩展安装好。扩展的版本如果和源库不一致后续还会引发函数签名不匹配的问题。恢复前先看一遍 dump 文件里的扩展声明比事后报错再查快得多。5.2 属主、权限和 RLS 带来的连锁反应权限问题不只在恢复那一瞬间爆发。pg_dump 默认会导出对象的属主OWNER TO和对象级权限GRANT/REVOKE这些语句都依赖角色先存在。有一次我在环境间迁移一套系统导数据时把全局角色也恢复了看起来一切正常唯独漏了一个逻辑源库里的某些表启用了行级安全策略RLS。这些策略在 dump 文件里是按属主身份保存的恢复之后由于角色名称虽然一样但角色属性比如 BYPASSRLS没对齐应用访问时策略表现完全不同排查了很久才发现问题根源。所以我现在的做法是迁移或恢复前把全局对象文件拆开看一遍至少确认涉及的角色名称、登录权限、成员关系都在。恢复完成后再用应用的专用账号去执行几条真实的 SQL而不是只拿超级用户验证表数量。5.3 大库备份慢三个优化方向单库达到一两百 GB 以上pg_dump 默认的单线程 COPY 导出会变得非常煎熬。我经历过最夸张的一次接近 1 TB 的库全量 dump 跑了七个小时第二天再看恢复又要几个小时窗口根本排不开。针对这种情况我一般会从三个方向入手第一是并行。pg_dump 的 -j 参数可以让多个表同时导出但前提是输出格式必须用目录格式-Fdpg_dump -h 127.0.0.1 -U backup_user \ -d appdb \ -F d -j 8 \ -f /backup/pg_backup/dbs/appdb_par第二个方向是分而治之。把大库里的日志表、流水表用 -T 单独排除备份主结构和小表日志表如果业务允许丢失一部分可以单独导最新的分区。这样主备份文件体积和耗时都会大幅下降。第三个方向是关注备份期间的锁行为。pg_dump 启动时会获得 AccessShareLock这个级别的锁不会挡普通 DMLINSERT、UPDATE、DELETE但会挡住那些需要更强锁的 DDL 操作比如 DROP TABLE、ALTER TABLE、TRUNCATE。所以备份窗口尽量挑业务低峰期尤其是不要在备份过程中执行大范围结构变更否则两边互相等待场面很难看。5.4 编码乱码的一个典型问题数据库编码问题平时不明显迁移时容易集中爆发。最常见的一幕是源库用 SQL_ASCII 或者 LATIN1 存着一些历史数据目标库统一规划成 UTF8恢复时执行 COPY 语句报错 invalid byte sequence for encoding UTF8: 0x...背后的逻辑是pg_dump 输出的文本内容会按数据库当前编码来解释如果源库声明的是 SQL_ASCII它不校验字节序列数据里实际存的却是别的编码的字节恢复到目标 UTF8 库时PostgreSQL 会严格校验每个字节是否符合 UTF8 规则不合法就整体报错。解决办法没有捷径先把源库的真实编码搞清楚尽量在导出前统一客户端编码比如在连接参数里加 options-c client_encodingUTF8必要时先用 SQL 把非法字符清洗一遍再做迁移。这个坑一旦踩到往往不是改个参数能解决的需要重导重转非常耗时。数据编码规划必须走在备份方案前面。6. 把定时备份定期演练做成固定工作流6.1 一份可直接改用的 bash 备份脚本备份这种事靠人肉每周执行一次早晚会忘。我建议把它落成一个脚本再交给系统的定时任务去跑。下面这份脚本是我项目的简化版可以直接拿来改#!/usr/bin/env bash set -euo pipefail BACKUP_ROOT/backup/pg_backup DB_HOST127.0.0.1 DB_PORT5432 DB_USERpostgres DB_LIST(appdb billing) KEEP_DAYS14 DATE$(date %Y%m%d_%H%M%S) mkdir -p ${BACKUP_ROOT}/global ${BACKUP_ROOT}/dbs # 1. 备份全局对象 pg_dumpall -g \ -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} \ -f ${BACKUP_ROOT}/global/globals_${DATE}.sql # 2. 逐个备份业务库 for db in ${DB_LIST[]}; do pg_dump \ -h ${DB_HOST} -p ${DB_PORT} -U ${DB_USER} \ -d ${db} -F c -Z 6 \ -f ${BACKUP_ROOT}/dbs/${db}_${DATE}.dump done # 3. 清理超过保留天数的旧备份 find ${BACKUP_ROOT}/global -type f -name globals_*.sql -mtime ${KEEP_DAYS} -delete find ${BACKUP_ROOT}/dbs -type f -name *.dump -mtime ${KEEP_DAYS} -delete密码这块多说一句不要在命令行参数里直接拼 PGPASSWORD也不要把密码写进 cron 配置。更靠谱的做法是给执行脚本的系统用户创建一个 ~/.pgpass 文件内容格式是 host:port:database:user:password然后把这个文件的权限设为 600。这样 pg_dump 和 pg_dumpall 会自动读取不会出现在进程参数里也不会被他人一眼看到。6.2 cron 调度、日志与保留周期脚本写好后用 cron 排程。我的习惯是把备份时间放在凌晨业务低峰期30 2 * * * /usr/local/bin/pg_backup.sh /var/log/pg_backup.log 21这里有一个非常实际的建议定时任务一定要有日志输出并且日志要有可检查性。如果你第二天不看一眼日志那脚本有没有执行、执行有没有报错全靠猜。我习惯每天上班先瞄一眼 /var/log/pg_backup.log搜一下 error、failed 这类关键字全程不过一分钟。保留周期用 -mtime 14 每天清理意思是最多保留两周。如果你担心只保留滚动窗口会丢掉历史归档可以单独把每月的第一个备份拷到另一个存储目录长期保存形成日备份滚动 月备份归档的组合。6.3 每月做一次恢复演练备份的终极意义在于能恢复而不是有文件。我见过太多环境天天跑定时备份文件也都在出事时恢复第一步就报错——因为备份脚本根本没有被验证过。恢复演练是检验整套方案的唯一办法。我的演练节奏是每个月挑一个周末把最近一次备份恢复到一台临时机上createdb -h 127.0.0.1 -U postgres appdb_drill pg_restore -h 127.0.0.1 -U postgres \ -d appdb_drill --no-owner --clean --if-exists \ /backup/pg_backup/dbs/appdb_latest.dump psql -h 127.0.0.1 -U postgres -d appdb_drill \ -c SELECT count(*) FROM public.orders; dropdb -h 127.0.0.1 -U postgres appdb_drill演练后我会顺便记录几个数字一次完整恢复花了多长时间、备份文件有多大、有没有报错。这些数据既能让心里有底也是后续调整备份频率和保留周期的依据。如果每次恢复演练都卡在同一类问题上那说明你的备份方案还停留在自欺欺人阶段。6.4 一些长期维护下来的习惯最后分享几个我长期坚持的、看起来很琐碎但很管用的习惯备份文件不要和数据库躺在同一块磁盘上。我见过有人把备份放在数据库机器的另一块盘里机器磁盘整体故障时全盘覆灭备份和源数据一起消失。哪怕是简单的异地或对象存储同步都比只放在本地强。对包含敏感业务的备份文件我会顺手跑一遍 sha256sum 生成校验文件并且在拷贝到异地后做一次比对。逻辑备份文件在传输过程中被截断、被篡改的情况很少见但校验一下成本很低值得保留。每次发生大的结构变更比如加表、改字段、重命名 schema我会手动画一条命令立刻重新做一次备份而不是干等每晚的定时任务。最新备份应该有实际意义而不是三个月前的一份旧文件。SQL 转储是整套备份体系的基石但它不是银弹。如果你的业务对恢复时效要求极高最好在它上面再叠加物理备份和 WAL 归档形成一个逻辑兜底 物理快速恢复的组合。多个方案并存不是浪费而是给自己留出选择的余地。