使用本地提供的列表从远程Postgresql数据库中删除记录 [英] Deleting records from a remote postgresql database using locally supplied list

问题描述

在工作的bash脚本中，我具有以下逻辑：

I have the following logic in a bash script that works:

 #copy records to delete file to respective servers.  faster than running locally
 scp -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null $dir/$server.records_to_delete.txt $server:/tmp/

 # trigger the deletion on remote machine.
 deletion_status=$(psql -U test testdb -h $server -c "CREATE TEMP TABLE tmp_cdr (id int); COPY tmp_widgets FROM '/tmp/$server.records_to_delete.txt'; DELETE FROM widgets USING tmp_cdr WHERE widgets.id = tmp_widgets.id;")

但我试图将其合并为一个命令。因此，我不想将记录列表删除并删除到远程服务器，而是希望将其保留在本地，并以某种方式使用本地文件创建tmp_widgets。

But I'm trying to consolidate into one command. So instead of scp'ing the list of records to delete over to the remote server, I'd like to keep it local and just somehow create tmp_widgets using the local file.

任何建议将不胜感激

推荐答案

您无需将文件复制到服务器。您正在寻找的是 STDIN ，用于 COPY ：

You don't need to copy the file to the server. What you're looking for is the STDIN for COPY:

cat records_to_delete.txt | psql $server -c "COPY tmp_widgets FROM STDIN;"

这篇关于使用本地提供的列表从远程Postgresql数据库中删除记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！