PostgreSQL Database Server

The following instructions are used to set up a PostGreSQL server for use with the Theatre Manager application. Please follow the directions appropriate for the server platform you are using.

Installation on a Macintosh
Installation on Windows
Postgres will run on linux and other Unix variants. You will have to install the server yourself by obtaining if from Postgres web site and follow much of the Macintosh setup steps for configuration and backups. We do not provide automatic installers, provide configuration or operational support Linux servers.

The server only needs to be set up on one machine where you want the database to reside. Theatre Manager can be set up on as many machines as you wish.

Do not install virus software on the Postgres Server except under very controlled circumstances and never allow virus scanner to scan the actual postgres database directories.

When downloading any update for Theatre Manager, please make sure your personal firewall is turned on (PCI requirement 1.4)

Macintosh PostgreSQL Server

The following instructions are used to set up a Macintosh PostGreSQL server for use with the Theatre Manager application. Click if you are doing Windows setup

The server needs to be set up on one machine and the application can be set up on as many machines as you wish.

Follow these steps if you are using the TheatreManager TM PostGresSetup installer program and you may want to bookmark this page in your browser in case you want to refer to these installation steps. If you are only installing a demo, refer to the last column for required steps.

task	Description	Full Install	Demo
1	If the server is OSX-LION (10.7.0 or later) and there are two drives in the computer (you will probably see two disk icons on the desktop or look in disk utilities), we strongly recommend STRIPING the two drives due to a very significant performance improvement. If you have this situation (very common in Mac Mini Servers), refer to this page to prepare the hardware and reinstall the operating system as Lion is not shipped with the drives striped. The steps are similar for those using OSX 10.6.x or earlier - but you will have install DVD which makes is a bit easier. The process is similar.	yes	no
2	download the PostGres installer for Mac	yes	yes
3	the installation of the PostGres SQL server	yes	yes
4	installing the demo database and the main TheatreManager User	optional	yes
5	configuration of the server parameters for maximizing performance in a production database	yes	no
6	creating a daily backup job in using cronnix to run the backup	yes	no
7	Turn off energy saving, airport and other energy saving features	yes	no

Notes and Assumptions:

This install process assumes you have NEVER installed PostGres or Theatre Manager on your computer before. If you have, you may need to refer to Updating Postgres Instructions
You MUST turn all virus protection while running the installer (especially Norton if you are using it). Virus software always interferes with proper software installation.
If this installer is being used to create a demo installation, then you only really need do steps 1, 2 and 3.
This process assumes that you have never installed Theatre Manager or Postgres on your machine. If you have already installed Postgres:
- you will be asked if you want to un-install Postgres (you may want to do that and then try to re-install after)
- you may need to remove the 'postgres' user from your computer if one exists, unless you know the password for the use.

Mac OSX-Lion Multiple Drive Setup and Install

The steps below are an outline of how to setup an Mac Mini (or Mac Pro) with OS-X Lion Server that has two or more drives.

The steps outlined are similar if you have a Mac Mini or Mac Pro running OSX 10.6.x or earlier. You will have and install DVD and can do most of the steps without connecting to the internet.

The general process to prepare such as machine for optimal performance is:

Stripe the drives
re-install lion server
Install Postgres as normal.
Restore a database to that server

The steps have a degree of technical complexity and assume you are able to interpret and adapt to variations in the process yourself.

Striping a Mac with two or more drives under Lion

Since Lion does not come with any install disks, you will need to follow a special procedure to stripe the drives on a lion servers as follows:

Make a backup of any data on the computer that you deem important if you have been using it for a while
Connect the Mac Mini/Mac Pro to the internet, a monitor and keyboard. The internet is important as it will be required to restore the operating system.
restart the mac with the Apple and R keys held down. This will boot from the internal lion partition on the drive.
When the machine responds with the OS-X operating system installer screen, select the language of your choice and begin the install
The next screen will be the utilities window. Select Disk Utility and:
- After selecting a drive, click on the the 'RAID' tab.
- Select 'STRIPED RAID SET' for the 'RAID Type' prompt. The default is a mirrored drive set, so be careful otherwise you might end up starting over.
- Drag both drives into the Raid Stripe and verify that the estimated size of the RAID set is the total of both drives.
- Create the Raid Striped Set.
- After confirming you want to do this, partition utility will bring the raid array back online.
- Quit 'Disk Utility'
On the main window, you should now select 'Reinstall Mac OSX Lion' (optionally, you could restore from a time machine backup). Follow any/all prompts and reinstall Lion. It may say that file vault or some other features are disabled. So be it -- its the price of gaining a lot of performance from the dual drive server.
The download from the Apple web site may take a while, so let the installer do its thing.

Post Installation Steps

After installing and configuring, download any updates using 'Software Update'. You may need to repeat this step until there are no more updates available.
Configure the server with a static IP address
Disable Airport, Spotlight indexing, Time Machine and disable other power saving settings
Setup the Firewall as required
Ensure the backup script is setup and thoroughly tested and that there is a step that can successfully FTP the database to another machine (or copy it using the unix 'cp' command). If a hard drive in the striped raid set dies, none of the data on any drive is recoverable so OFF-MACHINE backups are mandatory.

Step 1: Install PostGreSQL database server

When you run the installer for the database components, it will put the actual installer files into the Macintosh HD/Users/Shared directory along with all the support files needed for the rest of the steps.

At the and of the TM PostGres file installation phase, the actual Postgres install process will begin immediately. If you cancel the setup of PostGres, you can always start it again by repeating the process from the start.

