最近接了一个数据迁移的活儿帮朋友把一个老系统的业务数据从一台服务器搬到另一台服务器。库不大几十张表但外键、序列、视图全都有。一开始我想省事找图形化工具点点点结果点了几下就发现事情没那么简单要么编码不对要么权限报错要么导出来的文件回灌回去的时候外键对不上。绕了一圈最后还是老老实实把 PostgreSQL 自带的导入导出工具吃透了才把活干完。这篇东西就是那次迁移的完整记录。围绕 PostgreSQL 的数据导入和导出把我在实际操作中用的工具、踩过的坑、验证过的参数都写清楚。内容适合正在做数据库迁移、日常需要搬运表数据、或者刚接触 PostgreSQL 想知道用什么姿势导数据最稳的朋友。不夸张地说看完这篇你能少走至少两三个晚上排查问题的弯路。1. 为什么我会优先选 PostgreSQL 自带的数据搬运工具很多人一听说数据导入导出第一反应是去装一个第三方工具或者写一堆自定义脚本。我的建议很直接只要目标库是 PostgreSQL优先把自带的pg_dump、pg_restore、COPY和psql用明白。它们不是万能的但覆盖了 90% 以上的日常场景而且所有行为和数据库自身逻辑是一家的排错成本最低。1.1 一次迁移经历带来的选择当时我对比过几条路用 Navicat 之类的图形化客户端选中表右键导出再导入。看起来方便但大表容易卡界面字段类型在跨库迁移时也经常需要手动调整。自己写脚本一条条 SELECT 再拼 INSERT。小表可以碰到几百万行的表就是灾难光生成 SQL 的时间都够吃顿晚饭了。用 PostgreSQL 自带的COPY和pg_dump。前者负责表级数据流动后者负责全库或 schema 级的结构加数据备份迁移。两个结合起来基本可以应对绝大多数场景。那次迁移最后就是用pg_dump导出整个库再用pg_restore导入到新库。结构对象、数据、权限一点没乱整个过程顺利得让我自己都有点意外。1.2 自带工具和第三方工具的核心差异第三方工具最大的问题是“隔了一层”。它们一般通过 JDBC 或 ODBC 连接数据库导出的本质是先从数据库查数据再按自己的格式写成文件。这个过程中PostgreSQL 内部很多底层逻辑是拿不到也控制不了的。自带工具则直接在数据库内核层面干活COPY由后端进程直接读写文件不走 SQL 解析和客户端缓冲速度比一行行 INSERT 快几个数量级。pg_dump能识别当前数据库的完整对象依赖关系知道先导哪些表、后导哪些表才能不破坏外键约束。自带工具对 PostgreSQL 特有类型数组、jsonb、hstore、枚举等的支持是原生的第三方工具偶尔会在这里翻车。所以我的原则是能用自带的就不折腾第三方的。除非你需要把数据从 PostgreSQL 搬到 Oracle、MySQL、SQL Server那另说那种跨库场景再考虑中间格式转换工具。2. 动手前的环境准备版本选择、连接授权、客户端工具导入导出看着是命令行的活儿但很多报错其实根源在环境没搭好。我见过太多人花一晚上排查“为什么 COPY 报权限错误”最后发现是路径写错或者客户端版本太老。先花十分钟把环境捋顺后面能省很多事。2.1 PostgreSQL 版本到底怎么选我刷到过不少人在问“postgresql 下载哪个版本”尤其是新出的版本总会让人心痒。以我自己的观察版本选择其实只需要考虑三件事当前生产环境是多少就先用多少别贸然跨大版本。比如生产库里是 14迁移目标就不建议直接跨到 17除非你确认过所有兼容性影响。新项目可以优先用当前主流稳定版。比如写这篇文章时16 已经非常稳17 也已经发布了几个月但我个人在生产环境还是偏好“上一个版本的成熟度”不追求最新。便携版比如有人问的 postgresql 16 便携版适合拿来学习、测试、快速验证不建议直接跑正式业务。便携版本质上只是免安装服务和数据目录的组织方式和正式版有差异排查问题时的路径都和标准版不一样。版本匹配还有一个很容易被忽略的坑pg_dump和pg_restore的版本最好和数据库版本一致或者客户端版本不低于服务端版本。低版本客户端去连高版本服务端可能连不上去或者导出时漏掉新版本才有的对象类型。2.2 psql 连接授权排错先看这里导入导出离不开客户端连接。很多人第一步就卡在连不上数据库而且报错五花八门。常见的连接失败基本就三类找不到数据库服务报could not connect to server。先检查端口、IP、服务是否启动。密码错误报password authentication failed。如果确认密码没错再查一下 pg_hba.conf 里的认证方式有些默认配置下 md5 和 scram-sha-256 混用会出现怪问题。权限不足报permission denied for schema。这种通常不是连接层的问题而是登录用户对该库没权限。排查的时候别瞎猜顺序固定先pg_isready -h 主机 -p 端口确认网络和服务再psql -U 用户 -h 主机 -p 端口 -d 数据库测连接最后再查 pg_hba.conf。pg_hba.conf 改完要记得重载配置SELECT pg_reload_conf();或者直接pg_ctl reload。2.3 创建专用账号的检查项如果是给别人做数据迁移或者定期跑导出任务我建议新建一个专用账号别拿超级用户到处用。这个账号需要哪些权限取决于任务但以下检查项最好过一遍数据库连接权限CONNECT。读取表数据的权限SELECT或pg_read_all_dataPG 14 之后可用。写入数据的权限INSERT、UPDATE如果涉及结构变更还要CREATE、ALTER。如果是恢复备份需要CREATEDB或目标库的 owner 权限。经验之谈临时迁移任务可以直接用超级用户但如果是长期定时备份脚本一定要单独建一个最小权限账号避免某天误操作把生产库弄乱。3. 表级数据搬运COPY 与 \copy 的真实差异PostgreSQL 导出单表数据最常用的就是COPY。但新手特别容易搞混的是COPY和\copy这俩名字就差一个反斜杠行为却差很多。3.1 两者的本质区别直接看对比表格对比项COPY\copy执行主体服务端 PostgreSQL 进程psql 客户端进程文件位置服务器本机路径运行 psql 的这台机器权限要求需要超级用户或 pg_write_server_files 权限只需要普通客户端文件读写权限适用场景数据库和文件在同一台服务器远程连接时把数据导到本地我第一次用COPY导出时报错ERROR: could not open file for writing: Permission denied当时百思不得其解后来才明白COPY里的路径是给数据库服务器看的不是给你本机看的。如果你用远程工具连着一台云数据库写/tmp/xxx.csv实际上是写在服务器那边的/tmp本地根本找不到文件。反过来如果你在服务器上用 psql 连本机数据库直接COPY最方便因为文件就在旁边。如果是远程连库想导到本地电脑那就用\copy。3.2 服务器端 COPY 的权限与路径限制除了路径位置COPY在权限上还有一层限制普通的非超级用户执行COPY table TO /tmp/file.csv会报权限错误。这在 PostgreSQL 里是故意的安全设计防止普通用户随便读取服务端任意文件。解决方式有三个用超级用户执行。给目标用户授权pg_write_server_files对应导出或pg_read_server_files对应导入。用\copy绕开服务端文件权限问题。生产环境我一般不会为了导单个文件去授权临时任务直接超级用户干完就走长期任务才规规矩矩授权给专用账号。3.3 常用参数和进阶写法表级导出最常用的写法COPY users TO /backup/users.csv WITH (FORMAT CSV, HEADER true, DELIMITER ,);导入回来就是COPY users FROM /backup/users.csv WITH (FORMAT CSV, HEADER true, DELIMITER ,);几个值得记住的参数FORMAT CSVCSV 格式默认还有 text 和 binary 两种。text 是 PostgreSQL 原生的私有格式跨库迁移用它不容易出兼容问题二进制格式最快但只能给相同版本的 PostgreSQL 用。HEADER true第一行是列名。导出带上导入时可以用HEADER true跳过也能让 CSV 文件给 Excel、WPS 这种工具直接打开。DELIMITER ,分隔符。默认是 tab如果用逗号记得字段里有逗号时要靠QUOTE参数解决。ENCODING UTF8强烈建议显式指定编码尤其数据里有中文的时候。不指定容易跑到数据库默认编码上导出的文件可能在别的工具里显示乱码。另外一个容易坑人的点字段内容如果包含分隔符PostgreSQL 会自动加引号包裹但前提是FORMAT CSV。如果你用默认 text 格式又手动指定了特殊分隔符遇到特殊字符时处理逻辑会让人怀疑人生。所以能用 CSV 就用 CSV省心。4. 全库迁移pg_dump 和 pg_restore 组合拳单表数据用COPY没问题但整个库的结构加数据一起搬pg_dump才是真正的核心工具。它导出的是一个逻辑备份里面包含建表语句、索引、外键、视图、函数、序列、数据等等。4.1 pg_dump 的核心参数基础用法一句话pg_dump -h 源库IP -U 用户名 -d 数据库名 -f 备份文件名.dump但实际使用中我会按场景组合参数# 标准全库逻辑备份含数据 pg_dump -h 127.0.0.1 -U postgres -d mydb -F c -f mydb.dump # 只导结构不要数据 pg_dump -h 127.0.0.1 -U postgres -d mydb -F c --schema-only -f mydb_schema.dump # 只导数据不要结构 pg_dump -h 127.0.0.1 -U postgres -d mydb -F c --data-only -f mydb_data.dump几个关键参数说明一下-F c自定义格式压缩过的配合pg_restore用最灵活可以恢复时选择性地恢复某些表。-F p纯 SQL 文本格式可以直接用 psql 执行但灵活性差一些。--schema-only/--data-only结构和数据分开导这在某些场景下特别好用。-j并行导出配合-F d目录格式使用。多核 CPU 能明显加速大库导出。--no-owner、--no-privileges恢复时忽略原有的 owner 和权限信息。如果目标库的用户名和源库不一样这两个参数能省大量修改权限的工夫。4.2 pg_restore 如何还原用-F c生成的 dump 文件不能用 psql 直接跑必须用pg_restorepg_restore -h 目标库IP -U 用户名 -d 目标数据库名 mydb.dump需要注意目标数据库必须提前建好因为 pg_restore 不会帮你创建数据库除非你用--create参数。恢复时我最常用的是先恢复结构、再恢复数据的两段式# 先恢复结构 pg_restore -h 127.0.0.1 -U postgres -d newdb --schema-only mydb.dump # 再恢复数据 pg_restore -h 127.0.0.1 -U postgres -d newdb --data-only mydb.dump为什么要分两步因为如果结构和数据一起恢复一旦表之间存在外键依赖数据恢复的顺序错了就会报外键冲突。虽然 pg_restore 内部会处理依赖顺序但分两步可以在遇到问题时更容易定位是结构问题还是数据问题。4.3 外部工具导出的文件怎么进 PostgreSQL热搜里一堆乱七八糟的“导出”关键词比如 ArcGIS 导入 CSV 数据、华为运动健康导出数据、文华财经数据导出等等。这些工具导出的文件五花八门但最终的归宿大多是一个 CSV 或 Excel 表格。把这类外部数据导进 PostgreSQL思路其实都是统一的先把文件弄成一个干净的 CSV再用COPY ... FROM导入。我遇到过好几个人拿着 Excel 文件问我怎么导进数据库。实话实说PostgreSQL 没有直接读 xlsx 的原生命令最稳的路线是在 Excel 或 WPS 里把文件另存为 CSV。用文本编辑器检查 CSV 的编码和分隔符。很多国产表格工具默认导出 GBK 编码 CSV直接用可能乱码。在 PostgreSQL 里先建一张临时表字段类型全部先用 text 顶住导入成功后再用 SQL 转成正式类型。另外提一句表格软件导出 CSV 偶尔会报错比如有朋友遇到过 WPS 导出文件时提示 0x80000008 之类的错误。这种情况通常不是数据库的问题是表格文件本身格式异常或者导出路径没有写权限。换个目录、重新另存一份纯 CSV往往就好了。5. 大数据集导出、导入的性能与稳定性问题数据量一旦上去导入导出就不只是“能不能跑”的问题而是“多久能跑完”和“中途会不会挂”的问题。这里分享一下我在大数据集场景下的处理经验。5.1 为什么大表导出会慢甚至卡住很多人对大表导出慢的第一反应是“网络不行”或者“工具不行”但我想说先看几个更常见的瓶颈查询本身太慢。COPY导出本质是一个SELECT如果源表缺少合适的索引或者导出时还有其他大查询在跑IO 和 CPU 抢起来导出速度自然快不了。锁等待。如果导出期间有事务在写表COPY会被阻塞。反过来长期运行的大导出也可能拖住其他操作。客户端瓶颈。\copy模式下数据先由服务端发给客户端再由客户端写文件这个链路任何一个环节慢整体就慢。如果又是远程跨网网络带宽就是天花板。5.2 分批导出的具体方案什么情况下需要分批我的标准是单表超过 100GB或者导出时间超过业务允许的窗口就必须考虑分批。分批导出思路大概有四种按主键区间切分。比如每 100 万行一个区间分别导出COPY (SELECT * FROM big_table WHERE id BETWEEN 1 AND 1000000) TO /tmp/big_part1.csv WITH (FORMAT CSV, HEADER true);按时间切分。有 create_time 之类的字段时按天或按月切最自然。用游标在客户端分批拉取。写一个脚本每次 fetch 一定行数写入一个文件最后合并。用并行导出工具或插件。PostgreSQL 本身不做数据的并行COPY需要靠外部工具。网上流传的各种“大数据集导出插件”其实大多是把 shell 的配合多个导出任务并行跑本质不复杂但注意要控制并发数避免把源库 IO 打满。导入方向相同道理。一次性导入几千万行可以先BEGIN;再执行多个COPY最后一次性COMMIT这样能减少每次提交的 fsync 开销导入速度快很多。5.3 大文件传输时的压缩与校验数据导出来了文件怎么搬到目标服务器也是一个问题。裸传 CSV 可能非常大我的习惯是导出时直接压缩。用 pg_dump 的话-F c本身就带压缩不需要额外处理。用COPY导出 CSV 的话可以导出到管道直接压缩psql -h 源库IP -U postgres -d mydb -c COPY big_table TO STDOUT WITH (FORMAT CSV, HEADER true) | gzip big_table.csv.gz导入时再解压喂回去zcat big_table.csv.gz | psql -h 目标库IP -U postgres -d newdb -c COPY big_table FROM STDIN WITH (FORMAT CSV, HEADER true)拆解一下上面这两条命令核心在于把导出结果输出到标准输出然后在 shell 层做压缩和传输。这种方式不落中间文件磁盘占用小而且大文件也能持续流式传输不会因为磁盘爆掉而失败。数据到达目标端之后强烈建议做一次行数和校验值对比-- 源库统计 SELECT count(*), sum(take_hash) FROM big_table; -- 目标库统计 SELECT count(*), sum(take_hash) FROM big_table;count 只能证明行数一致sum(哈希) 能发现某一行内容是否在传输中损坏算是成本最低的校验方案。6. 编码、路径、权限高频踩坑排查链路导入导出的报错就那么多翻来覆去其实集中在编码、路径、权限这三类。我把完整的排查链路写出来遇到问题照着走一遍基本能解决 80% 的情况。6.1 编码问题从“乱码”逆推“编码”乱码的经典表现是导入后中文全变成“鍧愰噴”或者“???”导出后 Excel 打开全是乱码。这种问题的根因是编码不一致。PostgreSQL 数据库安装时常选 UTF8 编码而外部 CSV 文件可能是 GBK、GB2312 或者带 BOM 的 UTF8。排查链路先确定数据库编码SHOW server_encoding;再确定文件的真实编码。Linux 下可以用file命令file -i data.csvWindows 下可以用 Notepad 或 VS Code 看底部编码提示。导入时显式指定编码COPY big_table FROM /path/data.csv WITH (FORMAT CSV, HEADER true, ENCODING GBK);导出时也显式指定COPY big_table TO /path/data.csv WITH (FORMAT CSV, HEADER true, ENCODING UTF8);别偷懒不写 ENCODING数据库默认编码和文件实际编码常常是两回事显式声明是最保险的。6.2 路径问题为什么 COPY 报无法打开文件COPY报could not open file for reading/writing时第一步不是改文件权限而是确认你写的路径是服务端的路径还是客户端的路径。简单判断法如果你用 psql 在服务器本机执行那路径就是服务器的绝对路径如果你用 DBeaver、Navicat 这类远程工具执行COPY路径还是服务器的路径和你的笔记本没有任何关系。想导出到本地两条路用\copy直接从客户端写到本地。用COPY ... TO STDOUT加 shell 重定向把输出流接管到本地文件。另外服务端的路径还有个隐藏问题PostgreSQL 默认对COPY能读写的目录有限制。即使你是超级用户如果数据库是 Docker 容器跑的容器里和宿主机是两个文件系统写出来的文件在容器里不是你以为的宿主机位置。遇到这种场景要么用 docker cp 拷文件要么用\copy直接绕开。6.3 权限和对象还原序列、外键、owner 的坑整库恢复后业务报错的经典场景是表都在数据也在但插入数据时主键冲突。原因是序列没有跟着数据重置。pg_dump导出的 dump 文件里有时包含setval有时不包含取决于导出时的参数和数据状态。恢复完如果发现自增主键有问题手动重置一下序列SELECT setval(users_id_seq, (SELECT max(id) FROM users));外键的顺序问题则更隐蔽。如果你只导了数据--data-only恢复时先导子表、后导父表就会撞上外键约束。两个解决思路恢复数据前先禁用外键约束检查SET session_referential_integrity off;或ALTER TABLE ... DISABLE TRIGGER ALL;恢复完再打开。或者用pg_restore恢复自定义格式的 dump它会根据依赖关系自动排序这也是我一直推荐-F c的原因。至于 owner 的问题常见于跨账号迁移。原来表属于old_user新库只有new_user。如果 dump 文件里带了OWNER TO old_user恢复的时候会报错说角色不存在。解决办法就是前面提到的pg_dump ... --no-owner --no-privileges恢复完成后再用ALTER TABLE ... OWNER TO new_user;批量处理即可。这里我提供一个执行 ALL 语句改 owner 的 SQL方便批量处理SELECT ALTER TABLE || tablename || OWNER TO new_user; FROM pg_tables WHERE schemaname public;把所有生成的语句复制出来跑一遍就行。视图、序列、函数也要同样处理只看表是不够的。7. 把导入导出变成稳定流程的几个小习惯迁移这条路我走过几轮踩坑无数之后反倒觉得最重要的不是哪条命令多精妙而是每次操作前有没有一套稳定的流程。这里分享几个我自己的习惯供你参考。导出前先做一次VACUUM ANALYZE至少对要导的核心表做一次。目的是让统计信息更新让COPY的查询计划和文件读取更稳定。注意VACUUM在大库上可能耗时较长要安排在维护窗口内。任何一次重要导出导完先看日志再快速做抽样验证。比如导出一个 CSV先wc -l看行数再head -n 5看前五行基本能确认导出的文件是否正常。如果是在生产环境恢复数据恢复前一定要记录好当前生产库的版本、插件列表和数据库参数。比如源库装了 postgis 之类的扩展目标库没装恢复时就会因为找不到扩展而失败。提前装好同样版本的扩展比你恢复时再去一个个补要省心太多。大文件传输后务必校验。除了 count 加哈希还可以用pg_dump的--strict-checksums参数配合校验和但那更多用于物理复制逻辑备份场景下做一次 count 加哈希就够了。最后说一个我自己用了很久的收尾动作数据导入完成后不要急着跑业务让用户试先自己写几个查询验证外键关联比如订单表对用户表、明细表对订单表各跑一个LEFT JOIN看看有没有孤儿记录。所谓“导入成功”不是导入工具不报错而是业务查询的结果和源库一致。这个习惯帮我挡下过至少三次看起来成功、实际上有损耗的迁移事故。