Converting Oracle to Postgres or EDB Postgres

Migrating off Oracle to Reduce Cost

MGA is a database and data specialist company. We support a broad range of databases including migrating from one database to another, for example, Oracle to SQL Server. We are often approached by clients that have a desire to significantly reduce their database maintenance cost and are considering Postgres (PostgreSQL), MongoDB or other free or low cost databases. Migrating to SQL Server will often reduce cost, but our clients are after a significantly larger saving.

Many of the clients are currently running Oracle databases. Oracle is a well proven, high quality, highly secure, high performance and supported database. It has a broad range of capability such as spatial, XML support as well as tools that assist greatly with high availability, scalability and disaster recovery. This article details our findings in converting from Oracle to EDB Postgres and to Postgres.

EDB Postgres Features Compared to Oracle

EDB Postgres (EnterpriseDB) is an interesting database offering because it is designed to provide an easy migration off Oracle to its own proprietary database which is has many Oracle like features on top of a Postgres database. The migration does not only migrate the data, but also migrates the Oracle stored code. It provides most of the Oracle dictionary views as well as equivalents to Oracles stored procedure offerings such as DBMS_SCHEDULER.

So what is EDB Postgres missing compared to Oracle? EDB Postgres has no RAC, no Compression (Tables, Files, Network, Backups), no Advanced Compression, no Interval Partitioning (auto partition creation), no Partitioned Indexes, no Flashback, no In Memory Database, no Database Vault, no Distributed Transactions, no Apex, no XML DB, no Transportable Tablespaces, no Online Re-organisation, no Automatic Memory Management, no Automatic Storage Management, no Pipelined Functions and no Exadata.

Postgres Features Compared to Oracle and EDB Postgres

EDB Postgres (EnterpriseDB) comes with a license cost, albeit a significantly less cost than databases such as Oracle and SQL Server. Postgres on the other hand is a free database (no license or maintenance). But what features are missing in standard Postgres compared to Oracle and EDB Postgres?

As with EDB Postgres, Postgres has no RAC, no Compression (Tables, Files, Network, Backups), no Advanced Compression, no Interval Partitioning (auto partition creation), no Partitioned Indexes, no Flashback, no In Memory Database, no Database Vault, no Distributed Transactions, no Apex, no XML DB, no Transportable Tablespaces, no Online Re-organisation, no Automatic Memory Management, no Automatic Storage Management, no Pipelined Functions and no Exadata.

Compared to EDB Postgres, there is no conversion from Oracle capability (PL/SQL, functions, triggers, packages, procedures), no Oracle compatibility, less security features (no auditing [see pgaudit issues]), no password strength, no timeouts, no lockouts), less performance features, no hash partitioned indexes, no SQL query hints, no SQL profiler, no index advisor, no Postgres Expert or Oracle Tuning Advisor equivalent (performance advice), no database links (cant take Oracle code and make it work but – uses Foreign Data Wrappers) and there are no incremental backups.

Risks of Converting from Oracle to EDB or Postgres

Resourcing – Can teams be built with adequate skills in PostgreSQL and Enterprise DB? Typically our clients existing skills are in Oracle. Oracle, Postgres and EDB Postgres are all relational and despite their differences are quite similar in many aspects. However, we believe an Oracle DBA or developer would find it easier to move from Oracle to EDB Postgres than to Postgres. This is due to the Oracle dictionary views being available in EDB and the ability to develop PL/SQL and have it run on EDB Postgres.

Robustness – We comprehensively load tested Postgres and EDB Postgres (EnterpriseDB) in an attempt to break them. Both remained extremely robust, but ran slower when under extreme load. This no different to any other database including Oracle and SQL Server. Based on our results, both Postgres EDB Postgres are capable of supporting mission critical systems. This does not mean that you should convert to EDB Postgres or Postgres without performing your own comprehensive load testing.

Backup/Recovery – Oracle has the most advanced backup and recovery capability. However, EDB Postgres and Postgres both support hot backups (see barman tool RMAN equivalent) as well as having an equivalent of the Oracle Hot Standby, with the target database being read only, but can be used for queries.

Performance – Oracle has an abundance of self tuning performance features and EDB has more features than Postgres. Our observations of Postgres sites has been that they often lack the discipline and procedures of Oracle DBAs. They also often lack the tuning knowledge in the group, with the persons managing the database often not from an IT background. As a result of this, Postgres sites often fail due to performance and scalability issues. Example causes of the failure include not partitioning data appropriately and experiencing severe performance degradation during vacuuming. However, Postgres (and EDB Postgres) can be setup for high performance and scalability with the correct assistance the database is being built and the occasional tuning ongoing.

