Oracle to Postgres : The Database Darwinism

The launch of Oracle Autonomous Database sparked a debate about the future of the database administrator (DBA) role. In Oracle Corporation’s vision, database administrators would become data engineers.

Hold my beer — Postgres replied to Oracle

In spite of Oracle automating a huge amount of DBA work, I observe

• Databases are becoming larger every day.
• Data is being produced and consumed faster as time marches on.
• Data users and usage patterns are getting more complex.
• Database availability requirements are becoming more stringent.

Oracle DBAs will not become obsolete. Oracle hasn’t done anything wrong with this development, so I consider it a positive development. Nevertheless, it provides plenty of meme opportunities and food for thought.

Let’s talk about the savings that come with migrating from Oracle to PostgreSQL. PostgreSQL is open-source, so it’s free to use. Oracle, on the other hand, requires significant licensing fees. Additionally, Oracle’s licensing is based on a per-processor fee, which limits instance CPU scaling based on additional fees. Today’s cloud native companies are comfortable scaling up and down instances to achieve optimal performance at the lowest cost. You won’t have to pay extra for licensing if you bump your EC2 instance to double the vCPUs for a few hours during peak load. Annual support fees are Oracle’s bread and butter. Oracle’s strategy is to lock customers into decades of annual support by offering deep discounts off high list prices. Support expenses will be trimmed significantly as the PostgreSQL open source community is vast and active, providing ample resources and support. In the Postgres community Slack, I have found some very quick and knowledgeable responses to all my questions for free.

What is more important to you: spending several million dollars on Oracle database licensing and support or spending less than 1/10th on a small Postgresql DBA team?

Besides money talk, I see a lot more innovation in the Postgres open source community than in Oracle. Innovation in open source PostgreSQL development is thriving, with novel ideas and improvements constantly being brought to the table. One prominent example of this is the introduction of “Foreign Data Wrappers”, which allow PostgreSQL to seamlessly integrate with other data sources, such as NoSQL databases and other relational databases. Another exciting innovation is the development of advanced indexing techniques, such as GIN/GIST indexes for full text search, special “BRIN” (Block Range Index) for very large tables and space efficient “BLOOM” indexes. And let’s not forget PostgreSQL’s impressive array of extensions, which offer an array of powerful, cutting-edge features. The list doesn’t end here as several companies are focused on Postgresql such as Citus, which has an amazing sharding solution for Postgres, Neon, with serverless and branchable Postgres, and EDB with its cloud native Postgres BigAnimal. Oracle to Postgres evolution seems natural given the rich ecosystem of innovation and development.

I know what you’re thinking, “Migrating databases sounds like a nightmare!” But trust me, with Ora2PG, it’s super easy and even fun (yes, I said fun). Not only that, but you’ll end up with a more cost-effective and flexible database system that’s perfect for your needs.

Let me walk you through the switch using Ora2PG (Ubuntu).

Step 1: Installing Ora2PG
Oracle client must be installed, ORACLE_HOME must be set and Perl must be installed.

#Install DBI 
apt-get install cpanminus
cpanm DBI

#Install DBD::Oracle and DBD::Pg
cpanm DBD::Oracle
cpanm DBD::Pg

#Install ora2pg
tar xjf ora2pg-x.x.tar.bz2
cd ora2pg-x.x/
perl Makefile.PL
make && make install

Step 2: Configuring Ora2PG

This file tells Ora2PG what to do and how to do it. Check out this example of the config file:

# Oracle database source information
ORACLE_DSN      dbi:Oracle:host=linux1;sid=ora12c;port=1528
ORACLE_USER     system
ORACLE_PWD      xxxxxxxxxxxx

# Oracle schema/owner to use
SCHEMA HR

# Define the following directive to send export directly to a PostgreSQL
# database. This will disable file output.
PG_DSN          dbi:Pg:dbname=hr;host=localhost;port=5432
PG_USER        pg12user
PG_PWD        xxxxxxx

Step 3: Running Ora2PG

#test
ora2pg -t SHOW_VERSION -c ora2pg.conf

#Estimation report
ora2pg -t SHOW_REPORT --estimate_cost -c ora2pg.conf

#Execute
ora2pg -c ora2pg.conf

Ora2PG connects to Oracle database, grabs all the schema information, and generate SQL scripts that would be used to recreate the schema in PostgreSQL.

Hopefully, the following tips will make the migration as smooth as possible.

• Orafce extension provides functions and operators that emulate a subset of functions and packages from the Oracle RDBMS. For example, Oracle accepts 0 or a negative number as a start position argument when using the substring function, which differs from PostgreSQL. This difference can be resolved using oracle.substr from the orafce extension on Postgres.
• There is no nested transaction support in Postgresql, but Oracle does. You will likely have to do some manual porting if your Oracle deployment relies heavily on PL/SQL and nested transactions.
• You may have to do some manual porting if you use Oracle packages extensively.
• PostgreSQL requires aliasing for all sub-queries inlined within the FROM clause. Oracle does not throw an exception.
• In Oracle, scalar subquery caching and result cache can speed up inefficient queries. However, Postgres will force you to tune your code.
• View predicate pushing might be different and cause a change of explain plans.
• Oracle uses the (+) operator for left and right join but PostgreSQL does not use it. Postgres joins are ANSI standard sql joins.
• Postgres does not require a mandatory dual table like Oracle.
• Sequence syntax is slightly different.
• Study Patroni for maintaining high availability via autofailover enabled multi-node Postgres cluster.
• pgbackrest isn’t as user friendly as RMAN but one of the best backup options for Postgres.
• Last but not least, understand the differences in MVCC implementation and do not fear the autovacuum. The autovacuum is your friend.

Finally, if you’re considering switching from Oracle to PostgreSQL, don’t hesitate. It’s a natural evolutionary step for database systems.