上个月接了个迁移需求上游直接丢给我一个pg_dump -Fc打好的 dump 文件要求导入到目标库的ods schema下并且所有表必须落到新建的SSD 表空间上。我一开始按惯性直接pg_restore -d target db.dump结果对象全进了public表空间也用的是目标库默认表空间跟需求差了十万八千里。后来把 pg_dump/pg_restore 在 schema、tablespace 上的几个参数吃透才把跨 schema 和跨表空间的逻辑导入导出理顺。这篇就是那次迁移的完整复盘。内容适合正在做 PostgreSQL 数据迁移、逻辑备份恢复特别是要把备份恢复到和源库不同 schema 或不同表空间的人。我会从 dump 文件的格式结构讲起然后分别讲改 schema 和改表空间的几种做法最后给一个完整可复制的迁移过程。1. 先搞清楚 dump 文件里到底装了什么格式与 TOC 是恢复方案的根基很多人一上来就执行pg_restore报错再查反复折腾。我的习惯是任何恢复操作开始前先花两分钟搞清楚这个 dump 是什么格式、里面有哪些对象、对象挂在哪个 schema 下。这一步做好了后面改 schema、改表空间都是顺理成章的事。1.1 不同导出格式对“改 schema / 改表空间”的影响pg_dump默认导出的是纯文本 SQL也就是一堆CREATE TABLE、COPY、CREATE INDEX的原始语句。这种格式可以直接用psql执行但它有两个致命缺点第一不能选择性恢复某张表或某个 schema第二里面是纯文本对象定义想改 schema 只能靠文本替换改起来心里没底。所以我一直推荐业务迁移用自定义格式pg_dump -Fc -d orders_db -f orders.dump-Fc是 custom format生成的是一个带目录TOC的归档文件配合pg_restore可以精准控制恢复哪些对象、跳过哪些对象后面的 schema 重命名和表空间覆盖都是在这个基础上实现的。如果你用的是-Fd目录格式其实也行效果类似。纯文本格式则更多用在“我就想直接备份成一个 SQL 文件扔给别人执行”的场景它的可控性明显差一截。1.2 用 pg_restore -l 读档先看对象清单再决定改法拿到一个 dump我第一件事永远是看目录pg_restore -l orders.dump | head -50输出大概是这样的; ; Archive TOC 条目; ; 5047; 16391 TABLE public orders pg_dump ; 5048; 16392 SEQUENCE public orders_id_seq pg_dump ; 5049; 16393 TABLE DATA public orders pg_dump ; 5050; 16394 INDEX public orders_pkey pg_dump这个列表就是恢复时的执行地图。从里面你能快速看到对象的原始 schema 是什么、有哪些表、哪些序列、哪些索引。如果源库结构复杂我还会统计一下对象类型分布pg_restore -l orders.dump | awk {print $4} | sort | uniq -c | sort -nr这会输出42 TABLE 35 TABLE DATA 40 INDEX 12 SEQUENCE看这个分布有个直接好处你能判断后面改 schema 时的工作量。如果全是表和索引用ALTER TABLE SET SCHEMA搬起来很简单如果还有一堆函数、触发器、视图尤其是函数体里写死了public.xxx这种引用那就要优先考虑重命名 schema 而不是搬对象。另外提醒一句pg_restore -l看的是 TOC 条目TABLESPACE并不是一个独立的可筛选条目它是嵌在CREATE TABLE、CREATE INDEX定义里的子句。所以表空间不能在 TOC 里单独挑选恢复只能靠后面讲的覆盖参数来处理。2. 让对象落进另一个 schema三种路线的选择与操作细节目标 schema 和源 schema 不一致是这次迁移最核心的诉求。常见场景是源库所有表都在public但目标是把它们归到业务专用的ods/app_data这类 schema 里避免和目标库已有的public对象混在一起。这里有三条路线我按推荐程度和使用频率讲。2.1 路线A原样恢复之后用 ALTER SCHEMA / SET SCHEMA 搬运这是最直观的思路先按 dump 原样恢复让对象进到源 schema然后再用 SQL 把它们搬到目标 schema。如果只是想把整个源 schema 改名最粗暴的做法是ALTER SCHEMA public RENAME TO ods;一步到位所有表、索引、序列、视图、函数全部跟着走。但我个人不建议在生产目标库这么干原因有两个目标库如果还有其他程序依赖public作为默认 schema改完名之后这些程序的默认搜索路径就失效了PG15 之后publicschema 的所有者是pg_database_owner默认权限体系和普通 schema 不一样直接改名容易埋权限坑。更稳妥的是先恢复再批量移动表ALTER TABLE public.orders SET SCHEMA ods; ALTER TABLE public.order_items SET SCHEMA ods;表少的时候这么写没问题但表多的时候你得写脚本生成 SQL。我用过这样一段动态语句psql -d analytics -Atc \ SELECT ALTER TABLE || quote_ident(n.nspname) || . || quote_ident(c.relname) || SET SCHEMA ods; FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname public AND c.relkind IN (r,p) | psql -d analyticsrelkind IN (r,p)是表和分区表必要的时候把v视图、m物化视图、S序列也加进去但要记得视图、物化视图和序列都有自己的ALTER VIEW SET SCHEMA、ALTER SEQUENCE SET SCHEMA语法不能一概用ALTER TABLE处理。这个方法的缺点很明显对象类型一多脚本就复杂外键、序列归属、函数依赖都要逐个确认。所以它只适合对象少、以表为主的场景。2.2 路线B把 dump 转成 SQL 后做文本级预处理当对象很多、又不想在目标库里先建一堆中间对象时我常用的是路线B先用pg_restore把归档文件还原成 SQL 文本然后对文本做 schema 替换最后用psql执行。pg_restore -f orders.sql orders.dump然后看关键的 schema 设置行grep -n CREATE SCHEMA\|SET search_path orders.sql一般会看到CREATE SCHEMA public; SET search_path public, pg_catalog;如果你的需求是把public替换成ods可以用 sedsed -i s/SET search_path public/SET search_path ods/g; s/public\./ods\./g orders.sql psql -d analytics -v ON_ERROR_STOP1 -f orders.sql注意s/public\./ods\./g这步有风险。它会把 SQL 里所有以public.开头的地方都替换成ods.包括函数体内部写的public.func()这种调用。如果这些函数迁移后确实应该去ods下找函数那没问题但如果public.func()引用的对象并不在本次迁移范围内这里就会悄悄改变函数运行行为。所以我实际执行 sed 之前一定会先做一次差异检查grep -n public\. orders.sql | head -50先肉眼扫一遍都有哪些地方引用了public判断替换是不是安全的。如果函数体里大量硬编码public我会放弃 sed转用下面的路线C或者干脆在目标库里保住一个publicschema只让业务表进ods。2.3 路线CPG17 以后直接使用 --schema-rename如果你的源库和目标库都是 PostgreSQL 17 或更新版本pg_restore直接提供了 schema 重命名能力这是最省心的方案pg_restore -d analytics \ -n public \ --schema-renamepublic:ods \ -O -x \ orders.dump这里的--schema-renameold:new会把 dump 中原本属于public的对象直接创建到ods下。和 sed 方案相比它操作的是归档层的 schema 归属信息不会去改函数体里的字符串文本所以误伤概率小很多。-n public配合使用意思是“只恢复 dump 中属于 public 的对象”如果不加这个限制dump 里其他 schema 下的对象也会被恢复。不过要提醒一点--schema-rename是对整个 source schema 做重定向如果 dump 里有多个 schema 且相互之间有跨 schema 引用你最好在建映射的时候把所有相关 schema 都考虑进去不然恢复时可能会出现找不到对象的问题。另外我的实测经验是不同小版本对-j并行恢复和--schema-rename组合的支持略有差异生产环境第一次跑的时候先不要并行跑通一遍再加并行参数。2.4 三种路线怎么选我自己选路线的判断依据大概是这样场景推荐路线原因对象少十几张表主要是表路线A直接 ALTER直观可控对象很多PG 版本低于 17路线B文本预处理批量效果好对象很多PG 版本 17路线C归档层重定向最稳函数体大量硬编码 public路线C优先sed 和搬运都可能误伤函数逻辑还有个细节不管用哪条路线恢复完以后都要把序列的归属检查一遍。pg_restore会把序列恢复到对应 schema但如果你用的是路线A的搬运方式记得把ALTER SEQUENCE ... SET SCHEMA也带上否则序列留在原 schema表的 nextval 调用可能因为 search_path 找不到序列而报错。3. 表空间不是“导进去”的覆盖、剥离与事后搬移表空间这个问题很多人到了恢复现场才反应过来。原因很简单大多数测试环境里的表都建在默认表空间pg_default上大家导出导入从来不看表空间设置。但一旦源库有表被显式建到了某个业务表空间dump 里就会出现TABLESPACE xxx子句目标库如果没有这个表空间恢复直接报错。3.1 dump 中的 TABLESPACE 到底是怎么出现的我找了一个源库做过测试如果一张表是这样建的CREATE TABLE orders ( id bigint, created_at timestamptz ) TABLESPACE fast_ssd;那么pg_dump默认会在 dump 里带上这句 TABLESPACE。也就是说pg_dump 默认会输出表空间选择信息除非你显式告诉它不要。pg_dump提供了对应的开关pg_dump -Fc --no-tablespaces -d orders_db -f orders_nostbls.dump加上--no-tablespaces之后dump 里所有对象都不带表空间子句恢复时全部落到目标库的默认表空间。这是最彻底的“剥离表空间”方式适合那种“我根本不需要保留源表空间只要把数据导进去后面统一安排落盘位置”的场景。3.2 恢复时指定表空间pg_restore --tablespace如果不想重新导 dump或者你希望直接把所有对象恢复到一个确定的目标表空间pg_restore有现成的覆盖参数pg_restore -d analytics \ --tablespacefast_ssd \ -O -x \ orders.dump这个参数会把恢复过程中创建的表和索引全部放到fast_ssd表空间。实测下来它并不仅仅是加一个SET TABLESPACE命令而是会覆盖 dump 中每个对象自带的表空间设置。也就是说哪怕 dump 里的CREATE TABLE写的是TABLESPACE old_ts只要你在命令行指定了--tablespacefast_ssd最终建出来的表就在fast_ssd。使用前要确认三件事目标表空间在目标实例上已经存在否则恢复时直接报tablespace xxx does not exist当前恢复连接使用的角色或者 dump 中对象 owner 的角色对该表空间有 CREATE 权限如果并行恢复--tablespace会作用到所有并行 worker 上不会有对象漏网。表空间权限这个点特别容易踩很多 DBA 用超级用户创建了表空间但业务迁移用的账号并不是超级用户。如果你在恢复时没加-Opg_restore 默认会执行 dump 里的 owner 变更指令SET SESSION AUTHORIZATION也就是用源库对象 owner 的身份去建对象此时即使你连接的是超级用户实际建表的角色可能也变成了源库的 owner而这个角色在目标实例上可能根本不存在或者没有目标表空间的权限。所以跨环境恢复-O跳过 owner 设置和-x跳过权限几乎是我必加的参数。3.3 如果只是想落到默认表空间再加事后批量搬迁有时候你的目标是“先导进去再按业务需求分表空间”。这种情况我会在恢复时不指定--tablespace让对象全部落到pg_default然后事后批量迁移ALTER TABLE ods.orders SET TABLESPACE fast_ssd; ALTER TABLE ods.order_items SET TABLESPACE archive_hdd;批量迁移所有表到某个表空间可以用动态 SQLpsql -d analytics -Atc \ SELECT ALTER TABLE ods. || quote_ident(relname) || SET TABLESPACE fast_ssd; FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname ods AND c.relkind r; | psql -d analytics注意ALTER TABLE ... SET TABLESPACE会移动表数据文件过程中表会被 ACCESS EXCLUSIVE 锁锁住表越大锁的时间越长。所以生产环境千万别在业务高峰做这个操作。我一般会先按表大小排序把核心大表挪到目标表空间其他小表放在一起批量搬。另外索引不会跟着表自动换表空间。你把表搬到fast_ssd之后索引还是在原来的表空间上。如果目标是让读写都走 SSD索引也得搬。批量生成索引迁移 SQL 的方式类似把pg_class.relkind换成i即可psql -d analytics -Atc \ SELECT ALTER INDEX ods. || quote_ident(relname) || SET TABLESPACE fast_ssd; FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname ods AND c.relkind i; | psql -d analytics这里我建议先把主键索引一起搬了因为主键索引通常是查询最热的索引。4. 一次完整的“跨 schema 指定表空间”迁移实录最后把这套东西串起来还原一下我这次的完整操作过程。源库是orders_db所有表都在publicschema表空间是pg_default。目标库是analytics要求对象进odsschema大部分表落到fast_ssd表空间少量归档类大表落到archive_hdd。4.1 迁移环境与前置准备先确认目标实例里有没有目标 schema 和表空间psql -d analytics -c \dn psql -d analytics -c SELECT spcname FROM pg_tablespace;没有就创建CREATE SCHEMA ods; -- 需要提前在操作系统层面准备好目录 CREATE TABLESPACE fast_ssd OWNER postgres LOCATION /var/lib/postgresql/tblspc_ssd; CREATE TABLESPACE archive_hdd OWNER postgres LOCATION /var/lib/postgresql/tblspc_hdd;然后给迁移用的账号授权GRANT CREATE ON TABLESPACE fast_ssd TO migrator; GRANT CREATE ON TABLESPACE archive_hdd TO migrator;如果账号对某个表空间没有 CREATE 权限恢复时会报permission denied for tablespace这个我已经替大家踩过了。4.2 执行过程与命令导出源库pg_dump -h 10.0.0.5 -U app_owner -d orders_db -Fc -f /backup/orders_20250601.dump查看 TOC确认 schema 和对象分布pg_restore -l /backup/orders_20250601.dump | head -30因为两端都是 PG17我选了--schema-rename方案。恢复命令pg_restore -d analytics \ -n public \ --schema-renamepublic:ods \ --tablespacefast_ssd \ -O -x \ --exit-on-error \ /backup/orders_20250601.dump跑完之后先验证 schema 和表空间psql -d analytics -c \dn psql -d analytics -c SELECT schemaname, tablename FROM pg_tables WHERE schemanameods; psql -d analytics -c SELECT c.relname, ts.spcname FROM pg_class c LEFT JOIN pg_tablespace ts ON ts.oid c.reltablespace WHERE c.relnamespace ods::regnamespace;确认大部分表都在fast_ssd后我再把几张明显是用来做历史归档的大表单独挪到archive_hddALTER TABLE ods.orders_2020 SET TABLESPACE archive_hdd; ALTER TABLE ods.orders_2021 SET TABLESPACE archive_hdd;4.3 现场踩到的两个坑第一个坑就是表空间权限。第一次恢复时报错信息我记得很清楚ERROR: permission denied for tablespace fast_ssd排查过程是这样的我看连接用户是有权限的但恢复仍然失败。后来意识到dump 里带了 owner 信息pg_restore 用SET SESSION AUTHORIZATION切换到了源库的 owner 角色目标实例上没有这个角色角色不存在时原本会报 role 不存在我加了-O之后虽然不会再切 owner但是普通连接用户对fast_ssd又没有 CREATE 权限。最终是通过给 migrator 账号GRANT CREATE ON TABLESPACE fast_ssd解决的。所以我的建议是跨环境恢复一开始就把-O -x加上然后查看当前恢复账号对所有目标表空间有没有 CREATE 权限。第二个坑是函数体内硬编码 schema 引用。这次 dump 里有几个自定义函数函数体里写的是public.calc_discount(order_id)。我原本想用 sed 方案做 schema 替换后来在替换前检查时发现这个函数调用会被误改成ods.calc_discount但那个函数定义本身也要进ods所以这次因为函数确实在迁移范围内运气好没出问题。但如果目标库里还要保留 public并且 public 下也有同名函数这种 sed 替换就会导致函数调用语义完全改变。后来我改用 PG17 的--schema-rename函数体的文本不会被碰风险就小多了。最后补一个收尾习惯恢复完成后我会用pg_restore -l的输出和pg_database里的实际对象做一次比对主要看对象数量是否一致顺便跑几个关键的查询语句做冒烟测试确认外键、序列、权限这些隐性依赖都没丢。逻辑导入导出这件事真正麻烦的从来不是命令本身而是你对 dump 里对象关系掌握得够不够清楚。如果你也有类似的跨 schema / 表空间导入需求动手前先把pg_restore -l的输出和用户权限列表确认一遍恢复现场会少很多手忙脚乱。这次踩完坑之后我至少不会再一上来就无脑pg_restore -d target db.dump了。