Is there a way to add the password at a parameter and get it automatically when needed in a batch file?

Question

I have the following batch file in windows, in order to backup postgresql daily.

I will add this batch file to run daily at specific time in Task Scheduler.

pg_dumpall --exclude-database='template1' --host=127.0.0.1 --port=1234 --username=myuser --password=mypass > mydb_export_all.sql

However, postgresql contains more that one database and when I run it in cmd it requests the password more than once, since there are many databases.

Is there a way to add the password at a parameter and get it automatically when needed?

UPDATE: I tried the following

@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
    set dow=%%i
    set month=%%j
    set day=%%k
    set year=%%l
)

set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=pg_dump_%datestr%.sql
echo backup file name is %BACKUP_FILE%
   
SET/P hostname=Host: SET/P port=Port: SET/P database=Database: SET/P username=Username: SET/P password=Password: > C:\mypath\pgpass.conf
pg_dumpall --exclude-database='template1'  --username=myuser > %BACKUP_FILE% 

And the pgpass.conf contains the following:

127.0.0.1:1234:myuser:mypass

and it works, however, the content of the pgpass.conf file is updated to

Host: SET/P port=Port: SET/P database=Database: SET/P username=Username: SET/P password=Password:

Any ideas of how to keep the content of pgpass.conf file unchanged? Because right now, I can't add it to task scheduler, since the second time it won't run.

Answer 1

I found the solution. Thanks Laurenz Albe regarding your comment.

The batch script contains the following script:

@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
    set dow=%%i
    set month=%%j
    set day=%%k
    set year=%%l
)

set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=pg_dump_%datestr%.sql
echo backup file name is %BACKUP_FILE%


REM Set the PGPASSFILE environment variable
SET "PGPASSFILE=C:\Windows\ServiceProfiles\NetworkService\AppData\Roaming\postgresql\pgpass.conf"

REM Execute pg_dumpall with the environment variable for password
pg_dumpall --exclude-database='template1' --host 127.0.0.1 --port 1234 --username=myuser > P:\Rina\%BACKUP_FILE%

The pgpass.conf file contains the following:

127.0.0.1:1234:*:myuser:mypass

Answer 2

The old and no longer recommended way is to create an environment variable named PGPASSWORD. When pg_dumpall, psql and other tools see it, they'll attempt to use it and only prompt for entry if it doesn't match.

You can also define PGPASSFILE env variable and set it to the (secure) location of a file with your password in it

127.0.0.1:1234:template1:myuser:mypass

You need one line per db.