PostgreSQL.md

layout	title
default	PostgreSQL

This document explains how UniTime can be used with PostgreSQL. You can either create a new database (follow step 3A and skip 3B) or migrate an existing MySQL database (follow step 3B instead of 3A).

1. Prerequisites

Install PostgreSQL 12.0 (e.g., from https://www.postgresql.org/download/), using the default port 5432, and a custom password.

Download the JDBC Driver (e.g., from https://jdbc.postgresql.org/download.html,) and place it under Tomcat/lib.

If migrating from an existing MySQL database, install pgloader. See https://github.com/dimitri/pgloader for instructions.

UniTime needs to be updated to version 4.4.139 or later.

2. Create timetable user and timetable database

Using the same name and credentials as the default UniTime database:

createuser --interactive --pwprompt -U postgres
Enter name of role to add: timetable
Enter password for new role: unitime
Enter it again: unitime
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
Password: <password provided during install>
createdb timetable -U timetable -O timetable
Password: unitime

3A. Create database schema and populate it with the initial content (Variant A)

Use Documentation/Database/PostgreSQL/timetable.sql to create the database schema and populate it with initial data.

psql -U timetable <timetable.sql
Password: unitime

Note: The timetable database will contain the woebegon-example data as the online demo. You can delete the two example sessions once a new session is created (the status needs to be changed to Session Finished first), using the Administration > Academic Sessions > Academic Sessions page.

3B. Migrate an existing MySQL database (Variant B)

Create configuration file, e.g., migration.cfg:

LOAD DATABASE
  FROM mysql://timetable:unitime@localhost:3306/timetable
  INTO postgresql://timetable:unitime@localhost/timetable

CAST type int when (= precision 1) to boolean drop typemod using tinyint-to-boolean,
  type decimal when (and (= precision 1) (= scale 0)) to boolean drop typemod using tinyint-to-boolean,
  type decimal when (and (= precision 20) (= scale 0)) to bigint drop typemod,
  type decimal when (and (= precision 22) (= scale 0)) to bigint drop typemod,
  type decimal when (and (= precision 19) (= scale 0)) to bigint drop typemod,
  type decimal when (and (= precision 10) (= scale 0)) to int drop typemod
;

Execute pgloader:

pgloader -v migration.cfg

Note: If you get the MYSQL-UNSUPPORTED-AUTHENTICATION error, make the following changes:

A) Edit my.cnf and in [mysqld] section add the following line (restart MySQL afterward):

default-authentication-plugin=mysql_native_password

B) Update timetable user password to mysql_native_password (using mysql -uroot -p):

alter user timetable@localhost
  identified with mysql_native_password by 'unitime';

Once done, run the followings SQLs (using psql -U timetable)

alter table distribution_type add temp_seq boolean default false;
update distribution_type set temp_seq = 't' where sequencing_required = '1';
alter table distribution_type drop sequencing_required;
alter table distribution_type rename column temp_seq to sequencing_required;

4. Update UniTime connection properties

In the UniTime custom properties (see UniTime Installation, section Customization), replace MySQL connection properties with the following:

connection.url=jdbc:postgresql://localhost:5432/timetable
connection.driver_class=org.postgresql.Driver
dialect=org.hibernate.dialect.PostgreSQL9Dialect
connection.username=timetable
connection.password=unitime
default_schema=timetable
hibernate.dbcp.validationQuery=select 1
hibernate.globally_quoted_identifiers=true

Start UniTime, check the logs for any errors.