You are here

Home › Installing Theatre Manager › PostgreSQL Database Server › Macintosh PostgreSQL Server › Step 2: Configure the PostgreSQL server parameters

POSTGRESQL.conf file

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

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

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

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

su - postgres

enter the password
2. Go to the Postgres data directory by typing

cd data

3.

This will use VI to edit it. Type

VI postgresql.conf

when the list appears, type

i

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

 

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

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

For any setting that is about disk space or memory, you can type 1GB, 1000MB, or 1000000KB - they are all equivalent. Do not leave a space between the number and the memory amount at the GB, MB or KB; otherwise, Postgres will not start.
4. use pgtune to get some optimized parameters for your machine and replace the values below. It is probably better to append the results from pgtune at the end and filling any missing settings at the bottom.
bonjour If you wish your Postgres server to be discoverable using Bonjour services so that the Mac version of Theatre Manager can automatically locate a server on the network, this value can be uncommented and changed from off to on

It will probably look like #bonjour = off. Remove the # from the front of the line (if any) to activate that parameter and change off to on
max_connections The default is 100 which may be fine for most venues. For venues with more users and web servers, 200 to 400 is suggested.
shared_buffers This value should be 20-25% of the total system total RAM. You find this value on the task manager as the total physical memory. Enter values as xxMB.
temp_buffers This value should be 20MB.
work_mem This value should be 20MB. Enter values as xxMB.
effective_cache_size This value should be about 75% of AVAILABLE ram. So, on a 4GB system, this value would be 3072MB. Set the shared memory first. Shared memory is part of the effective cache size. If there is enough available ram in the machine, to exceed the size of the database, it means most reads will be cached in memory.
timezone and log_timezone we recommend changing both of these to 'GMT'. Theatre Manager is timezone aware and the timezone that the system is operating at is defined in company preferences or can be changed per machine in the devices for those who have laptops, travel and want their time zone to change automatically based on machine settings.
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.
password_encryption Change the minimum encryption to md5 from scram-sha-256. TM desktop will use scram encryption to allow any external user to have complete security. TM server can only use md5 at this time for historical reasons. You must allow any IP from which TM server accesses the database use md5. These will all be internal IP's, and typically will be 127.0.0.1/32 or so.

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

hit the 'esc' key

(the insert mode will disappear)

Shift Q

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

wq

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