Migrate PostgreSQL to Oracle - first attempts

For a customer case, I have been investigating whether it’s possible to migrate from PostgreSQL to Oracle. Recently, I did a MS Access to Oracle migration quite simply using SQL*Developer. Hoping that it was this simple with PostgreSQL too, started to Google around.

It seems that you can connect to PostgreSQL using SQL*Developer 4.0. Go to the site: http://jdbc.postgresql.org/download.html  and get the correct JDBC PostgreSQL driver (depending on your java version). Unpack this jar file and add it to SQL*Developer using the description in http://www.oracle.com/technetwork/developer-tools/sql-developer/thirdparty-095608.html .

Now when you create a new connection, you can select the tab PostgreSQL:

For this demo to work, I installed postgresql local, with the dvdrental demo database.

So what do you see when this is Connected in SQL*Developer? You can open up the database tree:

Also, you can view the data, so for example in table 'actor':

Now let’s try to migrate. I first tried to Tools > Migration > Migrate and follow the migration wizard. For this database, this takes some 5-10 minutes. It seems to work, but the results are very disappointing. When you select a target Oracle database, a schema will be created PUBLIC_DVDRENTAL where the tables should appear. The scripts that are created on your workstation, contain the Oracle-formatted DDL, but these appear to have some errors that need to be filtered out.

With some trial and error in the DDL scripting, I was able to get the table definitions right. The data however was not imported. You can choose for a separate export from Postgres (using SQL*Developer for example) and table-by-table move the data after creating the tables. The easiest way it seems to me, is to select all PostgreSQL tables, right-mouse Export, export them to csv in a directory as separate files, and then import them using SQL Loader.

Summarizing, I think this method gives some options to get started with your migration. It’s far from perfect however. We should wait for Oracle to get this tooling right in SQL*Developer.