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 it from the PostgreSQL web site and follow much of the Macintosh setup steps for configuration and backups. We do not provide automatic installers or configuration/operational support for 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.

  • Always make off machine AND offsite backups of the database. Consider implementing streaming replication. Backups are your best protection against almost all disasters, viruses or ransomware.
  • NEVER join a domain. This limits the people who can see or get into the machine remotely and largely avoids viruses.
  • Only allow external access via port 5432 for SQL traffic. Do not allow file server access.
  • If you must, only install virus software on the PostgreSQL Server under very controlled circumstances and never allow virus scanners to scan the actual Postgres database directories.

    Remember that Virus Software is the strongest attack vector for the bad guys and not guaranteed safety.
Replication is a feature of Postgres and is automatically set up for venues using AMS Cloud. Self service venues may set this up if they wish - the support team is unable to help you.
If you are using PCI schedule 'A", 'B', or 'C' compliance, credit card information will never pass through the database and it can effectively be taken out of PCI scope.

Refer to Postgres security notices for list of security issues addressed in each version.

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 Theatre Manager TMPostgresSetup installer program; 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 PostgreSQL installer for Mac yes yes
2 the installation of the PostgreSQL SQL server yes yes
3 configuration of the server parameters for maximizing performance in a production database yes no
4 creating a daily backup job using TM Server yes no
5 Turn off energy saving, airport and other energy saving features yes no
6 (Optional) Implement hot database standby server depending on load and other considerations. no no

Notes and Assumptions:

  • This install process will install or update PostgreSQL. If you are updating across versions, you may need to refer to Updating PostgreSQL Instructions
  • You MUST turn off 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 and 2.

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.

The actual Postgres install process is part of the install process. 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

Before starting the install, please check that the computer date and timezone settings are correct. Failure to do so may cause Postgres to think it is in a different timezone.
If you are unable to install, you might need to use the following commands in terminal before running the installer More info is under disable power saving settings - Step 1:

sudo spctl --master-disable
sudo defaults write /Library/Preferences/com.apple.security GKAutoRearm -bool NO

1.
  • Double click on the TMPostgresSetup.zip file that you downloaded. It will unzip and create a TMPostgresSetup.pkg file.
  • Double click on the TMPostgresSetup.pkg
  • You will see the introductory 'Splash' screen
  • Click Continue on the splash screen.
2. Click Continue on the Licence Agreement screen after reading it.

3. Click Install to begin the actual install.

4. You will need to enter an administrator user ID and password to continue

5. You will see the installation progress as the Postgres database engine is installed

5. When the isntaller is finished successfully, click the Close button

Step 2: Configure the PostgreSQL server parameters

