PostgreSQL如何从外部服务器迁移所有枚举类型

核心原因是PostgreSQL外部表要求源库和目标库的同名字段枚举类型的OID、枚举值顺序完全匹配，否则创建外部表时会抛出SQL错误。你可以通过以下两种方式批量导出源库的所有枚举类型，先同步到目标库再进行后续的 schema 迁移操作。

方式1：使用pg_dump过滤导出枚举定义 #

pg_dump 没有专门导出枚举类型的参数，但可以通过导出全量 schema 结构后过滤枚举相关DDL的方式实现，命令如下：

pg_dump -h [源库地址] -U [用户名] -d [源库名] --schema-only | grep -A 1 -B 1 'ENUM' > enums.sql

如果需要只导出指定schema的枚举，可以给pg_dump加上-n [schema名]参数缩小范围。

方式2：查询系统表生成精准的枚举DDL #

如果担心过滤方式遗漏枚举的权限设置、枚举值顺序问题，可以直接在源库执行如下SQL，自动生成所有枚举类型的创建语句：

WITH enum_types AS (
    SELECT 
        t.oid,
        n.nspname AS schema_name,
        t.typname AS enum_name
    FROM pg_type t
    JOIN pg_namespace n ON t.typnamespace = n.oid
    WHERE t.typtype = 'e'
    -- 取消注释并替换为目标schema可过滤指定schema的枚举
    -- AND n.nspname = 'your_schema_name'
)
SELECT 
    'CREATE TYPE ' || quote_ident(schema_name) || '.' || quote_ident(enum_name) || ' AS ENUM (' ||
    string_agg(quote_literal(e.enumlabel), ', ' ORDER BY e.enumsortorder) || ');' AS enum_ddl
FROM enum_types t
JOIN pg_enum e ON t.oid = e.enumtypid
GROUP BY t.oid, schema_name, enum_name
UNION ALL
-- 可选：生成枚举类型的权限授权语句
SELECT 
    'GRANT USAGE ON TYPE ' || quote_ident(n.nspname) || '.' || quote_ident(t.typname) || 
    ' TO ' || quote_ident(r.rolname) || ';'
FROM pg_type t
JOIN pg_namespace n ON t.typnamespace = n.oid
JOIN pg_acl acl ON acl.aclowner = t.typowner
JOIN pg_roles r ON acl.acl_grantee = r.oid
WHERE t.typtype = 'e' AND acl.acl_grantee <> 0;

将上述SQL的执行结果保存为enums.sql文件即可。

导入到目标库 #

1. 先在目标库创建对应schema（如果不存在）：

CREATE SCHEMA IF NOT EXISTS [你的schema名];

2. 执行枚举SQL文件导入所有枚举类型：

psql -h [目标库地址] -U [用户名] -d [目标库名] -f enums.sql

后续操作 #

枚举类型完全同步到目标库后，再创建外部表或导入完整schema的数据，即可避免枚举类型不匹配的报错。
注意：如果后续源库的枚举值有新增/调整，需要先同步目标库的枚举定义，再同步对应数据。

内容的提问来源于stack exchange，提问作者Peter Mølgaard Pallesen