'apps' database cluster

Purpose and Layout

The 'apps' database cluster contains databases to support internally-developed applications for the School. The cluster contains three databases:

• odyssey_dev - For development
• odyssey_test - For testing changes intended for production - the "dress rehearsal" for updating prod; not currently in regular use
• odyssey - Production database

Each application has a schema associated with it. We use schemas since we expect data in some applications may be used by others. It is expected that the set of schemas in each database will be more or less the same.

Applications Supported

• All the applications listed in the Odyssey Overview
• ASIS
• OAT database

Connection Information

Applications will authenticate with a userid and local password. Developers and administrators connect using their WatIAM userids and a Postgres-specific password. The cluster is accessed using hostname postgres.odyssey.uwaterloo.ca, port number 5432. Connections are restricted to the UW campus network.

Schema Change Control

Production Database

If you wish to make any schema change in a production database, you must do the following:

• Notify tg-dba outlining what changes you want to make.
• Once you get the OK from tg-dba, dump the prod database and restore to the test database.
• The test database should be a replica of the prod database.
• Develop a set of commands that will bring the current production schema in sync with your new schema.
• Run the set of commands you developed in the previous step against the test database.
• Test your application/code against the test database.
• If you ran into problems, fix them, then repeat the above steps.
• Once you are satisfied that your changes are OK, schedule a time for updating the production database.
• Make sure the appropriate stakeholders are notified about the time of the change to the production schema and make arrangements for a planned outage (if necessary).

Test Database

Follow the above procedures for the production database, since the whole point of schema changes on the test database is for a change to the production version!

Development Database

It is expected that ongoing schema changes will happen on a development database. In this case, normal rules for change control in software development should apply. If you are a lone developer working in your own schema, you should be able to change at will. If you are working on something with others, you should at least notify others of your changes!

Sysadmin support

Access to the apps cluster is via connection to postgres.odyssey.uwaterloo.ca port 5432.

Admin access can be gained by becoming user postgres on that server; psql will access the database without a password.

A manual backup can be performed via: pg_dumpall >dump.sql

A quick test of whether the database is working can be performed by accessing https://odyssey.uwaterloo.ca/sub - the RSG subscriptions application.

Restarting postgres can be done via: service postgresql restart