Migrating from MySQL to PostgreSQL

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

Migrating from MySQL to PostgreSQL

Post by mikeb » Wed Dec 06, 2017 10:28 am

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

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;
    update pay_stub_entry set rate = 0 where rate is NULL;
    update pay_stub_entry set units = 0 where units 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 DEFAULT NULL;
    alter table payroll_remittance_agency_event modify end_date timestamp NULL;
    alter table payroll_remittance_agency_event modify end_date timestamp DEFAULT NULL;
    alter table payroll_remittance_agency_event modify due_date timestamp NULL;
    alter table payroll_remittance_agency_event modify due_date timestamp 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 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 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 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';
    
  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
  5. Install a fresh copy of TimeTrex on PostgreSQL, make sure it matches the version currently installed and running on MySQL.
  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 to complete the migration.
  9. Done!

Locked