Installing Postgresql on a Macintosh

1.	Double click on the TMPostgresSetup application. You will be asked to authenticate the installation by OS-X. Please type the appropriate user id and password.
2.	Click 'Continue' on the splash screen
3.	Read the licence agreement and click 'Continue' to accept it.
4.	Click 'Agree' on this screen
5.	Click 'Install' on this screen.
5.	Click 'Install' on this screen.
6.	You will see the installer complete the work.
7.	When it is done, you will see a notice that postgres was installed. Click 'Continue'
8.	Click 'Quit' on this screen and restart the computer.

Step 2: Create user and import Database

The installation of postgres in step 1 should have imported the demo database for you. If you start Theatre Manager and cannot see a demo database, you may need to perform these steps.

In most circumstances, you can skips this and the remainder of the steps if you are only installing a demo. If you are not, you will need to proceed to steps 3 and 4.

Installing a demo if one was not installed

The database server needs a specific user called TheatreManager with specific privileges that will be assigned as the owner of each database. We also want to import a demo database. This step assumes that you have installed things into the /Users/Shared directory. If you did not, then you will need to edit the script and do this step manually

1.	Go to /Users/Shared directory. You should see some files and folders with names that look like below.
2.	Start terminal and change the user to 'postgres' by typing: su - postgres Press RETURN and then type the postgres user's password (password will not display anything)
3.	Drag the script '/Users/Shared/CreateDemoDB.sql onto the terminal window. This shortcut saves typing anything. Click into the terminal window and then press RETURN to start the command. If it does not run, then possible issues are: You need to have execute permissions on the 'CreateDemoDB.sql' script. Use File Examiner to check or fix that (or use unix chmod commands to give permission). Make sure that postgres was configured with 'trust' permissions for the local machine. Make sure that postgres was installed into the /Library/Postgresql8 directory.
4.	The script will run and load up the TheatreManagerDemo database. You can modify this script to load up a customer database if necessary by editing it in BBedit or in textedit (make sure to save it as text if you use textedit - its preference, unfortunately, is to save as an rtf document). Note, any WARNING messages from the TheatreManagerDemo database creation can be ignored. These warnings are normal.

Step 3: Configure the PostGreSQL server parameters

When you are able to connect to the database using Theatre Manager, its time to tune some of the parameters for PostGresSQL that are specific to your machine and setup. On a Mac, this needs to be done with VI under the postgres user account.

The general steps are:

Edit the pg_hba.conf file to indicate which IP addresses may talk to the database server
Edit the postgresql.conf file to adjust some memory settings for performance
Edit the .bash_profile if you plan on using terminal to access the database on a frequent basis
This step is optional to do and is done for you automatically when you run the Theatre Manager postgres database installer
Change the shared memory settings on OS-X to allocate more memory to the database server.
This step is done automatically for you if you have used the Theatre Manager postgres database installer and you have more than 2 gigs of ram on the sever

PG_HBA.conf file

What does this file do? This file controls all access by users to the PostgreSQL server.

In order for clients to connect to the server, their IP address must be in the allowed list of users. The two common authentication methods that you will see being used for Theatre Manager clients are MD5 and trust.

MD5 does md5 password authentication and should be used for just about all entries to this file.
Trust allows clients to connect without password authentication, the only 'trust' entry should should be for the local server machine and/or localhost.

1.	The first changes to make involve the pg_hba.conf file and the postgres.conf file. To do so, you'll need to use VI (a text editor) and be the postgres user in terminal. To do this, start terminal and type su - postgres enter the password
2.	Go to the postgres data directory by typing cd data
3.	Edit the postgres pg_hba.conf file that contains the addresses to listen on. Type VI pg_hba.conf You should see a page of information. If you do not, then type 'Shift Q' and then just a 'q' - after which you can start the process over. If you see the list of text similar to the right, then: Use the down arrow on your keyboard to go to the very end of the file
4.	When you are at the end of the file, use the up arrow on your keyboard so that you are right after the first line in the IPv4 settings there it says 'host all all 127.0.0.1/32 trust'. In the example the cursor is on the '#' on the line after. type the I key and the message at the bottom will change to Insert.
5.	Edit the pg_hba.conf so that its final settings are similar to the window on the right. Type directly into Terminal so the data looks like the window above. Use the delete key to get rid of characters. You will likely end up typing the following lines where the first one is your subnet. This is the most typical example we've seen at venues host all all 192.168.1.0/24 md5 NOTE: For the 127.0.0.1/32 option, edit the handshaking to be TRUST at the end of the line to allow backups to run unaided. NOTE: If your machine uses IPv6 (the new internet IP setting standard), you may also need to set ::1/128 to be TRUST instead of md5. If so, edit that line to look like: host all all ::1/128 trust NOTE: If you are running version 9.0 or higher of postgres in a Mac environment the local all all line should be set to TRUST. NOTE: You may also need to edit the local all all line from md5 to TRUST. This can be determined if the backup script will not run without a password after changing the settings above for 127.0.0.1 and ::1/128. The line you added (or need to add) are for: The local subnet - as in 192.168.9.0/24 Other subnets that need to access the data - as in 10.1.5.0/24 Any single machines that must have access - as in 55.66.77.88/32 (via VPN) At the end of the subnet, the /24 refers to a complete subnet when you want any machine on the subnet to access the database. This is what is used most often. The /32 refers to a particular machine. If you use this option, you will need to provide the exact computer IP that you want to allow to access the database.
6.	At the end, type, in this order: hit the 'esc' key (the insert mode will dissappear) Shift Q (the window will show the 'Entering Ex mode' message) wq and the window will clear. Reference for settings in the pg hba.conf file www.postgresql.org/docs/9.0/interactive/auth-pg-hba-conf.html

