PostgreSQL跨库跨机器数据插入迁移方案及脚本咨询

嘿，刚好对PostgreSQL跨库/跨机器数据转移这块熟得很，来给你拆解几种实用方案，完全能对标你提到的SQL Linked Servers需求～

同机器里跨库操作，主要有两种常用方式：

1. 用dblink扩展实时插入 #

PostgreSQL本身没有原生跨库查询能力，dblink就是官方推荐的扩展工具，能直接在一个库中查询另一个库的数据并插入：

• 先安装扩展（只需执行一次）：

CREATE EXTENSION IF NOT EXISTS dblink;

• 跨库插入示例：从db1的public.table1把数据插入到db2的public.table2

INSERT INTO db2.public.table2 (col1, col2, col3)
SELECT col1, col2, col3
FROM dblink(
    'dbname=db1 user=your_username password=your_pass host=localhost',
    'SELECT col1, col2, col3 FROM public.table1'
) AS t(col1 INT, col2 VARCHAR(50), col3 DATE);

小提示：为了避免明文密码暴露，建议把连接信息存在服务器的~/.pgpass文件里，这样dblink会自动读取，更安全。

2. 用pg_dump+psql批量迁移脚本 #

如果是批量转移大量数据，用导出+导入的脚本会更高效，还能做成自动化脚本供应用调用：

#!/bin/bash
# 导出源库指定表的纯数据
pg_dump -d db1 -t public.table1 --data-only > /tmp/table1_temp_data.sql
# 导入到目标库
psql -d db2 -f /tmp/table1_temp_data.sql
# 清理临时文件
rm /tmp/table1_temp_data.sql

跨机器的场景，除了上面的方法适配远程地址，还有专门的同步方案：

1. dblink跨机器直接插入 #

只需要在dblink的连接串里指定远程机器的IP、端口即可，前提是远程PostgreSQL已经开启远程访问（修改pg_hba.conf允许你的机器连接，postgresql.conf设置listen_addresses = '*'）：

INSERT INTO local_db.public.table2 (col1, col2)
SELECT col1, col2
FROM dblink(
    'dbname=remote_db user=remote_user password=remote_pass host=192.168.1.100 port=5432',
    'SELECT col1, col2 FROM public.table1'
) AS t(col1 INT, col2 VARCHAR(50));

2. pg_dump+scp批量迁移脚本 #

适合一次性迁移大量数据，脚本可以直接被应用调用：

#!/bin/bash
# 参数说明：远程主机IP 远程库名 源表名 本地库名 目标表名
REMOTE_HOST=$1
REMOTE_DB=$2
SOURCE_TABLE=$3
LOCAL_DB=$4
TARGET_TABLE=$5

# 直接从远程导出数据并导入到本地目标表（无需临时文件）
pg_dump -h $REMOTE_HOST -d $REMOTE_DB -t $SOURCE_TABLE --data-only | psql -d $LOCAL_DB -c "INSERT INTO $TARGET_TABLE SELECT * FROM stdin;"

3. pglogical逻辑复制（实时同步） #

如果需要像SQL Server复制那样的实时数据同步，pglogical是绝佳选择，支持双向同步、增量同步：

• 主库和从库都安装扩展：

CREATE EXTENSION IF NOT EXISTS pglogical;

• 主库创建节点：

SELECT pglogical.create_node(node_name := 'master_node', dsn := 'dbname=remote_db host=192.168.1.100');

• 从库创建节点并订阅主库：

SELECT pglogical.create_node(node_name := 'local_node', dsn := 'dbname=local_db');
SELECT pglogical.create_replication_set('my_sync_set', add_tables := ARRAY['public.table1']);
SELECT pglogical.create_subscription(
    subscription_name := 'sync_from_master',
    provider_dsn := 'dbname=remote_db host=192.168.1.100',
    replication_sets := ARRAY['my_sync_set']
);

不管是SQL层面还是脚本层面，都可以封装成可调用的工具：

1. 封装dblink为SQL函数 #

把跨库插入逻辑封装成PL/pgSQL函数，应用直接调用即可：

CREATE OR REPLACE FUNCTION copy_from_remote(
    remote_dsn TEXT,
    source_table TEXT,
    target_table TEXT
) RETURNS VOID AS $$
DECLARE
    col_defs TEXT;
BEGIN
    -- 自动获取源表的字段定义，避免手动写列名
    SELECT string_agg(column_name || ' ' || data_type, ', ')
    INTO col_defs
    FROM information_schema.columns
    WHERE table_name = source_table AND table_schema = 'public';

    -- 动态执行插入语句
    EXECUTE format(
        'INSERT INTO %I SELECT * FROM dblink(%L, ''SELECT * FROM %I'') AS t(%s)',
        target_table,
        remote_dsn,
        source_table,
        col_defs
    );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

调用示例：

SELECT copy_from_remote(
    'dbname=remote_db host=192.168.1.100 user=remote_user',
    'table1',
    'table2'
);

注意：SECURITY DEFINER会以函数创建者的权限执行，要谨慎使用，避免权限泄露。

2. 参数化Shell脚本 #

前面提到的批量迁移脚本已经是参数化的，应用可以直接通过命令行传入参数调用，比如：

./sync_data.sh 192.168.1.100 remote_db table1 local_db table2

PostgreSQL没有完全和SQL Linked Servers一模一样的功能，但**Foreign Data Wrapper（FDW）**是最接近的方案，能把远程数据库映射成“链接服务器”，直接操作远程表：

• 安装postgres_fdw扩展：

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

• 创建远程服务器对象（对应Linked Server）：

CREATE SERVER remote_server 
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '192.168.1.100', dbname 'remote_db', port '5432');

• 创建用户映射（对应Linked Server的登录映射）：

CREATE USER MAPPING FOR your_local_user 
SERVER remote_server 
OPTIONS (user 'remote_user', password 'remote_pass');

• 导入远程表为本地外部表：

IMPORT FOREIGN SCHEMA public 
LIMIT TO (table1) 
FROM SERVER remote_server INTO public;

之后你就可以像操作本地表一样操作table1，比如直接插入：

INSERT INTO local_table SELECT * FROM table1;

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