Update or Remove PostgreSQL

If you have already installed the Postgres database engine on your Windows server and need to update it, then follow the appropriate update steps. This also indicates when to make a backup after everybody has been locked out of the database.

Updating Postgres

Download the latest postgres installer from the Artsman web site. Once you have it, make sure you have done the following steps:

  1. Check the version of postgres you are running. This is in the 'About Theatre Manager' menu. Look at the bottom left of the 'about Theatre Manager' screen. You will see your database name followed by a number such as (9.4.x) or (9.3.x) etc. Record this version for later.
  2. Log everybody out of Theatre Manager, including
    • Any user at the login window and/or
    • The second generation TM Server and/or
    • Classic web listeners
  3. Edit the pg_hba.conf file to restrict access and
    • Comment out any access that is allowed from any another IP address - and only allow access from 127.0.0.1
    • Reload (or restart) the postgres server configuration to make sure no other user would be able to log in and disrupt the upgrade.
  4. Make sure you have made a backup of the database, using the procedures in the daily backup job process after everybody has been locked out.
  5. Once you have confirmed the backup exists and have made another copy of that in a different place (just to be safe), then follow the specific instructions for updating the same version or from an older version as required. Refer back to the version of the database that you recorded in the first step above
  6. After the database has been restored, edit the pg_hba.conf and
    • Un comment any access that you removed previously
    • Reload (or restart) the postgres server configuration and make sure others can now log in.
  7. Restart any web services

Updating the Same Version of PostgreSQL

These steps are for updating Postgres on a Windows server where the version of postgres is at the SAME major revision level as you are currently running. The major revision level is denoted by the first two digits of the postgres version.

Remember, do not attempt to try this unless you just made a backup of your database. Preferably, you should also have restored that backup on another machine for safety, logged into it using Theatre Manager to prove that you can restore a backup and that it has 100% integrity.

  1. Make sure you are running postgres version 9.2.0 or later.
  2. Refer to the overall instructions to download the latest TMPostGresSetup installer.
  3. run the TMPostGresSetup installer.
    1. This will place all the install files into the C:\BoxOffice folder.
    2. Do not install the latest version when asked by the installer
    3. Let the installer complete and quit out of it.
  4. Go to C:/BoxOffice and find the latest version of postgres.
    • It will have a file with a name similar to the one below - with a different version number on it reflecting the latest one.
    • As of March 2013, the current shipping version will be 'Postgresql-9.2.3-1' or later.
  5. Double click on it.
  6. It will start up the installer and default most of the settings based on the existing installation.
  7. Follow the instructions and you will generally only need to use the 'next' key to continue (a few times) until the upgrade (or install) begins.
  8. At the end you will need to restart the server.
  9. Check that you can log in to Theatre Manager from the serer or any workstation.
    1. If the Postgres Service did not start, make sure that the Postgres User password (system settings or active directory) is the same as the password for the Postgres Service in the services panel
    2. If Postgres did startup, you are done.

Updating Older Version of Postges

These steps will assist you upgrading Postgres on Windows that has an older major version number of Postgres to the most recent version. The major version number is denoted by the first two digits.

If you have postgres 9.4.x (or older), the upgrade process involves some extra steps and can be done by Arts Management Support team if you are not comfortable following the steps below.

Remember, do not attempt to try this unless you just made a backup of your database. Preferably, you should also have restored that backup on another machine for safety, logged into it using Theatre Manager to prove that you can restore a backup and that it has 100% integrity.

Upgrading older Versions of Postgres

  1. Make sure that nobody is using Theatre Manager and that all second generation servers and classic web listeners are shut down
  2. Making a manual backup of the database using the DOS bat file C:\BoxOffice\BackupTM.bat and restoring it to a dummy database to make sure the backup will restore.
  3. Recording the PG_HBA.CONF and POSTGRESQL.CONF settings unique to this server
  4. stopping the server using the 'services' control panel.
  5. Un-installing Postgres by:
    • going to Control Panel and
    • looking for 'Add or Remove Programs' (XP) or 'Programs and Features' (or whatever Microsoft changed it to in the version of windows you have)
    • Finding the installed version of Postgres that you have and 'uninstalling it'
  6. Deleting the old postgres server data directories. These will generally be in C:\Program Files\Postgres or C:\Program Files (X86)\Postgres - depending on if you have 32 or 64 bit versions.
  7. Use the download steps to obtain the latest version of the Postgres installer from the ArtsMan site.
  8. Changing the configuration parameters in PG_HBA.CONF and POSTGRESQL.CONF per the standard install instructions or you can match those that your recorded from the prior version's config files if you optimized anything. Note, you cannot simply copy the older versions of both files in the new install of postgres as parameters are sometimes added or removed.
  9. Restoring the old database by
    • Creating a new database on the server that matches its prior name. (eg, if it was called 'MyTheatreDB', call it the same name.)
    • Setting the owner to 'TheatreManager' and the Encoding to 'UTF8' (which is the default in all current versions of postgres)
    • Importing the backup of the database made at the beginning of the instructions into the new database server.
    • Starting Theatre Manager and attempting to log in. (if you cannot, make sure that the pg_hba.conf is correct)
  10. Setting up the backup job again and verifying that it works. Verifying it works means that you actually run it under the task scheduler and ensure that the backup file sizes are as expected.

Removing Postgres from Windows

Postgres can be removed buy un-installing it and then removing the data directory.

  1. Preferred Method:
    1. Go to Setup->Settings->Control Panel->Add/Remove Programs
    2. Find the line that refers to your Postgres installation
    3. Use the remove option
    4. Delete the C:\Program Files\PostgreSQL folder to remove your database. (Note: it could be on another drive)
  2. Secondary Method:
    1. Run the PostGres installer and 'uninstall' the database first, and then run the install process -and/or-
    2. Delete the C:\Program Files\PostgreSQL folder, (Note: it could be on another drive) -and/or-
    3. Remove the 'postgres' user from you list of users using the admin tools -and/or-
    4. make sure that the PostGres server is not running as a service and has been removed using RegEdit