POSTGRESQL.conf file

The postgresql.conf file contains parameters to help configure and manage performance of the database server. You can use most parameters as installed out of the box, but the server will go much faster if you alter about have a dozen key settings.

You will need to start by changing to the postgres user in Terminal. To do this, start terminal and type

su - postgres

enter the password

Go to the postgres data directory by typing

cd data

This will also use VI to edit it. Type

VI postgresql.conf

when the list appears, type

to go into insert mode and use the up and down arrows to find the options below

Find and edit the parameters in the list below and change them to the suggested values, if they are not already set to that value.

if any line contains a '#' at the beginning and you need to change that line as per the instructions below, make sure to remove the '#' as it uncomments the parameter. If there is no '#', then just change the values.

For any setting that is about disk space or memory, you can type 1GB, 1000MB, 1000000KB and they are the equivalent. Do not leave a space between the number and the memory amount at the GB, MB or KB otherwise postgres will not start.

listen_addresses	This value should always = '' It will probably look like #Listen_address = 'localhost'. Remove the # from the front of the line (if any) to activate that parameter and change 'localhost' to ''
bonjour	If you wish your postgres server to be discoverable using bonjour services so that the mac version of TM can automatically locate a server on the network, this value can be uncommented and changed from off to on It will probably look like #bonjour = off. Remove the # from the front of the line (if any) to activate that parameter and change off to on
max_connection	This is the number of simultaneous connections. If it is not at the default of 100, please change it to be 100.
maintenance_work_mem	This value should be 50MB for machines with 1 GB of RAM or more and 20MB for those with less. Enter values as xxMB.
shared_buffers	This value should be 20-25% of the total system total RAM. You find this value on the task manager as the total phyiscal memory. Enter values as xxMB.
temp_buffers	This value should be 50MB for machines with 2GB of RAM or more and 20MB for those with less. Enter values as xxMB.
work_mem	This value should be 50MB for machines with 1GB of RAM or more and 20MB for those with less. Enter values as xxMB.
effective_cache_size	This value should be about 512MB on a 4GB system, perhaps 1024MB on a larger system. Set the Shared memory first. In all cases the total of shared + effective must be less than available ram.

Reference for postgres.conf file parameters http://www.postgresql.org/docs/9.0/interactive/runtime-config.html

Once the changes are made, type, in this order:

hit the 'esc' key

(the insert mode will dissappear)

Shift Q

(the window will show the 'Entering EX mode' message)

and the window will clear and you will be back at terminal

.bash_profile in terminal

The .bash_profile settings should to be entered in the terminal session for the postgres user to establish the commonly used paths for the database and the executable binary files. This is always setup if you are using the Theatre Manager installers for postgres. If you install your own version of Postgres, you may need to do this IF YOU PLAN ON USING TERMINAL to interface with postgres on a frequent basis.

Otherwise, this step is not required under most circumstances.

Start Terminal and navigate to the home directory for postgres user.

Type:

cd /Library/PostgreSQL/9.1

Next, lets update the profile for terminal to that it makes life easier in postgres from this point on. Type

vi ~/.bash_profile

it will open with an empty window as below.

bashProfile

Just like when using VI for the other two files, type:

to put you in insert mode

Type the two lines into the file exactly as shown. When done, type, in this order:

hit the 'esc' key

(the insert mode will dissappear)

Shift Q

(the window will show the 'Entering Ex mode' message)

and the window will clear and you will be back at terminal. The next time you start terminal under the postgres user account, you will have access to the postgres commands and data directory in a more convenient fashion.

OS X Shared Memory Settings

Why do I need this?: This is done so that you can increase the shared_buffers parameter to more than the standard 128MB. Shared buffers allows PostgreSQL to use much more memory and can improve performance quite dramatically.

If you used the Theatre Manager postgres installer, it will detect if you have more than 2 gigs of ram in your machine and automatically insert these into the /etc/sysctl.conf file for you. After installing postgres the first time, simply reboot the server - these settings will have been done for you and you need not continue with the steps below.

BEFORE STARTING: Ensure you are in terminal at your own user, not postgres. This is a fairly technical topic and the reference: http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html

Open terminal again and type: su - [Your UserName]

Type

vi /etc/sysctl.conf

Type I to insert data

Add the lines:

kern.sysv.shmmax=2202009600
kern.sysv.shmmin=1
kern.sysv.shmmni=64
kern.sysv.shmseg=16
kern.sysv.semmns=130
kern.sysv.shmall=550502400
kern.sysv.maxproc=2048
kern.maxprocperuid=512

These settings reflect the maximum size of a shared buffer. These settings assume you are running at least 2GB of RAM. If you have less RAM, these settings may need to be altered. shmmax is the key setting; it is in bytes. If the machine has more memory to use, then this could be increased as well. shmall is the shmmax setting divided by 4.

Hit the Esc key

Hit Shift Q

Type wq

Hit Enter

Restart OS X after doing this. This is required to apply the changes.

sysctl There is a sample of this file in /users/Shared/ from the install. If your machine has 2GB of RAM or more, you could move it to the right folder instead by:

cd /users/shared sudo mv sysctl.conf /etc

Restart OS X after doing this. This is required to apply the changes.

Step 4: PostGreSQL server backups

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

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:

Create a directory called /Users/Shared/Backups
Get a copy of the file backupTM.php and place them into the /Users/Shared directory

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.

