Re: Adding data from mysql to postgresql periodically

On Sun, Nov 14, 2010 at 4:12 PM, Leif Biberg Kristensen
<leif(at)solumslekt(dot)org> wrote:
> On Sunday 14. November 2010 13.44.53 franrtorres77 wrote:
>>
>> Hi there
>>
>> I need to add periodically some data from a remote mysql database into our
>> postgresql database. So, does anyone know how to do it having in mind that
>> it must be runned every minute or so for adding new records to the
>> postresql?
>
> It should be trivial to write a Perl script that pulls the data from MySQL,
> inserts them into PostgreSQL, and then goes to sleep for 60 seconds.
>
> regards,
> Leif B. Kristensen
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I would recommend first exporting to CSV (or other text format) either
using MySQL's copy command (then use sed or other scripting
tool/language to transform/cleanup the data) and then loading this
file (or STDIN) using the COPY command.
Why?
1)Copy is a fast way to bulk load data.
2)The CSV file may come in handy when debugging/testing/auditing
providing "right from the horse's mouth" functionality when all is not
well.

You may want to spit the file using some number of lines threshold
(the split command may help) to avoid large transactions.

Allan.