When you are able to connect to the database using Theatre Manager, it's 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
An easy way to determine key config parameters is to use pgtune and paste the results at the bottom of the postgresql.conf file (don't forget to add ssl=on and Bonjour=on to the results).

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

(full pathname is /Library/Postgresql/[version]/data/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. EditHba

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, where 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. EditHba

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 as per CIDR rules. 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. EditHba

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 https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
7. In the command line, type the following to reload the configuration:

pg_ctl reload

Terminal will say 'server signalled'

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 a half-dozen key settings.

Note: you can also use pgadmin as described in the Windows setup to make these changes if you prefer.

Another was to get the key config parameters is to use pgtune and paste the results at the bottom of the postgresql.conf file (don't forget to add ssl=on and Bonjour=on to the results).

1. 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
2. Go to the Postgres data directory by typing

cd data

3.

This will use VI to edit it. Type

VI postgresql.conf

when the list appears, type

I

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, or 1000000KB - they are all equivalent. Do not leave a space between the number and the memory amount at the GB, MB or KB; otherwise, Postgres will not start.
4. use pgtune to get some optimized parameters for your machine and replace the values below. It is probably better to append the results from pgtune at the end and filling any missing settings at the bottom.
bonjour If you wish your Postgres server to be discoverable using Bonjour services so that the Mac version of Theatre Manager 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_connections The default is 100 which may be fine for most venues. For venues with more users and web servers, 200 to 400 is suggested.
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 physical memory. Enter values as xxMB.
temp_buffers This value should be 20MB.
work_mem This value should be 20MB. Enter values as xxMB.
effective_cache_size This value should be about 75% of AVAILABLE ram. So, on a 4GB system, this value would be 3072MB. Set the shared memory first. Shared memory is part of the effective cache size. If there is enough available ram in the machine, to exceed the size of the database, it means most reads will be cached in memory.
timezone The timezone parameter is set to match the computer's timezone during the installation of Postgres. If the timezone is incorrect on the computer, you may need to correct the timezone in the config file. Refer to Wikipedia article on time zones (Use the TZ column)
ssl Change this parameter from off to on to enable encrypted TLS communication with the database. You will need to put a self signed TLS certificate into the data directory by using either the one supplied with the installer, or making your own.

Reference for postgres.conf file parameters https://www.postgresql.org/docs/current/static/runtime-config.html
5. Once the changes are made, type, in this order:

hit the 'esc' key

(the insert mode will disappear)

Shift Q

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

wq

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

OSX Self Signed TLS Certificate

Making your own Self Signed TLS Certificate

It is generally best to create your own certificate. It takes about 30 seconds to do, and has the advantage that the certificate is unique to your database.

Start a terminal session, type the following 2 commands, and then follow the instructions as prompted. You can copy/paste the command.

cd /Users/Shared
openssl req -newkey rsa:4096 -nodes -keyout server.key -x509 -days 365 -out server.crt

Answer all the questions you are asked and when done, find the files in the /Users/Shared directory called:

  • server.crt
  • server.key
Continue to the installation step.

 

Using a supplied self Signed TLS Certificate

We have created a 4094 bit TLS certificate and included it with the installer. While it is better to create your own, if you need one fast to get started, you can use ours and create your own later (per the step above).

Go to the /Users/Shared folder and find the files called:

  • server.crt
  • server.key
Continue to the installation step.

 

Installing the server.crt and server.key Files

You will need to copy the files to the Postgres User directory as the postgres user. Do the following commands in Terminal:

su - postgres      (and enter the password when asked)
cd data
pwd      
Make sure the results of the pwd command says that the directory is /Library/PostgreSQL/x/data where 'x' is the version of PostgreSQL you have installed. It if does not, do not go any further. and call for assistance.
cp /Users/Shared/server.crt server.crt
cp /Users/Shared/server.key server.key
chown postgres:daemon server.*
chmod 600 server.*
ls -la

In the listing, the two files should now be in the postgres data directory and all that needs to occur is to stop and restart the database.

pg_ctl stop -m fast
pg_ctl start
Once the database is running, start Theatre Manager and go to the window showing employees that are logged in to see that the connection being used is secure.

Step 3: PostgreSQL server backups

Venues with databases on the AMS Cloud do not need to set up or manage backups - other than to request a special backup before running year end rollover

Once the database is set up, you will need to establish a back up frequency that is appropriate for your venue.

There are two steps to configuring the backup:

  1. Use the System Preferences->Backup to setup the backup frequency within Theatre Manager. The TM Server backup process uses this setup to do the backups
  2. Configuring a TM Server to do backups and monitor them

From then on, backups occur automatically. If they do not, you will receive a message on login that a backup has not been run for the past 24 hours.

Disable Power Saving Settings on Server

Additional Setup Considerations

Update to the latest version of macOS (macOS 12 Monterey or later).

It's free. The highest version you can update to will depend on how old your machine is.

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

1 If the Security and Privacy System Preference in MacOS does not show 'Anywhere' which allows applications to run from 'Anywhere', open Terminal and copy/paste the following two commands:

  • sudo spctl --master-disable
  • sudo defaults write /Library/Preferences/com.apple.security GKAutoRearm -bool NO

You may have to close System Preferences and open it again in order to see the change. If Anywhere is not selected, please select it - as it should allow TM to run when the user is not an administrator.

Installers from TM should make this option visible in system preferences per the image below. if not, run the above command.

Big Sur/Sonoma and later

Earlier versions of MacOS
2 Make sure to
  • turn OFF all energy saving options such as Prevent hard disk sleep, appnap, Do not allow the CPU to go into low processor mode, etc.

    Mojave Settings are to the right to prevent machine sleep.

  • turn ON features like restart the Mac after a power failure'. We also suggest altering the feature to auto-start the Mac at a time like '6:00am' should it just happen to be powered off. This way, your servers should always be on.
  • enable auto-login on machines with TM Server if it will have classic listeners, and set the machine so that you can lock the screen after inactivity. The classic listeners will halt if not running under a user.

    If you cannot set a mac to auto-login, it may be that file vault and/or cloud password needs disabled.

3 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'. Doing this will change the status from 'off' to 'inactive'.

4 Make sure to force the Mac Mini to use the built-in Graphics Processor Unit (GPU) when displaying screen shots instead of CPU. This prevents remote access from using up a CPU core to display the screen. You can do one of the following:
  1. physically plug in a monitor to the Mac -or-
  2. connect a KVM switch into it that is powered up. -or-
  3. use an attachment like a Headless Video Adapter

Disconnecting a monitor from the Mac will cause the computer to unnecessarily waste CPU cycles on display - when it should use the GPU.

5 Turn off Spotlight Indexing (mdsworker) using Terminal and typing

sudo mdutil -a -i off

On Lion and later, also use the following command

sudo launchctl unload -w /System/Library/LaunchDaemons/com.apple.metadata.mds.plist

This will prevent the operating system from doing unnecessary work while serving web pages. To recognize if Spotlight is running on a server, look for an 'mds' application running. It can use a lot of CPU resources. If you receive Operation not permitted while System Integrity Protection is engaged while doing this, you may wish to disable System Integrity Protection

note: If any mdworker messages are in the console logs (or if mdworker pops up in activity logs), then Spotlight is not turned off.

6 Using Terminal, disable Time Machine for the database folder (optionally, completely disable and local Time Machine files)
  • sudo tmutil addexclusion /Library/PostgreSQL
  • sudo tmutil disable
  • sudo tmutil disablelocal (only for sierra and earlier)
  • sudo tmutil thinLocalSnapshots / 10000000000 4 (for sierra and later to thin local backups if time machine enabled and you need space)
  • tmutil listlocalsnapshots / (optional) to see how many snapshots there are.

Alternatively, you can disable Time Machine through System Preferences.

If you must use time machine on the database server, see the next step for options

7 Do not use Time Machine for the Postgres backups. Use the backup script and move the backups to another machine. If Time Machine must be used on the database server machine:
  • make /Library/Postgresql one of the folders that is ignored by Time Machine.
  • change the backup interval so it is less frequent. 3600 is 1 hour (the default), 43200 is 12 hours. Use terminal and enter the following command.>/li>

sudo defaults write /System/Library/LaunchDaemons/com.apple.backupd-auto StartInterval -int 43200

8 Completely turn off any automatic Software Updates in the Mac's System Preferences. This is a database machine and should be manually updated on a periodic basis under controlled circumstances.

It may be either under 'Software Update' or 'App Store', depending on the version of macOS you have.

9 Completely disable App Nap on the computer running the Classic Listener using the Terminal command below:

defaults write NSGlobalDomain NSAppSleepDisabled -bool YES

10 Update to the latest version of macOS. It is free.
11 Disable 'handoff' in general system preferences as well as disconnect from iCloud.
12 Disable Air Play Receiver on MacOS 11 Monterey in System Preferences->sharing setup.

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.

Follow these steps if you are using the TMPostgresSetup 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 the required steps.

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 yes
4 Configuration of the server parameters for maximizing performance in a production database yes  
5 Creating a daily backup job in TM Server yes  
6 Considerations for installing virus protection on the PostgreSQL server - please do not include the Postgres data folder. yes  
7 Turn off Microsoft disk indexing on the volume that the database is running on. yes  
8 Turn off Microsoft Auto Updates on the database server so that it will not restart in the middle of sales. Applying Microsoft patches and updates should be done on a planned basis -- perhaps bi-weekly or monthly as a practice (or immediately if there is a current threat) 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 off 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 existing password for the postgres user.

Step 1: Install PostgreSQL Database Server

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

All Versions of Windows

DO NOT set up the Postgres database server to also act as ACTIVE DIRECTORY or as a DOMAIN CONTROLLER.

While it is possible to do so, the reasons not to are:

  • It is part of PCI DSS standard 2.2.1
  • You get better performance
  • More importantly, you obtain better safety/security leaving it a stand alone machine nobody logs into or connects to unless they physically visit the machine.
  • Updating the database server requires logging in as LOCAL admin anyway.

We DO NOT recommend that the database server JOIN a windows DOMAIN CONTROLLER either. There is no need for it.

If you wish to join a domain controller, please leave the database server login window pointing to the local machine (instead of the domain). It makes a user logging on for support and updates easier. Note that the machine should always be locked so sign-in is required - per PCI compliance.

We DO NOT recommend installing virus software on the Postgres database server. Since access to the server is under very controlled access via port 5432 from the Theatre Manager application only, it should not be required.

If you must install virus software on the database machine, set it to scan the machine daily and void the Postgres DB files. The best defence against any virus software is a backup of the database off machine and, even better, off premises.

Additional Notes for Specific Versions of Windows

Windows Small Business Server For Windows Small Business Server, you MUST turn off 'disk quota management' 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.
Windows 8 and higher For Windows 8 and higher, you may need to turn off UAC (user access control) if it is acting as a server. You can run Theatre Manager on other workstations with UAC on.
Windows 2012 Server and higher With Windows Server, you will need to be a local administrator to install Postgres.

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 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, postgres will install automatically for you and you can SKIP the next section describing how to install it manually and proceed to the step where the installer asks about installing a demo database

Alternatively, you can install them later manually by:

Run Postgres Installer

Do not do this step if you elected to let the Theatre Manager Postgres installer automatically install Postgres for you.

Only reference these instructions if you are running the actual Postgres installer from the Postgres web site manually.

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

Before starting the install, please check that the computer date and timezone settings are correct. Failure to do so may cause postgres to think it is in a different timezone.
Click Next
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 ignores the 32 or 64 bit version of the operating system.

you should always install to C:\Program Files\PostgeSQL\
Click Next

Enter a hardened ' Account Password' for the postgres user. If you do not supply one, we will generate one automatically. 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
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.

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

2. 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.
3. 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
4. You are asked for the password to create a 'TheatreManagerDemo' database. Type the same password used above and elsewhere in the install instructions.

import3
5. 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.

Another way to get the key config parameters is to use pgtune and paste the results at the bottom of the postgresql.conf file

1. Configuring postgres entails editing two files in the 'data' folder of the current postgres installation as highlighted below:
  • Navigate to C:\Program Files\PostgreSQL\15\data
  • The 15 in the path above may be different for you.
    • It could be the number 9 or higher - if you are using versions of Postgres older than version 15 (which is current at the time of writing)
    • If there are versions newer than 15, then this number could be 15 or higher.
    • You will have to look to be sure.
2. edit the pg_hba.conf file using Notepad++
Scroll to the bottom of the pg_hba.conf file and you should see something similar to the image to the right
LOCAL ACCESS Leave Local Access as:
  • scram-sha-256 (postgres 13 and later) -or-
  • MD5 (postgres 12 and earlier) -or-
Under IPV4 Local Connections Add lines for any subnets that need access:
  • 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.

Save your changes and RELOAD the server (see step 4)
3. Edit the postgesql.conf file. Some notes about editing:
  • Parameters are not in alphabetical order, so you may need to scroll up and down to find the ones that are in the list below (or use find) . 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.
  • 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.
***** 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.
An easy way to determine key config parameters is to use pgtune and paste the results at the bottom of the postgresql.conf file (don't forget to add ssl=on and Bonjour=on to the results).
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 '*'
max_connections The default is 100 which should be fine for most venues. If you have a number of users and web listeners, you might need to make it 150, 200 or even 400.
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 20MB.
work_mem This value should be 20MB. Enter values as xxMB.
effective_cache_size This value should be about 75% of AVAILABLE ram. So on a 4GB system, perhaps 3072MB on a larger system. Set the Shared memory first. Shared memory is part of the effective cache size. If there is enough available ram in the machine, to exceed the size of the database, it means most reads will be cached in memory.
timezone The timezone parameter is set to match the computers timezone during the installation of postgres. so, if the timezone is incorrect on the computer, you may need to correct the timezone in the config file. Refer to wikipedia article on time zones (Use the TZ column)
ssl Change this parameter from off to on to enable encrypted TLS communication with the database. You will need to take 30 seconds and put a self signed TLS certificate into the data directory by using either the one supplied with the installer, or making your own.

Reference for postgres.conf file parameters https://www.postgresql.org/docs/current/static/runtime-config.html
 

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

taskManager
Save your changes and RELOAD the server (see step 4) for most changes. If the change does not take effect, you may need to RESTART the server. This is usually only when first installing Postgres as changes to this file are rare
4. Reload or restart the postgres server to cause the new settings to be used.

All changes to the pg_hba.conf and many changes to postgresql.conf can be reloaded. That put the parameters in place without restarting the server, so any services (like web sales) that are running, continue to run.

A reload is done by:

  • using the windows start menu
  • finding the 'PostgreSQL application grouping
  • using the reload Configuration application

A few changes to the postgresql.conf file will require the postgres server to be completely restarted. This is usually noted in the comments beside the parameter. If this is required, the general steps are below. It is very rare that these parameters are changed after installation - so it might be best to ask support to help you with a complete server restart if you are not familiar:

  • Open services control panel
  • Find the postgresql service
  • Stop the postgresql service
  • Start the postgresql service
  • Make sure it is running
  • Restart the web services

Windows Self Signed TLS Certificate

Making your own Self Signed TLS Certificate

It is generally best to create your own certificate. It takes about 30 seconds to do, and has the advantage that the certificate is unique to your database.

Start a CMD prompt, type the following 3 commands, and then follow the instructions as prompted. You can copy/paste the commands.

cd C:\OpenSSL-Win32
cd bin
openssl req -newkey rsa:4096 -nodes -keyout server.key -x509 -days 365 -out server.crt

Answer all the questions you are asked and when done, find the files in the C:\OpenSSL-Win32\bin directory called:

  • server.crt
  • server.key
Continue to the installation step.

 

Using a supplied self Signed TLS Certificate

We have created a 4094 bit TLS certificate and included it with the installer. While it is better to create your own, if you need one fast to get started, you can use ours and create your own later (per the step above).

Go to the C:\BoxOffice folder and find the files called:

  • server.crt
  • server.key
Continue to the installation step.

 

Installing the server.crt and server.key Files

  1. Select both the server.crt and server.key files and right-click to COPY them
  2. Navigate to the postgres data directory which is C:\Program FIles\Postgres\x\data where 'x' is the version of PostgreSQL you have installed.
  3. Right-Click and PASTE them into the data directory
  4. You will need to restart the Postgres server for the changes to take effect
Once the database is running, start Theatre Manager and go to the window showing employees that are logged in to see that the connection being used is secure.

Step 4: Database Backups

Venues with databases on the AMS Cloud do not need to set up or manage backups - other than to request a special backup before running year end rollover

Once the database is set up, you will need to establish a backup frequency that is appropriate for your venue.

There are two steps to configuring the backup:

  1. Use the System Preferences->Backup to setup the backup frequency within Theatre Manager. The TM Server backup process uses this setup to do the backups
  2. Configuring a TM Server to do backups and monitor them

From then on, backups occur automatically. If they do not, you will receive a message on login that a backup has not been run for the past 24 hours.

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.

Increasing Windows Performance

Postgres MUST be on a stand alone server so that it can be left alone to do its job. If this is possible, then the only thing that needs to talk to it is Theatre Manager clients through port 5432. Under that scenario:

On Windows machines:

  • Enable Windows Firewall to allow incoming on port 5432 for the database and turn off other non-essential ports. Alternatively, make sure that your IT personnel have opened the correct ports through the firewall between computers as per these firewall/router rules.
  • 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.

  • Turn off:
    • microsoft indexing for all directories and sub directories. by looking at the properties of the drive that database is running on
    • And go to Services to find Windows Search and disable that.
  • Disable any disk quota management on the disk drive
  • turn off any virus scanning for ports and data directories used by postgres

    NOTE: if using windows 10 pro, you need to permanently disable windows defender using one of the methods in the link. In win 10 Pro, use gpedit.msc to disable by group policy editor since windows 10 turns it back on later if you only temporarily disable it (Another stupid idea form Microsoft)

    • Note: 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)

  • Do not install active directory or join it to a domain. Only local access is required to this machine
  • Turn off file sharing or any means that might allow a file to be added to the machine, other than via the postgres engine
  • 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)
  • set best performance options to maximize background performances.

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

Disable Defragmentation

Why Microsoft Windows turns on defragmentation by default, we don't know, but its not necessarily good for servers. It is particularly terrible if you have Solid State Drives as it affects the longevity of the device by interfering with the drive's wear leveling algorithms. We suggest turning it off from being automatic.

  • Click on the drive containing the database server
  • Right click to get properties
  • Click on the Tools tab
  • Click on the 'optimize' button
  • Look at the bottom to see if optimization is on (or off)
    • The button to turn it on and off is on the lower right
    • The current state is on the lower left

Disable Download of Updates and Auto Updating

For most versions of windows servers, you want to do controlled update so that you can pick the time of outage. We recommend using the sever versions of Windows as they behave as you expect (so far). Windows 10 has a mind of its own and may need some special treatment.

Windows Automatic Updates is now found in Services in Windows 10.

How to locate:

  • Open Control Panel and Administrative Tools
  • Select Services
  • In the Services window, scroll down to Windows Update and turn off the process.To turn it off, right-click on the process, click on Properties and select Disabled.
  • Note: windows 10 sometimes listens to you, most times it does not, despite what you indicate in various settings and you may have to:

Disable power saving on ethernet

For watever reason, Windows (out of the box) tends to be set to maximum power-saving which includes setting your network connection to go to sleep when it can:
  • especially on workstations, and
  • especially if you go take an extended coffee break or have a meeting.
This is not the best setting for any application, like Theatre Manager, QuickBooks or other accounting software that needs to connect to a database on a server.

We suggest disabling power management on the ethernet card.

  • use instructions like this for windows 7 and 8
  • Use instructions below for Windows 10 (they are very similar to windows 7)
    • Open Control Panel
    • select network and Internet
    • Pick Network Connections. the screen should look like below
    • click the network card
    • right click and show properties
    • click configure

    • click power management and uncheck the highlighted item per the image below

In addition, please make sure to disable power management except for monitors.

Making Windows Defender Manageable

Windows defender can prevent Theatre Manager auto updates from occurring in Windows 10 and can interfere with restarting of TM server. It can be addressed by:
  • Using 2012R2 or 2016 server for server processes instead of Windows 10. 2012R2 server does what you tell it with Defender, windows 10 does not - it automatically turns itself back on unless you
    • Set windows defender exclusion to all theatre manager folders on that machine -and/or-
    • Turning the Defender policy off -or-
    • Installing some other AV software that disables Defender and takes over. (we dislike this option - other AV software is much much worse)
  • Make sure to turn off DEP on windows 10 for all but essential programs - it might affect TM server from starting on startup

Setting a Custom Power Plan

Make sure to also turn off power saving on your ethernet card on all servers and workstations.

Create a custom Power Plan

  1. Open the control panel.
  2. Click Power Options.
  3. Click the Create a Power Plan option
  4. Select the bullet next to "High Performance"


  5. Click Next.
  6. Change the Display setting to "Never"


  7. Click Create.

Turning Off Indexing in Windows

There are a couple of ways to disable indexing.

Using Control Panel

You can usually turn of most indexing by clicking on the properties of the device. However, it seems windows 10 doesn't fully listen, so it may be best to approach this task via the control panel.

  1. Open Control Panels
  2. Look for where things are indexed
  3. Follow these instructions to disable indexing or something like the intent of the instructions.

Disabling indexing of disk from C drive properties

  1. Double-click on My Computer (or Computer).
  2. Right-click on C: drive (or the drive letter that Postgres is installed under).
  3. Select Properties from the popup context menu.
  4. Click the General tab.
  5. Remove the check in the "Allow files on this drive to have contents indexed in addition to file properties" box.

  6. Click Apply.

    It may take several minutes for Indexing to complete. If a message pops up indicating Administrator permissions are required click Ok. If the current user is not the Administrator a prompt for the Administrator password will appear. Enter the password and continue. If a prompt appears indicating select folders cannot be altered it may be they are already open. Click Ignore All and let the process continue.

  7. Click OK.
  8. Reboot the computer.

Repeat this setting on the Web Listener computer, and the Apache server as well

Turning Off Disk Quota Management

  1. Double-click on My Computer (or Computer).
  2. Right-click on C: drive (or the drive letter that Postgres is installed under).
  3. Select Properties from the popup context menu.
  4. Click the Quota tab.
  5. Remove the check in the "Enable quota management" box.

  6. Click Apply.
  7. Click OK.
  8. Reboot the computer.

Turning Off Windows Previous Versions

To check if the setting is turned on:

  • Double-click on My Computer (or Computer).
  • Right-click on C: drive (or the drive letter that Postgres is installed under).
  • Select Properties from the popup context menu.
  • Click the Previous Versions tab.

If Folder Versions reads "There are no previous versions available" this option is turned off. However, if backups are listed with date and time stamps, the feature is enabled and needs to be turned off.

  1. Click Start >> Control Panel.
  2. Click System in the Control Panel window.
  3. Click System Protection in the left column.

  4. Select the drive Postgres is installed on.
  5. Click the Configure button.
  6. Move the bullet to "Turn off system protection".

  7. Click Apply.
  8. Click OK.
  9. Reboot the computer.

Setting Best Performance Options

  1. Right-click on My Computer (or Computer).
  2. Select Properties from the popup context menu.
  3. Double-click on System Properties in the left column.
  4. Select the Advanced tab.
  5. Move the bullet to "Adjust for best performance".

  6. Click Apply.
  7. Click OK.
  8. Reboot the computer.

Turning Off UAC for Windows

  1. Open the control panel.
  2. Click User Accounts.
  3. Click the Change Users Account Control Settings link under Action Center
  4. Move the slider down to Never notify.
  5. Click Ok.

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 July 1, 2018, the most recent version of Postgres is 10.4 (or later) - we recommend the latest production version be installed on new machines.

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

Before starting the install, please check that the computer date and timezone settings are correct. Failure to do so may cause postgres to think it is in a different timezone.
1. Install the latest production version of PostgreSQL application using apt-get or yum as per the flavour of unix you are using.

Step 2: Create user and import Database

Installing a demo database

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.

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

import2
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:
  1. 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).
  2. Make sure that postgres was configured with 'trust' permissions for the local machine.
  3. Make sure that postgres was installed into the /Library/Postgresql8 directory.
import3
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.

step4

Update or Remove PostgreSQL

If you have already installed the Postgres database engine on your Windows server and need to update it, then follow the appropriate update steps. This also indicates when to make a backup after everybody has been locked out of the database.

Updating Postgres

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

  1. Check the version of postgres you are running. This is in the 'About Theatre Manager' menu. Look at the bottom left of the 'about Theatre Manager' screen. You will see your database name followed by a number such as (9.4.x) or (9.3.x) etc. Record this version for later.
  2. Log everybody out of Theatre Manager, including
    • Any user at the login window and/or
    • The second generation TM Server and/or
    • Classic web listeners
  3. Edit the pg_hba.conf file to restrict access and
    • Comment out any access that is allowed from any another IP address - and only allow access from 127.0.0.1
    • Reload (or restart) the postgres server configuration to make sure no other user would be able to log in and disrupt the upgrade.
  4. Make sure you have made a backup of the database, using the procedures in the daily backup job process after everybody has been locked out.
  5. 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 updating the same version or from an older version as required. Refer back to the version of the database that you recorded in the first step above
  6. After the database has been restored, edit the pg_hba.conf and
    • Un comment any access that you removed previously
    • Reload (or restart) the postgres server configuration and make sure others can now log in.
  7. Restart any web services

Updating the Same Version of PostgreSQL

These steps are for updating Postgres on a Windows server where the version of postgres is at the SAME major revision level as you are currently running. The major revision level is denoted by the first two digits of the postgres version.

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.

  1. Make sure you are running postgres version 9.2.0 or later.
  2. Refer to the overall instructions to download the latest TMPostGresSetup installer.
  3. 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.
  4. 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 March 2013, the current shipping version will be 'Postgresql-9.2.3-1' or later.
  5. Double click on it.
  6. It will start up the installer and default most of the settings based on the existing installation.
  7. 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.
  8. At the end you will need to restart the server.
  9. 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.

Updating Older Version of Postges

These steps will assist you upgrading Postgres on Windows that has an older major version number of Postgres to the most recent version. The major version number is denoted by the first two digits.

If you have postgres 9.4.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.

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.

Upgrading older Versions of Postgres

  1. Make sure that nobody is using Theatre Manager and that all second generation servers and classic web listeners are shut down
  2. Making a manual backup of the database using the DOS bat file C:\BoxOffice\BackupTM.bat and restoring it to a dummy database to make sure the backup will restore.
  3. Recording the PG_HBA.CONF and POSTGRESQL.CONF settings unique to this server
  4. stopping the server using the 'services' control panel.
  5. Un-installing Postgres by:
    • going to Control Panel and
    • looking for 'Add or Remove Programs' (XP) or 'Programs and Features' (or whatever Microsoft changed it to in the version of windows you have)
    • Finding the installed version of Postgres that you have and 'uninstalling it'
  6. Deleting the old postgres server data directories. These will generally be in C:\Program Files\Postgres or C:\Program Files (X86)\Postgres - depending on if you have 32 or 64 bit versions.
  7. Use the download steps to obtain the latest version of the Postgres installer from the ArtsMan site.
  8. Changing the configuration parameters in PG_HBA.CONF and POSTGRESQL.CONF per the standard install instructions or you can match those that your recorded from the prior version's config files if you optimized anything. Note, you cannot simply copy the older versions of both files in the new install of postgres as parameters are sometimes added or removed.
  9. Restoring the old database by
    • Creating a new database on the server that matches its prior name. (eg, if it was called 'MyTheatreDB', call it the same name.)
    • Setting the owner to 'TheatreManager' and the Encoding to 'UTF8' (which is the default in all current versions of postgres)
    • Importing the backup of the database made at the beginning of the instructions into the new database server.
    • Starting Theatre Manager and attempting to log in. (if you cannot, make sure that the pg_hba.conf is correct)
  10. Setting up the backup job again and verifying that it works. Verifying it works means that you actually run it under the task scheduler and ensure that the backup file sizes are as expected.

Removing Postgres from Windows

Postgres can be removed buy un-installing it and then removing the data directory.

  1. 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. (Note: it could be on another drive)
  2. 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, (Note: it could be on another drive) -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