Navigate to the /Users/Shared directory and find the 'backupTM.php' file.
Right click on the file and edit with Text Edit
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.
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';
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.

Test the Batch Script

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:

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
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

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.

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.

Update or Remove PostGreSQL

If you have already installed the Postgres database engine on a server and need to update it, then follow the update steps. Removing and installing postgres will cause all data to be lost, so you must have just made a backup before starting.

These steps are valid if you are using version 9 of Theatre Manager or later.

Updating Postgres

Download the latest postgres installer from the Artsman web site. Once you have it, make sure you have done the following steps:

Check the version of postgres you are running. This is in the 'About Theatre Manager' menu. Verify that the version is at least 9.1 by looking at the bottom left of the about screen. You will see your database name followed by (9.1.x). If you have version 8.3.x, 8.4.x, or 9.0.x do not attempt the upgrade process without consulting Arts Management Support.
If you have version 9.1 or later, log everybody out of Theatre Manager, including web listeners
Make sure you have made a backup of the database, using the procedures in the daily backup job process
Once you have confirmed the backup exists and have made another copy of that in a different place (just to be safe), then follow the specific instructions for your platform.

Remember, do not attempt to try this unless you just made a backup of your database. Preferably, you should also have restored that backup on another machine for safety, logged into it using Theatre Manager to prove that you can restore a backup and that it has 100% integrity.

Make sure you are running postgres version 9.1 or later.
Refer to downloading the latest Mac Installer for postgres
Make sure you have just made a backup of the databases in the server
use terminal and PG_CTL to 'stop' the database
a) start terminal
b) type su - postgres
c) provide the password
d) type pg_ctl stop -m immediate
Run the installer which will update and restart an existing PostgreSQL installation
try log in to Theatre Manager afterwards

Upgrading older Versions of Postgres

If you have postgres 9.0.x (or older), the upgrade process involves some extra steps and can be done by Arts Management Support team if you are not comfortable following the steps below. These are:

making a backup of the database
recording the PG_HBA.CONF and the POSTGRESQL.CONF settings unique to this server
stopping the server
Removing the old postgres server and data directories by running the un-installer found in the /librapry/postgres/x.x directory.
installing the new postgres server using the Theatre Manager postgres installer
creating the new database with the owner 'TheatreManager', and using encoding "UTF8'. If the TheatreManager user is not in the database, contact support right away and do not continue.
Importing the new database
redoing the configuration parameters for postgres for PG_HBA.CONF and the POSTGRESQL.CONF
setting up the backup job again
You should peek in the /Library/LaunchDaemons folder. If you are running postgresql 9.1 or later and there are files there called
com.edb.launchd.postgresql-8.3.plist
com.edb.launchd.postgresql-8.4.plist
com.edb.launchd.postgresql-9.0.plist
Please delete those file and restart your machine. If you uninstalled the older servers properly, they should not be there. Do not delete the file corresponding to the current version of the server.
Make sure that postgres 9.1 is still running

Removing an Installation of Postgres

Stop the postgres database using the 'pg_ctl stop -m immediate' command
When the server is stopped, use the un-install program in the /library/postgres/x.x directory to get rid of it properly
throw out the entire folder called /Library/PostgreSQL
restart the mac

Server Power Saving Settings

Additional Setup Considerations

The following settings should be made on all servers (postgres, apache and web listeners) that are installed on Macintosh.

1	Make sure to turn off all energy saving options such as prevent hard disk sleep, do not allow the CPU to go into low processor mode, etc.
2	Make sure to turn airport OFF if the Mac comes with it. Airport will cause the mac to temporarily freeze while it looks for a network to connect to - and will lock out sales while it does that. This is done by opening the control panel, clicking on the airport interface and then clicking on the 'gear' at the bottom to select the option 'make service inactive'. If you do this, the status will go from 'off' to 'inactive'
3	Make sure to physically plug in a Monitor to the Mac -or- connect a KVM switch into it that is powered up. If you disconnect a monitor from the Mac, it will stop working as a listener and web sales appear frozen. (it is an OSX thing)
4	Turn off Spotlight Indexing on Snow Leopard using sudo mdutil -a -i off On Lion, use the following command sudo launchctl unload -w /System/Library/LaunchDaemons/com.apple.metadata.mds.plist This will prevent the operating system doing un-neccessary work while serving web pages. you will know if Spotlight is running on a server if you see the 'mds' application running. It can take a lot of CPU.
5	Do not use the postgres server for Time Machine backups - use the backup script and move the backups to another machine

Windows PostgreSQL Server

The following instructions are used to set up a Windows PostGreSQL server for use with the Theatre Manager application. Click if you are doing Mac or Unix setup.

The server needs to be set up on one machine and the application can be set up on as many machines as you wish.

task	Description	Full Install	Demo
1	download the PostGres installer for Windows	yes	yes
2	the installation of the PostGreSQL server. Please make sure to read any caveats for the version of Windows you are using.	yes	yes
3	installing the demo database and the main Theatre Manager User	optional
4	configuration of the server parameters for maximizing performance in a production database	yes
5	creating a daily backup job in Windows Task Scheduler to run the backup	yes
6	Considerations for installing virus protection on the Postgresql server - please do not include the posrgres data folder.	yes
7	Turn off Microsoft disk indexing on the volume that the database is running on.	yes

Notes and Assumptions:

