Automating PG_Dump.exe

[swhite]

Hi

I tried automating Postgres' pg_dump.exe from PB. The problem is that it prompts for a password but I cannot send the password from PB because it starts a console and waits for input from the console and not it appears from StdIn. I am trying this using PB 5.62 in Windows Server 2016. So I wondered if there is a method to send the Password to the console or some kind of work around?

Thanks,
Simon

[Paul]

Here is a small snippet you can adapt...

Code: Select all

server.s="10.0.10.101" ;<-- location of database server
port=5432
username.s="postgres"
password.s="mypassword"
dbname.s="mydb"
savepath.s="C:\"

folder.s=PathRequester("Select folder containing Postgres: pg_dump.exe","")
If folder
  If Right(folder,1)<>"\":folder+"\":EndIf
  dump.s=folder+"pg_dump.exe"
  cmd$="--dbname=postgresql://"+username+":"+password+"@"+server+":"+Str(port)+"/"+dbname+" --format=c --blobs -v --file="+Chr(34)+savepath+dbname+FormatDate("-%yyyy%mm%dd%hh%ii%ss",Date())+".backup"+Chr(34)

  RunProgram(dump,cmd$,savepath,#PB_Program_Hide)  ;<-- remove the Hide flag if you want to see progress
EndIf 

[Marc56us]

If you do not want to be asked for passsword for PostgreSQL command line tools, and do not want to store it on your program, use -w (lowercase!).
But this work only if you set role (user) to user who launch pg_dump in configuration files.

Help:
pg_dump --help
Database Roles in PostgreSQL documentation.

[swhite]

Hi

I discovered the postgresql URI late latest night and it works perfectly as you have indicated. I am surprised that so many of the tips on automating pg_dump do not mention it. I found numerous examples using the pgpass.conf or environment variables then I stumbled on a post about the postgresql URI.

Thanks,
Simon

Here is a small snippet you can adapt...

Code: Select all

server.s="10.0.10.101" ;<-- location of database server
port=5432
username.s="postgres"
password.s="mypassword"
dbname.s="mydb"
savepath.s="C:\"

folder.s=PathRequester("Select folder containing Postgres: pg_dump.exe","")
If folder
  If Right(folder,1)<>"\":folder+"\":EndIf
  dump.s=folder+"pg_dump.exe"
  cmd$="--dbname=postgresql://"+username+":"+password+"@"+server+":"+Str(port)+"/"+dbname+" --format=c --blobs -v --file="+Chr(34)+savepath+dbname+FormatDate("-%yyyy%mm%dd%hh%ii%ss",Date())+".backup"+Chr(34)

  RunProgram(dump,cmd$,savepath,#PB_Program_Hide)  ;<-- remove the Hide flag if you want to see progress
EndIf