Mirroring tables from Oracle to Postgresql

[Miguel Guzman Miranda]

Hi there,

First of all, I suppose my problem should be at least farly common, but
haven't had luck trying to find what I need by searching on the docs,
google, etc. So please if this issue has already been covered please
point me to some URL, or the list archives, or what you think is fine.

My issue is that I need to do some replication from Oracle tables to
PSQL. One-way only, incremental, and to a remote machine.

I've been suggested to do a triggered procedure in Oracle so that
everytime a file is inserted, deleted or updated, a perl script is run
which modifies in turn the PSQL DB.

I'd like to know if this is the proper approach, and also if someone
else has done anything similar (this is for office work, and the bosses
would feel more comfortable with a pre-fabricated solution that with
some quick hacking of mine, even if it's the same stuff ;). If there
isn't previous work, doing the hacking is still fine. What do you
recommend me?

Thanks in advance,

Miguel
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Cees van de Griend]

Hello Miguel,

On Monday 05 April 2004 15:58, Miguel Guzman Miranda wrote:

My issue is that I need to do some replication from Oracle tables to
PSQL. One-way only, incremental, and to a remote machine.

I have to replicate a lot of data from Oracle/MS SQL/Progress to PostgreSQL
I have written an Java library which synchronize the data from a database to
another database and do some usefull things if a record was
inserted/changed/deleted. In our situation, we synchronize once a day.

At the moment I'm rewritting the library to use Metadata.

Regards,
Cees.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Andrew Sullivan]

On Mon, Apr 05, 2004 at 03:58:36PM +0200, Miguel Guzman Miranda wrote:

I've been suggested to do a triggered procedure in Oracle so that
everytime a file is inserted, deleted or updated, a perl script is run
which modifies in turn the PSQL DB.

If you go to gborg and get the latest erserver code, you could
probably use that as a base. It works pretty much as you described.
At the moment, it is designed to use Postgres as the master, but I've
been thinking about it lately, and it strikes me that it could be
modified to use other RDBMS. Since the data is pushed around using
JDBC, I suspect that it could be modified to sync any systems for
which you have a JDBC driver. Some work will be required for that,
though. In particular, it depends on the Postgres system tables, so
you'd have to figure out how to fix that.

Note that it does not do DDL, and it imposes a noticable cost to
transactions on the master database.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Rawnsley]

The java code is reasonably agnostic, provided the JDBC driver is smart
enough to
deal with any datatype properly as strings, although that intelligence
can be added.
Something else as a slave would be considerable easier, of course. For
something
else to be the master, you would have to find a way to mimic the
trigger code to make
the entry into the _rserve_log_?_ tables.

On Apr 16, 2004, at 8:17 AM, Andrew Sullivan wrote:

On Mon, Apr 05, 2004 at 03:58:36PM +0200, Miguel Guzman Miranda wrote:

I've been suggested to do a triggered procedure in Oracle so that
everytime a file is inserted, deleted or updated, a perl script is run
which modifies in turn the PSQL DB.

If you go to gborg and get the latest erserver code, you could
probably use that as a base. It works pretty much as you described.
At the moment, it is designed to use Postgres as the master, but I've
been thinking about it lately, and it strikes me that it could be
modified to use other RDBMS. Since the data is pushed around using
JDBC, I suspect that it could be modified to sync any systems for
which you have a JDBC driver. Some work will be required for that,
though. In particular, it depends on the Postgres system tables, so
you'd have to figure out how to fix that.

Note that it does not do DDL, and it imposes a noticable cost to
transactions on the master database.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Jan Wieck]

Andrew Rawnsley wrote:

The java code is reasonably agnostic, provided the JDBC driver is smart
enough to
deal with any datatype properly as strings, although that intelligence
can be added.
Something else as a slave would be considerable easier, of course. For
something
else to be the master, you would have to find a way to mimic the
trigger code to make
the entry into the _rserve_log_?_ tables.
Not only that. I was wondering how exactly a "snapshot" would be defined
in Oracle. I don't know if there are things like minxid, maxxid and xip
in any other DB than PostgreSQL.
Jan

On Apr 16, 2004, at 8:17 AM, Andrew Sullivan wrote:

On Mon, Apr 05, 2004 at 03:58:36PM +0200, Miguel Guzman Miranda wrote:

I've been suggested to do a triggered procedure in Oracle so that
everytime a file is inserted, deleted or updated, a perl script is run
which modifies in turn the PSQL DB.

If you go to gborg and get the latest erserver code, you could
probably use that as a base. It works pretty much as you described.
At the moment, it is designed to use Postgres as the master, but I've
been thinking about it lately, and it strikes me that it could be
modified to use other RDBMS. Since the data is pushed around using
JDBC, I suspect that it could be modified to sync any systems for
which you have a JDBC driver. Some work will be required for that,
though. In particular, it depends on the Postgres system tables, so
you'd have to figure out how to fix that.

Note that it does not do DDL, and it imposes a noticable cost to
transactions on the master database.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Jan Wieck]

Andrew Rawnsley wrote:

The java code is reasonably agnostic, provided the JDBC driver is smart
enough to
deal with any datatype properly as strings, although that intelligence
can be added.
Something else as a slave would be considerable easier, of course. For
something
else to be the master, you would have to find a way to mimic the
trigger code to make
the entry into the _rserve_log_?_ tables.
Not only that. I was wondering how exactly a "snapshot" would be defined
in Oracle. I don't know if there are things like minxid, maxxid and xip
in any other DB than PostgreSQL.
Jan

On Apr 16, 2004, at 8:17 AM, Andrew Sullivan wrote:

On Mon, Apr 05, 2004 at 03:58:36PM +0200, Miguel Guzman Miranda wrote:

I've been suggested to do a triggered procedure in Oracle so that
everytime a file is inserted, deleted or updated, a perl script is run
which modifies in turn the PSQL DB.

If you go to gborg and get the latest erserver code, you could
probably use that as a base. It works pretty much as you described.
At the moment, it is designed to use Postgres as the master, but I've
been thinking about it lately, and it strikes me that it could be
modified to use other RDBMS. Since the data is pushed around using
JDBC, I suspect that it could be modified to sync any systems for
which you have a JDBC driver. Some work will be required for that,
though. In particular, it depends on the Postgres system tables, so
you'd have to figure out how to fix that.

Note that it does not do DDL, and it imposes a noticable cost to
transactions on the master database.

A

--
Andrew Sullivan | aj*@crankycanuck.ca

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
ma*******@postgresql.org)

--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
[phone]
www.ravensfield.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly