Urlaubsverwaltung 5.0 Migration Guide To PostgreSQL

Migration Guide to PostgreSQL

Support for MySQL/MariaDB will be discontinued starting from Urlaubsverwaltung 5.0.0.

Proof of concept ahead As the technical details may differ from system to system, this guide comes without any warranty.
Please proceed at your own risk. In doubt please refer to the documentation of the tools used.

Hint This script can be used to migrate data from 4.72.1 to 5.0.0 (other versions will not be supported).
After you migrated the data into the PostgreSQL that is used by a Urlaubsverwaltung 5.0.0
you can just upgrade the Urlaubsverwaltung to a newer version and the schema migrations will be done via Liquibase.

Preparation

1. Upgrade your Urlaubsverwaltung installation at least to version 4.72.1
2. Stop Urlaubsverwaltung
3. Create a database backup of your instance.

Install PostgreSQL

see more on postgresql.org

Migrate data to PostgreSQL

Install pgloader

see how to install pgloader on pgloader.readthedocs.io

Create pgloader command file

Create the file /tmp/pgloader-command with the following content

LOAD DATABASE
    FROM mysql://${mariadb_username}:${mariadb_password}@localhost:${mariadb_port}/${database_name}
    INTO pgsql://${pqsql_username}:${pgsql_password}@localhost:${pgsql_port}/${database_name}

WITH data only

MATERIALIZE VIEWS
  'department_member_view' AS $$ select department_id, members_id, accession_date from department_member; $$,
  'settings_view' AS $$ select id, default_vacation_days, do_remaining_vacation_days_expire_globally, maximum_annual_vacation_days, allow_half_days, days_before_remind_for_upcoming_applications, days_before_remind_for_upcoming_holiday_replacement, days_before_remind_for_waiting_applications, maximum_months_to_apply_for_leave_in_advance, maximum_months_to_apply_for_leave_afterwards, remind_for_upcoming_applications, remind_for_upcoming_holiday_replacement, remind_for_waiting_applications, overtime_maximum, overtime_minimum, overtime_minimum_reduction, overtime_active, overtime_reduction_without_application_active, overtime_write_privileged_only, days_before_end_of_sick_pay_notification, maximum_sick_pay_days, timezoneid, work_day_begin_hour, work_day_end_hour, federal_state, friday, monday, saturday, sunday, thursday, tuesday, wednesday, working_duration_for_christmas_eve, working_duration_for_new_years_eve, gravatar_enabled from settings; $$

INCLUDING ONLY TABLE NAMES MATCHING ~/.*/
EXCLUDING TABLE NAMES MATCHING 'DATABASECHANGELOG','DATABASECHANGELOGLOCK','department_member','shedlock','SPRING_SESSION','SPRING_SESSION_ATTRIBUTES','settings'

ALTER TABLE NAMES MATCHING 'department_member_view' RENAME TO 'department_member'
ALTER TABLE NAMES MATCHING 'settings_view' RENAME TO 'settings'

ALTER SCHEMA 'urlaubsverwaltung' RENAME TO 'public'

BEFORE LOAD DO
  $$ truncate table public.vacation_type cascade; $$,
  $$ truncate table public.sick_note_type cascade; $$,
  $$ truncate table public.special_leave_settings cascade; $$,
  $$ truncate table public.settings cascade; $$

AFTER LOAD DO
  $$ SELECT setval('absence_mapping_id_seq', (SELECT MAX(id) FROM absence_mapping)); $$,
  $$ SELECT setval('account_id_seq', (SELECT MAX(id) FROM account)); $$,
  $$ SELECT setval('application_comment_id_seq', (SELECT MAX(id) FROM application_comment)); $$,
  $$ SELECT setval('application_id_seq', (SELECT MAX(id) FROM application)); $$,
  $$ SELECT setval('calendar_accessible_id_seq', (SELECT MAX(id) FROM calendar_accessible)); $$,
  $$ SELECT setval('company_calendar_id_seq', (SELECT MAX(id) FROM company_calendar)); $$,
  $$ SELECT setval('department_calendar_id_seq', (SELECT MAX(id) FROM department_calendar)); $$,
  $$ SELECT setval('department_id_seq', (SELECT MAX(id) FROM department)); $$,
  $$ SELECT setval('overtime_id_seq', (SELECT MAX(id) FROM overtime)); $$,
  $$ SELECT setval('overtime_comment_id_seq', (SELECT MAX(id) FROM overtime_comment)); $$,
  $$ SELECT setval('person_calendar_id_seq', (SELECT MAX(id) FROM person_calendar)); $$,
  $$ SELECT setval('person_id_seq', (SELECT MAX(id) FROM person)); $$,
  $$ SELECT setval('settings_id_seq', (SELECT MAX(id) FROM settings)); $$,
  $$ SELECT setval('sick_note_id_seq', (SELECT MAX(id) FROM sick_note)); $$,
  $$ SELECT setval('sick_note_comment_id_seq', (SELECT MAX(id) FROM sick_note_comment)); $$,
  $$ SELECT setval('special_leave_settings_id_seq', (SELECT MAX(id) FROM special_leave_settings)); $$,
  $$ SELECT setval('working_time_id_seq', (SELECT MAX(id) FROM working_time)); $$;

Please replace everything in the ${...} with your values.

Generate Schema in PostgreSQL via Urlaubsverwaltung

1. Update at least to Urlaubsverwaltung 5.0.0 (see Migration Guide)
2. Start the Urlaubsverwaltung
   1. This will create the schema
3. Stop the Urlaubsverwaltung

Migrating data from MariaDB into PostgreSQL

you can check your configuration by running:

$ pgloader --dry-run /tmp/pgloader-command

and if everything seems good. You can migrate the data via

$ pgloader --verbose /tmp/pgloader-command

Now your data was moved from the MariaDB instance into the PostgreSQL.

Update to the latest 5.x Version

Now the data is migrated from the MariaDB to your new PostgreSQL instance. You can upgrade the UV to the latest version.

Please do not migrate the data to a later version than 5.0.0.

Have fun!