如何跨PostgreSQL数据库复制表结构而不复制数据

你原本的CREATE TABLE new_table AS TABLE existing_table WITH NO DATA;只能在同一个PostgreSQL数据库实例内使用，因为PostgreSQL的不同数据库之间是完全隔离的，无法直接跨库执行查询或表创建操作。下面是几种可行的跨库复制表结构的方法：

方案1：使用pg_dump导出结构再导入（推荐） #

这是最可靠的方式，能完整复制表的所有结构细节（包括主键、索引、约束、默认值、字段注释等）：

• 第一步：在可访问生产库的环境中，导出目标表的纯结构：

  pg_dump -h 生产库IP/主机名 -U 生产库用户名 -d 生产库名称 -t existing_table --schema-only > table_schema.sql
  

  参数说明：
  • -t existing_table：指定要导出的表
  • --schema-only：仅导出表结构，不包含任何数据
• 第二步：将生成的table_schema.sql文件传输到本地环境，然后导入到本地数据库：

  psql -h 本地库IP/主机名 -U 本地库用户名 -d 本地库名称 -f table_schema.sql
  

• 如果需要将表重命名为new_table，可以在导入后执行：

  ALTER TABLE existing_table RENAME TO new_table;
  

  或者直接编辑table_schema.sql文件，把所有existing_table替换为new_table后再导入。

方案2：生成CREATE TABLE语句手动复制 #

通过PostgreSQL内置函数直接生成目标表的创建语句，然后在本地库执行：

• 在生产库的SQL客户端中执行：

  SELECT pg_get_tabledef('public.existing_table');
  

  （如果表不在public schema，替换成对应的schema名称）
• 该查询会返回完整的CREATE TABLE语句，复制这个语句，将其中的表名修改为new_table，然后在本地数据库中执行即可。

方案3：使用dblink跨库直接创建（仅复制列结构） #

如果你的本地库允许连接到生产库，可以使用dblink扩展实现跨库操作，但这种方式仅能复制列的基本结构和数据类型，不会复制主键、索引、约束等额外对象：

• 第一步：在本地数据库中安装dblink扩展（如果未安装）：

  CREATE EXTENSION IF NOT EXISTS dblink;
  

• 第二步：执行跨库创建表的语句：

  CREATE TABLE new_table AS
  SELECT *
  FROM dblink(
    'host=生产库IP port=5432 dbname=生产库名 user=生产库用户名 password=生产库密码',
    'SELECT * FROM existing_table LIMIT 0'
  ) AS remote_table;
  

  其中LIMIT 0确保只获取表结构而不读取任何数据。

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