Downloading PostgreSQL Installer for Mac & Installing Server

• Full Install: YES
• Demo: YES

Configuring Server Parameters for Maximizing Performance in Production Database

• Full Install: YES
• Demo: NO

Creating Daily Backup Job Using TM Server

• Full Install: YES
• Demo: NO

Turning Off Energy Saving Features

• Full Install: YES
• Demo: NO

(Optional) Implementing Hot Database Standby Server Depending on Load, Etc

• Full Install: NO
• Demo: NO

Click here to download the PostgreSQL Installer for Mac

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

• Click Continue on the License Agreement screen after reading it.

• Click Install to begin the actual install.

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

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

• When the installer is finished successfully, click the Close button.

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

• Go to the postgres data directory by typing cd data

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

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

• 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 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 (e.g. 192.168.9.0/24)
  • Other subnets that need to access the data (e.g. 10.1.5.0/24)
  • Any single machines that must have access (e.g. 55.66.77.88/32 (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.

• At the end, 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)
• Reference for settings in the pg_hba.conf file at https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

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

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

• Go to the Postgres data directory by typing cd data

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

• 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 https://www.postgresql.org/docs/current/static/runtime-config.html

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

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.

• 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 all energy saving options such as Prevent hard disk sleep, appnap, Do not allow the CPU to go into low processor mode, etc. Mojave Settings are to the right to prevent machine sleep.
• Turn ON features like restart the Mac after a power failure.
  • We also suggest altering the feature to auto-start the Mac at a time like '6:00am' should it just happen to be powered off. This way, your servers should always be on.
• Enable auto-login on machines with TM Server if it will have classic listeners, and set the machine so that you can lock the screen after inactivity. The classic listeners will halt if not running under a user.
  • If you cannot set a mac to auto-login, it may be that file vault and/or cloud password needs disabled.

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

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

• 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/com.apple.metadata.mds.plist

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.

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

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/com.apple.backupd-auto StartInterval -int 43200

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.

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

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

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

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

• Disable Air Play Receiver on Monterey in System Preferences >> Sharing Setup.