How to backup PostgreSQL Database with Xojo

Databases
21

Hi Maximilian and Rolf,

Maximilian: Yes, I am wanting to run the pg_dump command as a backup of the database, and would like to program this in Xojo. I am turning off wall_logging, as this test was to see if PostgreSQL would let the Xojo program backup. Thanks for your many helpful comments, as this clears up much of the thoughts around postgresql.

Rolf: Thanks for your kind suggestions, as this may be the only method that is able to backup the database until I can figure out how to let PostgreSQL backup with Xojo.

So far, all batch files and Xojo programs are not allowing the backup to occur - only manually running pg_dump in a Command Prompt is working.

I am trying this on PostgreSQL 9.3, and is there a possibility that this is a new security feature of PostgreSQL?

Thanks again for all of the helpful comments :slight_smile:

22

Hi Everyone,

Figured it out … the backup only seems to work in a batch file, and using the shell in Xojo to call the batchfile creates a full backup. Here is the code for anyone doing this on Windows…

Code in Xojo:

Dim sh1 as new Shell sh1.mode=0 sh1.TimeOut=5000 sh1.Execute "D:\\User\\Xojo\\Eugene\\PostgreSQL\\Chapter7\\testbackup.bat"

Code in testbackup.bat file:

[code]@echo off
set BACKUPDIR=“c:/test/”
set PGHOST=“127.0.0.1”
set PGUSER=“postgres”
set PGBIN=“C:/Program Files/PostgreSQL/9.3/bin/”
for /f “tokens=1-4 delims=/ " %%i in (”%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)

for /f “tokens=1-3 delims=: " %%i in (”%time%") do (
set hh=%%i
set nn=%%j
)

%PGBIN%pg_dumpall -h %PGHOST% -U %PGUSER% -f %BACKUPDIR%fullpgbackup-%year%%month%.sql[/code]

Thanks to Alexis, Eric, Maximilian, and Rolf for all of their help!!! :slight_smile:

23

One DB Backup

@echo off
set BACKUPDIR=“c:/PGBK/”
set PGHOST=“localhost”
set PGUSER=“postgres”
set PGDBNAME=“ADG”
set PGBIN=“C:/Program Files/PostgreSQL/9.6/bin/”
for /f “tokens=1-4 delims=/ " %%i in (”%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)

for /f “tokens=1-3 delims=: " %%i in (”%time%") do (
set hh=%%i
set nn=%%j
)

%PGBIN%pg_dump -h %PGHOST% -c -Fc -b -v -U %PGUSER% -d %PGDBNAME% -f %BACKUPDIR%fullpgbackup%PGDBNAME%-%year%%month%.dump