Re: mirroring oracle database in pgsql

On Mon, Jun 06, 2005 at 12:52:13PM -0700, Edward Peschko wrote:
> 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.

You should take a look at contrib/dblink, which AFAIK allows connections
from PostgreSQL to Oracle. It should make it easy to sync data between
the two.

> 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.

Unless you get Oracle backups from customers or something you should
probably be able to completely leave Oracle.

> However:
>
> a) I'm not sure how well stored procs, views, triggers and
> indexes transfer over from oracle to postgresql.

PostgreSQL goes to great lengths to comply with ANSI SQL, probably
moreso than any other database. Generally, most SQL written for Oracle
that isn't using features not yet supported by PostgreSQL (such as WITH
or the OLAP extensions) should play just fine. PL/PGSQL is also fairly
similar to PLSQL. I think there's also some Oracle -> PostgreSQL
migration tools out there.

> b) I'm not sure how scalable postgresql is, and how well
> it handles multiprocessor support (we'd be using a
> six-processor box.

It's not as scaleable as Oracle, but then again pretty much nothing else
is either. It really depends on what you're doing. PostgreSQL uses a
process for each connection, so an OLTP environment well make use of
multiple CPUs just fine, but there's currently no support for parallel
query processing so if you're doing a lot of large queries it might be
an issue.

> 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..

Something you might want to consider is MySQL's disregard for data
integrity. Try stuffing 'xx' into a varchar(1) some time and see what
happens. That's just one example; http://sql-info.de/mysql/gotchas.html
has a pretty complete list.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"