Re: [HACKERS] mirroring oracle database in pgsql

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: "Jonah H(dot) Harris" <jharris(at)tvi(dot)edu>
Cc: Edward Peschko <esp5(at)pge(dot)com>, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>, pgsql-hackers(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [HACKERS] mirroring oracle database in pgsql
Date: 2005-06-13 22:34:49
Message-ID: f7176ba8f8c858311b630d8ee74b4944@mail.nih.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

There is DBI-link, but this probably isn't an "enterprise" solution....

http://www.pervasive-postgres.com/postgresql/tidbits.asp

Sean

On Jun 13, 2005, at 2:31 PM, Jonah H. Harris wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jonah H. Harris 2005-06-13 22:48:33 Re: [HACKERS] mirroring oracle database in pgsql
Previous Message Adam Treat 2005-06-13 22:02:39 Announcing dataKiosk version 0.7

Browse pgsql-hackers by date

  From Date Subject
Next Message Jonah H. Harris 2005-06-13 22:47:20 Re: User Quota Implementation
Previous Message Yann Michel 2005-06-13 21:13:37 Re: User Quota Implementation