Data Instantiation for PostgreSQL (step-by-step guide)

Owned by Miroslav Sivon (Unlicensed)
Last updated: Jun 26, 2018 by Jakub Sejba
5 min read

We recommend ORA2PG - http://ora2pg.darold.net/ - for data instantiation from Oracle database to Postgres. There are several other tools for this purpose. However, those aren't free of charge. You can find them at https://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle. This tool can be used on Unix based OS or Windows.

Pre-requisites

Prerequisites and installation are similar for all operating systems, but the syntax is different. In this article we will install ORA2PG on Centos 7. The following steps are created based on ORA2PG manual - http://ora2pg.darold.net/documentation.html#installation. On the bottom of this page, you can find a link for Windows installation steps.

Before starting the installation, ensure you have the following components ready:

  • Download and install perl on your server.

    [root@target11g data]# yum install perl-DBD-Pg perl perl-devel  perl-DBI  perl-CPAN
  • Download and install Oracle instant client or Oracle database server on your server from www.oracle.com/downloads.

https://oracle-base.com/articles/misc/oracle-instant-client-installation

  • PostgreSQL server or PostgreSQL client is pre-installed on your server. Else download it using yum (yum install postgresql-server or yum install postgresql) or from "https://www.postgresql.org/download/linux/redhat/" if you want use specific version. 

    [root@target11g data]# yum install postgresql

ORA2PG installation

  1. Connect to the server as root.

  2. Download and install ora2pg:

    [root@target11g ~]# mkdir download; cd download
[root@target11g ~]# wget https://sourceforge.net/projects/ora2pg/files/18.2/ora2pg-18.2.tar.bz2
[root@target11g ~]# bzip2 -d ora2pg*.bz2
[root@target11g ~]# tar xvf ora2pg*
[root@target11g ~]# cd ora2pg*
[root@target11g ~]# perl Makefile.PL
[root@target11g ~]# make && make install
[root@target11g ~]# cd ../..; rm -rf download

  3. Install DBD::Oracle (if not already installed)

    [root@target11g ~]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1/
[root@target11g ~]# export LD_LIBRARY_PATH =/u01/app/oracle/product/11.2.0/dbhome_1/lib
[root@target11g ~]# perl -MCPAN -e shell
cpan> get DBD::Oracle
cpan> quit
[root@target11g ~]# cd ~/.cpan/build/DBD-Oracle*
[root@target11g ~]# perl Makefile.PL
[root@target11g ~]# make
[root@target11g ~]# make install

  4. Create the default configuration file and grant the privileges to the non-root user

    [root@target11g ~]# cp /etc/ora2pg/ora2pg.conf* /etc/ora2pg/ora2pg.conf
[root@target11g ~]# chown oracle:oinstall /etc/ora2pg/ora2pg.conf
  5. Disconnect from the root user.

If you are using CPAN for the first time, the wizard will ask you for lots of questions. In general, you can enter default settings. You need to be connected to the internet.

Steps above are just an example how to do the installation. They may differ a little bit in your case. For example you might want to preserve the download directory.

ORA2PG configuration

ORA2PG could be managed from a configuration file or a command line. The command line input is optional. Keep in mind, that If you specify any configuration variable when using command line it will take precedence over variables in configuration file.

You can either edit the default configuration file /etc/ora2pg/ora2pg.conf (the one you have created during the installation phase) or you can create your own file. The default file /etc/ora2pg/ora2pg.conf will be used by ora2pg unless you specify a different one.

For Dbvisit Replicate keep the default values in your config file except for the following changes:

  1. Find the following four Oracle related parameters and change them accordingly:

    ORACLE_HOME     /u01/app/oracle/product/12.1/dbhome_1
ORACLE_DSN      dbi:Oracle:host=localhost;sid=sd8eOM9L;port=1521
ORACLE_USER     system
ORACLE_PWD      oracle

  2. Find the following parameters and set them as descibed:

    TYPE TABLE VIEW GRANT TABLESPACE TYPE PARTITION
DISABLE_SEQUENCE 1
DISABLE_TRIGGERS USER 
TRUNCATE_TABLE 1
DROP_FKEY 1
  3. Set parameter OUTPUT to the desired value.
  4. Set parameter SCHEMA to the source DB schema(s)
  5. Set parameter PG_SCHEMA to the target schema name

  6. Add the following line to the configuration file:

    ORA_INITIAL_COMMAND commit
  7. connect back to non-root OS user

Copying the data from Oracle to Postgres

To copy the data from Oracle to Postgres you need to follow these steps: 

  1. Export tables DDL from Oracle
  2. Import DDL to Postgres
  3. Copy the data

1. Export DDL from Oracle

You can skip this step if you already have the table in your Postgres database.


[oracle@target11g ~]# $ ora2pg -d
Ora2Pg version: 18.2
Trying to connect to database: dbi:Oracle:host=localhost;sid=YGPCBm6y;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DEBUG: executing initial command to Oracle: commit
Looking forward functions declaration in schema SCOTT.
Retrieving table information...
[1] Scanning table ALL_DATATYPES (0 rows)...
[2] Scanning table ALL_DATATYPES_2 (1 rows)...
[3] Scanning table BONUS (0 rows)...
[4] Scanning table CDCLONGNAMES (0 rows)...
[5] Scanning table DEPT (4 rows)...
[6] Scanning table EMP (14 rows)...
[7] Scanning table LOB1 (0 rows)...
[8] Scanning table SALGRADE (5 rows)...
[9] Scanning table invalid table name (0 rows)...
Retrieving views information...
Retrieving users/roles/grants information...
Retrieving tablespaces information...
.......
...................................
Retrieving user defined types information...
Retrieving partitions information...


