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.

Only install virus software on the PostgreSQL Server under very controlled circumstances and never allow virus scanners to scan the actual Postgres database directories.

Do not join a domain to limit the people who can see or get into the machine remotely.

If you are using PCI schedule '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 in using Cronnix to run the backup 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.
In recent versions of OSX, you may need to make a temporary change in System Preferences after downloading the installer and before the installer will work. This is because the installer is not digitally signed with Apple.
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

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

Note: you can also use pgadmin to make these changes if you prefer.

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.
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 should be fine for most venues. On venues with a lot of users, you may need to make 150 to allow 20 for the Theatre Manager Server and up to 4 for each concurrent user. If Postgres fails to start following configuration of this parameter, set it back to 100 and attempt to start Postgres again. Generally, do not change this unless you need to.
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 dissappear)

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/9.x/data where 'x' is the version of postgres 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 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

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

However, it is perfectly okay to 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.

Performing a Manual Backup

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

php /Users/Shared/backupTM.php

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

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

Editing the backup script

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

Test the Batch Script

1. backuprun

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

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

backuplist

Creating a Daily Backup Job

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

scheduledTask

Only do this on the machine that has the database server on it and make sure you are logged on as the administrator.

2. Double click on the 'CronniX.app' icon (it may or may not have .app at the end). This will start the CronniX Task Scheduler. On a side note, Cronnix is an interface to the Unix CRON facility. CRON has been around for a long time and is one of the task scheduling tools that is built into the Unix operating system. You don't have to worry if it is on your system - it just is.

addTask

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

echo "Happy New Year!"

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

php /Users/Shared/backupTM.php

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

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

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

Entries for any of the items can be like:

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

wizard1

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

3. Click the Button (or the Button if you are editing the existing CronniX command).

Disable Power Saving Settings on Server

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.
  • turn ON '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.

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

3 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 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 the computer to unnecessarily waste CPU cycles on display - when it should use the GPU.

4 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 unneccessary 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.

5 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

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

6 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

7 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 OS X you have.

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

defaults write NSGlobalDomain NSAppSleepDisabled -bool YES

9 Update OS X to 10.9.5 (or later) if the current version is between 10.9 and 10.9.4.
  • There have been reports of earlier versions of Mavericks having TCP/IP problems.
  • We have no recommendations on Yosemite 10.01 as a server yet; it's too early to tell.
10 Disable 'handoff' in general system preferences as well as disconnect from iCloud.

Hot Standby Server

One of the features of Postgres is the ability to create a 'hot standby' database server as a way to introduce redundancy to the system. The notion is that if a catastrophic physical failure occurred to the database server (such as the drives died, the machine melted, it got doused in water or burnt in a fire) and was found to be non-recoverable, then it would be best to get to a backup as soon as possible.

The standard backup approach allows you to go back to a snapshot. If you do a number each day, that may mean going back a few hours and reconstructing what happened from the backup to the failure.

Replication means you have one (or more) standby servers that is shadowing the contents of the main server - and may be as close as seconds old. In the case of failure, you would turn off the replication feature and start using the standby database until your primary server gets fixed.

Do not use replication as the only means of backup - always set up snapshot and offsite copies of your database.

We always recommend using the latest version of PostgreSQL for replication. These instructions were created for PostgreSQL version 9.6.

If you do a minor level upgrade of PostgreSQL on the main database server (e.g. from PostgreSQL v9.6.x to v9.6.y), you must also upgrade the version of Postgres on the hot standby server at the same time. There is no need to migrate the database again.

However, if you do a major level revision, you will need to do ALL the steps to re-establish the hot standby. This will change the time it requires to upgrade (but having a hot backup is worth it).

If you update your version of Theatre Manager, any changes to the data in the main database are also updated on the hot standby. You never have to update the backup server as all changes come from the main server

The general steps for making a hot standby are:

Many thanks to this insightful article by Bartek Ciszkowski for helping make the process on Linux which we adapted to OS X. You can also read more detail on the Postgres site regarding high availability servers for any of the technical details.

Replication can be done on windows per this tutorial and it implies that there are few differences, except how to get the WAL logs replicated from one machine to the other - using a file share.

