Thread: syncing with a MySQL DB
I have a need to keep a PostgreSQL and MySQL table synchronized. My thoughts are to use triggers on the pgsql side to manipulate the MySQL table when data in the pgsql table is changed. I also plan on using PL/Perl to write the functions. Are there better ways to achieve this? -- Brandon
On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > I have a need to keep a PostgreSQL and MySQL table synchronized. My > thoughts are to use triggers on the pgsql side to manipulate the MySQL > table when data in the pgsql table is changed. I also plan on using > PL/Perl to write the functions. > > Are there better ways to achieve this? have you looked at dbi-link? merlin
Greetings again! Suppose I have a table named "myTable" with fields named "item" and "value". Item X has a value of 1. Inside a C++ application, I begin a transaction, open the table, change Item X's value to 2, and go to sleep. The transaction is still active. In PGAdmin, I open an SQL window and execute "select * from myTable". What value do I see for item X? Will it still be 1 because the transaction in which it was changed to 2 is still open? Thanks again! RobR
m == mmoncure@gmail.com writes: m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL m> > table when data in the pgsql table is changed. I also plan on using m> > PL/Perl to write the functions. m> > m> > Are there better ways to achieve this? m> have you looked at dbi-link? No, but just did and it looks very cool. Have you used it before? If so, would you mind if I ask you some questions? We could take it off the list if that's more appropriate. -- Brandon
On Tue, Oct 21, 2008 at 9:54 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > m == mmoncure@gmail.com writes: > > m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My > m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL > m> > table when data in the pgsql table is changed. I also plan on using > m> > PL/Perl to write the functions. > m> > > m> > Are there better ways to achieve this? > > m> have you looked at dbi-link? > > > No, but just did and it looks very cool. Have you used it before? If > so, would you mind if I ask you some questions? We could take it off > the list if that's more appropriate. i'm not a perl guy...hop on postgresql irc and ping 'davidfetter' :-) merlin
Correction from my previous post. The first word in the title should have been "when", not "where". RobR
m == mmoncure@gmail.com writes: m> On Tue, Oct 21, 2008 at 9:54 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: m> > m == mmoncure@gmail.com writes: m> > m> > m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: m> > m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My m> > m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL m> > m> > table when data in the pgsql table is changed. I also plan on using m> > m> > PL/Perl to write the functions. m> > m> > m> > m> > Are there better ways to achieve this? m> > m> > m> have you looked at dbi-link? m> > m> > m> > No, but just did and it looks very cool. Have you used it before? If m> > so, would you mind if I ask you some questions? We could take it off m> > the list if that's more appropriate. m> i'm not a perl guy...hop on postgresql irc and ping 'davidfetter' :-) OK. I'll dig in and can probably figure everything out. I'll contact David if I can't. Thanks. -- Brandon
Rob Richardson wrote: > Greetings again! > > Suppose I have a table named "myTable" with fields named "item" and > "value". Item X has a value of 1. Inside a C++ application, I begin a > transaction, open the table, change Item X's value to 2, and go to > sleep. The transaction is still active. In PGAdmin, I open an SQL > window and execute "select * from myTable". What value do I see for > item X? Will it still be 1 because the transaction in which it was > changed to 2 is still open? Yes, in both the READ COMMITTED and SERIALIZABLE isolation levels. "Dirty" reads, ie reads of uncomitted data, are AFAIK just not possible in PostgreSQL (though one uncommitted transaction can still affect another though locks etc). If you're unsure of these things, (a) read the excellent documentation available on transaction isolation levels, and (b) test with multiple simultaneous psql sessions. -- Craig Ringer
Answering my own question: The change is not visible until the commit, which is what I was expecting and hoping. I added a couple of buttons to a dialog box in an application that connected to our database. In the handler for one button, I began a transaction and updated a value. In the handler for the other, I committed the transaction. After clicking the first button, the old value appeared in PGAdmin. After clicking the second, the new value appeared. RobR -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rob Richardson Sent: Tuesday, October 21, 2008 9:58 AM To: pgsql-general@postgresql.org Subject: [GENERAL] When are updates from inside transactions visible? Correction from my previous post. The first word in the title should have been "when", not "where". RobR -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Tue, Oct 21, 2008 at 10:00 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > OK. I'll dig in and can probably figure everything out. I'll contact > David if I can't. You should try http://www.anysql.net/en/software/refresh_mysql.zip It's written in Perl and designed to replicate Oracle->MySQL, but you could easily emulate the Oracle-side by creating triggers in PG to capture the changes. -- Jonah H. Harris, Senior DBA myYearbook.com
I use dbi-link, work fine, but I have problems when I call mysql tables "linked" and these tables are big, maybe a millon records, the answers is really slow, I need to wait 5 or more minutes to have an answer in a single query like this "select * from table limit 10", I am thinking maybe dbi-link download all the data to pgsql before to give me the answer. Anybody knows how improve this? thnaks 2008/10/21 Brandon Metcalf <bmetcalf@nortel.com>: > m == mmoncure@gmail.com writes: > > m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My > m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL > m> > table when data in the pgsql table is changed. I also plan on using > m> > PL/Perl to write the functions. > m> > > m> > Are there better ways to achieve this? > > m> have you looked at dbi-link? > > > No, but just did and it looks very cool. Have you used it before? If > so, would you mind if I ask you some questions? We could take it off > the list if that's more appropriate. > > -- > Brandon > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Inscribete en las listas de APESOL http://www.apesol.org/listas.php Visita http://www.eqsoft.net Manuales, noticias, foros, etc.
On Sat, Oct 25, 2008 at 1:19 PM, Ernesto Quiñones <ernestoq@gmail.com> wrote: > I use dbi-link, work fine, but I have problems when I call mysql > tables "linked" and these tables are big, maybe a millon records, the > answers is really slow, I need to wait 5 or more minutes to have an > answer in a single query like this "select * from table limit 10", I > am thinking maybe dbi-link download all the data to pgsql before to > give me the answer. Yes, that's what Postgres is doing. DBI-link is currently incapable of pushing down the predicate to the remote system because Postgres can't give it access to the predicate. > Anybody knows how improve this? If I have to push the predicate down, I'll generally write a set-returning function which takes some of the predicate, limit, and offset info to build a dynamic sql query against the remote database using dblink. -- Jonah H. Harris, Senior DBA myYearbook.com
On Sun, Oct 26, 2008 at 12:41:39PM -0400, Jonah H. Harris wrote: > On Sat, Oct 25, 2008 at 1:19 PM, Ernesto Quiñones <ernestoq@gmail.com> wrote: > > I use dbi-link, work fine, but I have problems when I call mysql > > tables "linked" and these tables are big, maybe a millon records, > > the answers is really slow, I need to wait 5 or more minutes to > > have an answer in a single query like this "select * from table > > limit 10", I am thinking maybe dbi-link download all the data to > > pgsql before to give me the answer. > > Yes, that's what Postgres is doing. DBI-link is currently incapable > of pushing down the predicate to the remote system because Postgres > can't give it access to the predicate. More precisely, Postgres is (as yet) incapable of giving DBI-Link the information it needs. > > Anybody knows how improve this? > > If I have to push the predicate down, I'll generally write a > set-returning function which takes some of the predicate, limit, and > offset info to build a dynamic sql query against the remote database > using dblink. That's one way. For others, I can help out on a consulting basis :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Brandon Metcalf escribió: > m == mmoncure@gmail.com writes: > > m> On Tue, Oct 21, 2008 at 9:54 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > m> > m == mmoncure@gmail.com writes: > m> > > m> > m> On Tue, Oct 21, 2008 at 9:07 AM, Brandon Metcalf <bmetcalf@nortel.com> wrote: > m> > m> > I have a need to keep a PostgreSQL and MySQL table synchronized. My > m> > m> > thoughts are to use triggers on the pgsql side to manipulate the MySQL > m> > m> > table when data in the pgsql table is changed. I also plan on using > m> > m> > PL/Perl to write the functions. > m> > m> > > m> > m> > Are there better ways to achieve this? > m> > > m> > m> have you looked at dbi-link? > m> > > m> > > m> > No, but just did and it looks very cool. Have you used it before? If > m> > so, would you mind if I ask you some questions? We could take it off > m> > the list if that's more appropriate. > > m> i'm not a perl guy...hop on postgresql irc and ping 'davidfetter' :-) > > > OK. I'll dig in and can probably figure everything out. I'll contact > David if I can't. > > Thanks. > Maybe you can use too a multi-master replication solution that supports mysql and postgresql like symmetricds, http://symmetricds.sourceforge.net , it is developed in java and can works like a library or a daemon, i am using now between postgresql machines but it supports replicate heterogeneous database (a common usage in the forums it is oracle in a central and many mysql like clients), it is a trigger based replication so i think it can be good fit for your needs. Regards, Miguel Angel.