Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

You cannot specify a password for Postgres utilities like pg_dump on the command line.
This would be a giant security hole, requiring disgusting workarounds to mitigate.

What you CAN do is create a pgpass file, which pg_dump will consult.

Depending on your needs you may want to consider taking a filesystem level backup instead of using pg_dump -- pg_dump will need to lock various parts of your database while it runs, and may interrupt normal use of the system. Alternately, consider running your backup process on a slave system.

share|improve this answer
1  
There's also the PGPASSWORD env var, but it isn't significantly more secure than showing the password on the command line, so .pgpass is certainly the right thing to do. –  Craig Ringer May 31 '14 at 5:02

You can also create a dedicate user, restricted to localhost and with reduced privileges who can connect without password. (For example a unix user).

We used this and if the user is well protected it will not break the security.

share|improve this answer
    
Using IP for authentication is not very secure. And if you are connecting through a unix socket, there isn't going to be any IP at all. –  kasperd May 31 '14 at 9:48
1  
My bad yes. I wanted to say restricted to localhost –  ochurlaud May 31 '14 at 9:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.