CODEWITHSUNDEEP
bashpsql
aPugLife
aPugLife

Reputation: 1059

Run postgres query from another server, in bash

I have a Ubuntu server that processes documents and another that have the database (postgresql 9.3).

I ran psql -? to understand how to connect to another DB and the final command would be:

psql -U postgres -W -h 1.2.3.4 -d testdb -p 5432

It works, but I must type the password after the command is issued.

I was trying to adapt this command in a bash script:

#!/bin/bash
psql -U postgres -W mypassword -h 1.2.3.4 -d testdb -p 5432 << EOF
select * from mytable;
\q
EOF

Needless to say this is not the right command. Also, the password does not get recognized as a password, reporting the error:

psql: warning: extra command-line argument "mypassword" ignored
Password for user postgres: 
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

In another server, where the script runs on the local DB, my working script is:

su - postgres -c "psql myDatabase" << EOF
select * from "myOtherTable";
\q
EOF

The question is simple, how can I write the right command for bash, to connect to another database with user/password and issue commands?

A link I tried, but password seems to not be set: run psql query in bash

Thanks!

Upvotes: 3

Views: 5232

Answers (1)

ikettu
ikettu

Reputation: 1203

Try

PGPASSWORD=yourpass psql -U postgres -W -h 1.2.3.4 -d testdb -p 5432

See: https://www.postgresql.org/docs/9.3/static/libpq-envars.html

or ~/.pgpass file https://www.postgresql.org/docs/9.3/static/libpq-pgpass.html

Upvotes: 4

Related Questions