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
- dump database with no arguments
- run dump through mysql2pgsql.perl
- upload the result into a empty working copy on postgres
- dump that using pgdump with --column-inserts
- change the settings in ini.php to point at the new server and an empty database.
- run the timetrex installer.
- empty the database
- upload dump from step 5
- Deal with errors
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.