Main Postgres Server Changes

For the purposes of the example, let's assume per the diagram that

  • the main database server is at 192.168.0.5 and
  • the hot standby server is at 192.168.0.10

Do not restart any of the Postgres servers until told to do so.

Changes to postgresql.conf

You can make these changes using PGAdmin ('Tools->Server Configuration') or by using Terminal and VI to navigate to the Data folder and edit the file. This assumes you know how to make these changes. Note that there are ' (quotes) around the RSYNC parameters below. In PGAdmin, you will not need to enter them - in VI, you will need to enter them.

These settings tell Postgres to send enough information from the master to the slave. You can read about these parameters in the official documentation and adjust them as needed.

wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

archive_mode = on
archive_command = 'rsync -aq %p postgres@192.168.0.10:/Library/PostgreSQL/9.6/archive/%f'
archive_timeout = 60
The 'rsync' command has its own requirements to actually make it work. For now, set the IP address of your hot standby in place of 192.168.0.10. The timeout is set to 60 seconds so that, at most, the standby is 60 seconds behind the main database.

Changes to pg_hba.conf

Edit the pg_hba.conf file and scroll down to the bottom. There should already be 3 lines that have replication in them which will need to be uncommented and permissions set to 'trust'. Add a line that tells the master server that the 'replication' server is at 192.168.0.10 as per the last line. Replace with the IP address of your hot standby server.

Ensuring rsync permissions work

You must to establish secure credentials between the main Postgres server and the standby server to enable rsync to transfer the files using the command above. It requires some Terminal commands and knowing what you are doing. It may be easier to have two Terminal sessions working.

As Admin User

ON THE BACKUP SERVER, Open System Preferences and go to the 'Sharing'. Make sure 'Remote Login' is enabled.

It can be set to all users -or- if you want to limit users, make sure the postgres user is in the list.

Make the '.ssh' folder and 'archive' folder for the postgres user in the postgres user's home directory /Library/PostgreSQL/9.6 and provide access to it.

DO THE SAME four commands on the both the MAIN and on the HOT STANDBY server.

sudo mkdir /Library/PostgreSQL/9.6/.ssh
sudo chown postgres:daemon /Library/PostgreSQL/9.6/.ssh
sudo chmod 700 /Library/PostgreSQL/9.6/.ssh
(only postgres should have rwx access to the .ssh directory)

sudo mkdir /Library/PostgreSQL/9.6/archive
sudo chown postgres:daemon /Library/PostgreSQL/9.6/archive

 

As postgres User ON THE MAIN SERVER, use ssh-keygen to create a key/certificate file for SSH access and move it to the hot standby. Again, we are assuming that the hot standby will be 192.168.0.10 - replace with your hot standby IP address as required.

  su - postgres
cd /Library/PostgreSQL/9.6/.ssh/
ssh-keygen -b 1024 -t rsa -f id_rsa -P ''
(note: the end of the command is two single quotes in a row)
  you should get a response like:

Generating public/private rsa key pair.
Your identification has been saved in id_rsa.
Your public key has been saved in id_rsa.pub.
The key fingerprint is:
f4:a6:7b:b5:47:98:a8:b0:5e:5c:c5:92:b0:17:e7:c8 postgres@192.168.0.05
The key's randomart image is:
+--[ DSA 1024]----+
| . . . |
| + B |
| o E + |
| . o o |
| S +. o |
| .. +. + . |
| o+. . o |
| ..... . . |
| .. .. . |
+-----------------+

  touch authorized_keys
cat id_rsa.pub >> authorized_keys
chmod 400 id_rsa
scp authorized_keys postgres@192.168.0.10:/Library/PostgreSQL/9.6/.ssh
  You should get a response as below

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.
ECDSA key fingerprint is 5f:ac:f0:ec:73:a7:1e:27:45:28:ac:b2:55:b6:a7:db.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.0.10' (ECDSA) to the list of known hosts.
Password: Type your postgres password here
authorized_keys2 100% 614 0.6KB/s 00:00

Then type the following to SSH to the remote machine. You should no longer be asked for a password. If you cannot do this, then replication will not work.

  ssh postgres@192.168.0.10
  It should respond with a prompt and you can type
  exit

