Data Utilities

Theatre Manager

 

Database Utilities

Overview

Data utilities are tools used to repair corrupted data. Database corruption can occur for a variety of reasons spanning both hardware and software issues. Data corruption on a network is usually caused by networking issues i.e. broken connections to the server or multiple databases being created. The built-in utilities for Theatre Manager are located under the Setup >> Data Utilities Menu.

This menu will open the Database Utilities Window.

Click Here for a detailed description of this window and its functions.

Data Utilities Window

This is the window from which all database checking and repair is performed. You are given a breakdown of all the data in the database along with the number and size of the records each contains.

Parts of the Database Utilities Window

File Name
The name of the file.
Quantity
The number of records stored in the file.
Description
A brief description of what the file contains.
Size
The amount of memory the file occupies.
Held for possible future use.
Cleanup of a database rolls forward all temporary work files into the main database.
Held for possible future use.
Creates a printed copy of the list as shown.
Allows entry of an SQL query of the database.

Database Backup Instructions for Mac and PC

The following links guide you to set up a backup process for your Theatre Manager database and for automating that process. Please follow the instructions for the appropriate platform for your venue.

Your server will dictate whether you follow the PC or Mac instructions, regardless of the other workstations in the office.

Creating Automatic Backups - Mac Instructions

Once the database is set up, you will need to establish a back up frequency that is appropriate for your venue. Most often you can set up one backup daily to the backup directory and let it run late at night.


However, a busy location may want to set up 2 or more daily backups while Theatre Manager is running choose less busy times, for example 8:00am, 1:30pm and 8:00pm.

You can run 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.

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

This process should have been part of the original Theatre Manager Installation.

If automatic backups have been interrupted for some reason, but the backup script is still intact, please follow these instructions for recreating the CronniX task.

the backup script needs to be replaced, continue with the following instructions before moving on to creating a daily backup routine.

  1. Navigate to the /Users/Shared/Backups directory.

  2. Download the PHP script and place it in that directory.

  3. If you have multiple databases to backup, make a copy of this file for each database you want to backup and edit accordingly.

  4. Right-click on the file and open it for editing in Text Edit.

    Change the line

    $backupDB = 'TheatreManagerDemo';

    to be

    $backupDB = 'xxxxxx';

    where xxxxx is the name of the database set up in PostGreSQL.

    The name of the database is case sensitive and must match what is seen in PGAdmin III, or what is used to log on to the database via Theatre Manager.

  5. If you want to alter the backup location, change the path mapping in the line that is highlighted.

    $localFolder = '/Users/Shared/Backups';

    Normally, this is not changed.

    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.

    To do this, set the values of $ftpHost, $ftpUser, $ftpPass, and $ftpFolder.

    If you do not want to do this, then leave the $ftpHost blank.

  6. Also make the changes outlined here that were described in the release notes for version 8.15.

  7. Save and close the file.
  8. Test the backup script.

    Do this by running a manual backup as outlined in these instructions.

    When the manual backup is finished, go to the /Users/Shared/Backups directory and see if there is a recent backup for your database. There should be some size to the database backup...it should not be zero bytes. There will be a new file here each time the backup is run.

Creating a Daily Backup Job - Mac Instructions

Timed daily backups are set up as part of the initial Theatre Manager installation. The following instructions explain how to set up the daily routine if a client would like to change or set up the routine again.
  1. Create a timed backup for the database by going to the /Users/Shared directory and starting a program called CronniX.

    A copy is included in the setup of the PostGreSQL database. However CronniX is shareware and can be found at www.abstracture.de/projects-en/cronnix.

    Log in as Adminstrator and do this on the machine that has the database server.

  2. Double click on the 'CronniX.app' icon (it may or may not have .app at the end) to begin the setup process.

    The CronniX task Scheduler starts.

    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 Unix operating system.

  3. Click the New icon on the upper left. A new window in the Intervals tab opens.

    A sample script at the bottom under Command that says:

    echo "Happy New Year!"

  4. Replace with the same command used to start a backup manually.

    php /Users/Shared/backupTM.php

  5. Click on the expert tab and make the settings in the upper half of the screen as in 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 schedule two per day backups of your database (the recommended backup schedule to setup.)

