Migrating Oracle to PostgreSQL

So at work we're currently trying to see if we can move away from Oracle and onto PostgreSQL.

I expect this to be a long process.

This blog post is here to document some of the issues we ran into and also remember references I found.

Migrating schema and data

There are several tools that can migrate the schema and the data from Oracle to Postgres.

There are some differences in what both database support. See reference [1].

Make changes to SQL Scripts

References

[1] Hackermoon - How to Migrate from Oracle to PostgreSQL

https://hackernoon.com/how-to-migrate-from-oracle-to-postgresql

Postgres Wiki - Oracle to Postgres Conversion

https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

PostgreSQL Manuals

https://www.postgresql.org/docs/

Ora2Pg - Moves Oracle and MySQL database to PostgreSQL

https://ora2pg.darold.net/

Life in USA - install ora2pg on mac osx

https://vcfvct.wordpress.com/2016/10/18/install-ora2pg-on-mac-osx/

Migrating between JEE versions

I had an issue where I wanted to port my current JEE 6 (Web profile) application (YPPO) to JEE 7 (Full profile).

This concerns a Netbeans project (built using Ant)

I thought I'd just write down which files needed changing.

nbproject/project.properties

-j2ee.platform=1.6-web
+j2ee.platform=1.7

persistence.xml

-<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
+<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

beans.xml

-<beans xmlns="http://java.sun.com/xml/ns/javaee"
+<beans xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
- xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/beans_1_0.xsd">
+ xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/beans_1_1.xsd" bean-discovery-mode="annotated">

web.xml

-<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
+<web-app version="3.1" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"></dd>

References

Github - changed from JEE 6 Web (Lite) to JEE 7 (Full).

https://github.com/maartenl/YourPersonalPhotographOrganiser/commit/82d0c00812ec626e21c7b48a6a5523fb0311218e

Github - changed from JEE 6 Web (Lite) to JEE 7 (Full) Part II.

https://github.com/maartenl/YourPersonalPhotographOrganiser/commit/a092896dc00805f640a1dd305e248c39aa3cf70b

Github - bean-discovery-mode required attribute in beans.xml

https://github.com/maartenl/YourPersonalPhotographOrganiser/commit/b979c896711e7ebf30f90534aaffe9ddbe855681