How to backup PostgreSQL Database with Xojo

Hello Everyone,

After reading about the various ways to backup PostgreSQL databases, it appears that this cannot be accomplished with Xojo (please feel free to correct me).

Here is what I have tried:

  1. Using Wayne Goldings excellent code at Running Xojo Apps on Windows with Elevated UAC - This code was not designed for this application, and it came close
  2. Attempted to run in a Xojo Shell, and this doesn’t seem to work, as the password must be manually entered in a Shell and no data appears in the file
  3. Running the following code works in a command prompt, and I must add the password manually when the command prompt requests it…
C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dump --username=XojoUser Xojo_DB > C:\\test\\Postgre_Backup.sql
  1. Tried examples on the old Real Studio forums and didn’t work

I think the sticky part is the PostgreSQL database manually requests the password and if the password is not entered manually, then the backup is ignored. Is there a way to backup a PostgreSQL Database with Xojo, or is it just simpler to use the command prompt in Windows?

Thanks for your thoughts :slight_smile:

Hello Eugene

Following Batchfile should work.

@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%
echo datestr is %datestr%

set BACKUP_FILE=f:\backupdir\ame_%datestr%.backup
echo backup file name is %BACKUP_FILE%
SET PGPASSWORD=foopassword
echo on
f:\pgadmin\pg_dump -i -h -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% foopassword2

replace foopassword with your password for postgreSQL and foopassword2 with an optional password for protecting your backupfile
from restored by others.
Names and directorys and ip adresses can be set at will.
Script will make seperated backup files with date of backup in the name.

Regards Eric

You also have “select pg_start_backup(‘label’)”, which you can call from a Xojo PostgreSQL connection. See .

this is working perfect

sh1=New Shell
sh1.Execute  PgBackupPath + " " +" -i -c -Fc -b -v -U postgres GasDB > c:\\GasRB\\myDb.dump"

Hi Alexis,

Thanks for the Xojo code. When I run the following modified code, a backup file is created with no data.

Dim sh1 as new Shell sh1.mode=0 sh1.TimeOut=5000 sh1.Execute "C:\\Program Files\\PostgreSQL\\9.3\\bin\\pg_dump -i -c -Fc -b -v -U XojoUser Xojo_DB > c:\\test\\Postgre_Backup.sql"

Is it possible that the database on your computer does not have a password?

Thanks for your help.

My Db have Password

Hi Eric,

Thanks for the batch code. Unfortunately, it didn’t work as expected. A file with the name Name_01_26_2014.backup was created, and had no data in the file with a size of 0 KB. Here is the modified code that was used:

[code]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%
echo datestr is %datestr%

set BACKUP_FILE=c:\test\ame_%datestr%.backup
echo backup file name is %BACKUP_FILE%
echo on
C:\Progra~1\PostgreSQL\9.3\bin\pg_dump -i -h -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% secret[/code]

Is there something that I missed? The password is: secret
I had to modify the file path from Program File to Progra~1 since the DOS command didn’t like the space.

Thanks for your comments :slight_smile:

Hi Alexis,

Maybe it has something to do with the way I created my login roles…

I first created a Group Role called Web, then the login role called XojoUser is a member of the Group Role called Web. This was created to have multiple group roles which could be easily administrated by assigning a user to a certain group role.

Just a thought…

Hi Maximillian,

Thanks for the link. I tried the following code and there was no backup.

[code] Dim db as new PostgreSQLDatabase
db.Host = “”
db.Port = 5432
db.DatabaseName = “Xojo_DB”
db.UserName = “XojoUser”
db.Password = “secret”

If db.Connect = True then
db.SQLExecute(“postgres=# select pg_start_backup(‘my_test_backup’)”)
End If[/code]

I am going to try different users and permissions to see if there is something else.

This is kind-of weird, as the backup works in a command prompt but not with all of these great examples…

You need to be a superuser to issue this command (so try db.UserName = “postgres”) . Also always check for errors after you issue a command.

The password you use for PGPASSWORD is the password for the superuser so probably this case the password for user postgres

The -U in the last line stands for user in this case postgres, if you want to use another user use another name after -U .
The user should be as mentioned above a superuser.

If the myDb.dump file Exist the backup no works you have to delete

Dim sh1 As Shell
Dim DbDel as FolderItem = GetFolderItem(“c:\GasRB\myDb.dump”)

If DbDel.Exists Then
End If

If PgBackupPath.Trim.Len > 3 Then
sh1=New Shell
sh1.Execute PgBackupPath + " " +" -i -c -Fc -b -v -U postgres GasDB > c:\GasRB\myDb.dump"
End If

Thanks for everyone’s help, and I am getting closer to a solution.

The error that I am getting is:

[quote]ERROR: WAL level not sufficient for making an online backup
HINT: wal_level must be set to “archive” or “hot_standby” at server start[/quote]

After looking through the documentation, I can’t seem to find this setting. I’ll keep everyone updated with what I can find.

Thank you. :slight_smile:

After updating the postgresql.conf file by changing the wal_level = archive and making sure the archive_mode=on (making sure to remove the comment # tags), restarting the database, and then restarting the computer, there is no backup performed.

When running the following SQL query in PG Admin III, the setting are set to archive:

SELECT setting, source, boot_val, reset_val, sourcefile, sourceline FROM pg_settings WHERE name = 'wal_level';

Any other helpful suggestions?

Thanks for your patience.

Thanks for the thought of checking the user settings, and both XojoUser and postgres have all role priviledges which are:
-Can Login
-Inherits rights from parent roles
-Can create databases
-Can create roles
-Can modify catalog directly
-Can initiate streaming replication and backups

Thanks for getting me to check :slight_smile:

You are probably looking for some dump file, which is not there. But you just activated WAL-Logging, which you can think of as a backup de luxe, as it lets you perform the magic of point in time recovery (basically you are recording all commands that alter the data). Those log files reside in the pg_xlog directory, which is in the data directory. For the whole story look here.

If all you want is a dump, then executing PG_DUMP or PG_DUMPALL from shell is what you should do.

Hi Eugene,

well, to get a reliable working Backup on different environments I actually do the approach within Xojo.
One more benefit is you can use it on any computer connected to the server.

To get all Tables within a Database:
SELECT table_schema,table_name FROM information_schema.tables WHERE table_schema = ‘public’ ORDER BY table_name;

I put the results in a dictionary and then read the columns of the table with:
SELECT * from information_schema.columns WHERE table_schema = ‘public’ AND table_name = ‘" + dict_tab.Value(“Tab_” + cstr(i1)) + "’;"

Now this information you can use for creating a backup file, compress it etc.

Restore is straight forward,
After checking it all Cols exist don’t forget to restart id if you don’t create the table again:

After Restore you maybe have to set the constraints correct.
Example (if ‘rowid’ is used as primary key):
“SELECT setval(’” + TabeleName + "_rowid_seq’, coalesce((select max(rowid)+1 from " + TabeleName + “);”

Hope this helps a little to get started.

Hmm, “reliable” if you don’t care for your functions, triggers, indexes, tablespaces, rules, database users, enums - and all of your data. Not sure that’s exactly what Eugene needs.

Sure it depends what you have to backup, this was just a starter about tables.
You have to implement some version control and take care of the data you mentioned above if needed.
I did this approach because in one of my projects backups have to be done on any computer connected.
Main message was: it can be done.