Dumping table SALGRADE...
Dumping table invalid table name...
Dumping table CDCLONGNAMES...
Dumping table EMP...
Dumping table ALL_DATATYPES...
Dumping table DEPT...
Dumping table ALL_DATATYPES_2...
Dumping table BONUS...
Dumping table LOB1...
Dumping RI EMP...
Add views definition...
Add users/roles/grants privileges...
Add tablespaces definition...
Add custom types definition...
Add partitions definition...

In the previous example ora2pg uses the default configuration file /etc/ora2pg/ora2pg.conf.
Also notice that ora2pg only exports DDL for the schema SCOTT as we defined this schema within the configuration file.

2. Import DDL to Postgres

You can skip this step if you already have the table in your Postgres database.

Run psql to execute DDL commands dumped into the output file in the previous step.

psql -U dbvrep -d postgres < output.sql

If you didn't set parameter PG_SCHEMA and you don't have the default schema set in Postgres, the tables will be created inside Public schema.

For the parameter -U use the same user you are using to apply the changes to Dbvisit Replicate (check the username according to the parameter "show APPLY.APPLY_USER" in the dbvrep console). This way you will avoid the permission issues.

3. Copy the data

  1. Pause APPLY process

  2. Prepare the table you want to replicate:

    dbvrep> prepare table SCOTT.EMP
Prepare enabled supplemental logging, waiting for SCN advance enough to prevent ORA-01466 during consistent export....Waited 1 seconds until scn_to_timestamp changed.
Connecting to running apply [scott.emp]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).]
Connecting to running mine [SCOTT.EMP]: [Table prepared (1 internal records).]
Table SCOTT.EMP instantiated at SCN 3601476

  3. Add the following line to your ora2pg configuration file. Use the SCN number you got as a prepare command output

    ORA_INITIAL_COMMAND call dbms_flashback.enable_at_system_change_number(3601476)
  4. Copy the data:

You can either export the Oracle data to the file and import it to Postgres later, or you can send the exported data directly to Postgres. To send the data directly to Postgres find the following three Postgres related parameters in the control file and change them accordingly (use the same postgres user as the one you used in step 2. Import DDL to Postgres)

PG_DSN  dbi:Pg:dbname=postgres;host=localhost;port=5432
PG_USER dbvrep
PG_PWD  dbvpasswd

Run ora2pg to export/import the table data:

  • use -t COPY to instruct ora2pg to only copy the data
  • use -a table1,table2,...,tableN to specify which tables are to be imported (do not use spaces before or after commas)
[oracle@target11g ~]# ora2pg -d -t COPY -a EMP,DEPT
Ora2Pg version: 18.2
Trying to connect to database: dbi:Oracle:host=localhost;sid=YGPCBm6y;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DEBUG: executing initial command to Oracle: commit
DEBUG: executing initial command to Oracle: call dbms_flashback.enable_at_system_change_number(3601476)
Retrieving table information...
[1] Scanning table DEPT (4 rows)...
[2] Scanning table EMP (14 rows)...
Trying to connect to database: dbi:Oracle:host=localhost;sid=YGPCBm6y;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DEBUG: executing initial command to Oracle: commit
DEBUG: executing initial command to Oracle: call dbms_flashback.enable_at_system_change_number(3601476)
Retrieving partitions information...


Dropping foreign keys of table DEPT...
Dropping foreign keys of table EMP...
NOTICE:  constraint "fk_deptno" of relation "emp" does not exist, skipping
Truncating table DEPT...
Looking how to retrieve data from DEPT...
Fetching all data from DEPT tuples...
Dumping data from table DEPT into PostgreSQL...
Setting client_encoding to UTF8...
Disabling synchronous commit when writing to PostgreSQL...
DEBUG: Formatting bulk of 10000 data for PostgreSQL.
DEBUG: Creating output for 10000 tuples
DEBUG: Sending COPY bulk output directly to PostgreSQL backend
Extracted records from table DEPT: total_records = 4 (avg: 4 recs/sec)

[=====>                   ]  4/18 total rows (22.2%) - (0 sec., avg: 4 recs/sec).
Truncating table EMP...
Looking how to retrieve data from EMP...
Fetching all data from EMP tuples...
Dumping data from table EMP into PostgreSQL...
Setting client_encoding to UTF8...
Disabling synchronous commit when writing to PostgreSQL...
DEBUG: Formatting bulk of 10000 data for PostgreSQL.
DEBUG: Creating output for 10000 tuples
DEBUG: Sending COPY bulk output directly to PostgreSQL backend
Extracted records from table EMP: total_records = 14 (avg: 14 recs/sec)

[========================>] 18/18 total rows (100.0%) - (1 sec., avg: 18 recs/sec).
Restoring foreign keys of table DEPT...
Restoring foreign keys of table EMP...

When finished resume the apply process again.

Final steps

  • Disable triggers
  • Drop delete cascade constraints.
  • Disable deffered constraints

Related articles