This install process assumes you have NEVER installed PostGreSQL or Theatre Manager on your computer before. If you have, you may need to refer to Updating PostGreSQL Instructions
You MUST turn all virus protection while running the installer (especially Norton if you are using it). Virus software always interferes with proper software installation.
If this installer is being used to create a demo installation, then you only really need do steps 1, 2 and 3.
This process assumes that you have never installed Theatre Manager or PostGreSQL on your machine. If you have already installed PostGreSQL:
- you will be asked if you want to un-install PostGreSQL (you may want to do that and then try to re-install after)
- you may need to remove the 'postgres' user from your computer if one exists, unless you know the password for the use.

Step 1: Install PostGres Database Server

Caution: Please read to see if this applies to your installation:

Windows Small Business Server	For Windows Small Business Server, you MUST turn off 'disk quota' for all users prior to installing PostGres (and leave it off). Otherwise you may run out of space for the installer and any databases that get installed. Double-click on My Computer Right-click on C: drive (or the drive letter that Postgres is installed under) Select Properties from the popup context menu Click the Quota tab Turn OFF "Enable quota management" Click Apply Click OK Reboot your computer
VISTA or Windows 7	For VISTA/Windows 7, you may need to turn off UAC (user access control) because it is acting as a server. You can run Theatre Manager on other workstations with UAC on. To turn UAC off for a server: open the control panel click user accounts click the link that says 'turn User Control On/Off' and in the next window that shows up, turn it off reboot your computer.
DOMAIN CONTROLLER	If you want to run postgres on server that is acting as a DOMAIN CONTROLLER, you will need to: Log in as the LOCAL machine administrator, otherwise the database server will not install If you are running 2008 server as a Domain Controller, you may need to have remote assistance to do this.
Microsoft Windows Server 2008	If you are running Microsoft Windows Server 2008, the following steps should be followed when running the installation programs: Do not install PostgreSQL using a generic user account that has Administrative privileges. Use the actual Administrator user's account for this installation process otherwise passwords for the postgres user may not get set correctly and the installation process may fail. You may need to log into the server as the "Local" Administrator account, if there is a Domain server within the network. This is required when the passwords for the postgres user are not being accepted and password security setup errors are shown during the postgres user creation process. If there is a domain server, prior to installing postgreSQL you may need to manually create the postgres user on the Domain server. During the PostgreSQL installation process we have found that it references the Domain Server's user accounts at the same time as using the "local" postgres user account. If the Domain Server does not have the postgres user account, the final step in the PostgreSQL installation process where the initialization of the database cluster happens, may fail. It appears that the "local" postgres user account is referenced for starting/stopping the PostgreSQL database, however the Domain Server's postgres user is referenced when the database cluster is first initialized. Right-Click on the installers and select "Run As Administrator" and "remove" the option to run with restricted access In some cases, the PostgreSQL installer may need be run outside of the TMPostgresSetup installer (Run as Administrator) if the passwords for the postgres user are not being accepted and password security setup errors are shown during the postgres user creation process.
XP, 2000, or 2003	There are no known caveats with XP, 2000. With Windows 2003 server, you may need to refer to the the Microsoft Windows Server 2008 - depending on how your 2003 server is configured.

Run Main TMPostgresSetup Installer

When you run the installer for the database, accept all the defaults.

Click OK	Right click on the TMPostgresSetup.exe application and use Run As to begin the install. Select a LOCAL administrator as the user ID to use for the install. If a checkbox that implies "Protect My Computer" or "Run with Restrictions" is available and enabled, uncheck the box to allow the installer to run with full install privileges.
Click Next
Click Next
Click Next
Click Next	The installer files and all the support files needed for the rest of the steps are put into a C:\BoxOffice directory. Note: If you have multiple drives, you may want to install the database server on a larger data drive like D:\BoxOffice
Click Next
Click Yes	At the end of the TM PostGres installer, you are asked if you want to install the PostGreSQL database in the dialog (as in below). If you say yes, follow the instructions to install postgres in the next section. Alternatively, you can install them later manually by: Go to C:/BoxOffice Click the PostGresInstall Icon Follow the instructions to install postgres.

Run Postgres Installer

Accept all the defaults on the screens that follow except the last one that references 'stackbuilder'.

Click Next

To install PostGres on another drive instead of the C: drive, click the Browse button and select another drive.

If you change this location, you must also change the backupTM.bat files later to refer to the other drive.
If you changed the install location to D:\BoxOffice, this would have already changed for you in this window

Be aware that the standard install location depends on the 32 or 64 bit version of the operating system.

If you are using 32 bit server, the standard install location starts with C:\Program Files\PostgeSQL ..
If you are using 64 bit server, the standard install location stars with C:\Program Files (x86)\PostgreSQL ...

Click Next

Accept the default for the ' Account Password' for the postgres user. However, if this is a demo. In that case, please pick a user password that you remember - we suggest 'Master'.

For a purchased version of Theatre Manager, this will be set up for you by your trainer who will use a specific AMS password for this server that should not be changed.

Click Next

Leave the Port Number as 5432 (if you change the standard port, you will also have to change it in Theatre Manager login window)

Click Next

Leave both these settings as shown.

Click Next

You will need to wait for a bit while the database server is installed

Click Finish

Uncheck the 'Launch Stackbuilder at Exit' setting. There are no additional modules to install into your database at this time.

At this time, the database should have installed successfully and should be runnning.

Load Demo Database

You will be given an option to install a demo database. If you would like to do this, click 'Yes'. It is recommended that you do.

Click Yes

Wait

Wait while a DOS window pops up and shows the progress of the demo database being imported. Depending on the performance and RAM in your machine, this could take a few minutes to finish.