If you are not able to connect to the remote machine without typing a password, you may need to do one of:
  • use the -v argument to see why SSH may not be using the authorized key: ssh -v postgres@192.168.0.10
  • change the name of the 'authorized_keys2' file in the .ssh directory to 'authorized_keys' on both machines and try connecting again
  • remove the .ssh directory from both machines and start the ssh-keygen process again -- but do it carefully this time -or-
  • you may need to reset the Postgres password on the remote machine using System Preferences, if you just installed Postgres on that machine.

Hot Standby Server Configuration

For the purposes of the example, let's assume that:

  • the main database server is at 192.168.0.5 and
  • the hot standby server is at 192.168.0.10 as per the diagram
  • the version of postgres you are using is 9.6.x (otherwise change 9.6 to 9.x in all commands, depending on the version of postgres you are using)

Stop the Hot Standby server and do not restart it until told to do so.

Start Terminal, then sign on as the Postgres user via the following commands to stop the database:

su - postgres
pg_ctl stop -m fast
exit

Create recovery.conf

Create a file called 'recovery.conf' (which should not exist) in /Library/PostgreSQL/9.6 for safe keeping. We will be copying this file later on as Postgres expects to find it the 'data' folder in /Library/PostgreSQL/9.6/data. However, we cannot put it there yet as we will be deleting everything in that directory later. Replace 192.168.0.5 with the address of your primary database server.

Using Terminal as administrator of the machine, create and edit the recovery.conf since the Postgres user cannot create this file. Alternatively, copy the text and paste it into a TextWrangler file on the machine and use a copy command to put it into place.

As admin User
sudo vi /Library/PostgreSQL/9.6/recovery.conf

then enter the contents of the file by typing the yellow text below. (you can usually copy/paste into vi if in 'insert' mode)

standby_mode = on # enables stand-by readonly mode

# Connect to master postgres server using postgres user
primary_conninfo = 'host=192.168.0.5 port=5432 user=postgres'

# specify the name of a file whose presence should cause streaming replication to
# end (i.e. failover). If you create this file, then Postgres will rename 'recovery.conf'
# to 'recovery.done' and promote the hot standby to a primary server without a restart.
trigger_file = '/tmp/pg_failover_trigger'

# shell command to execute an archived segment of WAL files
# required for archive recovery if streaming repliation falls behind too far.

restore_command = 'cp /Library/PostgreSQL/9.6/archive/%f %p'
archive_cleanup_command = '/Library/PostgreSQL/9.6/bin/pg_archivecleanup /Library/PostgreSQL/9.6/archive/ %r'

Make sure that the archive directory exists

If you did not already do this command as part of the set up of the MASTER database, you will need to do so, so that the backup files can be received by the HOT STANDBY server.

As admin User

Make the '.ssh' folder and 'archive' folder for the postgres user in the postgres user's home directory /Library/PostgreSQL/9.6 and provide access to it.

DO THE SAME two commands on the both the MAIN and on the HOT STANDBY server if you have not previously done so.

sudo mkdir /Library/PostgreSQL/9.6/archive
sudo chown postgres:daemon /Library/PostgreSQL/9.6/archive

If you are re-establishing a hot-backup server, make sure to clear out all files from the 'archive' folder on the hot standby. In Terminal, type:

cd /Library/PostgreSQL/9.6/archive
sudo rm -rf *

You do not need to stop the main server while setting up the hot standby as we will make a base backup to 'restore to a point in time'.

Migrating the initial database

This step assumes that you have used the previous steps and configured the main database and the hot standby server prior to beginning with the following steps. Also, for the purposes of the example, let's assume that the main database server is at 192.168.0.5 and the hot standby server is at 192.168.0.10 as per the diagram.

Hot Standby Server

Stop the Hot Standby server and do not restart it until told to do so (it should be stopped already). We don't want any data flowing from the main database to the Hot Standby while we are preparing the servers.

In Terminal, as postgres user:

pg_ctl stop -m fast

Main Database Server

You may need to use pg_ctl to stop and then start the main server to get the max_wal_server change to take effect before starting the pg_basebackup. Make sure nobody is using the database, then type in Terminal, as postgres user:

