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.

Another way to get the key config parameters is to use pgtune and paste the results at the bottom of the postgresql.conf file

1. Configuring postgres entails editing two files in the 'data' folder of the current postgres installation as highlighted below:
  • Navigate to C:\Program Files\PostgreSQL\15\data
  • The 15 in the path above may be different for you.
    • It could be the number 9 or higher - if you are using versions of Postgres older than version 15 (which is current at the time of writing)
    • If there are versions newer than 15, then this number could be 15 or higher.
    • You will have to look to be sure.
2. edit the pg_hba.conf file using Notepad++
Scroll to the bottom of the pg_hba.conf file and you should see something similar to the image to the right
LOCAL ACCESS Leave Local Access as:
  • scram-sha-256 (postgres 13 and later) -or-
  • MD5 (postgres 12 and earlier) -or-
Under IPV4 Local Connections Add lines for any subnets that need access:
  • 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.

Save your changes and RELOAD the server (see step 4)
3. Edit the postgesql.conf file. Some notes about editing:
  • Parameters are not in alphabetical order, so you may need to scroll up and down to find the ones that are in the list below (or use find) . 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.
***** 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.
An easy way to determine 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).
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. If you have a number of users and web listeners, you might need to make it 150, 200 or even 400.
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
Save your changes and RELOAD the server (see step 4) for most changes. If the change does not take effect, you may need to RESTART the server. This is usually only when first installing Postgres as changes to this file are rare
4. Reload or restart the postgres server to cause the new settings to be used.

All changes to the pg_hba.conf and many changes to postgresql.conf can be reloaded. That put the parameters in place without restarting the server, so any services (like web sales) that are running, continue to run.

A reload is done by:

  • using the windows start menu
  • finding the 'PostgreSQL application grouping
  • using the reload Configuration application

A few changes to the postgresql.conf file will require the postgres server to be completely restarted. This is usually noted in the comments beside the parameter. If this is required, the general steps are below. It is very rare that these parameters are changed after installation - so it might be best to ask support to help you with a complete server restart if you are not familiar:

  • Open services control panel
  • Find the postgresql service
  • Stop the postgresql service
  • Start the postgresql service
  • Make sure it is running
  • Restart the web services

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\x\data where 'x' is the version of PostgreSQL you have installed.
  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.