You are here

Home › Installing Theatre Manager › PostgreSQL Database Server › Windows PostgreSQL Server

Step 3: Configure PostGreSQL server parameters

Subscribe to Syndicate
Sun, 05/16/2010 - 10:00 — Doug
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.
1.

Start the PG Admin III database management application. This is found using Start Menu->Programs->PostGresSQL-> PG Admin III.

PGAdmin

If you get any helpful tips, click 'close' to get rid of them.

2.

Click on the server for this machine and login. Use the password you created when installing the database server

Double Click on the server name as per the diagram to the right Server

Type in the same password that has been used elsewhere in the install instructions. For demo database, the suggestion was 'master'.

For production databases, this wll be different.

Then click 'ok'

password

You should see a list of objects in the server.

On the 'Databases' line, there should be (2) if you have imported the database or created your own database.

 

 Database
3. Click on the Databases line to begin the next step of configuration.
4.

edit the pg_hba.conf file.

Go to the Tools menu and pick Server Configuration->pg_hba.conf file. hbaconf

Edit the pg_hba.conf so that its final settings are similar to the list below below (see *** a few lines down).

edithba
The procedure for editing is done by double clicking on an empty line and typing in the proper values for your venue - one line at a time. Make sure that:

  • Enabled is checked
  • Type is 'host'
  • Database is 'all'
  • User is 'all'
  • IP Address is described as below. You will need at least the local subnet four your network. This example shows the entry for the 192.168.0 subnet
  • Method is md5 - this is the handshaking/encryption scheme used by the clients to talk to the server.
  • NOTE: for the 127.0.0.1/32 option, set the handshaking to be TRUST to allow backups to run unaided.
  • NOTE: on Windows Vista and/or if the machine uses IPv6 (the new internet IP setting standard), you may also need to set ::1/128 to also be TRUST

 

Add lines for:

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

*** At the end, the final hba file should look similar to the list at the right. It may have more lines in it for larger venues with multiple subnets or for remote computer access.

 

 

 finalhba
Once the changes are made, click the Save icon to save the changes. Then on the main menu, select File >> Reload Server. Reload
You will be asked to confirm that the changes. Click Yes. Confirm

Click the close box and you will be asked if you want to save your changes.

Click Yes.

 
5.

Edit the postgesql.conf file

Go to the Tools menu and pick Server Configuration-> postgreSQL.conf file. postgresconf

You will then see a list of properties of the database server that can be configured.

 list

Unfortunately, they are not in alphabetical order, so you may need to scroll up and down to find the ones that are in the list below. 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.

To edit any one of the lines, scroll to find it and then double click on it.

editBuf

Most of the parameters will tell you something about them. The key values to edit are:

  • The 'Enabled' flag. If you want to turn a parameter on, then click enabled
  • Value - is what you want to set the parameter to. There are specific values for some of the parameters as described in the table below
  • Comment - this may exist as a description of what the parameter is.

 

***** 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.
listen_addresses This value should always = '*'
effective_cache_size This value should be about 512MB on a 4GB system, perhaps 1024MB on a larger system. Set the Shared memory first. In all cases the total of shared + effective must be less than available ram.
maintenance_work_mem This value should be 50 MB for machines with 1 GB of RAM or more and 20 MB for those with less. Enter values as xx MB.
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 xx MB.

Note: if you are using 64 bit server, the current maximum for this parameter seems to be 1024MB. If you make it more than that, please check that Postgres will start up when the server is rebooted. If not, adjust this parameter so that it is 1024MB or lower.
temp_buffers This value should be 50 MB for machines with 2GB of RAM or more and 20 MB for those with less. Enter values as xx MB.
work_mem This value should be 50 MB for machines with 1GB of RAM or more and 20 MB for those with less. Enter values as xx MB.
 

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
Once the changes are made, go to the 'File' menu and pick 'Reload Server' (alternatively, use the green arrow on the toolbar that is the 3rd icon from the right).

You will be asked to confirm the changes.