pg_ctl stop -m fast
pg_ctl start

Now that the hot standby is configured, it’s necessary to get the contents of the main database onto it, so that replication can sync up. This is done using a handy command named pg_basebackup, which essentially copies the entire data directory of your main database into a location of your choice. It is also what we know as an online backup, as the main database can stay in use while the command copies all the data.

 

On the Main Database Server

Start the pg_basebackup command and put the backup file in some place that Postgres can access. The standard backup directory /Users/Shared/Backups should be fine. The base backup should take about as long as your normal backups take and progress is shown in the Terminal window.

Make sure that Postgres has read/write access on both the local machine and the remote machine to the directory you are using.

The scp command is used to copy the base backup to the same location on the hot standby server (replace 192.168.0.10 with your backup server IP). You could use a USB key or any other mechanism to transfer the file that you want. This may also take a long time and progress is shown in the Terminal window.

pg_basebackup -U postgres -v -D - -P -Ft | bzip2 > /Users/Shared/Backups/pg_basebackup.tar.bz2


scp /Users/Shared/Backups/pg_basebackup.tar.bz2 postgres@192.168.0.10:/Users/Shared/Backups

NOTE: The bzip2 command can be quite CPU intensive and only uses a single core. If a multi-core processor is available you may wish to consider using the pigz compression program instead to get a significant speed-up to the backup. Be aware that using pigz requires separate installation and it also uses a different extension than the bzip2 method: .tar.gz vs. .tar.bz2.

pg_basebackup -U postgres -v -D - -P -Ft | pigz > /Users/Shared/Backups/pg_basebackup.tar.gz


scp /Users/Shared/Backups/pg_basebackup.tar.gz postgres@192.168.0.10:/Users/Shared/Backups

 

On the Hot Standby Server

The hot standby server should already be stopped. We need to use Terminal (as the postgres user) and remove the entire data directory - if it exists.

su - postgres
Enter the Postgres password
pg_ctl stop -m fast (just to make sure Postgres is stopped on the backup server)
cd /Library/PostgreSQL/9.6/data
rm -rf *
ls
(There should be no files)
Then expand the copy of the base backup of the main database that we just copied to the hot standby server
tar -xjvf /Users/Shared/Backups/pg_basebackup.tar.bz2
Change the owner of the entire contents of the database directory to be postgres
chown -R postgres:daemon /Library/PostgreSQL/9.6/data
Copy the recovery config file that we made earlier
cp /Library/PostgreSQL/9.6/recovery.conf /Library/PostgreSQL/9.6/data/recovery.conf

 

Changes to postgresql.conf on the Hot Standby Server

The expansion of the base backup database from the main server will also restore its postgresql.conf and pg_hba.conf file.

We now need to adjust them to ensure that they are set to be a hot standby database. You can make these changes using PGAdmin ('Tools->Server Configuration') or by using Terminal and VI to navigate to the Data folder and edit the postgresql.conf file.

The settings below tell Postgres that it is the slave machine. You can read about these parameters in the official documentation and adjust them as needed.

hot_standby = on
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

Look for the 'archive' settings that were restored from the base backup and comment them out by putting a '#' in front of the parameter.

#archive_mode = on
#archive_command = 'rsync -aq %p postgres@192.168.0.10:/Library/PostgreSQL/9.6/archive/%f'
#archive_timeout = 60

 

Changes to pg_hba.conf on the Hot Standby Server

Edit the pg_hba.conf file and scroll down to the bottom. There should already be 3 lines that have replication in them from the main database. If they are not uncommented, you will need to do so and set permissions to 'trust' (and go back and verify the contents of the same file in the main database).

Finally, start the database on the hot standby server

pg_ctl start

 

Cleanup after Replication is Confirmed to be Working

Please confirm replication is working before doing the next step to clean up the temporary base backup files.

The base backup file was created on both the main server and migrated to the hot standby machines. If you have a large database, this file could be sizeable itself and it is best to delete it. On both machines, you can navigate to the /Users/Shared/Backups directory and remove pg_baseBackup.tar.bz2.

Alternatively: in Terminal you can:

