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.
