How to save a postgres query to a file from a remote computer

Question

I have a script which outputs some data from a postgres database:

#!/bin/bash

dbname="inventory"
username="postgres"
NOW=$(date +"%d-%m-%Y-%H%M%S")
OUTPUT="output.$NOW"

psql $dbname $username << EOF
\o | cat >> $OUTPUT
select count(*) from component;
select * from product where procode='AAA';
\q
\o
EOF

This happily exports the data I need to a text file if I run it on the local machine.

Now I want to do the same thing for 10 remote computers and have the output stored in one file on the machine I run the script from. I have the following code that will grep some information from the remote machines but I also need to add the postgres data above to my $FILE_OUT below:

#!/bin/bash

NOW=$(date +"%d-%m-%Y-%H%M%S")
dbname="inventory"
username="postgres"

echo "Enter the file name"
read FILE_IN
FILE_OUT="invcheck.$NOW"

for i in $(cat $FILE_IN); do
echo $i >> $FILE_OUT

ssh user@$i "ps -efw | grep TRADINGTIMEPLUS /opt/jrms/jrms.ini" >> $FILE_OUT

I have tried to put the postgres code in a line like:

ssh rmsasi@hj$i "psql $dbname $username << EOF \o | cat >> $FILE_OUT select * from component where code='1000';\q;\o; EOF"

but I get errors and the postgres data is not included in the $FILE_OUT

psql: warning: extra option o ignored
cat: from: No such file or directory
cat: component: No such file or directory
cat: where: No such file or directory
cat: code=1000: No such file or directory
bash: line 1: q: command not found
bash: line 1: o: command not found
bash: line 1: EOF: command not found

I am only new to scripting so hopefully I am just missing something simple.

My thanks in advance and if there is a better way to do what I am trying to achieve I appreciate any pointers.

Answer 1

psql's \ commands are terminated by newlines, not semicolons. A bit of a wart IMO.

Because of that you need newlines in your command. Literal newlines are permissible within a quoted literal in the shell, so you could insert them in the command, but it's better to just have your here-document locally and send it to psql's stdin via ssh's stdin. This'll still leave the output on the remote machine, though:

ssh rmsasi@hj$i "psql $dbname $username" << __EOF__
 \o | cat >> $FILE_OUT
SELECT * from component where code='1000';
\q
\o
__EOF__

It's much better to instead read from psql's stdout or redirect it, rather than using \o commands; that way the output comes back down the ssh session and you can redirect it locally:

echo "SELECT * from component where code='1000';" |\
ssh rmsasi@hj$i >> $FILE_OUT \
  "psql --quiet --no-align --no-readline --tuples-only -P footer=off --no-password $dbname $username"

Alternately you could have psql connect over the network from the other machines.

Since you're driving psql from bash you may want to know that you can do so interactively with a co-process. You also don't need to redirect output to a tempfile like that; just suppress psql's printing of prompts and headers (see linked answer) and you can simply read the results from psql's stdout.