rm -i /Users/Shared/Backups/pg_basebackup.tar.bz2 (you will be asked to confirm the deletion)

Verifying that the database is replicating

There are some simple ways to ensure that the main database is properly replicating to the hot standby. You will need to do most of this observation on the hot standby machine.
  • Observe the Postgres log roll forward the changes
  • Looking at the replication logs to see that they are created and then removed
  • Make some changes to the main database and then using pgAdmin to verify that the changes are in the backup in real time

Using pgAdmin to see Replication Status

On the main server, you can run the following query in pgAdmin to look at the state of replication:

select * from pg_stat_replication

This should give a row back that says the server is 'streaming'. You can repeat the query and the numbers will change, indicating that replication is proceeding.

If the query gives no rows back (or some other status), then things may not be set up right, or replication has not yet begun because archive log files are being recovered on the Hot Standby Server.

Using pgAdmin to Watch Transactions

Alternatives

Data replicates from the main server to the Hot Standby immediately. If there is a change to the main database, it should appear in the hot standby immediately after that.

One way to verify changes is to have the box office sell a ticket to a patron using Theatre Manager. Before the sale, there should be no transaction history for that order; after the sale, there should be some ticket transactions.

Perform this test daily or weekly to make sure that the database is replicating.

Occasionally, if you take the standby down for maintenance, it may get a bit behind. In that case, you may not see changes until the archive files have been fully recovered and you may need to look at the standby server's Postgres log files for progress. The archives are created periodically as per the frequency that you set in the archive_timeout parameter)

Verifying Simultaneous Activity on Both Servers

Follow these steps to see that data is being migrated:

  1. Start pgAdmin on the main server, point to the main database and start an SQL session
  2. Start pgAdmin on the Hot Standby server, point to the standby database (it is a different IP address) and start an SQL session
  3. On both machines, enter the SQL below into pgAdmin to verify the last 10 transactions

    select * from f_transaction order by t_seq desc limit 10
  4. Log in to Theatre Manager on any workstation (or have anybody sell a ticket)
  5. in pgAdmin on the MAIN database, rerun the SQL above. There should now be a new login transaction or transactions that match the number of tickets sold.
  6. In pgAdmin on the Hot Standby database, rerun the SQL above.
    • There should be the same transactions visible on the hot standby right away.
    • That means the servers have replicated the data.
  7. In Theatre Manager, release the tickets or log out.
  8. in pgAdmin on the MAIN database, rerun the SQL above. There should now be MORE transactions representing your activity.
  9. In pgAdmin on the Hot Standby database, rerun the SQL above.
    • You should see the new transactions on the standby server, meaning the servers have again replicated the data.

Reviewing Postgres Log File for Replication Entries

The data is replicating from the Main server to the hot standby periodically, as per the frequency that you set in the archive_timeout parameter. If there is a change to the main database, it should appear in the hot standby very shortly after that.

You should go to the log files once in a while and ensure that it is replicating and able to see/connect to the other database. After the tail command, you should see that the server entered standby mode and that is connecting to the main server.

Even after setting up the replication to the hot standby server, you should check this log periodically to make sure it continues to work.

su - postgres
Enter the Postgres password
cd /Library/PostgreSQL/9.6/data/pg_log
ls -la (to see a list of logs)
tail -f name-of-most-recent-log-file
The most recent log file will have today's date in it and the 'tail -f' command will continue to display the changes to the file as they occur.

The example below shows the startup of the standby server after first being created (or being stopped for a period of time). The log files are consumed and, finally, replication starts. Once replication starts, no further 'restore' messages will appear in the logs -- you can then use the pgAdmin query method to view the status of replication on the main server.

Inspect the Archive Directory

The data is replicating from the main server to the hot standby periodically as per the frequency that you set in the archive_timeout parameter. If there is a change to the main database, it should appear in the hot standby very shortly after that.

You can go to the replication folder and see that new files appear once a minute and old ones go away.