Entries for any of the items can be:
  • * 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'

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.

Creating Automatic Backups - PC Instructions

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 okay to repeat the steps below, and set up 2 or more backups daily 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:00 pm, for example.
You can also do a manual backup at any time by double clicking on the 'TMBackup.bat' file mentioned in this section - even if Theatre Manager is running.
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.

This process should have been part of the original Theatre Manager Installation.

If automatic backups have been interrupted for some reason, but the backup script is still intact, please follow these instructions for recreating the Scheduled Task. If the backup script needs to be replaced, continue with the following instructions before moving on to creating a daily backup routine.
  1. Download the BackupTM BAT file.
  2. Create a directory called C:\BoxOffice\Backups (or D: or E: as appropriate) and move the BAT file into that folder.

    If you have multiple databases to backup, make a one copy of this file for each database you want to backup and edit accordingly.

    .
  3. Right-click on BackupTM.bat and Edit with WordPad or NotePad.
  4. change the line:

    set DATABASE_NAME=TheatreManager

    to be

    et DATABASE_NAME=xxxxx

    where xxxxx is the name of the client database set up in PostgreSQL.

    The name of the database is case sensitive and must match what is seen in PGAdmin III, or what you use to log on to the database.

  5. If you altered the install location of Postgres or the box office directory, change the drive mappings in the two lines that are highlighted.

    Set POSTGRESQL_PATH=C:\ ..... to D:, etc to match the path to the PostgreSQL directory. The default one for the installer is already in the file.

    set BOXOFFICE_DIR=C:\ .... to D:, etc, and

  6. Set BACKUP_PATH="C:\BoxOffice\Backups" to match the path where backups will be placed.

    Again the default one is already there and may not need to be changed.

  7. In most situations, with default installations, you will probably only need to edit the DATABASE_NAME parameter.

  8. Also make the changes outlined herethat were described in the release notes for version 8.15.
  9. Save and close the file.
  10. Test the backup script by running a manual backup as outlined in these instructions.
  11. When complete, and the DOS window closes, go to the C:\BoxOffice\Backups directory and see if there is a recent backup for your database.

    There should be some size to the database backup...it should not be zero bytes. There will be a new file here each time the backup is run.

Creating a Daily Backup Job - PC Instructions

Timed daily backups should have been set up as part of the initial Theatre Manager installation. The following instructions outline how to set up the daily routine.

  1. Open Start->Settings->Control Panel->Scheduled Tasks.

    Only do this on the machine that has the database server on it.

    If you are using Vista or do not have a 'Classic' view of the task scheduler,you may wish to change the control panel view to 'Classic' mode. It can make things easier to find.

  2. Double click on the Scheduled Task icon.
  3. Click the 'Add Scheduled Task' icon.

    The Scheduled Task Wizard opens.

  4. Click the "Next"button.

  5. On the displayed list of applications, click the "Browse" button.

  6. On the 'Select Program to Schedule' dialog, navigate to the C:\BoxOffice folder and click on the 'BackupTM' icon.

    Then click 'Open'.

  7. Choose a frequency for the backup.

    Daily is suggested.

    Then click 'Next'

  8. Pick the time that you want the backup to run.

    Generally picking a time in the middle of the night is the most conveniant. Then Click 'Next'

  9. Enter the password for the administrator of the machine.

    Then Click 'Next'.

  10. Click 'Finish' to save the job.

You can schedule backupTM to run as many times as you want during the day by creating more jobs. Once is normally enough but you may feel that more times is better for your backup requirements.

Backing Up the Database Manually

The database backup process is automated and would have been set up for you during installation or conversion. If not, follow the instructions on downloading and upgrading the latest scripts.

If the backups processes were created, you would have at least one recent backup. To create another one, follow the instructions and run the appropriate backup script on the database server machine.

Backing Up Manually By Running the Backup Script

  1. On the Mac, you open the terminal on the server and type

    php /Users/Shared/backupTM.php

  2. On a PC, go to C:/BoxOffice directory on the server and run the 'BackupTM.bat' file (by double clicking on it.)

    In either case, check that there is a new backup in the 'Backups' directory that has 'yyyymmdd_mmhhss' as part of the file name where the date is the last few minutes.