When the DOS window closes, the database server is installed, and the TheatreManagerDemo database is imported.

Step 2: Create user and import Database

Only perform this step if you did not install the demo database when installing server.

After the database server is installed, You need to create a specific user called TheatreManager and give them privileges. You also want to import a demo database. This step assumes that you have installed into C:\BoxOffice. If you did not, then you will need to edit the .bat files and do this step manually.

Go to C:\BoxOffice directory. You will see some files and folders with names similar to below.

Double click on the 'ImportDemo' bat file. This starts a DOS prompt and start the bat file running.

If the server is 64 bit, you will need to change the ImportDemo.bat' file to refer to C:\Program Files (x86).

If you have altered the install directory, you will need to change the path name to point to the location that Postgres was installed in. Often, this is just changing the drive letter.

You are asked for the password to create the 'TheatreManager' user. Type the password you used for the installation of the database in the preceding section. If this is a demo database install, this may have been 'master' you used when installing the server.

The password is not be echoed back to you and you will not see the cursor move. There is no visual feedback that even a character was typed. You'll just have to get it right. If any of the steps are not right, you can start at the top of this step at any time.

import2

You are asked for the password to create a 'TheatreManagerDemo' database. Type the same password used above and elsewhere in the install instructions.

import3

You are asked again for the password to import data into the TheatreManagerDemo database. Type the same password again and you will see a lot of lines displayed to you after that point as the demo database is imported.

step4

Step 3: Configure PostGreSQL server parameters

When you are able to connect to the database using Theatre Manager, its time to tune some of the parameters for PostGresSQL that are specific to your machine and setup.

Start the PG Admin III database management application. This is found using Start Menu->Programs->PostGresSQL-> PG Admin III.

PGAdmin

If you get any helpful tips, click 'close' to get rid of them.

Click on the server for this machine and login. Use the password you created when installing the database server

Double Click on the server name as per the diagram to the right

Type in the same password that has been used elsewhere in the install instructions. For demo database, the suggestion was 'master'.

For production databases, this wll be different.

Then click 'ok'

You should see a list of objects in the server.

On the 'Databases' line, there should be (2) if you have imported the database or created your own database.

Click on the Databases line to begin the next step of configuration.

edit the pg_hba.conf file.

Go to the Tools menu and pick Server Configuration->pg_hba.conf file.
Edit the pg_hba.conf so that its final settings are similar to the list below below (see *** a few lines down).
	The procedure for editing is done by double clicking on an empty line and typing in the proper values for your venue - one line at a time. Make sure that: Enabled is checked Type is 'host' Database is 'all' User is 'all' IP Address is described as below. You will need at least the local subnet four your network. This example shows the entry for the 192.168.0 subnet Method is md5 - this is the handshaking/encryption scheme used by the clients to talk to the server. NOTE: for the 127.0.0.1/32 option, set the handshaking to be TRUST to allow backups to run unaided. NOTE: on Windows Vista and/or if the machine uses IPv6 (the new internet IP setting standard), you may also need to set ::1/128 to also be TRUST Add lines for: The local subnet - as in 192.168.9.0/24 Other subnets that need to access the data - as in 10.1.5.0/24 Any single machines that must have access - as in 55.66.77.88/32 (via VPN) At the end of the subnet, the /24 refers to a complete subnet when you want any machine on the subnet to access the database. This is what is used most often. The /32 refers to a particular machine. If you use this option, you will need to provide the exact computer IP that you want to allow to access the database.
*** At the end, the final hba file should look similar to the list at the right. It may have more lines in it for larger venues with multiple subnets or for remote computer access.
Once the changes are made, click the Save icon to save the changes. Then on the main menu, select File >> Reload Server.
You will be asked to confirm that the changes. Click Yes.
Click the close box and you will be asked if you want to save your changes. Click Yes.

Edit the postgesql.conf file

Go to the Tools menu and pick Server Configuration-> postgreSQL.conf file.

You will then see a list of properties of the database server that can be configured.

Unfortunately, they are not in alphabetical order, so you may need to scroll up and down to find the ones that are in the list below. We've tried to put them in the order that you will find them in the config file. (see *****)

Do not change any parameters other than the suggested ones, or unless you have been advised to do so by an expert in PostGres databases.

To edit any one of the lines, scroll to find it and then double click on it.

Most of the parameters will tell you something about them. The key values to edit are:

The 'Enabled' flag. If you want to turn a parameter on, then click enabled
Value - is what you want to set the parameter to. There are specific values for some of the parameters as described in the table below
Comment - this may exist as a description of what the parameter is.

***** Find and edit the parameters in the list to the right and change them to the suggested values, if they are not already set to that value.

listen_addresses	This value should always = '*'
effective_cache_size	This value should be about 512MB on a 4GB system, perhaps 1024MB on a larger system. Set the Shared memory first. In all cases the total of shared + effective must be less than available ram.
maintenance_work_mem	This value should be 50 MB for machines with 1 GB of RAM or more and 20 MB for those with less. Enter values as xx MB.
shared_buffers	This value should be 20-25% of the total system total RAM. You find this value on the task manager as the total phyiscal memory. Enter values as xx MB. Note: if you are using 64 bit server, the current maximum for this parameter seems to be 1024MB. If you make it more than that, please check that Postgres will start up when the server is rebooted. If not, adjust this parameter so that it is 1024MB or lower.
temp_buffers	This value should be 50 MB for machines with 2GB of RAM or more and 20 MB for those with less. Enter values as xx MB.
work_mem	This value should be 50 MB for machines with 1GB of RAM or more and 20 MB for those with less. Enter values as xx MB.

Note: the best place to get memory values is from the 'Activity Monitor' on the 'Task Manager'. See an example below for what this screen looks like.

To find it, right click on the task bar and pick 'Task Manager'.

Once the changes are made, go to the 'File' menu and pick 'Reload Server' (alternatively, use the green arrow on the toolbar that is the 3rd icon from the right).

You will be asked to confirm the changes.

Step 4: Database Backups

However, it is perfectly ok 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. Backups can run while Theatre Manager is being used.

Manual Backup

You can also do a manual backup at any time by double clicking on the 'BackupTM.bat' file mentioned in this section - again, even if Theatre Manager is running. It is generally found in the C:\BoxOffice folder, although it may be on another drive on the database server. The location of this file is where you placed it using the instructions on the rest of this page.

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 two files and then:

Create a directory called C:\BoxOffice (or on D: or E: .. as appropriate)
Create a directory called C:\BoxOffice\Backups (or on D: etc.)
Get a copy of the files backupTM.bat and realdate an place them into the C:\Boxoffice directory (or D: etc.)

If the files are in the correct place, then we will need to edit the BackupTM.bat file. Note, if you have multiple databases to backup, make a one copy of this file for each database you want to backup and edit accordingly.

Navigate to the C:\BoxOffice directory and find the 'BackupTM.bat file.
Right click on the file and edit with WordPad or NotePad.	change the line set DATABASE_NAME=TheatreManager to be set DATABASE_NAME=xxxxx where xxxxx is the name of the customer database set up in postgres. Note that the name of the database is case sensitive and must match what is seen in PGAdmin III, or what you used to log on to the database.
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: set BOXOFFICE_DIR=C:\ .... to D: etc normally, these are not changed
Close the batch file and save the changes.

test the batch file by double clicking on the TMBackup.bat to see that it runs. You may be asked for a password. if so, enter it and you should see a bunch of feedback as the database is backed up. If you do have to enter a password, refer to #4 (below)

backuprun

When done and the DOS window closes, go to the C:\BoxOffice\Backups directory and see if there is a recent backup for your database. Note that there shold be some size to the database backup.. is should not be zero bytes. In this sample, we have the original demo and a backup made on 20070913 at 12:40:04 in the afternoon. There will be a new file here each time the backup is run.

backuplist

If you entered a password to make the backups run, then you need to tell the postgres to allow 'Trust' permissions for the local machine so that backups will run un-aided.

On XP, you may just need to provide trust access to 127.0.0.1/32. On Vista, you may need to provide 'trust' access to ::1/128 as well. Refer to the section on editing the pg_hba.conf. file

Creating a Daily backup Job

Create a timed backup for the database by going to Start->Settings->Control Panels->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, then you may wish to change the control panel view to 'classic' mode. Somehow, this just seems easier to find things.

scheduledTask

Double click on the Scheduled Task icon to begin the setup process

Click the 'Add Scheduled Task' icon. This will start the Scheduled Task Wizard.
Click Next
On the list of applications, click the 'Browse' button.
On the 'Select Program to Schedule' dialog, navigate to the C:\BoxOffice folder and click on the 'BackupTM' icon. Then click 'Open'
You will then have the ability to pick a frequency for the backup. Daily is suggested, then click 'Next'
Pick the time that you want the backup to run. 2:00 am is as good a time as any. Then Click Next
Enter the password for the administrator of the machine. Note that this is not the same as the password for the postgres user Then Click Next
Click 'Finish' to save the job. There is no need to go to the advanced properties.
If the task is created in Windows 7 or Windows Server 2008 the Wizard will be slightly different. When creating the Schedule Task on these platforms the option for Run With Highest Privileges must be checked to ensure the database is updated with the backup size and the vacuum process runs.

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.

Update or Remove PostGreSQL

These steps are valid if you are using version 9 of Theatre Manager or later.

Updating Postgres

Download the latest postgres installer from the Artsman web site. Once you have it, make sure you have done the following steps:

Check the version of postgres you are running. This is in the 'About Theatre Manager' menu. Verify that the version is at least 9.1 by looking at the bottom left of the about screen. You will see your database name followed by (9.1.x). If you have version 9.0.x or earlier, do not attempt the upgrade process.
If you have version 9.1.0 or later, log everybody out of Theatre Manager, including web listeners
Make sure you have made a backup of the database, using the procedures in the daily backup job process
Once you have confirmed the backup exists and have made another copy of that in a different place (just to be safe), then follow the specific instructions for your platform.

Make sure you are running postgres version 9.1.0 or later.
Refer to the overall instructions to download the latest TMPostGresSetup installer.
run the TMPostGresSetup installer.
1. This will place all the install files into the C:\BoxOffice folder.
2. Do not install the latest version when asked by the installer
3. Let the installer complete and quit out of it.
Go to C:/BoxOffice and find the latest version of postgres. It will have a file with a name similar to the one below - with a different version number on it reflecting the latest one. As of November 2011, the current shipping vesrion will be 'Postgresql-9.1.1-1' or later.
Double click on it.
It will start up the installer and default most of the settings based on the existing installation.
Follow the instructions and you will generally only need to use the 'next' key to continue (a few times) until the upgrade (or install) begins.
At the end you will need to restart the server.
Check that you can log in to Theatre Manager from the serer or any workstation.
1. If the Postgres Service did not start, make sure that the Postgres User password (system settings or active directory) is the same as the password for the Postgres Service in the services panel
2. If Postgres did startup, you are done.

