How to run Postgres in Oracle compatibility mode

One of the biggest advantages of choosing the EnterpriseDB version of PostgreSQL is its Oracle compatibility. EnterpriseDB helps make migration easier and much quicker because you are able to use the existing queries and applications along with our professional services team, which is well versed with Oracle. Not to say that it does not have its challenges but it makes it worth it to move to an Open Source Platform.

Since the Oracle compatibility is limited to EDB Postgres Advanced Server, you’ll need to download the specific installer or rpm from the EnterpriseDB website itself. There is a trial version available for 60 days, which gives you full feature access. 

Now let’s see how we can set up Oracle compatibility using the installer, or, if we already have EDB Advanced Server installed, we can start a new cluster for Oracle compatibility.

Setting Oracle compatibility while installing

After installing the EDB Advanced server, you will be required to initialize the cluster. In order to set the Oracle compatibility options you will need to edit the edb-as-11.sysconfig file: 

To make sure the Advanced Server instance is Oracle compatible, you need to add the hash in the last line that states “--no-redwood-compat” if it is not there already.

NOTE: if you already have an installed server, you can use the initialization cluster option (initdb) with “--no-redwood-compat" in order to create a non-Oracle compatible server. By default, the instance created with initdb from an EDB Advanced Server would be an Oracle compatible server.

Checking whether an existing cluster is Oracle compatible 

You can use the following query at the SQL prompt to verify whether the current installation is Oracle compatible or not:

edb=# show db_dialect;



 db_dialect

------------

 Redwood

If the result comes back as “Redwood,” then you are running an Oracle compatible cluster. If not, then the query will error out::

ERROR:  unrecognized configuration parameter "db_dialect"

SQL state: 42704

Additionally, you can run the following queries to determine the cluster’s Oracle compatibility:

select * from dual ; 



or 



select sysdate;