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  5046 
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 working on a project to let Mozilla's open-source desktop
software work better with Oracle's business applications, in the
latest move by the database giant to promote open-source software.
|
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 7 / 2000 as DB Engines
at the back end.
The SQL server has two databases configured:
Logging Database - Massive updates every second, the data grows
|
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
execute in the database server with better performance?
Please advise good references for Oracle stored procedures also.
thanks!!
|
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 7 / 2000 as DB Engines
at the back end.
The SQL server has two databases configured:
Logging Database - Massive updates every second, the data grows
|
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 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...
|
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 looking forward to implement it is, A new partitioning strategy in Oracle Database 11g, Interval partitioning extends the capabilities of the range method to define equipartitioned ranges using an interval definition. Rather than specifying individual...
|
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 locations send the respective mdb files to a seperate team for consolidation. The reports from each mdb file is generated separately and consolidated into one single report. We wish to consolidate all the stand alone mdb copies into a single centralized...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
