05 June 2009, 11:13

Yippee I managed to install mahara and all is going well.

Now befeore I llok at setting my whole school up on it I need to automate backups of the database and I guess the mahara folder.

Can anyone tell me how this would be possible.

I can just about use tar but I don't know how I could backup a postgres database or automate the whole thing.

Thanks again for a brilliant eportfolio system.

08 June 2009, 0:15

Hi - good job on getting it going!

Yes, you need to back up the Mahara database and dataroot file. For your purposes, I would suggest that doing the job nightly would be enough.

To back up the database, you can use the command line program pg_dump. Are you able to use the command line? If so, you could write a script to use pg_dump to dump a copy of your database every night.

With the dataroot, you can use tar to make a compressed copy, like you say.

Here is a quick example script I've written off the top of my head, that might help:

#!/bin/sh
DATE=$(date +%F_%T)
su postgres -c "pg_dump -Fc -O -x -f /database-backup-directory/mahara-db-$DATE.pgdump" mahara-database-name
su www-data -c "tar czvf /dataroot-backup-directory/mahara-dataroot-$DATE.tar.gz /path/to/mahara/dataroot"

 That would need to be run as root, and you'd need to set up a cron job to do it. You'd need to make the backup directories and make sure they were owned by the postgres/www-data user. And of course test it all

That's a command line way, at least. It should give you a start anyway

19 June 2009, 4:09

Hi Nigel, Thanks for the help again.

I now have the database saved BUT how can I now tell that it is correct.

Sorry for sounding a bit dumb but if i had saved a mysql db I would open it in nano and view the sql commands but when i view the postgres db in nano i see this sort of thing...

PGDMP^A

^@^D^H^A^A^A^@^@^@^@^V^@^@^@^@^C^@^@^@^@^K^@^@^@^@^S^@^@^@^@^E^@^@^@^@m^@^@^@^@^A^@^@^@^@^H^@^@^@postgres^@^E^@^$

^@^@^@^@^@^A^A^@^@^@^A^A^@^@^@^A^A^@^@^@^@^@^@^@^@^@^E^@^@^@false^A^A^@^@^@^C^@^@^@^@^@^@^@^@^@�^F^@^@^@^@^@^@^^@^@^@STDSTRINGS^@

Is this because nano can't read it or because I have backed it up incorrectly.

thanks again.

once I have my script all sorted I'll post it back here for scrutiny by other users.

Thanks 

19 June 2009, 9:48

Sorry, also as a follow on, if that is the correct database how would you in the future restore that database.

For example I have now used and modified your script slightly for ubuntu and I have it backing up the database, the mahara data folder and the mahara install folder. Then that gets moved to an nfs share for safe keeping. If I was then to have a computer fail and I needed to get mahara back up and running I could replace the mahara folders but i wouldn't know how to replace the dbase?

Thanks 

21 June 2009, 19:30

Hi - firstly, that looks like the dump is in the correct format. The file is a binary file because of the -Fc argument to pg_dump ("format: custom"). If you want to see SQL in that file, you could use -Fp ("format: plain"), but there's not really any point, the custom format is smaller which means less to back up anyway.

To restore it, you use pg_restore (and I suggest you test all of this out by doing backups, trashing your install and then restoring of course..):

createdb -O [database-user] -EUTF8 [database-name]

pg_restore -d [database-name] -Fc -U [database-user] -O -x -f [filename-of-backup]

Note: when you do a restore of the database and dataroot, if you have moved the URL where the site is visible, you should change it in the config database table after you restore (this is useful e.g. if you use a backup to set up the system on another machine for testing purposes). Of course this is not necessary if you are just restoring to the same URL as before!

22 June 2009, 5:36

Thanks Nigel,

I'll give that all a go.

22 June 2009, 7:25

Hi Nigel,

I tried this script, too.

It works fine, however the command line ... 

su postgres -c "pg_dump -Fc -O -x -f /database-backup-directory/mahara-db-$DATE.pgdump" mahara-database-name 

...  asks for the DB password although I executed it as root. Is there any way to avoid this? Otherwise this shell script could not be executed via cron job :(

BR, Peer 

23 June 2009, 18:51

You actually executed the command as postgres. The postgres system user translates to the postgres database user in lieu of using the -U flag on pg_dump, and the postgres user pretty much always needs a password.

My suggestion to you is to read the postgres docs about how user authentication work - you'll want to create a user who can access the database without a password, which will involve using 'createuser' and editing the pg_hba.conf file. Best to read the docs to get a clear picture about what's involved

24 June 2009, 7:09

For me it works by using this: 

So, running pg_dump as root and using a ".pgpass" file in the root user home directory (i.e. "/root")  containing the line

127.0.0.1:5432:mahara-database-name:mahara-database-username:mahara-database-password

and changing access rights by 

chmod 0600 .pgpass 

 BR, Peer 

24 June 2009, 23:17