0

I want to execute a command on a remote server (Cent OS 7) and use the environment variables defined on that server. However, when I run this command

ssh myuser@remotehost.com "PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c 'COPY myapp_currencyprice to STDOUT WITH (DELIMITER \",\", FORMAT CSV, HEADER)' > /tmp/prices.csv"

it seems the command is trying to use "$DB_PASS" and other env vars from the localhost instead of from "remotehost.com". What's the proper way to reference env vars from the remote server?

Dave
  • 2,548
  • Try to escape the $-signs to prevent local expansion. – RudiC Dec 11 '21 at 20:29
  • @RudiC that's an answer: please post it as an answer and not a comment. Posting answers in comments is actively harmful since people cannot vote on them, so it circumvents the normal quality control processes of the site, and they can also discourage others from posting the solution as a proper answer meaning the question can remain unanswered for ever. – terdon Dec 11 '21 at 21:02
  • @roaima see above. – terdon Dec 11 '21 at 21:03
  • @RudiC, Tried '"PGPASSWORD=$DB_PASS' but this didn't work. – Dave Dec 11 '21 at 21:12
  • @roaima, can you post the command that you mean? The issue is I already have single quotes in the expression itself. I tried to escape them with "'" but that didn't work. – Dave Dec 11 '21 at 21:16
  • @Dave "Did not work", in what particular way? How do you expect these variables be set on the remote host? – Kusalananda Dec 11 '21 at 21:16
  • On the remote host, they are loaded through ~/.bash_profile (e.g. export MY_VAR=value) but I am open to loading them a different way on the remote host if it accommodates an answer. – Dave Dec 11 '21 at 21:45

2 Answers2

2

You need to use single quotes to stop the local shell getting at the string:

ssh myuser@remotehost.com 'stuff here...'

You then need to swap the internal quotes around:

ssh myuser@remotehost.com 'PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c "COPY myapp_currencyprice to STDOUT WITH (DELIMITER \",\", FORMAT CSV, HEADER)" > /tmp/prices.csv'

However, this still won't work because your .bash_profile won't be included as you aren't using an interactive shell. At this point the most obvious solution is to include it explicitly:

ssh myuser@remotehost.com '. .bash_profile; PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -c "COPY myapp_currencyprice to STDOUT WITH (DELIMITER \",\", FORMAT CSV, HEADER)" > /tmp/prices.csv'

You may want to look up these two references to understand the difference between the different types of quotes

Chris Davies
  • 116,213
  • 16
  • 160
  • 287
0

(Comment converted to an answer, as proposed by terdon...)

Try to escape the $-signs to prevent local expansion. Like

"PGPASSWORD=\$DB_PASS psql -U \$DB_USER -d $DB_NAME ..."

What exactly did not work, as alluded to in your comment? Did you also enclose the command in single quotes? Don't, select either.

RudiC
  • 8,969