纲要pg_dump— 单数据库逻辑备份工具输出格式plain-Fp、custom-Fc、directory-Fd、tar-Ft核心选项-a--data-only、-s--schema-only、-t--table、-T--exclude-table、-n--schema、-j--jobs、--lock-wait-timeout、--snapshot管道传输与跨版本迁移pg_dumpall— 实例级逻辑备份工具全局对象备份roles、tablespaces、配置参数权限-g--globals-only选项pg_restore— 归档格式恢复工具并行恢复-j选择性恢复-t、-n、-PCOPY与\copy— 高性能数据导入导出服务端COPY与客户端\copy的区别PostgreSQL 17ON_ERROR选项stop/ignore并行备份与加速策略directory格式-Fd是唯一支持并行转储的格式单表大表的加速方案pg_export_snapshot() 多事务并行COPY第三方工具生态pgcopydb— 全量迁移与克隆pg_dumpbinary/pg_restorebinary— 二进制格式转储pg_dump单数据库逻辑备份pg_dump是PostgreSQL官方自带的单数据库逻辑备份工具即使在数据库并发读写期间也能创建一致性备份且不会阻塞其他用户的访问。pg_dump仅转储单个数据库如需备份整个集群或集群范围内的全局对象如角色和表空间应使用pg_dumpall。输出格式pg_dump支持四种输出格式通过-F或--format选项指定格式选项特点适用场景plain纯文本-Fp生成SQL脚本可用psql执行可手动编辑跨版本迁移、简单备份custom自定义-Fc压缩存储支持pg_restore选择性恢复和并行恢复生产环境推荐directory目录-Fd每个表生成独立文件唯一支持并行转储的格式大数据库并行备份tar-Fttar归档格式支持pg_restore兼容性需求custom和directory格式是最灵活的两种输出格式支持对归档项目的选择和重新排序支持并行恢复且默认启用压缩。常用选项以下为pg_dump的核心选项选项简写说明--data-only-a仅转储数据不转储结构定义--schema-only-s仅转储结构定义不转储数据--tableTABLE-t仅转储指定表可多次指定--exclude-tableTABLE-T排除指定表--schemaSCHEMA-n仅转储指定模式--jobsN-j并行转储的工作进程数仅directory格式有效--lock-wait-timeoutTIME—获取表锁的超时时间--snapshotSNAPSHOT—使用指定的同步快照--fileFILENAME-f指定输出文件--clean-c在创建对象前先DROP仅纯文本格式有效基础示例创建测试表并插入数据CREATETABLEt1(idINT,infoTEXT);INSERTINTOt1SELECTgenerate_series(1,10),data_||generate_series(1,10);仅导出数据-apg_dump-a-tt1-ft1_data_only.sql postgres仅导出结构-spg_dump-s-tt1-ft1_schema_only.sql postgres导出为纯文本格式pg_dump-Fp-tt1-ft1_dump.sql postgres查看导出的SQL脚本内容head-20t1_dump.sql输出示例---- PostgreSQL database dump--SETstatement_timeout0;SETlock_timeout0;SETclient_encodingUTF8;CREATETABLEpublic.t1(idinteger,infotext);COPYpublic.t1(id,info)FROMstdin;1data_12data_2...管道传输无落地备份与恢复pg_dump默认输出到标准输出可借助Linux管道特性实现不落地的数据库迁移# 创建目标数据库createdb mydb# 通过管道直接将备份数据灌入目标库pg_dump-Fppostgres|psql-dmydb此种方式适用于小数据量的快速迁移场景。pg_dumpall实例级逻辑备份pg_dumpall用于将整个PostgreSQL集群的所有数据库转储到一个脚本文件中。其实现方式是依次为集群中的每个数据库调用pg_dump。与pg_dump的核心区别在于pg_dumpall还会转储所有数据库通用的全局对象global objects包括数据库角色roles、表空间tablespaces以及配置参数的权限授予privilege grants for configuration parameters。pg_dump不保存这些对象。全局对象备份使用-g或--globals-only选项仅转储全局对象角色和表空间不转储任何数据库pg_dumpall-gglobals.sql查看输出内容catglobals.sql输出示例---- PostgreSQL database cluster dump--SETdefault_transaction_read_onlyoff;---- Roles--CREATEROLE postgres;ALTERROLE postgresWITHSUPERUSER INHERIT CREATEROLE CREATEDB LOGINREPLICATIONBYPASSRLS;CREATEROLE app_user;ALTERROLE app_userWITHNOLOGIN;注意事项pg_dumpall需要多次连接PostgreSQL服务器每个数据库一次使用密码认证时每次都会提示输入密码建议配置~/.pgpass文件。执行完整的pg_dumpall备份通常需要数据库超级用户权限。pg_restore归档格式恢复工具pg_restore用于从pg_dump以非纯文本格式custom、directory、tar创建的归档文件中恢复PostgreSQL数据库。它支持两种运行模式直接恢复模式指定目标数据库名称直接将归档内容恢复到该数据库脚本生成模式不指定目标数据库生成包含重建数据库所需SQL命令的脚本文件并行恢复pg_restore支持通过-j或--jobs选项并发执行恢复过程中最耗时的步骤——即加载数据、创建索引和创建约束# 并行恢复使用4个并发工作进程pg_restore-dtarget_db-j4-Fd./backup_dir选择性恢复pg_restore支持仅恢复指定的表、模式或函数# 仅恢复特定表pg_restore-dtarget_db-tt1-Fcbackup.dump# 仅恢复特定模式pg_restore-dtarget_db-npublic-Fcbackup.dumpCOPY与\copy高性能数据导入导出COPY是PostgreSQL中在表和文件之间批量移动数据的高性能命令。COPY vs \copy特性COPY服务端\copy客户端psql元命令执行位置PostgreSQL服务器psql客户端文件路径服务器文件系统路径客户端文件系统路径权限要求PostgreSQL服务进程用户需有文件读写权限客户端用户需有文件读写权限适用场景服务端本地文件操作远程客户端文件操作基础用法导出数据-- 导出整个表COPY t1TO/tmp/t1.csvWITH(FORMAT CSV,HEADERtrue,DELIMITER,);-- 导出查询结果COPY(SELECT*FROMt1WHEREid5)TO/tmp/t1_filtered.csvWITH(FORMAT CSV,HEADERtrue);# 查看导出结果cat/tmp/t1.csv导入数据-- 从CSV文件导入COPY t1FROM/tmp/t1.csvWITH(FORMAT CSV,HEADERtrue,DELIMITER,);psql中使用\copy-- 导出到客户端本地文件\copy t1TOt1.csvWITH(FORMAT CSV,HEADERtrue);-- 从客户端本地文件导入\copy t1FROMt1.csvWITH(FORMAT CSV,HEADERtrue);性能优势COPY命令针对批量数据加载进行了高度优化。根据PostgreSQL官方文档使用COPY加载大量行几乎总是比使用INSERT快即使使用了PREPARE并将多个插入分批到单个事务中。COPY在与之前的CREATE TABLE或TRUNCATE命令在同一事务中使用时速度最快。性能提升主要源于减少网络往返延迟避免重复的SQL解析开销减少事务和上下文切换开销内部针对批量操作的缓冲区优化PostgreSQL 17 增强ON_ERROR 选项在PostgreSQL 17之前COPY FROM在导入过程中如果遇到任何数据转换错误如类型不兼容、异常字符等整个事务会回滚已导入的数据全部不可见变为死元组需要VACUUM清理。这对于大规模数据导入场景极为不利。PostgreSQL 17引入了ON_ERROR选项允许指定错误处理策略ON_ERROR stop默认遇到错误时失败并回滚ON_ERROR ignore跳过错误行继续处理后续数据-- PostgreSQL 17忽略类型转换错误继续导入COPY t1FROM/data/import.csvWITH(FORMAT CSV,HEADERtrue,ON_ERRORignore);当设置为ignore时COPY会跳过无法转换的行并在NOTICE消息中报告被跳过的行数。此选项仅适用于COPY FROM且FORMAT为text或csv时。并行备份与性能优化pg_dump 并行转储pg_dump的-j或--jobs选项支持并行转储但仅适用于directory格式-Fd。这是因为directory格式是唯一允许多个进程同时写入数据的输出格式。# 使用4个并行工作进程进行备份pg_dump-Fd-j4-f./my_backup postgres并行转储的本质是表级并行每个工作进程负责转储一个或多个表。如果数据库中只有一个大表如200GB即使设置-j 10该表的转储仍然由单个进程完成无法实现表内并行。单表大表的加速方案pg_export_snapshot针对单个大表的加速备份可利用PostgreSQL的快照导出功能。核心原理在REPEATABLE READ隔离级别下开启一个事务使用pg_export_snapshot()导出当前事务的快照ID多个并发事务使用相同的快照ID确保看到一致的数据视图每个事务使用COPY导出表的不同数据分片-- 会话1导出快照BEGINTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SELECTpg_export_snapshot();-- 返回快照ID如 00000017-0000002F-1-- 会话2、3、...使用相同快照并行导出不同分片BEGINTRANSACTIONISOLATIONLEVELREPEATABLEREAD;SETTRANSACTIONSNAPSHOT00000017-0000002F-1;COPY(SELECT*FROMlarge_tableWHEREidBETWEEN1AND1000000)TO/data/part1.csvWITH(FORMAT CSV);COMMIT;pg_dump本身也支持--snapshot选项可指定使用已导出的快照。结合--exclude-table排除已通过COPY并行导出的表可实现混合加速策略。pgcopydb全量迁移工具pgcopydb是一个将整个PostgreSQL数据库从源实例复制到目标实例的工具。它本质上利用了pg_dump的能力同时实现了以目录格式并行转储并行恢复克隆clone和分叉fork功能变更回放change replay# 全量克隆数据库pgcopydb clone--sourcepostgresql://usersource:5432/db\--targetpostgresql://usertarget:5432/dbpg_dumpbinary二进制格式转储pg_dump在处理某些特殊数据类型时存在限制bytea类型数据通过escape/hex编码后的总大小超过1GB时无法导出某些自定义类型或包含\0字节的数据可能被截断pg_dumpbinary将PostgreSQL数据库以二进制格式转储需使用配套的pg_restorebinary恢复。对于常规场景仍应优先使用官方自带的pg_dump/pg_restore。API 速览pg_dump 命令行接口方法签名pg_dump[connection-option...][option...][dbname]核心参数参数类型说明-f, --fileFILENAMEstring输出文件路径-F, --formatpcd-j, --jobsNinteger并行工作进程数仅directory格式-a, --data-onlyflag仅转储数据-s, --schema-onlyflag仅转储结构-t, --tableTABLEstring指定要转储的表可重复-T, --exclude-tableTABLEstring排除指定表可重复-n, --schemaSCHEMAstring指定要转储的模式可重复-c, --cleanflag恢复前先DROP对象仅纯文本格式--lock-wait-timeoutTIMEstring获取锁的超时时间--snapshotSNAPSHOTstring使用指定的同步快照-v, --verboseflag输出详细信息返回值命令成功时返回0失败时返回非0值。代码示例# 使用custom格式备份启用压缩pg_dump-Fc-fmydb.dump mydb# 使用directory格式并行备份4个进程pg_dump-Fd-j4-f./backup_dir mydb# 仅备份指定表的结构pg_dump-s-torders-tcustomers-fschema.sql mydb# 带超时选项的备份pg_dump --lock-wait-timeout10s-fmydb.sql mydbpg_dumpall 命令行接口方法签名pg_dumpall[connection-option...][option...]核心参数参数类型说明-f, --fileFILENAMEstring输出文件路径-g, --globals-onlyflag仅转储全局对象角色和表空间-a, --data-onlyflag仅转储数据-c, --cleanflag恢复前先DROP所有对象-O, --no-ownerflag不输出对象所有者设置-r, --roles-onlyflag仅转储角色代码示例# 完整备份整个集群pg_dumpall-fcluster_full.sql# 仅备份全局对象pg_dumpall-g-fglobals_only.sql# 备份时不包含所有者信息便于非超级用户恢复pg_dumpall-O-fcluster_no_owner.sqlpg_restore 命令行接口方法签名pg_restore[connection-option...][option...][filename]核心参数参数类型说明-d, --dbnameDBNAMEstring目标数据库名称-f, --fileFILENAMEstring输出脚本文件-j, --jobsNinteger并行恢复的工作进程数-a, --data-onlyflag仅恢复数据-c, --cleanflag恢复前先DROP对象-C, --createflag先创建目标数据库-t, --tableTABLEstring仅恢复指定表-n, --schemaSCHEMAstring仅恢复指定模式-P, --functionNAMEstring仅恢复指定函数-l, --listflag列出归档内容TOC代码示例# 直接恢复到数据库pg_restore-dtarget_db-Fcmydb.dump# 并行恢复4个进程pg_restore-dtarget_db-j4-Fd./backup_dir# 先清理再恢复pg_restore-dtarget_db-c-Fcmydb.dump# 仅列出归档内容TOCpg_restore-l-Fd./backup_dirCOPY SQL 命令方法签名COPY { table_name[(column_name[,...])]|(query)}TO{filename|PROGRAMcommand|STDOUT }[[WITH](option[,...])]COPY table_name[(column_name[,...])]FROM{filename|PROGRAMcommand|STDIN }[[WITH](option[,...])][WHEREcondition]核心选项选项类型说明FORMATtext/csv/binary数据格式DELIMITERcharacter字段分隔符NULLstringNULL值的表示HEADERbooleanCSV是否包含表头QUOTEcharacterCSV引用字符ESCAPEcharacterCSV转义字符ENCODINGstring文件编码ON_ERRORstop/ignore错误处理策略PostgreSQL 17代码示例-- 导出为CSV含表头COPY t1TO/tmp/t1.csvWITH(FORMAT CSV,HEADERtrue,DELIMITER,);-- 导出查询结果COPY(SELECTid,infoFROMt1WHEREid5)TO/tmp/t1_filtered.csvWITH(FORMAT CSV);-- 从CSV导入PostgreSQL 17 支持ON_ERRORCOPY t1FROM/tmp/t1.csvWITH(FORMAT CSV,HEADERtrue,ON_ERRORignore);-- 使用psql \copy客户端路径\copy t1TOt1.csvWITH(FORMAT CSV,HEADERtrue);\copy t1FROMt1.csvWITH(FORMAT CSV,HEADERtrue);Demo 完整示例本Demo演示如何使用pg_dump和pg_restore完成一个完整的数据库备份与恢复流程涵盖custom格式备份、并行恢复以及COPY数据导出。环境准备# 创建测试数据库createdb demo_db# 连接到数据库并创建测试表psql-ddemo_dbEOF CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_name TEXT NOT NULL, amount NUMERIC(10,2), created_at TIMESTAMP DEFAULT NOW() ); INSERT INTO orders (customer_name, amount) SELECT Customer_ || generate_series(1, 10000), round((random() * 1000)::numeric, 2) FROM generate_series(1, 10000); CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(id), product_name TEXT, quantity INT, price NUMERIC(10,2) ); INSERT INTO order_items (order_id, product_name, quantity, price) SELECT (random() * 10000)::INT 1, Product_ || generate_series(1, 50000), (random() * 10)::INT 1, round((random() * 100)::numeric, 2) FROM generate_series(1, 50000); -- 确认数据量 SELECT orders AS table_name, COUNT(*) FROM orders UNION ALL SELECT order_items, COUNT(*) FROM order_items; EOF备份# 使用custom格式备份默认压缩支持并行恢复pg_dump-Fc-fdemo_db.dump demo_db# 查看备份文件大小ls-lhdemo_db.dump# 使用directory格式并行备份4个进程pg_dump-Fd-j4-f./demo_backup_dir demo_db# 查看目录结构tree ./demo_backup_dir./demo_backup_dir ├── 44814.dat.gz ├── 44815.dat.gz ├── 44816.dat.gz ├── 44817.dat.gz ├── 44818.dat.gz ├── toc.dat └── ...恢复# 创建目标数据库createdb demo_db_restored# 从custom格式恢复pg_restore-ddemo_db_restored-Fcdemo_db.dump# 验证恢复结果psql-ddemo_db_restored-cSELECT orders AS table_name, COUNT(*) FROM orders UNION ALL SELECT order_items, COUNT(*) FROM order_items;# 并行恢复从directory格式4个进程pg_restore-ddemo_db_restored-j4-Fd./demo_backup_dir使用COPY导出数据# 导出orders表为CSVpsql-ddemo_db-c\copy orders TO orders.csv WITH (FORMAT CSV, HEADER true)# 导出order_items表仅部分列psql-ddemo_db-c\copy (SELECT id, order_id, product_name, quantity FROM order_items) TO order_items_subset.csv WITH (FORMAT CSV, HEADER true)# 查看导出文件行数wc-lorders.csv order_items_subset.csv运行说明环境要求PostgreSQL 14ON_ERROR特性需要PostgreSQL 17执行顺序环境准备 → 备份 → 恢复 → COPY导出权限要求执行pg_dump和pg_restore需要数据库连接权限COPY到文件需要PostgreSQL服务进程对目标目录有写入权限技术点总结技术点说明pg_dump -Fccustom格式压缩存储支持pg_restore选择性恢复和并行恢复pg_dump -Fd -jdirectory格式 并行转储适合大数据库pg_restore -j并行恢复充分利用多核CPUpg_restore -l列出归档TOC可用于选择性恢复前的预览\copypsql客户端命令数据导出到客户端本地文件COPY WITH (FORMAT CSV, HEADER true)CSV格式导出含表头便于与其他工具交互项目难点与解决方案核心难点单表大表的并行备份pg_dump的并行能力仅限于表级单表无法通过-j实现并行加速导入过程中的数据质量问题COPY FROM在PostgreSQL 17之前遇到任何错误即回滚全部事务大规模导入时风险极高特殊数据类型的导出限制pg_dump无法处理超过1GB的bytea类型数据以及包含\0字节的自定义类型一致性快照的跨进程共享多进程并行备份时需要确保所有进程看到同一时间点的数据解决方案难点解决方案适用版本单表大表备份慢使用pg_export_snapshot()导出快照多事务并行COPY导出不同数据分片PostgreSQL 9.2COPY导入错误回滚PostgreSQL 17 使用ON_ERROR ignore跳过错误行PostgreSQL 17特殊数据类型导出使用pg_dumpbinary/pg_restorebinary进行二进制格式转储第三方工具多进程一致性pg_dump --snapshot或手动SET TRANSACTION SNAPSHOTPostgreSQL 9.2广度本文覆盖了PostgreSQL逻辑备份的完整工具链pg_dump单库、pg_dumpall实例级、pg_restore恢复、COPY/\copy高性能导入导出以及第三方扩展工具pgcopydb和pg_dumpbinary。深度深入剖析了各工具的核心参数、格式差异、性能特性重点讲解了directory格式是唯一支持并行转储的格式单表大表通过pg_export_snapshot()实现伪并行备份的原理PostgreSQL 17ON_ERROR选项的设计动机和使用方式COPY相比INSERT的性能优势来源复杂度涉及的技术复杂度包括多格式输出plain/custom/directory/tar的选型权衡并行备份与恢复的参数调优-j快照导出与跨事务一致性保证服务端COPY与客户端\copy的权限和路径差异官方文档pg_dumppg_dumpallpg_restoreCOPYpg_export_snapshotPostgreSQL 17 Release Notes参考链接pgcopydb GitHub Repositorypg_dumpbinary GitHub RepositoryStack Overflow: COPY vs INSERT performancePostgreSQL Wiki: Backup and Restore总结本文系统梳理了PostgreSQL逻辑备份的完整工具链。pg_dump作为单数据库逻辑备份的核心工具支持plain、custom、directory和tar四种输出格式其中custom和directory格式支持pg_restore的选择性恢复与并行恢复directory格式是唯一支持并行转储的格式。pg_dumpall扩展了pg_dump的能力可备份整个集群及全局对象角色、表空间、配置参数权限。COPY命令是PostgreSQL官方提供的高性能批量数据传输工具在批量加载场景下性能显著优于INSERTPostgreSQL 17引入的ON_ERROR选项解决了大规模导入时错误即回滚的痛点。对于单表极大的场景可借助pg_export_snapshot()导出快照并结合多事务并行COPY实现加速对于特殊数据类型如超大bytea可选用pg_dumpbinary进行二进制格式转储。
