3

I am working on a remote Unix machine where I connect via SSH from my local machine. There is a Postgres database on another host that I need to connect from my remote machine using psql.

I would like to be able to run queries from an input file and save the results to an output file, however I did not manage to automatically set the password, surpassing the need to do it every time I run a query.

I tried the following:

  1. Run

    psql "user=username password=password host=hostname port=port dbname=database"`

    with my username, password, hostname, port and database values. This worked OK but I did not find a way to add arguments for input/output files (not needing to go through Postgres interactive environment).

  2. According to another SO question, I created a file ~/.pgpass with the following format: hostname:port:database:username:password

    and then chmod 0600 ~/.pgpass but when connecting as: psql -h hostname -U username -d database -w psql ignored this (I got the following failed authentication message.)

    psql: fe_sendauth: no password supplied.

    Note that my remote machine's name is different than my username, so this might also create a problem when creating the .pgpass file. Is there any other way to do it?

Any ideas?

Improve this question
5
  • What does the name of your remote machine have to do with your username? By the way, you didn't specify the port in your second psql invocation, but I doubt that matters in this context. Commented Oct 28, 2015 at 11:45
  • Well, according to this [serverfault.com/questions/526170/…, it might make a difference. I tried specifying the port as well but as you said this is not the problem. Commented Oct 28, 2015 at 12:32
  • This question is about using sudo to call psql. Are you using sudo to call psql? Commented Oct 28, 2015 at 14:22
  • No, apologies for this. I cannot use sudo since it is a remote machine. I want to create a bash script that takes two arguments (input/output files) and runs the query automatically using psql. Commented Oct 28, 2015 at 14:55
  • 1
    I managed to find a solution to what I wanted in this SO question [stackoverflow.com/questions/6523019/…, I did search enough before posting but I needed to rephrase my question I guess. The solution is to write PGPASSWORD=password psql -h hostname -U username -d database -f inputfile -o outputfile Commented Oct 28, 2015 at 15:06

1 Answer 1

Reset to default
0

This worked OK but I did not find a way to add arguments for input/output files

You can either redirect the input file to psql, or specify it in -f option:

psql < input.sql
psql -f input.sql

As for output file, just redirect psql's output to it:

psql -f input.sql > output.txt
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thank you. This did not work using the psql string mode. The solution was to add PGPASSWORD=password before psql and then do as you write.
Why not? psql "host=... dbname=... user=... port=... password=..." < input.sql > output.sql works fine.
I did not try this, I tried to use -f -o arguments and it did not work, thank you

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.