mirroring oracle database in pgsql
Edward Peschko
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 6: Have you searched our list archives?
Scott Marlowe
> 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.
If you've been using Oracle, PostgreSQL is likely to be a much better
fit. MySQL's tendency to silently do stupid things (create a table as
innodb, but spell it innobd, it will make an isam table and not tell
you. insert data, roll back, find out that you can't roll back, the
list goes on and on.) and lack of features you likely take for granted
in Oracle will likely make Postgresql the better fit.
You might want to look at either CJDBC or Daffodil for what you're
thinking of. I'm not sure how well they'll work in a mixed environment,
but they seem to be the leaders in client side clustering.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Sean Davis
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
>>> 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 | jha...@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
> majo...@postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Sean Davis
On Jun 13, 2005, at 6:48 PM, Jonah H. Harris wrote:
> I wouldn't say it's enterprise-grade, but one could probably make it
> work.
>
I totally agree--I use it relatively often. This single piece of
software opened my eyes as to the extent to which the procedure
languages can be leveraged.
Sean
> Sean Davis wrote:
>> There is DBI-link, but this probably isn't an "enterprise"
>> solution....
>> http://www.pervasive-postgres.com/postgresql/tidbits.asp
>> Sean
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly