4

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?

Community
  • 1
  • 1
user90772
  • 387
  • 1
  • 5
  • 12
  • 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. – zb226 Oct 28 '15 at 11:45
  • Well, according to this [http://serverfault.com/questions/526170/psql-fe-sendauth-no-password-supplied], it might make a difference. I tried specifying the port as well but as you said this is not the problem. – user90772 Oct 28 '15 at 12:32
  • This question is about using `sudo` to call `psql`. Are you using `sudo` to call `psql`? – zb226 Oct 28 '15 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. – user90772 Oct 28 '15 at 14:55
  • 1
    I managed to find a solution to what I wanted in this SO question [http://stackoverflow.com/questions/6523019/postgresql-scripting-psql-execution-with-password], 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` – user90772 Oct 28 '15 at 15:06

1 Answers1

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
Egor Rogov
  • 5,278
  • 24
  • 38
  • 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. – user90772 Oct 28 '15 at 15:12
  • Why not? `psql "host=... dbname=... user=... port=... password=..." < input.sql > output.sql` works fine. – Egor Rogov Oct 28 '15 at 15:27
  • I did not try this, I tried to use -f -o arguments and it did not work, thank you – user90772 Oct 28 '15 at 15:32