如何在PowerShell中用管道执行两个带DB URI的psql命令迁移数据？

问题描述 #

希望通过PowerShell脚本调用psql，以单次管道调用的方式将记录从一个数据库迁移到另一个不同位置的数据库（源表与目标表结构一致）。本地同库环境下的管道方式已可行，但因两个数据库密码不同，尝试用PostgreSQL的DB URI定义连接时遇到语法问题，现有代码如下：

本地可行代码（同密码环境）：

$env:PGPASSWORD = 'local_pass';    
# execute 2 queries in sequence 
$res = & $psqlPath -U 'user' -d 'localMasterDB' -h 'local' `
               -c "\copy (SELECT * FROM target_table WHERE id IN ( $idsToMove)) TO stdout" `
       | & $psqlPath -U 'postgres' -d 'localReplicaDB' -h 'local' `
               -c 'COPY target_table FROM stdin' 

尝试的DB URI版本（存在语法错误）：

$res =  "\copy (SELECT * FROM target_table WHERE id IN ( $idsToMove)) TO stdout" `
         | & $psqlPath "postgresql://user:local_pass@master_db:5432/localMasterDB" `
         | 'COPY target_table FROM stdin' `  # <== 此处应为第二个psql命令的起始
         | & $psqlPath -U 'postgres' -d 'remote_db'

核心疑问：

1. 是否可以通过管道执行两个使用DB URI的psql命令？
2. 还有哪些类似的方法可以通过psql实现数据迁移？

解答 #

1. 可以用管道执行两个DB URI的psql命令（修正后代码） #

你的尝试方向是对的，但语法有误：SQL命令需要作为psql的-c参数传入，不能直接作为管道内容。正确的PowerShell代码应该是让第一个psql通过DB URI连接源库，执行导出的\copy命令并输出到stdout，再将stdout管道给第二个psql（通过DB URI或普通参数连接目标库）执行导入的COPY命令。

示例代码：

# 定义源库和目标库的DB URI
$sourceUri = "postgresql://user:local_pass@master_db:5432/localMasterDB"
$targetUri = "postgresql://postgres:remote_pass@remote_db_host:5432/remote_db"

# 管道执行导出+导入
$res = & $psqlPath $sourceUri -c "\copy (SELECT * FROM target_table WHERE id IN ($idsToMove)) TO stdout" `
       | & $psqlPath $targetUri -c "COPY target_table FROM stdin"

如果目标库不想在URI里明文写密码，也可以混合使用URI和环境变量：

$env:PGPASSWORD = 'remote_pass'
$sourceUri = "postgresql://user:local_pass@master_db:5432/localMasterDB"

$res = & $psqlPath $sourceUri -c "\copy (SELECT * FROM target_table WHERE id IN ($idsToMove)) TO stdout" `
       | & $psqlPath -U 'postgres' -d 'remote_db' -h 'remote_db_host' -c "COPY target_table FROM stdin"

2. 其他psql实现数据迁移的方法 #

除了stdout管道的方式，还有以下几种常用方案：

• 使用pg_dump + pg_restore
  如果是迁移整表或批量数据，pg_dump更适合（支持结构+数据导出，可压缩），配合pg_restore导入：

  # 导出指定数据
  & $pgDumpPath -U 'user' -h 'master_db' -d 'localMasterDB' `
    -t target_table -c "WHERE id IN ($idsToMove)" -f - `
    | & $pgRestorePath -U 'postgres' -h 'remote_db_host' -d 'remote_db' -
  

  优势：支持复杂表结构、索引，可增量迁移，适合大规模数据。

• \copy结合临时文件
  如果管道传输有问题（比如网络不稳定），可以先将数据导出到临时文件，再导入：

  $tempFile = "C:\temp\migrate_data.csv"
  # 导出到临时文件
  & $psqlPath $sourceUri -c "\copy (SELECT * FROM target_table WHERE id IN ($idsToMove)) TO '$tempFile' WITH CSV"
  # 导入到目标库
  & $psqlPath $targetUri -c "\copy target_table FROM '$tempFile' WITH CSV"
  # 删除临时文件
  Remove-Item $tempFile
  

• 使用dblink跨库直接插入
  无需导出到本地，直接在目标库通过dblink连接源库查询并插入：

  1. 先在目标库安装dblink扩展：

     CREATE EXTENSION IF NOT EXISTS dblink;
     

  2. PowerShell调用psql执行跨库插入：

     $insertQuery = @"
     INSERT INTO target_table
     SELECT * FROM dblink('$sourceUri', 'SELECT * FROM target_table WHERE id IN ($idsToMove)')
     AS t(col1, col2, col3); -- 需匹配表结构的列名
     "@
     & $psqlPath $targetUri -c $insertQuery
     

  优势：全程在数据库层面操作，无需本地存储数据，适合小批量数据迁移。

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