philipsoutham
Mangle case-sensitive schemas
Sorry if this is not the right place, couldnt find another... :) I need to migrate a MySQL database to a PostgreSQL server. On mysql I have all lower-case database, tables and field names, but on Postgres I have CamelCase names. This is the bacula (linux backup tool) database, and thus I cannot change any definition on both servers. When I run py-mysql2pgsql it tells me:
"psycopg2.ProgrammingError: ERROR: relation "BaseFiles" does not exist"
or something like that in pt_BR. Couldn't your script handle this, or can you suggest a workaround?
Tks, best regards.
Maybe there are some native ways for doing this for bacula? There are some other rambling on the net like http://mtu.net/~jpschewe/blog/2010/06/migrating-bacula-from-mysql-to-postgresql/ but there's nothing about CamelCase.
I don't think personally that this project would help you. It can be used for preliminary data import but MySQL and PostgreSQL differ a lot under the hood and people writing interfaces know that. It's quite possible that database structure would be different for this bases. There are even edge cases were highly optimized MySQL query looks plain gibberish and behaves so on PostgreSQL and vice versa.
I think it will be great if there is some name translation option. For this case it will be best if we can ie set lowercasing table and column names in yaml (I know this is too specific and probably one use case only). For your info, bacula scheme is same in postgresql and mysql - only difference is came casing.
Anyway I ended up with this nice tool with same problem as mmerlone. But from what I found postgresql is really nice and allows you to rename table and columns so I will probably end up with migrating data using this tool and then rename tables and columns if needed (as I'm not sure if bacula will complain).
Edit: just a quick update. Bacula needs exact casing in column and table names. I ended with importing correct schema into postgresql and script which alter table and column names to camelcase and back. So I convert it to camelcase, do data-only import with py-mysql2pgsql and then convert it back to lowercase.
