Migrating from Oracle to Postgres for a Java Hibernate application

Posted on January 23, 2014 by justme

For an old project which is Spring/Hibernate based, there was a need to migrate from an Oracle database to a PostgreSQL database.

The initial task was to migrate the database. I used ora2pg for this. This is a command line tool which allows you to extract your Oracle database to SQL. It was somewhat difficult to install on my Ubuntu system (because I needed the Perl Oracle client – once I got that fully installed it was a breeze, these two articles helped me a lot).

Ora2pg uses a configuration file. You need to fill in the correct for ORACLE_DSN, ORACLE_USER and ORACLE_PWD. Do not forget to also set the SCHEMA value (likely to the same value as ORACLE_USER).
To control the data which needs to be exported, you have to set the TYPE.
I first exported the table structure using “TABLE”. I then copied the result file (to prevent it from being overwritten) and then ran ora2pg again using “INSERT” as type to export the data.

Before being able to use these files, I needed to convert them to UTF-8. This was done using the command

recode ISO-8859-1..UTF8 *.sql

To create the database in Postgres I started by splitting the file with the table structure in a file which only creates the table structure itself and one which contains the constraints. I also had to extract the tail of output file with the data which contains the new values for the sequences. It was then possible to rebuild the database by first creating the structure, then inserting the data and lastly rebuilding the constraints.

psql wd_test -f output-table-structure-structure.sql
psql wd_test -f output.sql
psql wd_test -f output-table-structure-constraints.sql

To finish off I still had to add the necessary sequences (you should be able to get them though ora2pg again though I used the Hibernate generated DDL) and reset the sequences to the correct values.

Last step of the data migration was to compare the Hibernate generated DDL so see whether all constraints exist. I particularly has to add a primary key in all tables, foreign key constraints and define various unique constraints.

Then test your application.

I encountered two problems. First, there are some functions which were not available. I checked all HQL and native queries to search for functions which are used. There were two missing for this application, month and year. These had to be defined.

CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(MONTH FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;
 
CREATE OR REPLACE FUNCTION MONTH(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(MONTH FROM $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
 
CREATE OR REPLACE FUNCTION MONTH(DATE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(MONTH FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;
 
 
CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP without TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(YEAR FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;
 
CREATE OR REPLACE FUNCTION YEAR(TIMESTAMP WITH TIME ZONE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(YEAR FROM $1)::INTEGER;
$$ LANGUAGE SQL STABLE;
 
CREATE OR REPLACE FUNCTION YEAR(DATE) RETURNS INTEGER
AS $$
      SELECT EXTRACT(YEAR FROM $1)::INTEGER;
$$ LANGUAGE SQL IMMUTABLE;

Lastly, there were count queries which had an order defined on them. Typically the query was built using generic code and either executed normally or using a count projection. This works perfectly in Oracle but Postgres complains saying

column “tableName.fieldName” must appear in the GROUP BY clause or be used in an aggregate function

Fixing the code for this seemed non-trivial and possible cause of bugs, so I introduced “patch” code which removes the order from the query and is called before applying the count projection.

public final class PgCompatibility {
 
    private static final Logger LOG = LoggerFactory.getLogger(PgCompatibility.class);
 
    private PgCompatibility() {
        // hide constructor
    }
 
    /**
     * Criteria from which the order needs to be removed.
     * <p/>
     * On queries like "select count(*) from TD_MAANDPRESTATIE order by D_EERSTE_KALENDERDAG asc" the order is
     * irrelevant. Oracle seems to recognize this while Postgres gives an error like
     * "column "d_eerste_kalenderdag" must appear in the GROUP BY clause or be used in an aggregate function"
     *
     * @param criteria criteria from which the order needs to be removed if possible
     */
    public static void removeOrder(Criteria criteria) {
        if (criteria instanceof CriteriaImpl) {
            try {
                Field orderEntries = CriteriaImpl.class.getDeclaredField("orderEntries");
                makeAccessible(orderEntries);
                ((List) orderEntries.get(criteria)).clear();
            } catch (NoSuchFieldException nsfe) {
                LOG.error("Cannot find field.", nsfe);
            } catch (IllegalAccessException iae) {
                LOG.error("Cannot access field.", iae);
            }
        }
    }
 
    /**
     * Make the given field accessible, explicitly setting it accessible if necessary.
     * The setAccessible(true) method is only called when actually necessary, to avoid unnecessary
     * conflicts with a JVM SecurityManager (if active).
     * <p/>
     * This method is borrowed from Spring's ReflectionUtil class.
     *
     * @param field the field to make accessible
     * @see java.lang.reflect.Field#setAccessible
     */
    public static void makeAccessible(Field field) {
        if ((!Modifier.isPublic(field.getModifiers()) || !Modifier.isPublic(field.getDeclaringClass().getModifiers()) ||
                Modifier.isFinal(field.getModifiers())) && !field.isAccessible()) {
            field.setAccessible(true);
        }
    }
}

Presto, migration worked.

Adam Roberson says:

March 20, 2014 at 16:04

Can you be a little clearer on where you introduced the patch code? We are experiencing the exact same problem on our application.

joachim says:

May 5, 2014 at 10:12

Sorry for the late reply.
I explicitly call the patch code before executing the criteria. This is an extra step introduced when it mey be needed.
Does that help?