By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,809 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,809 IT Pros & Developers. It's quick & easy.

oracle synchronization strategy

P: n/a
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
Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
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

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

P: n/a
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

Nov 23 '05 #6

P: n/a
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

Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.