Thread: mirroring oracle database in pgsql
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..
)
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@decibel.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?"
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
On Mon, 2005-06-06 at 14:52, 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. 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.
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
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 >
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
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