Update or Remove PostgreSQL

If you have already installed the Postgres database engine on your Macintosh 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. (optional) If you are worried that staff people might log in to Theatre Manager while the upgrade is happening, use PGAdmin (Tools->Server Configuration menu) to 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. You do this by double clicking on the row containing the IP address and unchecking it.
    • 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. (only after everybody is logged 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
    • Uncomment 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 Postgres

These steps are for updating Postgres on a Macintosh OSX 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.

If you are using Hot Streaming Replication, so MUST stop the replication server FIRST and upgrade the version of postgres on it before doing the main server. If not, you may end up redoing the replication server.

Always verify that replication is working properly after a postgres update.

  1. Make sure you are running postgres version 9.2 or later.
  2. Refer to downloading the latest Mac Installer for postgres
  3. Make sure you have just made a backup of the databases in the server
  4. use terminal and PG_CTL to 'stop' the database
    a) start terminal
    b) type su - postgres
    c) provide the password
    d) type pg_ctl stop -m immediate

  5. Run the installer which will update and restart an existing PostgreSQL installation.

    Make sure to read the next step before starting the install to decide if you can do an easy install or the custom install.

  6. If you have set up the hot standby server, make sure to
    • stop the hot standby at the same time
    • Only use the custom install to update the MAIN server (which does not install a revised demo database) as per below.

    • Upgrade the Hot Standby to the same version of the database server using the same custom installer.
    • Verify that replication is still working after the upgrade
  7. try log in to Theatre Manager afterwards

Update Older Version of Postgres

These steps assist migrating an older version of Postgres to the most recent. 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.

The steps are:

  1. Sign all users out of Theatre Manager and stop all TM Web Services
  2. Make a manual backup of the database using the terminal command php /Users/Shared/backupTM.php (and restore the backup to a 'test' database to ensure it works).
  3. Save the PG_HBA.CONF and the POSTGRESQL.CONF settings unique to this server
  4. Stopping the Postgres server
  5. Removing the old postgres server by running the un-installer found in the /Library/PostgreSQL/x.x directory.
    • This will only uninstall postgres and the services
    • Once complete, you will need to move (or delete) the /Library/PostgreSQL/x.x directory which now should contain only the 'data' folder.
    • The 'data' folder is retained only for temporary safekeeping until you have installed the next version of Postgres and restored your data satisfactorily.
  6. Installing the new postgres server using the Theatre Manager postgres installer for OSX
  7. Reconfiguring the parameters for postgres for PG_HBA.CONF and the POSTGRESQL.CONF
  8. Restoring the main database from the backup
    • Create the new database with the owner 'TheatreManager', and using encoding "UTF8'. If the TheatreManager user is not in the database, contact support right away and do not continue.
    • Restore the backup of the database
  9. setting up the backup job again
  10. If you previously set up the hot standby server, you will need to follow the complete installation steps for and set it up again for the new version of postgres.
    Replication is a feature of postgres and is automatically set up for cloud venues. Self service venues may set this up if they wish - the support team is unable to help you.

Removing Postgres

Use the following steps to remove postgres from a macintosh OSX server.
  1. Stop the postgres database using the

    pg_ctl stop -m immediate' command

  2. When the server is stopped, use the un-install program in the /Library/PostgreSQL/x.x directory to get rid of it properly
  3. throw out the entire folder called /Library/PostgreSQL
  4. restart the mac