Re: [GENERAL] mirroring oracle database in pgsql

I wouldn't say it's enterprise-grade, but one could probably make it work.

Sean Davis wrote:
> 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@tvi.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@postgresql.org
>>
>

--
Jonah H. Harris, UNIX Administrator  | phone: 505.224.4814
Albuquerque TVI                      | fax:   505.224.3014
525 Buena Vista SE                   | jharris@tvi.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