Upgrading older Versions of Postgres

If you have postgres 9.0.5 (or any earlier version), the upgrade process involves some extra steps and can be done by Arts Management Support team if you are not comfortable following the steps below. These are:

making a backup of the database
recording the PG_HBA.CONF and POSTGRESQL.CONF settings unique to this server
stopping the server
deleting the old postgres server data directories
installing the new postgres server
creating the new database (owner 'TheatreManager', Encoding "UTF8')
Importing the new database
redoing the configuration parameters for postgres (item 4 at the top of the page)
setting up the backup job again

Removing an Installation

Preferred Method:
1. Go to Setup->Settings->Control Panel->Add/Remove Programs
2. Find the line that refers to your Postgres installation
3. Use the remove option
4. Delete the C:\Program Files\PostgreSQL folder to remove your database
Secondary Method:
1. Run the PostGres installer and 'uninstall' the database first, and then run the install process -and/or-
2. Delete the C:\Program Files\PostgreSQL folder -and/or-
3. Remove the 'postgres' user from you list of users using the admin tools -and/or-
4. make sure that the PostGres server is not running as a service and has been removed using RegEdit

Postgres User Password

Postgres is installed using a secure password for the 'service' user under windows. It may be changed by the venue if they wish and if you do so, then you may need to:

Find the local 'postgres' user on the computer using the Windows Administrative tools an edit the user id. You can change the password the way you would normally change the password for any other user (and is dependant on your version of windows)
If you change the service password, then you must also open the 'services' control panel and find the postgres service. Change the password there as well. To confirm that you got it right, please stop and start the postgres server. if it stops and starts, its safe to bet that the server will restart on the next reboot of the server.
Normally, the backups are set to run as administrator using the task scheduler. However, if you altered them to run as the 'Postgres' user, then please change the password under the task scheduler as well - other wise you'll get messages that backups are not running.

Turn off Indexing and performance

On a windows machines:

Turn off auto-updates completely. These are something to be done manually and on a periodic scheduled basis. You do not want servers restarting in the middle of the night, nor do you want downloading to affect performance of your servers.
make sure to look at the properties of the drive that database is on and turn of microsoft indexing for all directories and sub directories.
Disable any disk quota management on the disk drive
turn off any virus scanning for ports used by postgres
turn off any virus scanning against the database directory which is usually
C:\Program Files\PostgreSQL
or
D:\Program Files\PostgreSQL (if there are two drives)
turn off any energy saving options
do not use the machine for saving snapshots of files by turning off 'use shadow copies' on the appropriate drives. (This is set in the properties of the drive)

Leaving any of those on will affect performance of the server for the database

Linux PostgreSQL Server

The following instructions are used to set up a Linux PostGreSQL server for use with the Theatre Manager application. Click if you are doing Windows setup or Click if you are doing Macintosh setup.

As of Oct, 2010, the current minimum acceptable version of Postgres is 8.4.4 (or later). Theatre Manager has been tested with version 9.0 and works with the bytea_encoding set to escape (see parameters in step 4).

The server needs to be set up on one machine and the application can be set up on as many machines as you wish.

Follow these steps and you may want to bookmark this page in your browser in case you want to refer to these installation steps. If you are only installing a demo, refer to the last column for required steps.

task	Description	Full Install	Demo
1	download the PostGres installer for Linux from Postgresql.org	yes	yes
2	the installation of the PostGres SQL server	yes	yes
3	installing the demo database and the main TheatreManager User	optional	yes
4	configuration of the server parameters for maximizing performance in a production database	yes
5	creating a daily backup job in using cronnix to run the backup	yes

Notes and Assumptions:

This install process assumes you have NEVER installed PostGres or Theatre Manager on your computer before. If you have, you may need to refer to Updating Postgres Instructions
You MUST turn all virus protection while running the installer (especially Norton if you are using it). Virus software always interferes with proper software installation.
If this installer is being used to create a demo installation, then you only really need do steps 1, 2 and 3.
This process assumes that you have never installed Theatre Manager or Postgres on your machine. If you have already installed Postgres:
- you will be asked if you want to un-install Postgres (you may want to do that and then try to re-install after)
- you may need to remove the 'postgres' user from your computer if one exists, unless you know the password for the use.

Step 1: Install PostGreSQL Database Server

PostgreSQL for a Linux implementation is the responsibility of your organization to provide the necessary expertise to install, configure, upgrade and maintain the database server.

Installing Postgresql on Linux

1.	Install the PostgreSQL application.

Step 2: Create user and import Database

Installing a demo database

1.	Go to /Users/Shared directory. You should see some files and folders with names that look like below.
2.	Start terminal and change the user to 'postgres' by typing: su - postgres Press RETURN and then type the postgres user's password (password will not display anything)
3.	Drag the script '/Users/Shared/CreateDemoDB.sql onto the terminal window. This shortcut saves typing anything. Click into the terminal window and then press RETURN to start the command. If it does not run, then possible issues are: You need to have execute permissions on the 'CreateDemoDB.sql' script. Use File Examiner to check or fix that (or use unix chmod commands to give permission). Make sure that postgres was configured with 'trust' permissions for the local machine. Make sure that postgres was installed into the /Library/Postgresql8 directory.
4.	The script will run and load up the TheatreManagerDemo database. You can modify this script to load up a customer database if necessary by editing it in BBedit or in textedit (make sure to save it as text if you use textedit - its preference, unfortunately, is to save as an rtf document). Note, any WARNING messages from the TheatreManagerDemo database creation can be ignored. These warnings are normal.