Continuous Informix to Postgres data replication

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.

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

Art, Thanks for the suggestion, I appreciate your help. Correct me if I'm wrong, but it looks like shadow columns are only available in 11.50+? Some of the systems I need to work with are 9.40 and can't be upgraded easily. Besides pure SQL approach, maybe there are standard system tools that could help with that? I'm no Informix expert by any stretch so I may have missed something. Regards, Alex.

All of those suggestions imply latency and the possibility of data being out of sync. You stated that performance and data integrity are a priority, so why not just query the Informix data directly from Postgres using ODBC? That would be the fastest and most reliable method!

Ahh, not suggesting the "shadow columns" created for Enterprise Replication, but just another table (which I call a shadow table or audit table) with the same structure as the table you need to replicate except with two new columns, a timestamp and an operation indicator (ie "D"elete, "I"nsert, "A"fter update, or "B"efore update), that's all. This will work with any version of Informix. Then you have to craft triggers to duplicate the changes to this audit table for post processing. There are no Informix tools for replicating data to an external database, no. As I said, DbMoto can replicate data between nearly any two kinds of servers. 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 1:00 PM, TOKAREV ALEX <nohuhu@nohuhu.org> wrote: > Art, > > Thanks for the suggestion, I appreciate your help. Correct me if I'm wrong, > but it looks like shadow columns are only available in 11.50+? Some of the > systems I need to work with are 9.40 and can't be upgraded easily. > > Besides pure SQL approach, maybe there are standard system tools that could > help with that? I'm no Informix expert by any stretch so I may have missed > something. > > Regards, > Alex. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d17424bad6f04e1a6d8d7

Frank, Thanks for the suggestion. You are talking about Postgres Foreign Data Wrappers, right? I had the impression that although the support for this feature has been available in the Postgres core since 9.1, the wrapper connectors themselves are not of production quality yet. I'd love to use Informix FDW but it's in alpha stage as described by its author, not sure about ODBC FDW. Please correct me if I'm wrong. Regards, Alex.

Art, Thanks, I see what you mean now. This approach most probably will work for the first two replication types, however I'm not sure how to achieve full sync for the third one. Fortunately for me, I don't have to sync the data between multiple nodes, it's one-to-one relationship. Any advice on that? Regards, Alex.

Full sync? I think I missed something. Do you also need to sync some tables back to Informix? I'm going to ask the obvious question: Why involve PostgreSQL at all? Why not just use Informix for the whole project? If it is license cost, there is a free-for-all-uses edition of Informix - Informix Innovator Edition! Optional IBM support for IE is even available at a very reasonable rate (IB its around $2,000/year). 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 6:17 PM, TOKAREV ALEX <nohuhu@nohuhu.org> wrote: > Art, > > Thanks, I see what you mean now. This approach most probably will work for > the > first two replication types, however I'm not sure how to achieve full sync > for > the third one. Fortunately for me, I don't have to sync the data between > multiple nodes, it's one-to-one relationship. Any advice on that? > > Regards, > Alex. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013d1cf854f46c04e1a8d0b7

Art, Yes, that is type 3 replication. There are several tables (not many) that need to be synchronized two-way between Informix and Postgres. That is the part that actually gives me the most trouble, as for the first two I can think up some kludgy, maybe slow, but reliable ways to do it but #3 is really tricky to manage without transactions. Using Informix for the whole project is not an option. The cost-free Innovator-C license you mentioned cannot be redistributed and that is exactly what I need to do. The license closest to my needs is Express Edition which is $10,000 a copy. My product connects to a legacy system that is based on Informix; that one has been paid for already by the customer. I can't use Informix in my software because that would cause the cost to skyrocket and my product will suddenly move from "wow, that's affordable!" category into "meh, too expensive". Can't do that. Regards, Alex.

Alex: Actually, you CAN distribute IBM Informix Innovator Edition, you just have to set up a distribution agreement with IBM though you generally cannot distribute IE for free. IB that IBM wants a something on the order of $200 US per copy (don't quote me on that number, but it's in the ballpark). However, if we are talking about a single customer, they can download and install the free copy themselves and your software can just run against it. They can even buy annual IBM support for around $2,000/ann which is affordable for any business. You could also just run your software against a new database in the cuatomer's existing Informix instance. No? 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 Sat, Jul 27, 2013 at 1:24 PM, TOKAREV ALEX <nohuhu@nohuhu.org> wrote: > Art, > > Yes, that is type 3 replication. There are several tables (not many) that > need > to be synchronized two-way between Informix and Postgres. That is the part > that actually gives me the most trouble, as for the first two I can think > up > some kludgy, maybe slow, but reliable ways to do it but #3 is really > tricky to > manage without transactions. > > Using Informix for the whole project is not an option. The cost-free > Innovator-C license you mentioned cannot be redistributed and that is > exactly > what I need to do. The license closest to my needs is Express Edition > which is > $10,000 a copy. > > My product connects to a legacy system that is based on Informix; that one > has > been paid for already by the customer. I can't use Informix in my software > because that would cause the cost to skyrocket and my product will suddenly > move from "wow, that's affordable!" category into "meh, too expensive". > Can't > do that. > > Regards, > Alex. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e014940eabc3da504e291c6eb