如何无需本地中转直接在PostgreSQL数据库间复制数据？

有几种靠谱的方法能实现PostgreSQL数据库之间的直接数据复制，不用让数据经过本地机器中转：

1. 用dblink在目标库远程拉取数据 #

dblink是PostgreSQL官方扩展，能让一个数据库直接连接到另一个远程库执行操作。

操作步骤： #

• 先在目标库开启dblink扩展：

  CREATE EXTENSION IF NOT EXISTS dblink;
  

• 接着通过dblink连接源库，直接把数据导入。比如复制整张表：

  INSERT INTO target_table
  SELECT * FROM dblink(
    'host=源服务器IP port=5432 dbname=source_db user=postgres password=你的密码',
    'SELECT * FROM source_table'
  ) AS t(列1 类型1, 列2 类型2, ...);
  

• 如果要复制整个库的结构加数据，可以在目标服务器上通过dblink直接执行跨库管道命令——数据完全在源和目标服务器之间传输，本地根本碰不到：

  SELECT dblink_exec(
    'host=目标服务器IP port=5432 dbname=target_db user=postgres password=你的密码',
    'pg_dump -U postgres -h 源服务器IP source_db | psql -U postgres target_db'
  );
  

2. 用pg_basebackup复制整个集群（主从场景适用） #

要是需要复制整个数据库集群（不是单个库），pg_basebackup能直接从源集群把数据拷到目标集群，全程在服务器之间传输：

# 在目标服务器上运行这条命令
pg_basebackup -h 源服务器IP -U postgres -D /var/lib/postgresql/15/main -P -X stream

注意：这会覆盖目标集群的数据目录，适合搭建从库的场景，不适合单个库的复制。

3. 单表复制：用psql的\copy在目标服务器执行 #

只复制单表的话，直接在目标服务器上跑命令，让源库把数据输出到目标库，本地仅发起命令不中转数据：

# 目标服务器上执行
psql -h 源服务器IP -U postgres -d source_db -c "COPY source_table TO STDOUT" | psql -h localhost -U postgres -d target_db -c "COPY target_table FROM STDIN"

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