Thread: Adding data from mysql to postgresql periodically

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?

Best regards
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Adding-data-from-mysql-to-postgresql-periodically-tp3264392p3264392.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

well, I know how to query to mysql but what i dont know is how to then write
that data on the fly to the postgresql
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Adding-data-from-mysql-to-postgresql-periodically-tp3264392p3264417.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Sunday 14. November 2010 14.33.39 franrtorres77 wrote:
>
> well, I know how to query to mysql but what i dont know is how to then
write
> that data on the fly to the postgresql

The DBD::Pg package has an excellent documentation:
<http://search.cpan.org/dist/DBD-Pg/Pg.pm>

regards,
Leif B. Kristensen

On Sunday 14. November 2010 14.33.39 franrtorres77 wrote:
>
> well, I know how to query to mysql but what i dont know is how to then
write
> that data on the fly to the postgresql

I'd also like to say that it's an interesting question, and a lot of
people (including me) might want to take a stab at the solution.

If you can tell what the data looks like coming from MySQL, and the
corresponding table structure in PostgreSQL, you may well get a much
more detailed reply.

regards,
Leif B. Kristensen

On Sun, Nov 14, 2010 at 4:12 PM, Leif Biberg Kristensen
<leif@solumslekt.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@postgresql.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.

On Sunday 14 November 2010 4:44:53 am 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?
>
> Best regards
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Adding-data-from-mysql-to-postgresq
>l-periodically-tp3264392p3264392.html Sent from the PostgreSQL - general
> mailing list archive at Nabble.com.

Some questions.
1) Are you only pulling records from the MySQL db that are not in the Pg db?
What about previously pulled records that have changed in MySQL, are the
changes going to be propagated to Pg?
What about deleted records?
2) As mentioned in another post what about data cleanup?
For instance MySQL '00-00-000' date, or empty string in integer fields?

I have done this using a Python script. Not on a minute to minute basis, but I
could see doing it either using sleep() or by calling the script in a cron job.

--
Adrian Klaver
adrian.klaver@gmail.com

Hello again.

Well, the data that I want to import is some satellite coordinates saved in
mysql , the fields are X and Y and want to import them into a postgis table
for converting these coordinates to a normal latitude and longitude points
because in mysql are saved in Utm format.

I have foound an example for connecting to mysql using perl, but i would
prefer using a query in postgresql but I think it will be necessary firstly
create a linking connection with the mysql db. I did it with sql server but
in postgresql dont know how to do it.

The other way to do it, exporting the data to a csv file, would be an
acceptable solution, and also an easiest way to perform the operation.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Adding-data-from-mysql-to-postgresql-periodically-tp3264392p3265406.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Sun, Nov 14, 2010 at 7:44 AM, franrtorres77 <franrtorres77@gmail.com> wrote:
> 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?

People have written systems to replicate data from various
heterogeneous databases.  Usually they take the form of writing
insert/update/delete triggers on each and every table. This trigger
will write the change log into another table (either full row, or just
the fields changed depending on how clever you think you want to be).
Then a daemon program that runs continuously reads that log table and
applies the changes to the destination system then deletes the log
rows it just processed.

The trick is getting the ordering of your operations correct, and
using transactions to ensure you don't miss/lose data while applying
it to the destination system.

I have seen a presentation by OmniTI where they built such a system to
replicate from postgres to oracle.  Also, the slony1 replication
software for postgres operates basically this way, but is only for
replication to another postgres instance.  You could review the slony
software for details on how it ensures correctness of order of
operations.

On Nov 14, 2010, at 4:44 AM PST, 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?
>

Have you looked at Tungsten to replicate from MySQL to PostgreSQL?  Linas Virbalas and I did a talk on using Tungsten
toreplicate out of MySQL and into PostgreSQL at PG-West.  (URL:
https://www.postgresqlconference.org/content/liberating-your-data-mysql-cross-database-replication-rescue) Linas posted
theslides but I don't see them on the PG-West site yet.  If you send a mail off-list we would be happy to send slides.
Linas will be doing a similar talk at PG-Day EU in Stuttgart next month.  

The software we discussed is open source and available from SourceForge.net at
http://sourceforge.net/projects/tungsten/. We are doing some commercial work to replicate into Greenplum and will keep
buildingout the generic MySQL to PostgreSQL replication along the way.  

Cheers, Robert Hodges

Disclosure:  Linas and I both work on Tungsten for Continuent.

Hi,

franrtorres77 <franrtorres77@gmail.com> writes:
> 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?

I've been doing that with pgloader already, using mysqldump -T -w
options to get csv output and a where clause (incremental dumps of some
sort). It worked and allows to validate that we'd be better served with
PostgreSQL, so we migrated away from MySQL as soon as possible.

Don't miss the pgloader user module reformat feature, and the mysql to
PostgreSQL timestamp that you have to use sometime (depends on the MySQL
minor version, if memory serves).

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support