Oracle to PostgreSQL Tutorial

[Oracle to PostgreSQL Converter] [Introduction to Migration] [FAQ] [Command line]

The process of database migration from Oracle to PostgreSQL consists of the following steps:

Select Migration Mode
Connect to Oracle
Connect to PostgreSQL
Select Destination
Conversion Settings
Select Tables
Select Views

Step 1 of 7. Select Migration Mode

Specify the method to migrate Oracle database to PostgreSQL. Oracle to PostgreSQL converter provides two options of database migration:

Move to PostgreSQL server directly - use this approach to migrate database from Oracle to PostgreSQL server directly. You need PostgreSQL server launched and necessary privileges to write into the target database. See Connect to PostgreSQL Server section for related information.

Export into script file - use this approach to export the Oracle database into a local PostgreSQL script file. The resulting file contains SQL statements to create all tables and to fill them with the data. See Import PostgreSQL script files article for related information.

Oracle to PostgreSQL: Select Migration Mode

Step 2 of 7. Connect to Oracle Server

This page allows specifying the necessary settings to establish connection with Oracle server:

Server - IP address or network name of Oracle server. If Oracle to PostgreSQL converter has been installed on the server machine, "127.0.0.1" or "localhost" can be specified in this box.

Database name - enter the same title that is used by SQLPlus or any other standard Oracle tools to point the database.

Port - port for connecting to Oracle. It must be specified only when the value differs from the default value 1521.

Username - Oracle user name

Password - Oracle user password

Oracle to PostgreSQL Migration: Connect to Oracle

Oracle to PostgreSQL migration tool provides two methods of connecting to Oracle server: direct and via TNSNAME.ORA entry. To use the first method, enter information in all fields except the port if it is configured by default (1521). The second method requires leaving both "Server" and "Port" fields empty and enter Oracle service name as it appears in TNSNAME.ORA file in "Database" field.

Sometime user may get the following error: "Oracle data source does not exist. Check to verify correct name was given." despite of the fact that all Oracle connection settings have been specified properly. It means the converter failed to connect to Oracle server using TNSNAME.ORA. To bypass the error leave both "Server" and "Port" fields empty and enter in "Database" field the connection string as follows:


(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=IP address of Oracle server)(PORT=the port)))

Step 3 of 7. Connect to PostgreSQL Server

This wizard screen collects information to connect to PostgreSQL server. Oracle to PostgreSQL converter is able to connect to local or remote target server. Local mode allows to connect PostgreSQL server launched on the same machine where the application is running. Remote mode is designed to connect to PostgreSQL server launched on another computer.

By default, Oracle to PostgreSQL converter uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection, you should select "Remote" radio button. For remote PostgreSQL servers you should also specify the host and the port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default PostgreSQL port number 5432. Otherwise you may leave this field as is.

Oracle to PostgreSQL Migration: Connect to PostgreSQL

Enter user name and password or leave these fields blank for anonymous connection. To use an existing PostgreSQL database, specify it on this page.

Step 4 of 7. Select Destination

On this wizard screen the destination PostgreSQL database name or script file must be specified. Oracle to PostgreSQL converter can process existing database using any of the following options:

Overwrite the entire database
Overwrite existing tables only
Skip existing tables
Merge
Synchronize

Also, you can specify a log file to enable the program write execution traces. If is used to analyze possible error messages and get more control over the conversion process. If no log file is specified, the logging is disabled.

Oracle to PostgreSQL: Select Destination

Step 5 of 7. Conversion Settings

On this wizard screen you can customize Oracle to PostgreSQL migration using the following settings:

Convert table definitions only - select this checkbox to convert only table definitions of Oracle data. All the data will not be migrated
Make names case sensitive - select this checkbox to make table and column names case sensitive. In this mode all table and column names are enclosed in double quotes to preserve upper/lower case. If you don't want to use double quotes around names in queries, keep this option inactive.
Skip converting indexes - select this checkbox if you don't want to convert indexes
NUMBER w/o precision -> bigint - select this checkbox to convert all columns having type NUMBER without precision into BIGINT (by default it is converted into DOUBLE PRECISION based on the range of accepted values)

Oracle to PostgreSQL: Conversion Settings

You can handle schema in the destination database according one of the following options:

Use existing: specify existing schema to import all tables there.
Create new: create new schema to import all tables there.
Full replication - import full list of schemas from the original database. Use this mode to preserve full names of all tables the same as in the original database.

Step 6 of 7. Select Tables

This wizard page is designed to select tables and to compose filtering queries for Oracle to PostgreSQL migration.

To add a table for migration, select it in "Available tables" list and click "Add" button (or double-click the selected item). Use "Add all" button to add all available tables. To remove a table from "Selected tables" list highlight it and click "Remove" button. Use "Remove all" button to remove all items from "Selected tables" list.

Use "Add Query" button for composing SELECT-query to filter data that will be migrated to PostgreSQL. See Using Queries article for the further information.

Oracle to PostgreSQL Migration: Select Tables

Step 7 of 7. Select Views

This wizard page is designed to manage Oracle views for migrating to PostgreSQL.

To add Oracle view for converting it into PostgreSQL format, highlight it in "Available queries" list and click "Add" button (or double-click the selected item). Use "Add all" button to add all Oracle views. To remove an item from "Selected queries" list, highlight it and click "Remove" button (or double-click the selected item). Use "Remove all" button to remove all Oracle views from "Selected queries" list.

Sometimes it is important to convert views into PostgreSQL format according to the particular order (for example, to preserve dependencies). Use "up" and "down" buttons in right part of the wizard page to organize all queries in the appropriate order.

Oracle to PostgreSQL Migration: Select Views