Migrating from Oracle to PostgreSQL: Tips and Tricks

Migrating to PostgreSQL from Oracle is a topic that often comes up in discussions around PostgreSQL. At Crunchy Data, we are of course not surprised that there is broad interest in moving to the world's most advanced database.

There are a lot of great reasons to migrate to PostgreSQL, and if you haven't looked at PostgreSQL lately, it would be a good time to do so again given the various improvements in PostgreSQL 12.

That said, migrating from one database to another is inherently new technology and can raise a multitude of questions. To help ease the transition, we are providing a few frequently asked questions and answers from users transitioning from Oracle to PostgreSQL, based on real life scenarios, to serve as a helpful resource.

Common Questions, Differences and Solutions

How To Install Orafce

Orafce is a useful extension that allows you to implement some functions from Oracle in PostgreSQL. For example, if you are used to DATE functions in Oracle, this extension allows you to use those functions. For additional information about Orafce: https://github.com/orafce/orafce.

Simply follow these steps to get Orafce up and running in a PostgreSQL 12 and RHEL 7 environment.

Typically, the process to build Orafce from source code is relatively user-friendly, but requires a number of dependencies. First, it is necessary to have the postgresql12-devel package installed, as it contains the binary for pg_config. Assuming postgresql12-devel is installed, you may proceed to the following steps to build Orafce and create the extension.

1. Install the dependencies:

   sudo yum -y install flex bison readline-devel zlib-devel openssl-devel wget libicu-devel install llvm5.0-devel llvm-toolset-7-clang gcc-c++
   

2. Download the full Orafce source code, available on GitHub. If you are able to connect to GitHub directly, you may use the following command:

   git clone git@github.com:orafce/orafce.git
   

3. Make sure you have pg_config in your path. You may use echo $PATH to check if /usr/pgsql-12/bin is present. If not, do the following: export PATH=$PATH:/usr/pgsql-12/bin/

4. Build the source code. From within the orafce directory, run the following command:

   make all
   

5. Install the source code. From within the orafce directory, run the following command:

   make install
   

6. Create the orafce extension inside the database. Connect to the database as a user with extension creating privileges and use the following command:

   CREATE EXTENSION orafce;
   

You will also need to have rhel-7-server-devtools-rpms enabled in order to access the llvm-toolset-7-clang package. This repo can be enabled by running the following command as superuser: subscription-manager repos --enable=rhel-7-server-devtools-rpms.

Having performed all of these steps, you will have successfully created the orafce extension for your PostgreSQL database.

How To Disable and Enable Constraints

As you may know, Oracle allows you to disable and enable a constraint as many times as needed. This is something that is not commonly done in PostgreSQL and generally isn’t recommended in any database instance. Even though Oracle allows users to disable and enable constraints, this can cause you to run into data corruption if not handled with great care.

In PostgreSQL, instead of disabling constraints, one typically creates the constraints as deferrable and then uses the SET CONSTRAINTS command to defer them. If the constraint isn't currently deferrable then it will need to be dropped and recreated as deferrable. When creating a constraint, the deferrable clause specifies the default time to check the constraint.

It may also possible to alter the constraint and make it deferrable, avoiding the need to drop and recreate. Note that all DDL in PostgreSQL is transactional, so if you wish to drop and recreate things without letting users enter potentially bad data, you can put all of the DDL in a transaction denoted by the BEGIN/COMMIT block. The tables will be locked for the transaction.

How To Disable ‘NOT NULL’ Constraint

Similar to the question above, we were asked how to disable NOT NULL constraint in PostgreSQL. In Oracle, when you run the command DISABLE CONSTRAINT it disabled all of the constraints including NOT NULL. As mentioned before it is not recommended to disable and enable constraints because the constraints can allow bad data into the database table without warning or notice. If this happens there would be no way to tell how long queries will have been returning necessarily insufficient and/or incorrect results based on bad data.

Fortunately, it is currently not possible to disable/enable NOT NULL in PostgreSQL. If you are required to do this, a better way is to drop and re-add the constraint. The command ALTER TABLE tablename ALTER COLUMN columnname DROP NOT NULL; is how you drop the NOT NULL constraint.

To re-add the NOT NULL constraint you will use the command ALTER TABLE tablename ALTER COLUMN columnname SET NOT NULL;. Re-adding the NOT NULL constraint will cause the constraint to be validated again, so this is not an instant operation. However, dropping and re-adding the NOT NULL constraints might be faster than having the constraint evaluated on every write during your process.

The GRANT Command

The GRANT command in PostgreSQL operates very similarly to Oracle. There are two basic variants to the command. It can grant privileges on a database object and grant membership to a role. A common question is how to grant create procedure or trigger to a role in PostgreSQL.

