Migrating from MySQL to PostgreSQL

Frequently Asked Questions
Locked
mikeb
Posts: 725
Joined: Thu Jul 27, 2006 11:58 am

Migrating from MySQL to PostgreSQL

Post by mikeb »

MySQL support was deprecated in v11.0 and removed in v11.1. All installations of TimeTrex will need to be migrated to the PostgreSQL database prior to upgrading to v11.1 or newer.

As always, please ensure that you have a backup of your TimeTrex database and TimeTrex files/directories prior to following this procedure.

Procedure to Convert MySQL to PostgreSQL:
  1. Upgrade to latest v11.0.X of TimeTrex still using MySQL. Confirm that you can still login to TimeTrex and that everything is working as normal.
  2. Run the following SQL queries from the MySQL console:

    Code: Select all

    drop table if exists user_tax;
    drop table if exists policy_group_accrual_policy_id_seq;        
    drop table if exists policy_group_over_time_policy_id_seq;      
    drop table if exists policy_group_premium_policy_id_seq;        
    drop table if exists policy_group_round_interval_policy_id_seq;
    update pay_stub_entry set rate = 0 where rate is NULL;
    update pay_stub_entry set units = 0 where units is NULL;
    update exception set acknowledged_reason_id = '00000000-0000-0000-0000-000000000000' where acknowledged_reason_id is NULL; 
    update user_date_total set src_object_id = '00000000-0000-0000-0000-000000000000' where src_object_id is NULL;
    update user_date_total set currency_id = '00000000-0000-0000-0000-000000000000' where currency_id is NULL;
    set explicit_defaults_for_timestamp = TRUE;
    set SQL_MODE='ALLOW_INVALID_DATES';
    alter table payroll_remittance_agency_event modify start_date timestamp NULL;
    alter table payroll_remittance_agency_event modify start_date timestamp NULL DEFAULT NULL;
    alter table payroll_remittance_agency_event modify end_date timestamp NULL;
    alter table payroll_remittance_agency_event modify end_date timestamp NULL DEFAULT NULL;
    alter table payroll_remittance_agency_event modify due_date timestamp NULL;
    alter table payroll_remittance_agency_event modify due_date timestamp NULL DEFAULT NULL;
    alter table payroll_remittance_agency_event modify last_due_date timestamp NULL;
    alter table payroll_remittance_agency_event modify last_due_date timestamp NULL DEFAULT NULL;
    alter table payroll_remittance_agency_event modify next_reminder_date timestamp NULL;
    alter table payroll_remittance_agency_event modify next_reminder_date timestamp NULL DEFAULT NULL;
    alter table payroll_remittance_agency_event modify last_reminder_date timestamp NULL;
    alter table payroll_remittance_agency_event modify last_reminder_date timestamp NULL DEFAULT NULL;
    update payroll_remittance_agency_event set start_date = NULL where start_date = '0000-00-00 00:00:00';
    update payroll_remittance_agency_event set end_date = NULL where end_date = '0000-00-00 00:00:00';
    update payroll_remittance_agency_event set due_date = NULL where due_date = '0000-00-00 00:00:00';
    update payroll_remittance_agency_event set last_due_date = NULL where last_due_date = '0000-00-00 00:00:00';
    update payroll_remittance_agency_event set next_reminder_date = NULL where next_reminder_date = '0000-00-00 00:00:00';
    update payroll_remittance_agency_event set last_reminder_date = NULL where last_reminder_date = '0000-00-00 00:00:00';
    alter table pay_period_schedule modify primary_date timestamp NULL;
    alter table pay_period_schedule modify primary_date timestamp NULL DEFAULT NULL;
    alter table pay_period_schedule modify primary_transaction_date timestamp NULL;
    alter table pay_period_schedule modify primary_transaction_date timestamp NULL DEFAULT NULL;
    alter table pay_period_schedule modify secondary_date timestamp NULL;
    alter table pay_period_schedule modify secondary_date timestamp NULL DEFAULT NULL;
    alter table pay_period_schedule modify secondary_transaction_date timestamp NULL;
    alter table pay_period_schedule modify secondary_transaction_date timestamp NULL DEFAULT NULL;
    update pay_period_schedule set primary_date = NULL where primary_date = '0000-00-00 00:00:00';
    update pay_period_schedule set primary_transaction_date = NULL where primary_transaction_date = '0000-00-00 00:00:00';
    update pay_period_schedule set secondary_date = NULL where secondary_date = '0000-00-00 00:00:00';
    update pay_period_schedule set secondary_transaction_date = NULL where secondary_transaction_date = '0000-00-00 00:00:00';
    alter table station modify pull_start_time timestamp NULL;
    alter table station modify pull_start_time timestamp NULL DEFAULT NULL;
    alter table station modify pull_end_time timestamp NULL;
    alter table station modify pull_end_time timestamp NULL DEFAULT NULL;
    alter table station modify push_start_time timestamp NULL;
    alter table station modify push_start_time timestamp NULL DEFAULT NULL;
    alter table station modify push_end_time timestamp NULL;
    alter table station modify push_end_time timestamp NULL DEFAULT NULL;
    alter table station modify partial_push_start_time timestamp NULL;
    alter table station modify partial_push_start_time timestamp NULL DEFAULT NULL;
    alter table station modify partial_push_end_time timestamp NULL;
    alter table station modify partial_push_end_time timestamp NULL DEFAULT NULL;
    update station set pull_start_time = NULL where pull_start_time = '0000-00-00 00:00:00';
    update station set pull_end_time = NULL where pull_end_time = '0000-00-00 00:00:00';
    update station set push_start_time = NULL where push_start_time = '0000-00-00 00:00:00';
    update station set push_end_time = NULL where push_end_time = '0000-00-00 00:00:00';
    update station set partial_push_start_time = NULL where partial_push_start_time = '0000-00-00 00:00:00';
    update station set partial_push_end_time = NULL where partial_push_end_time = '0000-00-00 00:00:00';
    
  3. From the TimeTrex directory, run:

    Code: Select all

    php tools/convert_mysql_to_postgresql.php truncate > delete_all_data.sql
  4. Dump the MySQL database with the following command, be sure to replace <database user>, <database password>, and <TimeTrex_Database_Name> with your corresponding information:

    Code: Select all

    cat delete_all_data.sql > timetrex_mysql.sql ; echo "SET TIME ZONE 'UTC'; SET standard_conforming_strings = 'off';SET backslash_quote = 'on';SET escape_string_warning = 'off';" >> timetrex_mysql.sql ; mysqldump -t --tz-utc=true --default-character-set=utf8 --compatible=postgresql --complete-insert --extended-insert=FALSE --no-create-info --skip-quote-names --skip-comments --skip-lock-tables --skip-add-locks --verbose -u <database user> -p<database password> <TimeTrex_Database_Name> >> timetrex_mysql.sql ; echo "WITH pp_tz AS ( SELECTpp.*, (extract(TIMEZONE FROM pp.start_date :: TIMESTAMP AT TIME ZONE pps.time_zone) :: TEXT || ' secs') :: INTERVAL AS utc_offset FROM pay_period AS pp LEFT JOIN pay_period_schedule AS pps ON (pp.pay_period_schedule_id = pps.id) ) UPDATE pay_period SET start_date = (pay_period.start_date + pp_tz.utc_offset), end_date = (pay_period.end_date + pp_tz.utc_offset), transaction_date = (pay_period.transaction_date + pp_tz.utc_offset) FROM pp_tz WHERE pay_period.id = pp_tz.id; WITH pp_tz AS ( SELECTpp.*, (extract(TIMEZONE FROM pp.start_date :: TIMESTAMP AT TIME ZONE pps.time_zone) :: TEXT || ' secs') :: INTERVAL AS utc_offset FROM pay_period AS pp LEFT JOIN pay_period_schedule AS pps ON (pp.pay_period_schedule_id = pps.id) ) UPDATE pay_stub SET start_date = (pay_stub.start_date + pp_tz.utc_offset), end_date = (pay_stub.end_date + pp_tz.utc_offset), transaction_date = (pay_stub.transaction_date + pp_tz.utc_offset) FROM pp_tz WHERE pay_period_id = pp_tz.id;" >> timetrex_mysql.sql
  5. Install a fresh copy of TimeTrex on PostgreSQL, make sure it matches the version currently installed and running on MySQL. Ensure that you fully complete the web-based installer including create a new company and administrator user (these will be deleted in the next step).
  6. Run the following command, be sure to replace <database user>, <TimeTrex_Database_Name> with your corresponding information:

    Code: Select all

    psql -U <database user> -v ON_ERROR_STOP=1 -f timetrex_mysql.sql <TimeTrex_Database_Name>
    If you see any errors appear on the screen, not all your data was migrated, therefore please post the errors to the forums and we will be happy to assist you in resolving them.
  7. Copy the "salt" line from the old MySQL timetrex.ini.php to the new PostgreSQL timetrex.ini.php file.
  8. Run the TimeTrex web-based installer using the PostgreSQL database to complete the migration.
  9. You should now be able to login with your original usernames/passwords to confirm that the data was migrated properly.
Important things to check:
  • TimeSheets, ensure that punch times and daily totals match.
  • Pay Periods, ensure that start/end/transaction dates/times match.
  • Pay Stubs, ensure that pay stub data matches.
Locked