Backing Up Manually Using pgAdmin

  1. On the server, open pgAdmin by navigating to either Applications > PostgreSQL > pgAdmin (Mac) or by looking in Start Menu > Programs > PostgreSQL > pgAdmin.
  2. Select/log in to the PostgreSQL Server by double-clicking.

    When the password prompt comes up, click "OK".

  3. Double-click "Databases" to display the list of databases.

    Right-click on the database you wish to backup to bring up the context menu.

  4. Select Backup.

  5. Set up the window as shown.

    This file path is setup for Mac OS X. The equivalent path on a PC would be C:\BoxOffice\Backups\MyFile.backup

  6. Click "OK" to start the backup.

Restoring a Database from a Backup

Clients often ask how to set up a training or demo database. Instructions for doing so follow.

  • Please note that these instructions are written and intended for experienced/technical users only.
  • It would be easy for an inexperienced user to accidentally delete the live database while attempting this process.
  • Please do not attempt if you are uncertain about any of the instructions below.
  1. Open pgAdmin and select/log in to the PostgreSQL Server.

    do this directly on the server machine to bypass having to enter a password. When the password prompt comes up, just click "OK" to log in.

  2. Highlight Databases and Edit >> New Object >> New Database.

  3. In Properties, set the Name to something descriptive of what kind of database this will be.

    In the above example - Training

  4. Set the Owner to TheatreManager.
  5. ***Version 8 ONLY**Select SQL_ASCII as the Encoding format.

    ***Version 9 ONLY**Select UTF8 as the Encoding format.

  6. ***Version 8 ONLY**Set the Template to Template0.

    ***Version 9 ONLY**Skip selecting this field.

  7. Click OK to create the database.

    You can use a backup of an existing database to populate the training database for staff use. Look in:

    • Mac: /Users/Shared/Backups
    • PC: C:/Box Office/Backups

    Then you can restore a backup to the new database you just created in pgAdmin:

  8. Highlight the new database.

    TrainingDatabase in the example.

  9. Select Tools >> Restore.
  10. Choose the backup to use and click OK.

    The Restore Database window opens.

    This process can take anywhere from a few minutes to a few hours to complete. Theis depends on the size of the database and speed of the server. Let it run without interruption.

    When the database is finished loading the window will have a line that reads

    "Process returned exit code 0" or "Process returned exit code 1".

  11. Either return exit code (0 or 1) is acceptable.

    Users can now log in to Theatre Manager and click Change Data File on the login window to access the new database from the database drop-down menu.

Database Utilities - Sending a Database to ArtsMan - Macintosh

Sending a Database to Arts Management Systems using Timbuktu - Macintosh

To speed up the file transfer process you may compress your database and transaction files into an archive using Stuff-It. Their demonstration software can be downloaded from the website http://www.aladdinsys.com. If you require a copy of Timbuktu, please visit http://www.netopia.com/en-us/software/products/tb2/index.html for downloading an evaluation copy. If you find this tool useful, we recommend you purchasing a copy for your organization.

Prior to sending a database to Arts Management, you must obtain 3 things to complete the process. Contact Arts Management Technical Support for these values:

  • the TCP/IP address of the computer you are about to send the database to
  • the login user name
  • the login password
  1. Open Timbuktu
  2. Select New Connection from the File Menu

    The following New Connection window will appear

  3. Click the TCP/IP tab.

  4. Enter the IP address given to you by Arts Management Systems

    It will be in a form of xxx.xxx.xxx.xxx

  5. Click the Send Button

    The Network Password Window will open

  6. Enter the Name and Password given to you by Arts Management Systems
  7. Click the OK Button

    This will open the send window

  8. Click the Add Files Button

    The following window will open

  9. Navigate to where your database files are stored

    If you are unsure of their location ask your System Administrator.

  10. Select all of the files and click the Add Button

    If your database is made up of multiple segments, select all of the segments. If a segment is not sent, we will not be able to log into the database after we have received it. If you are unsure if you have multiple segments, ask your System Administrator.

    The files will be added to the middle portion of the screen

    You will return to the Send Window

  11. Enter in a Message that indicates who you are and the organization you are with.
  12. Click the Send Button to begin the file transfer

    The file transfer will begin.

