Postgres pgpass windows - not working

Question

DB: Postgres 9.0 Client: Windows 7 Server Windows 2008, 64bit

I'm trying to connect remotely to a postgres instance for purposes of performing a pg_dump to my local machine.

Everything works from my client machine, except that I need to provide a password at the password prompt, and I'd ultimately like to batch this with a script.

I've followed the instructions here:

http://www.postgresql.org/docs/current/static/libpq-pgpass.html

but it's not working.

To recap, I've created a file on the client (and tried the server as well): C:/Users/postgres/AppData/postgresql/pgpass.conf, where postgresql is the db user.

The file has one line with the following data:

\*:5432:\*postgres:[mypassword]

(also tried explicit ip/dbname values, all asterisks, and every combination in between.

(I've also tried replacing each '*' with [localhost|myip] and [mydatabasename] respectively.

From my client machine, I connect using:

pg_dump -h [myip] -U postgres -w [mydbname] > [mylocaldumpfile]

I'm presuming that I need to provide the '-w' switch in order to ignore password prompt, at which point it should look in the AppData directory on the server.

It just comes back with "connection to database failed: fe_sendauth: no password supplied.

Any insights are appreciated.

As a hack workaround, if there was a way I could tell the windows batch file on my client machine to inject the password at the postgres prompt, that would work as well.

Thanks.

Answer 1

Please use this script for generating backup.

PGPASSWORD='database password'
PGUSER='database user'
PGHOST='localhost'
PGPORT='psql portno'
PGDATABASE='database name'
pg_dump -Ft <database name> -x -O -f '<backup file name.tar>'

Answer 2

I would try the equivalent of strace to see whether and where it looks for pgpass.conf and what it does with it.

The -w option is unnecessary and unrelated to your problem.