Features Compatibility with Oracle
A few days back I was given the task to write a document giving the compatibility features in PostgreSQL when comparing with Oracle as a prospective customer wanted to move out of Oracle. They gave us a list of features for which we had to give some sort of compatibility in PostgreSQL.
Here is how I ended up doing it:
|
Feature |
Comments |
|
|
|
|
Procedural Language |
Supported using PLPGSQL which is very simiar to PLSQL in Oracle |
|
Windowing Functions |
RANK, OVER, PARTITION BY, WITH supported for Recursive queries |
|
Full Text Search |
Proper linguistic, searching and ordering support for natural language documents |
|
Table Paritioning |
Range and list paritioning is supported |
|
Tablespaces |
Oracle like tablespaces support |
|
Database Links |
Supported using “dblinks” contribution module |
|
Localization |
Provides vast majority of character sets including multi-byte ones. |
|
Transactions |
ACID compliant and uses MVCC |
|
GIS Support |
Supported with PostGIS which uses GEOS 3.1 |
|
Query Optimization |
Cost based query optimization |
|
Large Objects Support |
Options available for Oracle like BFILE, BLOB and RAW data types using pre-defined large object methods |
|
XML Support |
XML data type supported |
|
Application Drivers |
Perl, Java, C/C++, PHP .NET, Python, TCL, Delphi, Ruby, ODBC, OLEDB and Openoffice drivers |
|
Embedded SQL |
Using ECPG |
|
Process Monitoring |
Using tools like ‘ptop’ |
|
Replication and Load Balancing |
Using Slony with PgPool |
|
Recovery Mecahnism |
With PITR (Point In Time Recovery) |
|
Backup and Restore |
Standard backup and restore procedures for database using pg_dump and pg_restore |
|
Access Control |
Done by controlling a configuration file where Operating System level authentication can also be used |
|
Gateway Product support |
Using DBI-Link it is possible to connect to any datasource from within PostgreSQL |
|
Portability |
Supported on most of the Unix based platforms (Solaris, RHEL, Ubuntu, SuSe, FreeBSD and many more) and Windows |
–
Shoaib Mir
shoaibmir[@]gmail.com
this is pretty useless unless you provide more details on implementation. E.g. dblink implementation in Oracle is much more advance than in Postgres (you can’t mix local and remote query in one sql which makes it useless in 90% of cases). There is also no cold backup (rman) alternative in postgres other than os-native.
Well I agree with what you are saying, but this was only meant to give the users a general overview not a detailed one which is why you dont find any implementation details. I will be covering it sometime later as I start preparing that
Thank you, will be looking forward.
On the one hand, I agree with Slava that for purposes of compatibility, this list isn’t terribly helpful, since many of the features in Postgres and not compatible with Oracle, even though they allow you to accomplish the same goals (Think CONNECT BY vs. WITH RECURSIVE)
On the other hand, and this is what I tell me customers when doing Oracle conversions, generally the tools Postgres gives you can help you accomplish the same goals as you would using Oracle’s tools; for example what are you really accomplishing with cold backup?
Also, you have to remember that just because you are familiar with a tool, doesn’t mean it is better: I like the syntax of oralink more than dblink (and you can mix remote/local queries, it just isn’t always wise to do so, just like in Oracle), but if you want to do something like connect and execute a remote function one a non-native database, dblink is a whole lot better for that task.
This list was actually just a general overview so not really an extensive one, this is why I didnt cover all the details but yes I have done a number of Oracle conversions and I found PostgreSQL to be really handy in almost every case where PostgreSQL tools were able to do what I needed and the community help in that process had always been very handy with giving the work-arounds.
A couple of points.
First: v8.4 is in beta, and will be released soon. Several of these list items are included or expanded upon in 8.4. Ref http://www.postgresql.org/about/featurematrix.html#1
Second. Take a look at EnterpriseDB – This version of PostgreSQL has had a lot of Oracle compatibility added to it.
>>First: v8.4 is in beta, and will be released soon. Several of these list items are included or expanded upon >>in 8.4. Ref http://www.postgresql.org/about/featurematrix.html#1
Yes, I have already used them from versions out of the CVS and very happy with everything, cant wait for the release itself. This is why I added them in the list.
>>Second. Take a look at EnterpriseDB – This version of PostgreSQL has had a lot of Oracle compatibility added >>to it.
I worked for EnterpriseDB for more then two years from the very start back in end of 2005
I know they are very much Oracle compatible but the client I am working with is much more interested in standard PostgreSQL and not the modified version.
FANTASTIC!
For us, the stumbling block is always our large base of 3rd party oracle forms. If only there was a suitable answer for that.
not really an expert on the forms but I guess you can use ODBC in there or the best bet if possible to convert them to JSP pages. I cant really recall the exact name of the product but there used to be one which can take your Oracle forms and convert them to JSP pages.