PostgreSQL Terminal Commands

This is a quick reference guide for how to edit settings via the terminal command line.

To download a pdf copy of each OS
Windows Cheatsheet
Mac OS Cheatsheet

Starting/Stopping the Server
Refer to: http://www.postgresql.org/docs/current/interactive/app-pg-ctl.html

Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl start or pg_ctl stop or pg_ctl restart
  4. - or - you may need to enter the full pathname of postgresql bin's folder including the location of the data folder if the PATH environment variables are set incorrectly. As in:
    /Library/PostgreSQL/11/bin/pg_ctl start -D /Library/PostgreSQL/11/data
    /Library/PostgreSQL/11/bin/pg_ctl stop -D /Library/PostgreSQL/11/data
  5. (Optional) Leave the terminal window open to view stderr log messages as you execute queries against the server.
Windows
  1. Open Control Panel
  2. Open Administrative Tools
  3. Open Services
  4. Find the PostgreSQL Server service
  5. Start, Stop or Restart the service
Reloading Changes to the Configuration File
Refer to: http://www.postgresql.org/docs/current/interactive/app-pg-ctl.html - simply sends the postgres process a SIGHUP signal, causing it to reread its configuration files (postgresql.conf, pg_hba.conf, etc.). This allows changing of configuration-file options that do not require a complete restart to take effect.

Mac OS X
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_ctl reload
Windows
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\11\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_ctl reload
Backing Up A Database
Uncompressed backups from this point forward will use the .sql extenstion, while compressed backups will use the .backup extension.

Mac OS X - Uncompressed
  1. Open the terminal
  2. Type su - postgres
  3. Type pg_dump [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump Demo > /Users/Shared/MyBackups/Demo.sql
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Type pg_dump -F c -v [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -F c -v Demo > /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\11\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_dump -U postgres -o [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -U postgres -o Demo > /Users/Shared/MyBackups/Demo.sql
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\11\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. pg_dump -U postgres -F c -v [DatabaseName] > [Path] e.g For Database 'Demo' ->
    pg_dump -U postgres -F c -v Demo > /Users/Shared/MyBackups/Demo.backup
Windows - Compressed (via pgAdmin on a different machine then the server)
  1. Open Command Prompt
  2. Go to C:\Program Files\pgAdmin III\1.12 (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd pgAdmin III - enter - cd 1.12 enter
  3. pg_dump --host [IP address of server] --port 5432 --username "postgres" --format=c --compress=9 --file "C:\BoxOffice\[DatabaseBackupName].backup [DatabaseName] e.g For Database 'Demo' -> pg_dump --host 192.168.0.3 --port 5432 --username "postgres" --format=c --compress=9 --file "C:\BoxOffice\Demo.backup" Demo
Restoring a Database
Mac OS X - Uncompressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type psql -v [DatabaseName] < [Path] e.g for Database 'Demo' ->
    psql -v Demo < /Users/Shared/MyBackups/Demo.sql
Mac OS X - Compressed
  1. Open Terminal
  2. Type su - postgres
  3. Drop the existing database (If it exists)
  4. Create a new database with the same name
  5. Type pg_restore -F c -v -U postgres -d [DatabaseName] [Path] e.g for Database 'Demo' ->
    pg_restore -F c -v -U postgres -d Demo /Users/Shared/MyBackups/Demo.backup
Windows - Uncompressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\11\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. Type psql -U postgres [DatabaseName] < [Path] e.g for Database 'Demo' ->
    psql -U postgres Demo < /Users/Shared/MyBackups/Demo.sql
Windows - Compressed
  1. Open Command Prompt
  2. Go to C:\Program Files\PostgreSQL\11\bin (Do cd.. to get back to C: prompt then type cd "Program Files" - enter - cd PostgreSQL - enter - cd 11 enter - cd bin - enter
  3. Type pg_restore -F c -v -U postgres -d [DatabaseName] < [Path] e.g for Database 'Demo' ->
    pg_restore -F c -v -U postgres -d Demo < C:\BoxOffice\/Backups\Demo.backup