Postgres Installation (How To)

Overview   Top

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.

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

Mac OS   Top

Overview   Top

The following instructions are used to set up a Macintosh PostgreSQL server for use with the Theatre Manager application.

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.

Step 3

Creating Daily Backup Job Using TM Server

  • Full Install: YES
  • Demo: NO

Step 4

Turning Off Energy Saving Features

  • Full Install: YES
  • Demo: NO

Notes & Assumptions   Top

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

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.

PostgreSQL Install Screen 1
  • Double click on the 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.

PostgreSQL Install Screen 2
  • Click Continue on the License Agreement screen after reading it.

PostgreSQL Install Screen 3
  • Click Install to begin the actual install.

PostgreSQL Install Screen 4
  • You will need to enter an administrator user ID and password to continue.

PostgreSQL Install Screen 5
  • You will see the installation progress as the Postgres database engine is installed.

PostgreSQL Install Screen 6
  • When the installer is finished successfully, click the Close button.

Step 2 - Configure the PostgreSQL Server Parameters   Top

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:

PG_HBA.conf File   Top

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.

Terminal Step 1

To edit the pg_hba.conf file and the postgres.conf file, you'll need to use VI (a text editor) and be the postgres user in Terminal:

  • Start Terminal
  • Type su - postgres
  • Enter the password

Terminal Step 2
  • Go to the postgres data directory by typing cd data

Terminal Step 3
  • Edit the postgres pg_hba.conf file that contains the addresses to listen on by typing 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.

Terminal Step 4
  • When you are at the end of the file, use the up arrow on your keyboard so that you are right after the first line in the IPv4 settings, where it says host all all 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.