In PostgreSQL, you can grant the TRIGGER privilege to a table which gives the ability to create triggers on that table, not to use them. So, if trigger creation is all you are trying to grant, that is the only privilege you need to grant. You do not have to grant any special privileges to roles other than normal write privileges (INSERT,UPDATE,DELETE) in order to be able to then use the triggers on that table. As long as a role has normal write privileges to that table the triggers will automatically fire as needed.

All triggers in PostgreSQL use FUNCTIONS as the underlying object that performs the trigger action, not PROCEDURES. PROCEDURES code> did not exist in PostgreSQL prior to version 11 and as of version 11 they are two distinct object types.

The command syntax for CREATE TRIGGER requires some consideration. Prior to version 11, the clause to the CREATE TRIGGER command used the phrase EXECUTE PROCEDURE to name the object that the trigger will fire. As of version 11, it allows you to use the clause EXECUTE PROCEDURE or FUNCTION, however a function is still the only object allowed to be given here as the argument. As the current documentation for the command states:

"In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and depreciated."

Additional information regarding CREATE TRIGGER can be found here.

You can also do mass grants of specific privileges on existing objects, to grant all privileges to all procedures in the given schema. So to grant trigger creation privileges on all tables in a given schema you can do:
GRANT TRIGGER ON ALL TABLES IN SCHEMA <schema_name> TO ;

Note that it does not give the privilege to then drop triggers. Only the owner of a table can drop them. This command only does that for existing objects and not any future objects that may be created.

Is it possible to drop database objects in PostgreSQL?

In PostgreSQL, only the owner of the database or a super user is allowed to drop objects.

As per the following documentation, “The right to drop or alter an object, is not treated as a grantable privilege. The owner inherits this privilege. (However, a similar effect can be obtained by granting or revoking membership in the role that owns the object) The owner implicitly has all grant options for the object, too.”

Please note: If your application or service depends on the Oracle ability to drop objects, it is possible that you might need to rewrite or reconfigure how this action is performed.

How To Check for NOT NULL

In Oracle, if you want to know which columns in a table are NOT NULL you can use the command CHECK (<col_name> IS NOT NULL). PostgreSQL does this a little differently. Here’s how to check for this.

There is a NOT NULL constraint column in the pg_attribute systems catalog. The pg_attribute catalog stores information about table columns. As stated in the documentation (https://www.postgresql.org/docs/current/catalog-pg-attribute.html), “there will be exactly one pg_attribute row for every column in every table in the database.” attnotnull is the column name in pg_attribute that represents NOT NULL constraints.

If you are wondering where other constraints are stored in the system catalog, you can look at the following documentation (https://www.postgresql.org/docs/11/catalog-pg-constraint.html).

For an example query of how to link catalogs together to find not null constraint information, the query below that shows all user tables in the database that have not-null columns along with which columns they are.
If you also want to see these columns in the system catalogs, you can remove the WHERE condition that excludes the system schemas. Many links in system catalogs are managed via "oid" values and which tables they relate to are explicitly mentioned in the documentation for that system catalog (Ex: pg_attribute.attrelid relates to pg_class.oid).

SELECT n.nspname as schemaname, c.relname as tablename, a.attname as columnname
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_attribute a ON c.oid = a.attrelid
WHERE a.attnotnull IS NOT NULL
AND n.nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
AND a.attnum > 0
ORDER BY 1,2,3;

ROWID, CTID and Identity columns

Oracle has a pseudocolumn called ROWID, which returns the address of the row in a table. PostgreSQL has something similar to this called CTID. The only problem is that the CTID gets changed after every VACUUM function. Fortunately, there is a good alternative for this: identity columns.

Since there is no ROWID in PostgreSQL we suggest using self-generated unique identifiers. This can be achieved in the form of identity columns. Identity columns will help you because they are generated when the row is created and will never change during the life of that row. It is important to know that the way IDENTITY is implemented means that you cannot pre-allocate values. IDENTITY also has additional logic controlling their generation/application, even though it is backed by a sequence. Use the following syntax to create an identity column:

column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

In the syntax you can see the additional logic as how you would generate the identity column. GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you try to insert/update a value in this GENERATED ALWAYS column, PostgreSQL will give you a warning. This is because the values are system generated and GENERATED ALWAYS means it can only have the system generated values.

GENERATED BY DEFAULT also instructs PostgreSQL to generate a value for the identity column. However, with GENERATED BY DEFAULT you can insert or update a value into the column, and PostgreSQL will use that value for the identity column instead of using the system-generated value.

We hope that this overview of a few common issues in transitioning from Oracle to PostgreSQL eases the process and gives long time Oracle users greater comfort as they evaluate PostgreSQL as an alternative.

For those just getting started with the migration consideration, no blog post on Oracle to PostgreSQL migration would be complete without a mention of ora2pg - a great open source Oracle migration tool that can help evaluate the of difficulty in your migration. Of course Crunchy Data is always here and happy to assist as well!