如何将远程PostgreSQL服务器视图数据复制到本地服务器新表

以下是几种实用方案，可根据环境和需求选择：

方案一：通过psql管道直接传输（高效全量复制） #

无需生成中间文件，直接通过终端管道完成数据传输，适合大数据量全量复制场景。

步骤1：同步本地目标表结构 #

从远程视图同步表结构到本地：

PGPASSWORD=passA psql -h hostA -p 5432 -U userA -d dbA -c "SELECT * FROM schemaA.target_view LIMIT 0" | PGPASSWORD=passB psql -h hostB -p 5432 -U userB -d dbB -c "CREATE TABLE schemaB.target_table AS TABLE schemaA.target_view WITH NO DATA"

步骤2：复制视图数据到本地表 #

执行管道命令完成数据传输：

PGPASSWORD=passA psql -h hostA -p 5432 -U userA -d dbA -c "COPY schemaA.target_view TO STDOUT" | PGPASSWORD=passB psql -h hostB -p 5432 -U userB -d dbB -c "COPY schemaB.target_table FROM STDIN"

说明：PGPASSWORD用于避免密码交互，生产环境建议用~/.pgpass文件配置密码，更安全。

方案二：使用dblink在本地数据库远程查询插入 #

适合需要在本地执行数据过滤、转换后再插入的场景，无需切换终端操作。

步骤1：安装dblink扩展（本地数据库） #

-- 登录本地数据库dbB执行
CREATE EXTENSION IF NOT EXISTS dblink;

步骤2：创建表并复制数据（二选一） #

• 直接创建表并导入数据：

CREATE TABLE schemaB.target_table AS
SELECT * FROM dblink(
  'host=hostA port=5432 dbname=dbA user=userA password=passA',
  'SELECT * FROM schemaA.target_view'
) AS t(
  -- 替换为远程视图实际列定义，示例：
  id INT,
  name VARCHAR(100),
  create_time TIMESTAMP
);

• 先建空表再插入数据：

-- 创建空表
CREATE TABLE schemaB.target_table AS
SELECT * FROM dblink(
  'host=hostA port=5432 dbname=dbA user=userA password=passA',
  'SELECT * FROM schemaA.target_view LIMIT 0'
) AS t(id INT, name VARCHAR(100), create_time TIMESTAMP);

-- 插入数据
INSERT INTO schemaB.target_table
SELECT * FROM dblink(
  'host=hostA port=5432 dbname=dbA user=userA password=passA',
  'SELECT * FROM schemaA.target_view'
) AS t(id INT, name VARCHAR(100), create_time TIMESTAMP);

方案三：使用pg_dump导出再导入（需保留备份文件场景） #

如果需要留存数据备份文件，可采用导出+导入的方式。

步骤1：导出远程视图的结构和数据 #

• 导出结构：

PGPASSWORD=passA pg_dump -h hostA -p 5432 -U userA -d dbA -t schemaA.target_view --schema-only > view_schema.sql

• 导出数据：

PGPASSWORD=passA pg_dump -h hostA -p 5432 -U userA -d dbA -t schemaA.target_view --data-only > view_data.sql

步骤2：导入到本地数据库 #

• 先导入结构：

PGPASSWORD=passB psql -h hostB -p 5432 -U userB -d dbB -f view_schema.sql

• 再导入数据：

PGPASSWORD=passB psql -h hostB -p 5432 -U userB -d dbB -f view_data.sql

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