pg_dump password authentication failed error with pgpass.conf file in Windows

Question

I couldn't do a backup because pg_dump shows the following error.

pg_dump: [archiver (db)] connection to database "mydb" failed: 
FATAL:  password   authentication failed for user "postgres"
password retrieved from file 
"C:\Users\MyName\AppData\Roaming\postgresql\pgpass.conf"

This is what my pgpass file look like.

localhost:5432:mydb:postgres:mypassword

When I remove pgpass.conf file and run the pg_dump file, it prompts for password and the backup runs properly.

This is what the pg_dump script looks like.

set PGPASSFILE=%APPDATA%\postgresql\pgpass.conf
%PG_BIN%pg_dump -h %PG_HOST% -p %PG_PORT% -U %PG_USER% -F c -b -v -f %BACKUP_FILE% %DB%

This is a Windows scheduled task so I don't want the user to enter the password.

Answer 1

I found the problem. The script that creates the pgpass.conf file created a space after the password and that space made the password - mypassword to be wrong. So when I removed the space, it works properly!

The script that created the pgpass file was:

echo %PG_HOST%:%PG_PORT%:*:%PG_USER%:%PG_PASSWORD% > %PGPASS_FILE%

I changed it to the following. See the space between PG_PASSWORD% and > is removed.

echo %PG_HOST%:%PG_PORT%:*:%PG_USER%:%PG_PASSWORD%> %PGPASS_FILE%