Windows – Postgres pgpass windows – not working

postgresqlwindows

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.

Best Answer

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>'

Related Solutions

Windows – How to find what process is holding a file open in Windows

I've had success with Sysinternals Process Explorer. With this, you can search to find what process(es) have a file open, and you can use it to close the handle(s) if you want. Of course, it is safer to close the whole process. Exercise caution and judgement.

To find a specific file, use the menu option Find->Find Handle or DLL... Type in part of the path to the file. The list of processes will appear below.

If you prefer command line, Sysinternals suite includes command line tool Handle, that lists open handles.

Examples

c:\Program Files\SysinternalsSuite>handle.exe |findstr /i "e:\" (finds all files opened from drive e:\"
c:\Program Files\SysinternalsSuite>handle.exe |findstr /i "file-or-path-in-question"

PostgreSQL – Default Superuser Username/Password After New Install

CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!

This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.

What you should do is follow Chris James's answer:

sudo -u postgres psql postgres

# \password postgres

Enter new password:

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql. Note that sudo -u does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).

So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Related Topic