如何通过SQL查询无数据克隆Postgres Schema？无法使用pg_dump

如果你没法用pg_dump，完全可以通过Postgres内置的系统函数和SQL查询来生成Schema的结构脚本，进而完成跨库克隆。下面是具体实现和流程：

一、生成源Schema的完整结构DDL（无数据） #

针对public Schema，我们可以用Postgres提供的pg_get_*def系列函数（这也是pg_dump底层用来生成DDL的核心工具），分别导出各类对象的定义：

1. 表（含约束、索引） #

SELECT pg_get_tabledef(quote_ident(schemaname) || '.' || quote_ident(tablename)) || ';'
FROM pg_tables
WHERE schemaname = 'public';

2. 视图 #

SELECT 'CREATE VIEW ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' AS ' || pg_get_viewdef(quote_ident(schemaname) || '.' || quote_ident(viewname)) || ';'
FROM pg_views
WHERE schemaname = 'public';

3. 函数/存储过程 #

SELECT pg_get_functiondef(oid) || ';'
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

4. 序列 #

SELECT pg_get_tabledef(quote_ident(nspname) || '.' || quote_ident(relname)) || ';'
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'S' AND n.nspname = 'public';

5. 自定义类型（枚举、复合） #

枚举类型：

SELECT 'CREATE TYPE ' || quote_ident(nspname) || '.' || quote_ident(typname) || ' AS ENUM (' || array_to_string(enumvals, ', ') || ');'
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE n.nspname = 'public';

复合类型：

SELECT pg_get_type_def(oid) || ';'
FROM pg_type
WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') AND typtype = 'c';

6. 一键导出所有DDL #

把上面的查询合并成一个，直接得到完整的Schema脚本：

-- 表结构
SELECT pg_get_tabledef(quote_ident(schemaname) || '.' || quote_ident(tablename)) || ';' AS ddl
FROM pg_tables
WHERE schemaname = 'public'

UNION ALL

-- 视图
SELECT 'CREATE VIEW ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' AS ' || pg_get_viewdef(quote_ident(schemaname) || '.' || quote_ident(viewname)) || ';' AS ddl
FROM pg_views
WHERE schemaname = 'public'

UNION ALL

-- 函数
SELECT pg_get_functiondef(oid) || ';' AS ddl
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')

UNION ALL

-- 序列
SELECT pg_get_tabledef(quote_ident(nspname) || '.' || quote_ident(relname)) || ';' AS ddl
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'S' AND n.nspname = 'public'

UNION ALL

-- 枚举类型
SELECT 'CREATE TYPE ' || quote_ident(nspname) || '.' || quote_ident(typname) || ' AS ENUM (' || array_to_string(enumvals, ', ') || ');' AS ddl
FROM pg_enum e
JOIN pg_type t ON e.enumtypid = t.oid
JOIN pg_namespace n ON t.typnamespace = n.oid
WHERE n.nspname = 'public'

UNION ALL

-- 复合类型
SELECT pg_get_type_def(oid) || ';' AS ddl
FROM pg_type
WHERE typnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') AND typtype = 'c';

运行这个查询后，把结果里的ddl列内容全部复制出来，就是你需要的无数据Schema脚本。

二、完整克隆流程 #

1. 连接源库：用你的SQL编辑器（psql、DBeaver、Navicat等）连接到待克隆的数据库。
2. 导出Schema脚本：运行上面的整合查询，把所有DDL语句复制到文本文件或者直接保存结果。
3. 连接目标库：切换编辑器的连接到目标数据库（确保目标库已创建，且你有CREATE对象的权限）。
4. 执行脚本：把复制的DDL语句在目标库中执行，完成Schema结构的克隆。

三、关于SQL模拟pg_dump导出Schema的问题 #

pg_dump是客户端命令行工具，没法直接通过SQL调用，但它底层就是用Postgres的pg_get_*def系列函数来生成Schema DDL的。所以上面的SQL查询完全等价于pg_dump导出Schema的逻辑，你可以直接在SQL编辑器中运行这些查询，不需要依赖pg_dump工具。

补充：特殊对象的处理 #

如果你的Schema里还有触发器、规则或者权限设置，需要额外添加对应的查询：

• 触发器：

  SELECT pg_get_triggerdef(oid) || ';'
  FROM pg_trigger
  WHERE tgnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') AND NOT tgisinternal;
  

• 表权限：

  SELECT 'GRANT ' || privilege_type || ' ON ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' TO ' || quote_ident(grantee) || ';'
  FROM information_schema.table_privileges
  WHERE schemaname = 'public';
  

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