simple script to backup PostgreSQL database

Question

Hello I write simple batch script to backup postgeSQL databases, but I find one strange problem whether the pg_dump command can specify a password?

There is batch script:

 REM script to backup PostgresSQL databases
 @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%
 SET db1=opennms
 SET db2=postgres
 SET db3=sr_preproduction
 REM SET db4=sr_production

 ECHO datestr is %datestr%

 SET BACKUP_FILE1=D:\%db1%_%datestr%.sql
 SET FIlLENAME1=%db1%_%datestr%.sql

 SET BACKUP_FILE2=D:\%db2%_%datestr%.sql
 SET FIlLENAME2=%db2%_%datestr%.sql

 SET BACKUP_FILE3=D:\%db3%_%datestr%.sql
 SET FIlLENAME3=%db3%_%datestr%.sql

 SET BACKUP_FILE4=D:\%db14%_%datestr%.sql
 SET FIlLENAME4=%db4%_%datestr%.sql

 ECHO Backup file name is %FIlLENAME1% , %FIlLENAME2% , %FIlLENAME3% , %FIlLENAME4%

 ECHO off
 pg_dump -U postgres -h localhost -p 5432 %db1%  > %BACKUP_FILE1%
 pg_dump -U postgres -h localhost -p 5432 %db2%  > %BACKUP_FILE2%
 pg_dump -U postgres -h localhost -p 5432 %db3%  > %BACKUP_FILE3%
 REM pg_dump -U postgres -h localhost -p 5432 %db4%  > %BACKUP_FILE4%

 ECHO DONE !

Please give me advice

Regards Mick

MC ND · Answer 1 · 2014-05-30T13:17:55.903

2

edited to reflect information in comments

There is no option to feed the password form command line. You need to include the -w or --no-password switch and create a file that will contain the password for the connection. It is a text file in the form

hostname:port:database:username:password

By default this file will be readed from %APPDATA%\postgresql\pgpass.conf, but its location can be changed setting the environment variable PGPASSFILE to the path to the file to use.

You can find more information in the product documentation

edited May 30 '14 at 13:17

answered May 30 '14 at 11:10

MC ND

69,615
8
84
126

Or set the [`PGPASSWORD`](http://www.postgresql.org/docs/current/static/libpq-envars.html) environment variable. – Milen A. Radev May 30 '14 at 11:14
@MilenA.Radev, of course, you are right. It is just that i don't like the `Use of this environment variable is not recommended` in the documentation you link. – MC ND May 30 '14 at 11:37
1

or set the `PGPASSFILE` shell/environment variable. – wildplasser May 30 '14 at 11:57
@wildplasser, i forget to include that, answer updated. Thank you – MC ND May 30 '14 at 13:18

simple script to backup PostgreSQL database

1 Answers1