Re: [gentoo-user] Re: mysql to postgresql migration

[J. Roeleveld]

On 15 July 2014 19:40:14 CEST, James <wireless@×××××××××××.com> wrote:
>J. Roeleveld <joost <at> antarean.org> writes:
>
>
>> >I recently ran across this script: py-mysql2pgsql [1]
>> >and this discussion on it's origin [2]. I'm keenly interested
>> >in the recommendations of others for migrating mysql databases
>> >to postgresql and any comments on this aforementioned script
>> >or other methodologies....
>> >[1] https://github.com/philipsoutham/py-mysql2pgsql
>> >[2]
>> >http://www.tryolabs.com/Blog/2012/02/10/django-migrating-mysql-
>postgresql/
>> James,
>
>> I haven't looked into this recently. But I believe that the DDLs and
>data
>> can be migrated relatively easy.
>
>> Just be aware that software specifically written using MySQLs version
>of >
>SQL is unlikely to work on a different RDBMS without extensive
>rewrites.
>
>So, If you run the same program, say gnucash, on top of mysql, then
>migrate
>the mysql dB it to pgsql, it will require an extensive rewrite?

Not always. But if the software was written using the non standard SQL that is common when the developers only know MySQL then you are likely to find that the SQL is invalid for other databases.

>This shouild be an easy example, which is quite common (google).
>So, let's just say that I run across mysql --> pgsql quite often to the
>point that it's time for me to develop some slick_skills here.

I deal with migrations and integration projects on a daily basis as part of my job. Some are simple. Some require extensive skills and knowledge. 

>> This is the biggest problem people are facing when porting websites
>> to use a different database.
>> What is the reason for migrating and what kind of data and
>> applications are you using?
>> Joost
>
>Another more serious problem:
>I'm not porting websites, but more working on science applications with
>huge data. Some of it is organized via mysql, others are more
>in the form of vary large test vectors (matricies) that are sparsely
>populated.  Others portions are double float or other forms of
>scientific
>data.   So in this case there is not a one-2-one semantic. But, I do
>need to extract (dump?) mysql into a form where I can later
>include it into a much larger, designed from the ground floor up,
>pgsql dB.  I relaize this sort of effort is unique, but surely some
>additional slick_tools exist for this sort of effort?

The tools that exist to make these things easier require plenty of practice and experience to use properly.
For your usecase, if not too often, I would recommend exporting the DDL (all create table/index/.... statements) and export the table contents to CSV files (with headers to ensure data goes back to correct columns)

--
Joost


-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.