Security – Both EDB Postgres and Postgres have relatively strong security that is sufficient to cope with most mission critical applications. With vanilla Postgres, the site will need to set standards that ensure items such as password strength are secure and that passwords are changed on a periodic basis. Oracle and EDB Postgres will ensure password strength automatically as well as ageing passwords.

Scalability – EDB Postgres and Postgres are limited to single master writeable host and cant write across multiple servers (they do not have a RAC equivalent). However, you can have query only users access a Read Only Hot Standby of the primary database. Without a RAC equivalent, it is important that the database server is adequately sized and can have memory and CPUs added if required.

Support (Patching, Bug Fixes) – EDB Postgres offers full database support, where a user can raise tickets via a phone or online and receive frequent patches to repair any issues as well as advice on workarounds in the interim. The free Postgres does not have a formal support mechanism, but many users respond on online forums. Postgres patches and typically released every 4-6 weeks. Many Postgres sites purchase an inexpensive Postgres support contract with an external service provider.

Indemnification – EDB Postgres indemnifies its license but PostgreSQL doesn’t.

Cost Comparisons

This section talks about the cost comparisons of using Oracle versus EDB Postgres versus Postgres. If your site is currently running its database on Oracle, and wanting to convert, you need to factor in the conversion cost, the functional and technical testing and determine if it is cost justified to undertake the migration. There is also an element of risk in any database migration.

The costings below were obtained at the time of writing of this article from the vendors published pricing. In reality, the pricing may be heavily discounted. It will provide an indication of how prices may compare. Keep in mind that EnterpriseDB includes all add on’s in its price, whereas Oracle may charge an additional license cost.

License – Oracle EE list price $47,599USD per core, Oracle SE2 list price $17,500USD, EnterpriseDB list price $1,950USD, PostgreSQL list price $0.

Resourcing– At the time of writing this article, Postgres professionals cost 10%+ more on average than the equivalent Oracle professionals. There were no pay scales for Enterprise DB that could be compared.

Conversion Effort – EnterpriseDB has a conversion tool that converts data and stored code (packages, functions, procedures, triggers) from Oracle to EDB Postgres. Postgres has tools to migrate the data but requires a re-write of the stored Oracle code to Postgres procedures. EDB Postgres allows you to maintain PL/SQL and it runs as though it is native to the database.

Case Study – Oracle to EDB Postgres Migration

To truly test the Oracle to EDB Postgres migration capability, we chose an actual mission critical production client site that we manage. We converted its existing Oracle databases to EDB Postgres in our test labs as part of a proof of concept. Each database had around 12500 lines of stored code contained in packages, procedures, functions and triggers. The database also had views that referenced Oracle functions.

MGA Duplica (our replication product) replicates data across 7 states in near real time at this site. The replication was included in the EDB Postgres Proof of Concept. Duplica uses Oracle stored packages and procedures to pull data from the source databases using dynamic SQL. The source databases tracks changes using triggers and log tables. The data replication is master to master with multiple sites able to update the same record requiring conflict resolution.

Our first step was to transfer test data from an Oracle database to EDB Postgres. The data conversion tool was quite fast and we experienced no data issues whatsoever. We then migrated the Oracle stored code. We found that around 80% of the PL/SQL, packages, procedures, triggers and functions ran correctly without any modifications.

Issues with EDB Postgres PL/SQL & Oracle Scheduler

->You cant have high level global declarations in a package
->Compiling only does syntax checking & doesn’t check for dependant objects
->A package is always valid
->Some DBA views are missing (e.g DBA_JOBS_RUNNING, v$instance, v$mystat, and function sys_context)
->DBMS_JOBS initially didn’t work but EDB provided an update and it worked. We were impressed with their support
->Lack of error trapping and reporting makes it harder to find errors and bugs in PL./SQL

From a coding PL/SQL perspective, we modified our stored PL/SQL code to make it operate on EDB Postgres to repair issues. It truly felt as though we were coding in Oracle with no syntax changes required to make it operate on EDB Postgres.

Following the migration we performed comprehensive functional and technical testing to make sure that the data was correct, EDB Postgres provided the required runtimes and remained robust under extreme load. We previously performed the identical tests in Oracle. We found that the data was identical to that in Oracle, performance was almost the same (marginally slower in EDB) and EDB Postgres remained robust, even under extreme load.

Conclusion

Oracle Pros – Proven to satisfy our clients requirements, feature rich, minimal change to existing landscape, internal support strong, Oracle support, indemnification
Oracle Cons – High Cost of ownership

EDB Postgres Pros – Able to migrate DB from Oracle to EDB (including stored code), Oracle skills can use EDB, Oracle like capability, support, indemnification
EDB Postgres Cons – Cost of Ownership, Migration Effort

Postgres Pros – Reduced cost of ownership
Postgres Cons – Large migration cost, tools, no indemnification, minimal support