How do you reference env vars from the remote machine when running ssh command locally?

Question

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 [email protected] "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?

Answer 1

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

ssh [email protected] 'stuff here...'

You then need to swap the internal quotes around:

ssh [email protected] '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 [email protected] '. .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

• What is the difference between the "...", '...', $'...', and $"..." quotes in the shell?
• Difference between single and double quotes in Bash

Answer 2

(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.