Re: [HACKERS] mirroring oracle database in pgsql

The contrib/dblink module only works for creating a database link to
another PostgreSQL database. I'm working on a dblink_ora which allows
you to connect to an 8i, 9i, or 10g system the same way. dblink_ora is
based on dblink, not dblink_tds (for SQL Server) so it has more
features. Also, I'm using the Oracle Instant Client libraries/SDK, so
you don't need to do the whole Oracle Client install to use dblink_ora.

I'm currently doing some alpha testing on it but if you would like to
use it in beta, let me know. Also, if anyone has *a lot* of experience
with OCI, I'd like to talk about a couple things.

-Jonah

Christopher Kings-Lynne wrote:
> Check out EnterprisDB: www.enterprisedb.com
>
> Chris
>
> Edward Peschko wrote:
>
>> hey all,
>>
>>
>> I'm trying to convince some people here to adopt either mysql or
>> postgresql
>> as a relational database here.. However, we can't start from a clean
>> slate; we have a very mature oracle database that applications point
>> to right now, and so we need a migration path. I went to the mysql
>> folks, and it looks
>> like its going to be quite a while before mysql is up to the task, so
>> I thought I'd try pgsql.
>> Anyways, I was thinking of taking the following steps:
>>
>>
>> a) finding a Java API that transparently supports both postgresql and
>> Oracle data access and stored procedure calls.
>>
>> b) instrumenting the Oracle database so that all tables support
>> timestamps on data rows.
>>
>> c) mirroring the Oracle database in MySQL.
>>
>> d) making interface code connecting the MySQL database to the
>> Oracle database (and both applying updates to the database
>> as well as data.
>>
>> In other words, I'm looking to make a postgresql -> Oracle mirroring
>> tool, and syncing the databases on a nightly basis, and I was
>> wondering if anybody had experience with this sort of thing.
>>
>> As I see it, if we pull this off we could save quite a bit in
>> licensing costs - we'd still have oracle around, but it would only be
>> a datastore for talking to other oracle databases, and run by batch,
>> not accessed by end users.
>>
>> However:
>>
>> a) I'm not sure how well stored procs, views, triggers and
>> indexes transfer over from oracle to postgresql.
>>
>> b) I'm not sure how scalable postgresql is, and how well
>> it handles multiprocessor support (we'd be using a
>> six-processor box.
>>
>>
>> As an aside, how much experience do people on the list have with
>> enterprise db? I was thinking that they might alleviate the mirroring
>> headaches quite a bit, but they don't seem to have a solaris port..
>> Anybody have a take on their db?
>>
>>
>> Ed
>>
>> (
>> ps - if you subscribe to the mysql list, no you're not seeing double.
>> I posted a very similar message on the mysql lists a couple
>> of days ago.. )
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 8: explain analyze is your friend
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Jonah H. Harris, UNIX Administrator | phone: 505.224.4814
Albuquerque TVI | fax: 505.224.3014
525 Buena Vista SE | jharris(at)tvi(dot)edu
Albuquerque, New Mexico 87106 | http://w3.tvi.edu/~jharris/

A hacker on a roll may be able to produce, in a period of a few
months, something that a small development group (say, 7-8 people)
would have a hard time getting together over a year. IBM used to
report that certain programmers might be as much as 100 times as
productive as other workers, or more.

-- Peter Seebach