Thread: oracle synchronization strategy
I am going to sync a schema in postgres with one in an oracle db. The tables are simple, but there are 200 of them. I would like to try to keep the sync lag < 1 minute. Here is my idea. Please critique/suggest. 1. Set up stored proc on oracle that records a INSERT, UPDATE, DELETE SQL action taken on a table into a log table. 2. Program reads the log table on oracle and issues the same SQL command on the postgres db. In the same transaction, postgres writes to a log showing the command has been executed. 3. The program will query the oracle log table on some frequency ~30 seconds. What are your thoughts? Randall
Hi Randall, On Sun, Oct 31, 2004 at 11:25:46PM -0600, Randall Smith wrote: > 1. Set up stored proc on oracle that records a INSERT, UPDATE, DELETE > SQL action taken on a table into a log table. > 2. Program reads the log table on oracle and issues the same SQL command > on the postgres db. In the same transaction, postgres writes to a log > showing the command has been executed. > 3. The program will query the oracle log table on some frequency ~30 > seconds. It depends on what you're trying to achieve. Your way might work if you only want to mirror oracle -> pgsql but not vice versa. Furthermore you need to do manual maintenance on the pgsql side if you change your schema on the oracle side (create/drop/change tables, ...) I've done something similar with MS SQL -> pgsql and perl some years ago. Shout if you're interested. There's also dbmirror in contrib/ that works in a similar way. Joachim
Thanks Joachim, The mirror only has to go from oracle to pgsql and the schema/tables never change. I'm going to take a look at dbmirror. Thanks for the advice. Randall Joachim Wieland wrote: > Hi Randall, > > On Sun, Oct 31, 2004 at 11:25:46PM -0600, Randall Smith wrote: > >>1. Set up stored proc on oracle that records a INSERT, UPDATE, DELETE >>SQL action taken on a table into a log table. >>2. Program reads the log table on oracle and issues the same SQL command >>on the postgres db. In the same transaction, postgres writes to a log >>showing the command has been executed. >>3. The program will query the oracle log table on some frequency ~30 >>seconds. > > > It depends on what you're trying to achieve. > > Your way might work if you only want to mirror oracle -> pgsql but not vice > versa. > > Furthermore you need to do manual maintenance on the pgsql side if you > change your schema on the oracle side (create/drop/change tables, ...) > > I've done something similar with MS SQL -> pgsql and perl some years ago. > Shout if you're interested. > > There's also dbmirror in contrib/ that works in a similar way. > > > Joachim > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >
For anyone interested, the below procedure worked well. Randall Randall Smith wrote: > I am going to sync a schema in postgres with one in an oracle db. The > tables are simple, but there are 200 of them. I would like to try to > keep the sync lag < 1 minute. Here is my idea. Please critique/suggest. > > 1. Set up stored proc on oracle that records a INSERT, UPDATE, DELETE > SQL action taken on a table into a log table. > 2. Program reads the log table on oracle and issues the same SQL command > on the postgres db. In the same transaction, postgres writes to a log > showing the command has been executed. > 3. The program will query the oracle log table on some frequency ~30 > seconds. > > What are your thoughts? > > Randall