Migrating back end data from Mysql to Postgres Howto

Topics brought up by the TimeTrex open source community.
Locked
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Migrating back end data from Mysql to Postgres Howto

Post by andrewbauman »

Hello TimeTrex onsite users.
I Have recently gone through the process of migrating my data to postgres.
This Post will become a how to.
I will attempt to incorporate current experiences from user comments below.

Enviroment:
-Proxmox virtual machine on ridiculous hardware. 4 core 16 g Ram and solid state drive.
Clearos LAMP stack
PROXMOX container for postgresql 9.2

PROBLEM: timetrex 7.x human resource module did not work. (SQL errrors in mysql) Recent update had more similar issues.

SKILLS required.
linux cmd line comfort and a good googling knowledge.

After ensuring that I had a good backup and creating a working copy.

1
  • delete all the id_seq tables out of the database
2
  • dump database with no arguments
3
  • run dump through mysql2pgsql.perl
4
  • upload the result into a empty working copy on postgres
5
  • dump that using pgdump with --column-inserts
6
  • change the settings in ini.php to point at the new server and an empty database.
7
  • run the timetrex installer.
8
  • empty the database
9
  • upload dump from step 5
10
  • Deal with errors
11 Done.

I had the conversion script misname the debit_account column in pay_stub_entry.
Timetrex used Null values in Mysql table pay_stub_entry but postgres had a no null constraint.
this caused a considerable investment of time because I had not carefully deal with all the errors listed in Step 10.
I dumped the table using postgres admin into a csv file used replace in excel to change /N to 0; saved that and uploaded it into the table. :D
Andrew Bauman
shaunw
Posts: 7839
Joined: Tue Sep 19, 2006 2:22 pm

Re: Migrating back end data from Mysql to Postgres Howto

Post by shaunw »

Thanks for posting this Andrew, before you started this process did you look at the "tools/convert_mysql_to_postgresql.php" script, specifically the comments at the top of that script?
andrewbauman
Posts: 43
Joined: Mon Jan 23, 2012 9:40 am

Re: Migrating back end data from Mysql to Postgres Howto

Post by andrewbauman »

Hello.
Yes I did in fact test those instructions.
I did see that those are not TESTED or SUPPORTED same for my comments!!

However I was unable to figure out how to make the convert....php script work.
I ran it in browser and in a cli; But it only gave me blank sql.
So then after dissecting what the script was doing, I decided to test if a insert statement would automatically update the sequence id in postgres. After deleting the the Id tables I tried the compatible mysql dump. This dump did run with errors but not the errors I was expecting. These errors were making a big pile of missing data. So google became my buddy and I ended up with the above process.
Yes the sequences are automatically made correct without the special update script.

I would be interested in hearing from others with real world conversion experiences
I will try to maintain the top post with the best known working info.
Andrew Bauman
Locked