Run postgres query from another server, in bash

Question

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!

ikettu · Accepted Answer · 2017-05-05 09:56:18Z

4

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

answered May 5, 2017 at 9:56

ikettu

1,20312 silver badges17 bronze badges

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

2 Comments

aPugLife Over a year ago

good links! thanks i check them out and when working will mark your answer (:

aPugLife Over a year ago

At first it kept asking for the password, then it just stopped. Test server rebooted, script ran and worked! I tried the .pgpass solution. creating this file in the root folder, permissions to 0600 and the command in the script is psql -U postgres -h 1.2.3.4 -d dbname << eof [...] \q eof

Collectives™ on Stack Overflow

Run postgres query from another server, in bash

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related