You are here

Home › Installing Theatre Manager › PostgreSQL Database Server › Macintosh PostgreSQL Server

Step 4: PostGreSQL server backups

Subscribe to Syndicate
Sun, 05/16/2010 - 13:11 — Doug
Once the database is set up, you will need to establish a back up frequency that is appropriate for your venue. Mostly, setting up one backup daily to the backup directory should be enough and let it run late at night.

However, it is perfectly ok to set up 2 or more daily backups while Theatre Manager is running. You may wish to do this on a high volume site and pick times like 8:00am, 1:30pm and 8:00pm, for example. Backups can run while Theatre Manager is being used.

Manual Backup

You can also do a manual backup at any time by going into Terminal and running the 'backupTM.php' file mentioned in this section, even if Theatre Manager is running. You would do this using terminal on the server by typing

php /Users/Shared/backupTM.php

based on what you had already done to configure the script using the instructions below.

This backup process only exports data from the database and creates a compressed backup file. You will need to take those backups and copy them to another machine and/or establish a backup rotation and take some offsite.

Editing the backup script

1. If you used the installer to place files into the correct location, you can skip to Step 2 in this section. If you did not, then you will need a copy of the backup script files and then:
  1. Create a directory called /Users/Shared/Backups
  2. Get a copy of the file backupTM.php and place them into the /Users/Shared directory
2. If the files are in the correct place because they were installed by the installer, then we will need to edit the backupTM.php file. Note, if you have multiple databases to backup, make a copy of this file for each database you want to backup and edit accordingly.
  1. Navigate to the /Users/Shared directory and find the 'backupTM.php' file.
  2. Right click on the file and edit with Text Edit
    edit
  3. Change the line
    $backupDB = 'TheatreManagerDemo';
    to be
    $backupDB = 'xxxxxx';
    where xxxxx is the name of the database set up in PostGreSQL. Note that the name of the database is case sensitive and must match what is seen in PGAdmin III, or what s used to log on to the database via Theatre Manager.
  4. If you wish to alter the backup location, change the path mapping in the line that is highlighted. Normally, it is not changed.
    $localFolder = '/Users/Shared/Backups';
    edit2
  5. The backup script can be set to automatically log into a remote FTP site and upload the database right at the end of the backups. If you wish to do that, set the values of $ftpHost, $ftpUser, $ftpPass, and $ftpFolder (if need be). If you do not want to do this, then leave the $ftpHost blank
    Close the batch file and save the changes.
    edit2

Test the Batch Script

1. backuprun

Test the batch file by starting up terminal and typing the highlighted command
php /Users/Shared/backupTM.php
and terminal should come back with a listing of the files being dumped. If you get errors about access denied, then there are likely issues:

  1. The backupTM.php script needs to have execute permissions. Use file examiner to fix that in Leopard. Get info should work on OSX 10.4
  2. Make sure that the local user in the pg_hba.conf file has 'trust' access to the database. You might need to enable it for 127.0.0.1/32 and/or ::1/128 if your network uses IPv6
When done and the terminal window closes, go to the /Users/Shared/Backups directory and see if there is a recent backup for your database. Note that there should be some size to the database backup. It should not be zero bytes in size. In this sample, we have the two backups of the TheatreManagerDemo. The highlighted one was made on 20080116 at 07:34:49 in the afternoon. There will be a new file here each time the backup is run.

backuplist

Creating a Daily backup Job

1. Create a timed backup for the database by going to the /applications directory and starting a program called CronniX. A copy was installed by the PostGres Setup program. CronniX is shareware and can be found at www.abstracture.de/projects-en/cronnix

scheduledTask

Only do this on the machine that has the database server on it and make sure you are logged on as the administrator.
2. Double click on the 'CronniX.app' icon (it may or may not have .app at the end). This will start the CronniX task Scheduler. On a side note, Cronnix is an interface to the Unix CRON facility. CRON has been around for a long time and is one of the task scheduling tools that is built into the unix operating system. You don't have to worry if it is on your system.... it just is.

addTask

Click the New icon on the upper left. It will open a new window with a sample script at the bottom that says:

echo "Happy New Year!"

Replace this with the same command used to start a backup in the preceding section.

php /Users/Shared/backupTM.php

Click on the expert tab and make the settings in the upper half of the screen as per the example. These settings adjust your backup schedule. For example if you want a daily 2am and 2pm backup:

  • minute is on the hour (0)
  • hour is 2am and 2pm (2,14)
  • day of month is anything (*)
  • month is anything (*)
  • day of week is anything (*)

This will effectively schedule two per day backup of your database and is the recommended backup schedule to setup.

Entries for any of the items can be like:

  • * means 'always' for any entry. If minute said 10 and hour said *, it would mean every 10 minutes, regardless of hour.
  • 1,4,7,10 means on the 1,4,7, and 10. If this was hour, then there would be 4 backups at that time. If this was day, then only on the 1st of the month, 4th of the month, etc.
  • If you wish to read about more esoteric cron settings, please refer to internet sites by googling for 'cron settings'

wizard1

Note: you can schedule backupTM to run as many times as you want during the day by changing the parameters of the one CRON job, or by creating more jobs. Once or twice per day is normally enough but you may feel that more times is better for your backup requirements on busy days.