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 6 5022
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Peter |
last post by:
Company thought DB2 will be better than Oracle.
The bottom line is when you do select, the system crash.
I think it may take 4-5 years for DB2 to reach Oracle standard.
Peter
|
by: michael newport |
last post by:
Oracle opens arms to Mozilla
By Stephen Shankland and Alorie Gilbert, CNET News.com
Oracle wants its applications to integrate better with Mozilla's
open-source desktop software
Oracle is...
|
by: omeropee |
last post by:
Hi,
Our company is an independent Voice applications solution provider with
number clients using our suite. We have a CT application suite which is
running with Application Server and SQL Server...
|
by: jrefactors |
last post by:
I want to know the differences between SQL Server 2000 stored
procedures and oracle stored procedures? Do they have different
syntax? The concept should be the same that the stored procedures...
|
by: Tavish Muldoon |
last post by:
At a high level - what would be involved in switching from Oracle to DB2?
Pretty vague, I know - but anyone have experience with this kind of migration?
Pointers? Things to look for?
Tmuld
|
by: omeropee |
last post by:
Hi,
Our company is an independent Voice applications solution provider with
number clients using our suite. We have a CT application suite which is
running with Application Server and SQL Server...
|
by: Randall Smith |
last post by:
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...
|
by: debasisdas |
last post by:
Interval Partitioning: A new partitioning strategy in Oracle Database 11g
========================================================
One more features which really look interesting and I will be...
|
by: maxpirate |
last post by:
application is built on an MS Access database utilizing the GUI facilities of MS Access. Copies of this application are used stand alone in 4 different geographical locations.The users in these...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
| |