Is there any way to fix pgpass.conf to work properly on Windows?

Question

Base on my previous question How to fix Automated Backup script for postgres [Window]?.

It looks like there is some problem with pgpass.conf on my PC which does not seem to provide the required password when try to backup the database using pg_dump. I have found that I have a weird situation. However, I could not figure out how to solve this.

On my PC, there is no postgresql folder to keep pgpass.conf -> I have try to install Postgres, but it looks like the error message shows

problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.

However, I already have a pgAdmin folder on my PC.
I have tried to find pgpass.conf by using dir /s | find /i "pgpass.conf" from root c:\ .

The result looks like it does not even exist on my PC -> So that's why I tried to create a new file for pgpass.conf inside the folder pgAdmin instead of the postgresql folder.
I have tried to set an environment variable as well, based on What do I need for pg_dumpall to work without a password?. The error on the console shows

'PGPASSWORD' is not recognized as an internal or external command

I have tried to add/edit the script like this

@echo off    
  echo 192.168.1.161:5432:_wolfcom:postgres:1234>"%APPDATA%\pgAdmin\pgpass.conf"
  set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
  cd /d "%root%"
  PGPASSWORD=R0m3o^%%Ech0-5910^& pg_dumpall -h 192.168.1.161 -w -U postgres _wolfcom > "D:\Backup\DatabaseBackUp\32312.sql" 
  pause

Can someone figure out how to let pgpass.conf work properly ?

Here is the current script for the batch file

@echo off    
  echo 192.168.1.161:5432:_wolfcom:postgres:R0m3o^%%Ech0-5910^&>"%APPDATA%\pgAdmin\pgpass.conf"
  set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
  cd /d "%root%"
  pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" _wolfcom
  pause

Here is Script which work for me

@echo off    
  echo 192.168.1.161:5432:_wolfcom:postgres:R0m3o^%%Ech0-5910^&>"%APPDATA%\postgresql\pgpass.conf"
  set "root=C:\Program Files (x86)\pgAdmin 4\v3\runtime\"
  cd /d "%root%"
  pg_dump.exe -h 192.168.1.161 -p 5432 -U postgres -F c -b -v -f "D:\Backup\DatabaseBackUp\SQL\123456.backup" _wolfcom
  pause

as mentioned in my other answer's comment, it is expecting the file in postgresql, not pgAdmin. so creeate the dir, redirect to that folder. — Gerhard, May 08 '19 at 06:14
`PGPASSWORD=R0m3o^%%Ech0-5910^` is a unixstyle "inline" definition of an environment variable. That won't work in Windows. You need to run a `SET "PGPASSWORD=..."` before calling `pg_dump` — , May 08 '19 at 06:16
Finally, I can back up database!!!! . Thank you so much both of you. What i have done is just create postgresql (just only new folder and new pgpass.conf ) without installing postgres. — Peera, May 08 '19 at 06:25
I will added my new script in the question which work for me and hopefully will be benefit for others — Peera, May 08 '19 at 06:27

score 6 · Accepted Answer · answered May 08 '19 at 06:03

6

Like the documentation says:

On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile).

You don't have to search for it, it has to be exactly in this place (unless you set the PGPASSFILE environment variable). If it doesn't exist, create it.

An alternative (and maybe simpler) way is to use pgAdmin and check the checkbox that makes it save the password for you. That creates the password file automatically.

answered May 08 '19 at 06:03

Laurenz Albe

209,280
17
206
263

Do you mean reset/clear password something like this https://www.pgadmin.org/docs/pgadmin4/dev/reset_passwords.html ? – Peera May 08 '19 at 06:15
No. The checkbox is (or used to be) in the dialog where you enter the password. – Laurenz Albe May 08 '19 at 06:17
Thank you for your help. I finally solved it (see my reply on other comments). – Peera May 08 '19 at 06:30
@LaurenzAlbe Ok i got it – Peera May 08 '19 at 08:40

score 4 · Answer 2 · answered Aug 11 '21 at 01:16

The Postgres documentation is good, but not completely clear.

On my system (Windows 10), %APPDATA% was C:\Users\<UserName>\AppData\Roaming; so the file had to be in C:\Users\<UserName>\AppData\Roaming\postgresql\pgpass.conf to be found by Postgres.

While I had tried placing it under <UserName>\AppData initially, that didn't work. Use echo %APPDATA% from the command-line to confirm the correct location on your system.

Is there any way to fix pgpass.conf to work properly on Windows?

2 Answers2