PostgreSQL如何一次性生成含依赖关系的所有对象脚本

方法一：利用pg_dump生成标准有序脚本 #

pg_dump本身会自动处理对象的依赖顺序，你之前备份失败大概率是参数没选对。用以下命令生成带删除+创建逻辑的纯schema脚本：

# 替换<schema名>和<数据库名>为实际值
pg_dump -s -c --if-exists -n <schema名> -f schema_rebuild.sql <数据库名>

参数说明：

• -s: 只导出schema结构，不含数据
• -c: 在创建对象前生成DROP语句
• --if-exists: 避免删除不存在的对象时报错
• -n: 指定要导出的schema（如果是public可以省略）

生成的脚本会自动按被依赖对象优先创建的顺序执行：先建基础表，再建依赖表的视图、函数/存储过程；删除时则反过来，先删依赖者，再删被依赖对象，不会出现外键或依赖报错。

方法二：自定义递归查询生成脚本（灵活控制范围） #

如果pg_dump的输出不符合你的定制需求，可以直接查询PostgreSQL系统视图，手动生成按依赖顺序排列的DROP/CREATE语句。

生成删除+创建语句的SQL查询 #

WITH RECURSIVE object_hierarchy AS (
    -- 初始化：获取所有表、视图
    SELECT
        c.oid AS obj_oid,
        c.relname AS obj_name,
        CASE c.relkind
            WHEN 'r' THEN 'TABLE'
            WHEN 'v' THEN 'VIEW'
        END AS obj_type,
        0 AS dependency_level
    FROM pg_class c
    JOIN pg_namespace ns ON c.relnamespace = ns.oid
    WHERE ns.nspname = '<你的schema名>' -- 替换为实际schema
        AND c.relkind IN ('r', 'v')

    UNION ALL

    -- 递归获取依赖的函数/存储过程
    SELECT
        p.oid AS obj_oid,
        p.proname AS obj_name,
        CASE p.prokind
            WHEN 'f' THEN 'FUNCTION'
            WHEN 'p' THEN 'PROCEDURE'
        END AS obj_type,
        oh.dependency_level + 1 AS dependency_level
    FROM pg_depend d
    JOIN object_hierarchy oh ON d.refobjid = oh.obj_oid
    JOIN pg_proc p ON d.objid = p.oid
    JOIN pg_namespace ns ON p.pronamespace = ns.oid
    WHERE ns.nspname = '<你的schema名>'
        AND p.prokind IN ('f', 'p')
)
-- 生成DROP语句（按依赖层级从高到低：先删依赖者）
SELECT 'DROP ' || obj_type || ' IF EXISTS ' ||
       CASE obj_type
           WHEN 'FUNCTION' THEN obj_name || '(' || pg_get_function_arguments(obj_oid) || ')'
           WHEN 'PROCEDURE' THEN obj_name || '(' || pg_get_function_arguments(obj_oid) || ')'
           ELSE obj_name
       END || ' CASCADE;' AS drop_statement
FROM object_hierarchy
ORDER BY dependency_level DESC, obj_type

UNION ALL

-- 生成CREATE语句（按依赖层级从低到高：先建被依赖对象）
SELECT
    CASE obj_type
        WHEN 'TABLE' THEN pg_get_create_table(obj_oid) || ';'
        WHEN 'VIEW' THEN 'CREATE VIEW ' || obj_name || ' AS ' || pg_get_viewdef(obj_oid) || ';'
        WHEN 'FUNCTION' THEN pg_get_functiondef(obj_oid) || ';'
        WHEN 'PROCEDURE' THEN pg_get_functiondef(obj_oid) || ';'
    END AS create_statement
FROM object_hierarchy
ORDER BY dependency_level, obj_type;

使用说明 #

1. 替换<你的schema名>为实际的schema（比如public）
2. 执行查询后，将结果中的语句复制出来，保存为.sql脚本
3. 执行脚本时，会先删除所有指定对象（带CASCADE自动处理依赖），再按正确顺序重建

关键注意事项 #

• 所有操作前务必备份原始数据库，避免不可逆损失
• 若涉及权限、序列等对象，可在pg_dump中添加-x参数排除权限，或在递归查询中加入对应的系统表（比如pg_sequence）
• 存储过程在PostgreSQL中属于PROCEDURE类型，上述查询已自动区分函数和存储过程

内容的提问来源于stack exchange，提问作者user21707082