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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