Terminal Step 5
  • Edit the pg_hba.conf so that its final settings are similar to the window on the right. Type directly into Terminal so the data looks like the example image. 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 md5
  • NOTE: For the 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 and ::1/128. The line you added (or need to add) are for:
    • The local subnet (e.g.
    • Other subnets that need to access the data (e.g.
    • Any single machines that must have access (e.g. (via VPN))
    • NOTE: At the end of the subnet, the /24 refers to a complete subnet when you want any machine on the subnet to access the database as per CIDR rules. This is what is used most often. The /32 refers to a particular machine. If you use this option, you will need to provide the exact computer IP that you want to allow to access the database.

Terminal Step 6

Terminal Step 7

  • In the command line, type the following to reload the configuration: pg_ctl reload
  • Terminal will say server signaled

POSTGRESQL.conf File   Top

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.

Terminal Step 1
  • To change the postgres user, start Terminal
  • Type su - postgres
  • Enter the password

Terminal Step 2
  • Go to the Postgres data directory by typing cd data

Terminal Step 3

  • Use VI to edit it by typing 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 next step and change them to the suggested values, if they are not already set to that value.
    • NOTE: 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.
    • NOTE: For any setting that is about disk space or memory, you can type 1GB, 1000MB, or 1000000KB as 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.

Terminal Step 3

  • Use pgtune to get some optimized parameters for your machine and replace the values below. It is probably better to append the results from pgtune at the end and filling any missing settings at the bottom.
    • bonjour
      • If you wish your Postgres server to be discoverable using Bonjour services so that the Mac version of Theatre Manager can automatically locate a server on the network, this value can be uncommented and changed from off to on.
      • It will probably look like #bonjour = off. Remove the # from the front of the line (if any) to activate that parameter and change off to on
    • max_connections
      • The default is 100 which may be fine for most venues. On venues with a more of users and web servers, 200 to 400 is suggested.
    • shared_buffers
      • This value should be 20-25% of the total system total RAM. You find this value on the task manager as the total physical memory. Enter values as xxMB.
    • temp_buffers
      • This value should be 20MB.
    • work_mem
      • This value should be 20MB. Enter values as xxMB.
    • effective_cache_size
      • This value should be about 75% of AVAILABLE ram. So, on a 4GB system, this value would be 3072MB. Set the shared memory first. Shared memory is part of the effective cache size. If there is enough available ram in the machine, to exceed the size of the database, it means most reads will be cached in memory.
    • timezone
      • The timezone parameter is set to match the computer's timezone during the installation of Postgres.
      • If the timezone is incorrect on the computer, you may need to correct the timezone in the config file.
      • Refer to Wikipedia article on time zones (Use the TZ column)
    • ssl
      • Change this parameter from off to on to enable encrypted TLS communication with the database.
      • You will need to put a self signed TLS certificate into the data directory by using either the one supplied with the installer, or making your own.
  • Reference for postgres.conf file parameters

Terminal Step 3

  • Once the changes are made, type, in this order:
    • hit the esc key (the insert mode will disappear)
    • Shift Q (the window will show the Entering EX mode message)
    • wq (and the window will clear and you will be back at terminal)

OSX Self Signed TLS Certificate   Top

{% reference(text="Option 1) %} 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 commands:
    • 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 steps. {% end %}

{% reference(text="Option 2) %} 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 steps. {% end %}

Installation Steps

Installing the server.crt and server.key Files
You will need to copy the files to the Postgres User directory as the postgres user.

  • In Terminal, type su - postgres
  • Enter the password when asked
  • Type cd data
  • Type pwd
    • NOTE: 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.
  • Type cp /Users/Shared/server.crt server.crt
  • Type cp /Users/Shared/server.key server.key
  • Type chown postgres:daemon server.*
  • Type chmod 600 server.*
  • Type ls -la
    • NOTE: 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.
  • Type pg_ctl stop -m fast
  • Type 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   Top

Once the database is set up, you will need to establish a back up frequency that is appropriate for your venue. There are two steps to configuring the backup:

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

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

Step 4 - Disable Power Saving Settings on Server   Top

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

Allow Applications from Anywhere in Sys Prefs
  • If the Security and Privacy System Preference in MacOS does not show Anywhere, which allows applications to run from anywhere:
    • Open Terminal
    • Type sudo spctl --master-disable
    • NOTE: You may have to close System Preferences and open it again in order to see the change.
  • If Anywhere is not selected, please select it - as it should allow TM to run when the user is not an administrator.
  • Installers from TM should make this option visible. if not, run the above command.

Turn Off Energy Saver Options in Sys Prefs
  • Turn OFF all energy saving options such as Prevent hard disk sleep, appnap, Do not allow the CPU to go into low processor mode, etc. Mojave Settings are to the right to prevent machine sleep.
  • Turn ON features like restart the Mac after a power failure.
    • We also suggest altering the feature to auto-start the Mac at a time like '6:00am' should it just happen to be powered off. This way, your servers should always be on.
  • Enable auto-login on machines with TM Server if it will have classic listeners, and set the machine so that you can lock the screen after inactivity. The classic listeners will halt if not running under a user.

Turn Airport Off in Sys Prefs
  • 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.
    • Open the control panel
    • Click on the Airport interface
    • Click on the gear at the bottom to select the option make service inactive, which changes the status from off to inactive.

Change Display from CPU to GPU

  • 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:
    • Physically plug in a monitor to the Mac
    • Connect a KVM switch into it that is powered up
    • 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.

Shut Off Spotlight Indexing

  • Turn off Spotlight Indexing (mdsworker) by:
    • Opening Terminal and typing sudo mdutil -a -i off
    • NOTe: On Lion and later, also use the command sudo launchctl unload -w /System/Library/LaunchDaemons/

This will prevent the operating system from doing unneccesary 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. If any mdworker messages are in the console logs (or if mdworker pops up in activity logs), then Spotlight is not turned off.

Disable Time Machine - Preferred Option

  • Open Terminal
  • Disable Time Machine for the database folder (optionally, completely disable and local Time Machine files) with the following commands:
    • sudo tmutil addexclusion /Library/PostgreSQL
    • sudo tmutil disable
    • sudo tmutil disablelocal
      • only for Sierra and earlier
    • sudo tmutil thinLocalSnapshots / 10000000000 4
      • For Sierra and later to thin local backups if time machine enabled and you need space
    • tmutil listlocalsnapshots /
      • Optional - to see how many snapshots there are

Alternatively, you can disable Time Machine through System Preferences. If you must use time machine on the database server, see the next Disable Time Machine - alternative Option step.

Disable Time Machine - Alternative Option

Disabling Time Machine completely is recommended. If, however, you MUST use Time Machine on the database server, do not use Time Machine for the Postgres backups. Use the backup script and move the backups to another 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 the default of 1 hour, 43200 is 12 hours):
    • Use terminal and enter the following command: >/li> sudo defaults write /System/Library/LaunchDaemons/ StartInterval -int 43200

Turn Off Automatic Software Updates in sys Prefs

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.

Disable App Nap on Classic Listeners

Completely disable App Nap on the computer running the Classic Listener:

  • Open Terminal
  • Type defaults write NSGlobalDomain NSAppSleepDisabled -bool YES

Update to Newest OS

Update to the latest version of OS X. It is free.

Disable Handoff & Disconnect from iCloud in Sys Prefs

  • Disable handoff in general system preferences.
  • Make sure the machine is disconnected from iCloud.

Disable Air Play Received

Windows   Top

See steps in the current help pages.