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@xxxxxxxxxxx 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?" ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
