Not executing postgresql query on remote machine with ssh

Question

I am facing problem with executing postgresql queries on remote machine from local.

Shell script which I am running on local machine:

[postgres@local:~]$ cat main.sh
#!/bin/bash
REMOTE="[email protected]"
PKEY="/home/postgres/pr-key.ppk"
SCRIPT_LOCATION=/home/postgres/scripts
CRONLOG_LOCATION=/home/postgres/project/cronlogs
PGBIN=/usr/pgsql-9.6/bin
PGUSER=postgres
PGDATA=/opt/PostgreSQL/9.6/data
PGDATABASE=postgres
PGPORT=5432
PGHOST=192.168.1.200
BLOG=/home/postgres/blogfile
query=`ssh -i $PKEY $REMOTE $PGBIN/psql -U $PGUSER -h $PGHOST -p $PGPORT -d $PGDATABASE -Atc "select datname from pg_database where datname not in ('template0','template1');"`
echo $query

Execution:

[postgres@local:~]$ ./main.sh
bash: -c: line 0: syntax error near unexpected token `('
bash: -c: line 0: `/usr/pgsql-9.6/bin/psql -U postgres -h 192.168.1.200 -p 5432 -d postgres -Atc select datname from pg_database where datname not in ('template0','template1');'

query isn't quoted after interpretation, take a look on last line of output bash: -c:... -Atc select datname... — sKwa
– sKwa, Commented Jan 6, 2018 at 15:01
even if i quote, still i am facing problem. Kindly let me know if you have any suggestions. — Siva Krishna
– Siva Krishna, Commented Jan 6, 2018 at 16:11
Why are you assigning results to query and then expanding that variable unquoted? Consider running your code through shellcheck.net and fixing what it finds. — Charles Duffy
– Charles Duffy, Commented Jan 6, 2018 at 17:10
@SivaKrishna can you post fixed code&error(or is it same error)? can you run script in debug mode (with -x flag) and post last lines? why via ssh(postgres available only on local machine)? — sKwa
– sKwa, Commented Jan 6, 2018 at 18:18
@SivaKrishna try to enclose query with single quotes, i.e. -Atc '"select ...('template0','template1');"' — sKwa
– sKwa, Commented Jan 6, 2018 at 19:59

Charles Duffy · Accepted Answer · 2018-01-06 17:09:20Z

1

ssh allows you run a script file stored on the ssh client that runs on the remote machine as if it was actually on the remote machine, which makes the scripting easier. If you can get you progree.sql.sh file to work in the local context then

ssh user@hostname 'bash -s' < sql.script_on_this_machine.sh

I use keys so I don't have to worry about passwords here.

edited Jan 6, 2018 at 17:09

Charles Duffy

299k43 gold badges441 silver badges496 bronze badges

answered Jan 6, 2018 at 13:11

newbie

6388 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Siva Krishna Over a year ago

Thank you for quick reply, if i create sql file on remote machine and give input to ssh along with psql then this is working. as i have caveats, remote server is production. i should not create any kind of file . all Variable values mentioned on above script(sample) will change dynamically and i understood and did r&d i have only problem with single quotes(') and double quotes(""). is there any solution without creating file on remote server. Thanks in advance

Charles Duffy Over a year ago

@SivaKrishna, the code in this answer does not require the script to be remote. The suggestion is that instead of having the script run ssh -i ... itself, you use ssh to invoke an interpreter on the remote system, and feed it script text to execute on stdin.

Siva Krishna Over a year ago

@Charles, Thank you for suggestion. the code which i pasted above is Sample And script has ssh commands in side like above. As sKwa suggested code is working now. Thank you very much for spending your time.

sKwa · Accepted Answer · 2018-01-08 14:05:56Z

0

As I thought the problem in quotes and bash string's interpretation with quotes.

Try next:

...
QUERY='"select datname from pg_database where datname not in ('\'template0\'', '\'template1\'');"'
RESULT=$(ssh ... -Atc "${QUERY}")
echo "${RESULT}"
...

edited Jan 8, 2018 at 14:05

answered Jan 7, 2018 at 17:48

sKwa

8895 silver badges11 bronze badges

3 Comments

Siva Krishna Over a year ago

Thank you very much, this is perfectly working, i did small change, i.e RESULT=$(ssh ... -Atc ${QUERY}). with $"{QUERY}" it is not working means throwing error. Any how. Could you please give small info on this line QUERY='"select datname from pg_database where datname not in ('\'template0\'', '\'template1\'');"'. because i did understand with double quotes inside single quotes and '\' and \''. Once again thank you for providing solution.

Siva Krishna Over a year ago

i did not *, typo

sKwa Over a year ago

@SivaKrishna, typo should be "${QUERY}"(sorry), about quotes I will update answer later.

Collectives™ on Stack Overflow

Not executing postgresql query on remote machine with ssh

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related