Search code examples
windowspostgresqlbatch-filepgadmin

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

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.

  1. 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.

  2. 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.

  3. 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
Solution

  • 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.