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).
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?
@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 127.0.0.1 -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.
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%
SET PGPASSWORD=secret
echo on
C:\Progra~1\PostgreSQL\9.3\bin\pg_dump -i -h 127.0.0.1 -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.
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.
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.
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';
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
-Superuser
-Can create databases
-Can create roles
-Can modify catalog directly
-Can initiate streaming replication and backups
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.
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:
TRUNCATE " + TableName + " RESTART IDENTITY;
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 + “);”
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.