cd /Library/PostgreSQL/9.6/archive
ls -la (to see a list of recovery files).
The file names will be very long with digits in them. The name of them will also increase numerically.
For example:
  • 000000010000000000000055 will have as the next file 000000010000000000000056 - about 60 seconds later or whatever time period you set in the archive_timeout command
  • Eventually 000000010000000000000055 will disappear as it is processed by the hot standby
  • Generally, on the server, there should be only a few of these files (5 to 10). if there are more, the server may be a little busy. If they do not decrease, replication is not working right or the server is overloaded.
  • and the above will repeat.

Promoting a Standby to the Main server

Except for an absolute calamity, there is no reason to promote the hot standby to be the main server. If you need to, the general recovery steps are outlined below. There is only ONE command to do at the backup server to make it the primary server (see bottom half of page).

  1. Shut down, turn off, unplug and/or remove the main server from the network so that it is no longer active
  2. On the backup server, create the failover trigger file using the primary failover methodology (described in detail below):
    sudo touch /tmp/pg_failover_trigger
  3. The hot standby is now your main server.
  4. You may need to change the pg_hba.conf file as required to allow workstations to access the database.

    It is probably okay because it was originally copied from the main server at time of making the standby. You will only need to change the pg_hba.conf file if you added more sub-nets to the main server.

  5. Do all the work necessary to get Theatre Manager clients to point to a new database server by doing one of:
    • Use Theatre Manager to change the IP address of the database you log into -or-
    • Change the IP address of the hot standby to be the same as the Master main server -or-
    • Change the DNS to point to the new IP address, if that's what you use to connect to the server
  6. Log all clients, second generation and classic listeners into the new server IP address
    • Log into the database using Theatre Manager.
    • Quit and restart the Theatre Manager Server
    • Quit and restart any web listeners
  7. Fix the reason that the main server died and plan to set up a new hot backup server

 

Primary Method to promoting the Backup Server to Main Server

If your recovery.conf file in the Postgres data directory was set up according to these instructions, it should have a trigger_file parameter and look like the except below.

# specify the name of a file whose presence should cause streaming replication to
# end (i.e. failover). If you create this file, then Postgres will rename 'recovery.conf'
# to 'recovery.done' and promote the hot standby to a primary server without a restart.
trigger_file = '/tmp/pg_failover_trigger'
Assuming that the path name of the trigger file is /tmp/pg_failover_trigger, then, in Terminal as an admin user, type
sudo touch /tmp/pg_failover_trigger
This will cause Postgres to:
  • Notice the file
  • Finish off the slave process and recover any log files transferred from the main server that still needs recovery
  • Rename the 'recovery.conf' file to 'recovery.done'
  • when that happens, it is now the primary server

Alternate Method using pg_ctl

use pg_ctl to promote the server

su - postgres
enter the postgres password
pg_ctl promote

For more information refer to the postgres fail-over documentation

Update or Remove PostgreSQL

