IDS Forum

Re: Continuous Informix to Postgres data replicati

Posted By: Art Kagel
Date: Tuesday, 16 July 2013, at 10:43 a.m.

In Response To: Continuous Informix to Postgres data replication (TOKAREV ALEX)

There is a commercial solution, DbMoto. As for grow-your-own, I would
create a shadow or audit table for each table that needs to be replicated
which includes all columns, an operation id, and timestamp. Add insert,
update, and delete triggers to each table that copies the pre-image and/or
post-image of the record (as appropriate) to the shadow table populating
the operation code and timestamp. Then you can have a cron job on either
the source or target server query the shadow tables by a date range (id all
rows with a timestamp less than 2013-07-16 10:40:00) and apply those
changes to the target database, then delete the rows using the same data
range from the shadow table once the reapply has successfully completed.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.

On Tue, Jul 16, 2013 at 8:50 AM, TOKAREV ALEX <nohuhu@nohuhu.org> wrote:

> The master server is Informix, version varies from 9.40 to the 11.53,
> database
> is *unlogged* by design that can't be changed. Slave server is the latest
> PostgreSQL. Master and slave are separate machines, network latency is
> unpredictable. Master schema is statically defined, well known and does not
> change, so it's only the data that needs to be replicated. In the master,
> there are three types of tables:
>
> 1. Numeric data tables, usually one date column, one time column and 15-300
> int columns keyed by 2-3 primary keys. The data is never changed, only
> added
> once in a set interval (15, 30, or 60 minutes) and deleted when the
> retention
> point is reached. Replication data set can be up to 80,000 rows but
> usually is
> in the range of hundreds. This data needs to be replicated one way, master
> to
> slave. There is about 30 tables of this type and they need to be replicated
> all at once and as fast as possible, typically in under one minute after
> new
> interval set has been committed to the master.
> 2. Mixed data tables, with date, time, int, and string types, 30-100
> columns,
> again 2-3 primary keys. This data is also never changed, added continuously
> and is deleted when the retention point is reached. The data set is up to
> 100,000 rows per hour. One way replication is needed, master to slave.
> There
> are a few tables like that, less than 5 usually.
> 3. Mixed data tables, with int and string types, less than 10 columns, 2-3
> primary keys. The data largely stays intact, with occasional additions,
> edits
> or deletions. The usual replication set size is unpredictable, but probably
> will be in low hundreds of rows. This data needs to be replicated both
> ways,
> as fast as possible. There are a few tables of this type, and they need to
> be
> synched independently.
>
> I've been looking for an existing tool that could do what I need, but it
> looks
> like there is none that is open source. I'm probably going to write one
> for my
> needs, and I'm looking for advice from DB gurus on how to approach this
> task.
>
> In my estimate, there's probably no single algorithm that would cover all
> the
> use cases so I may be in fact looking for two or three algorithms. Here's
> what
> I found so far:
>
> 1. Fire trigger on master changes, record row OIDs (does Informix have
> them?)
> to temp table, dump the changed rows to a file, transfer it and load up.
> Question: how to buffer the trigger? The master DB is unlogged (no
> transactions), so trigger will fire upon each INSERT. Additional strain on
> the
> master, not good.
> 2. Add a cron job on the slave that will pull latest date/time keys from
> the
> master, and if the data is newer, pull it. Problem: although the update
> interval is defined, in reality it's based on the _data source_ clock (not
> master DB clock) which is guaranteed to vary from slave server clock. More
> of
> it, there can be several data sources, each with varying clocks, and the
> data
> needs to be replicated ASAP. The only way here that I see is to constantly
> poll the master from the slave, hoping that by the time the poll comes in,
> the
> data is *all* committed (no transactions, remember?). Kludgy, slow, not
> good.
> 3. Add Informix as foreign data wrapper in the Postgres and run queries
> directly instead of bothering with replication. Pros: simplicity. Cons:
> Informix connector seems to be in alpha stage, and the whole approach is an
> unknown factor at best.
>
> I've been researching this topic for some time, and it seems that the core
> of
> the problem is the lack of transactions on the master side. If the master
> DB
> was logged, it would be much easier to replicate it, but without
> transactions
> the task suddenly becomes much more complicated. For one, how do I ensure
> that
> there are no dupes? Another one, how to avoid update loops in type 3
> tables?
> Considering all that, how to make replication as fast-reacting as
> possible? I
> mean the delay between data update and sync start here, data transfer is
> another topic altogether.
>
> Any input is appreciated.
>
> Regards,
> Alex.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--089e013d174278b37e04e1a1fecc

Messages In This Thread

• Continuous Informix to Postgres data replication
  TOKAREV ALEX -- Tuesday, 16 July 2013, at 8:50 a.m.
  • Re: Continuous Informix to Postgres data replicati
    Art Kagel -- Tuesday, 16 July 2013, at 10:43 a.m.
    • Re: Continuous Informix to Postgres data replicati
      TOKAREV ALEX -- Tuesday, 16 July 2013, at 1:00 p.m.
      • Re: Continuous Informix to Postgres data replicati
        FRANK DEVELOPER -- Tuesday, 16 July 2013, at 1:11 p.m.
        • Re: Continuous Informix to Postgres data replicati
          TOKAREV ALEX -- Tuesday, 16 July 2013, at 6:13 p.m.
      • Re: Continuous Informix to Postgres data replicati
        Art Kagel -- Tuesday, 16 July 2013, at 4:30 p.m.
        • Re: Continuous Informix to Postgres data replicati
          TOKAREV ALEX -- Tuesday, 16 July 2013, at 6:17 p.m.
          • Re: Continuous Informix to Postgres data replicati
            Art Kagel -- Tuesday, 16 July 2013, at 6:51 p.m.
            • Re: Continuous Informix to Postgres data replicati
              TOKAREV ALEX -- Saturday, 27 July 2013, at 1:24 p.m.
              • Re: Continuous Informix to Postgres data replicati
                Art Kagel -- Sunday, 28 July 2013, at 8:47 a.m.
                • Re: Continuous Informix to Postgres data replicati
                  TOKAREV ALEX -- Monday, 29 July 2013, at 5:33 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.