2

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

1

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.

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

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.

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

Thanks for your cmd script. I used it to write my own as show below. It will backup all databases stored in postgresql to file formatted with the custom format used by pg_dump with date yyyymmdd_hhss in the name. ToDo : backup full schema without data.

The database name is stored in the %%d variable in the loop. You can change safely :

  • "backup_data_dir" (must end by )
  • "pg_user" (postgres by default)
  • "pg_bin_dir" (must end by )

Attention : I'm french. dd/mm/yyyy for %date%, "DEBUT" for "START", "F I N" for "E N D".

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET backup_data_dir=C:\path\to\backup\
SET backup_file_name=%COMPUTERNAME%.%date:~6,4%%date:~3,2%%date:~0,2%_%time:~0,2%%time:~3,2%.PGDMP
SET pg_user=--username=postgres
SET pg_dmp_option=--clean %pg_user% --format=custom
SET pg_lst_db=psql.exe %pg_user% --list --field-separator=^; --tuples-only --no-align
SET pg_bin_dir=C:\Program Files\PostgreSQL\9.6\bin\

CD /D %pg_bin_dir%
FOR /f "delims=; tokens=1" %%d IN ('!pg_lst_db!^| find ";"^|find /v "template0"') DO (
  @ECHO !date! !time! pg_dump de la base [%%d] --- DEBUT ---
  SET backup_file_name=%%d.%backup_file_name%
  SET pg_dmp_option=%pg_dmp_option% --file="%backup_data_dir%!backup_file_name!" --dbname=%%d
  pg_dump.exe !pg_dmp_option!
  ECHO !date! !time! pg_dump de la base [%%d] --- F I N ---
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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