If you have already installed the Postgres database engine on your Macintosh 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. (optional) If you are worried that staff people might log in to Theatre Manager while the upgrade is happening, use PGAdmin (Tools->Server Configuration menu) to 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. You do this by double clicking on the row containing the IP address and unchecking it.
    • 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. (only after everybody is logged 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
    • Uncomment 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 Postgres

These steps are for updating Postgres on a Macintosh OSX 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.

If you are using Hot Streaming Replication, so MUST stop the replication server FIRST and upgrade the version of postgres on it before doing the main server. If not, you may end up redoing the replication server.

Always verify that replication is working properly after a postgres update.

  1. Make sure you are running postgres version 9.2 or later.
  2. Refer to downloading the latest Mac Installer for postgres
  3. Make sure you have just made a backup of the databases in the server
  4. 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

  5. Run the installer which will update and restart an existing PostgreSQL installation.

    Make sure to read the next step before starting the install to decide if you can do an easy install or the custom install.

  6. If you have set up the hot standby server, make sure to
    • stop the hot standby at the same time
    • Only use the custom install to update the MAIN server (which does not install a revised demo database) as per below.

    • Upgrade the Hot Standby to the same version of the database server using the same custom installer.
    • Verify that replication is still working after the upgrade
  7. try log in to Theatre Manager afterwards

Update Older Version of Postgres

These steps assist migrating an older version of Postgres to the most recent. 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.

The steps are:

  1. Sign all users out of Theatre Manager and stop all TM Web Services
  2. Make a manual backup of the database using the terminal command php /Users/Shared/backupTM.php (and restore the backup to a 'test' database to ensure it works).
  3. Save the PG_HBA.CONF and the POSTGRESQL.CONF settings unique to this server
  4. Stopping the Postgres server
  5. Removing the old postgres server by running the un-installer found in the /Library/PostgreSQL/x.x directory.
    • This will only uninstall postgres and the services
    • Once complete, you will need to move (or delete) the /Library/PostgreSQL/x.x directory which now should contain only the 'data' folder.
    • The 'data' folder is retained only for temporary safekeeping until you have installed the next version of Postgres and restored your data satisfactorily.
  6. Installing the new postgres server using the Theatre Manager postgres installer for OSX
  7. Reconfiguring the parameters for postgres for PG_HBA.CONF and the POSTGRESQL.CONF
  8. Restoring the main database from the backup
    • Create 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.
    • Restore the backup of the database
  9. setting up the backup job again
  10. If you previously set up the hot standby server, you will need to follow the complete installation steps for and set it up again for the new version of postgres.

Removing Postgres

Use the following steps to remove postgres from a macintosh OSX server.
  1. Stop the postgres database using the

    pg_ctl stop -m immediate' command

  2. When the server is stopped, use the un-install program in the /Library/PostgreSQL/x.x directory to get rid of it properly
  3. throw out the entire folder called /Library/PostgreSQL
  4. restart the mac

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

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 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. Avoid the Postgres DB files.

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.
VISTA, Windows 7 or later For VISTA/Windows 7/8, 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 2003 With Windows 2003 server, you will need to be a local administrator to install Postgres. Since postgres v9.3, in some cases, you may need to:
  • Run the postgres installer
  • Manually set permissions on the data folder to everyone, all access
  • re-run the postgres installer

Note: Microsoft stopped supporting XP on April 8, 2014

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

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.

2.

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 Server

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'

password

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.

 

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

edit the pg_hba.conf file.

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

Edit the pg_hba.conf so that its final settings are similar to the list below below (see *** a few lines down).

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

 

 

finalhba
Once the changes are made, click the Save icon to save the changes. Then on the main menu, select File >> Reload Server. Reload
You will be asked to confirm that the changes. Click Yes. Confirm

Click the close box and you will be asked if you want to save your changes.

Click Yes.

 
5.

Edit the postgesql.conf file

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

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

list

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.

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.

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

editBuf

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 = '*'

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. In rare cases, venues with a lot of users may need to allow 150 connections. If Postgres fails to start following configuration of this parameter, set it back to 100 and attempt to start Postgres again. Generally, do not change this unless you need to.
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
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.

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\9.x\data
  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

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

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.

1.

If you used the installer to place files into the correct location, you can skip to step 2 in this section. If you did not, then you will need a copy of two files and then:

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

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.

backupbat
Right click on the file and edit with WordPad or NotePad. edit

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

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

BackupTM.bat uses robocopy to make a month-end copy to a separate directory. This is part of windows 7 and up. If you are using server 2003, you can Download Windows Resource Kit Tools for Server 2003 to get robocopy.

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

4

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

1.

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

2.

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.

addTask
Click Next wizard1
On the list of applications, click the 'Browse' button. wizard2

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

Then click 'Open'

PickApplicaiton

You will then have the ability to pick a frequency for the backup.

Daily is suggested, then click 'Next'

Scheduel

Pick the time that you want the backup to run. 2:00 am is as good a time as any.

Then Click Next

RunTime

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

RunTime

Click 'Finish' to save the job. There is no need to go to the advanced properties.

RunTime
Run with highest Privileges. 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.

RunTime

If you want a log If you would like to have the output of the backup file from the task scheduler go to a log file, then you can set the command to be:

cmd /c C:\Backups\backupTM.bat > c:\backupLog.txt

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.

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.

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.

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

  • Turn off microsoft indexing for all directories and sub directories. by looking at the properties of the drive that database is running on
  • 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)

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.

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 Postgres

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

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 Dec 1, 2016, the current minimum acceptable version of Postgres is 9.6.1 (or later).

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 PostgreSQL application.

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