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 The timezone parameter is set to match the computer's timezone during the installation of Postgres. If the timezone is incorrect on the computer, you may need to correct the timezone in the config file. Refer to Wikipedia article on time zones (Use the TZ column)
ssl Change this parameter from off to on to enable encrypted TLS communication with the database. You will need to put a self signed TLS certificate into the data directory by using either the one supplied with the installer, or making your own.

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

hit the 'esc' key

(the insert mode will disappear)

Shift Q

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

wq

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