Re: Getting Mysql data into Postgres: least painful methods?

On 01/15/2013 04:09 PM, Ken Tanzer wrote:
> Thanks for all the responses.  I would totally go with the FDW, were I
> not stuck using (someone else's) 8.4.

8.4 supports FDW so I will assume you do not have the permissions to
create one.

>
> In this case it looks like 24 tables, with CSV-like import files
> totaling 7G.
>
> Since there didn't seem to be a clean, simple and automated path from
> mysql to postgres, I'm back to skipping mysql entirely and just trying
> to modify the mysql files to feed directly into postgres.
>
> To that end, they have to be transformed a bit, which I've written a bit
> of script to accomplish.  I'm wondering if there's a way to avoid
> creating another 7G of slightly-modified import files before feeding
> them to postgres.  Specifically, is there a way to do something like
>
> \copy my_table FROM '`cat my_import_file | my_transform_script`'


The way I have done this is to create a script using Python that follows
this flow:

MySQL --> MySQLdb module --> Data transform --> psycopg2 --> Postgres
                             OR
csv --> csv module ------^


In the script you can set up the transactions as you like, per row,
batches, or everything in one transaction.

>
> My 2 goals here are to be somewhat efficient (by not duplicating the
> input files), and to keep this all within a transaction.  I could have
> the script transform each file separately and pipe it to postgres:
>
> (echo 'copy mytable from stdin...' ; cat my_import_file |
> my_transform_script ) | psql
>
> but I'm thinking that there's no way to group those all into a transaction.
>
> Hopefully this makes sense, and any suggestions welcome.  Thanks.
>
> Ken



--
Adrian Klaver
adrian.klaver@gmail.com