By Mouhamadou Diaw
PostgreSQL is one of the most used Rdbms.
In this blog we are going to talk about migrating from oracle to postgresql using ora2pg. A previous blog about this topic can be found here.
After installing ora2pg tool, we will see how to configure and to run it to migrate our data. We are using an oracle 12.1 database and a postgresql 9.6.2. The server is runing on OEL 7.2
The oracle database and the postgresql server are running on the same server. But different servers can be used.
To install ora2pg we need following:
-DBD-Oracle-1.75_2.tar.gz : Oracle database driver for the DBI module
-DBD-Pg-3.6.0.tar.gz : PostgreSQL database driver for the DBI module
-DBI-1.636.tar.gz : Database independent interface for Perl
-ora2pg-18.1.tar.gz : ora2pg archive
The DBI modules can be found here
And the ora2pg can be downloaded from here
Install DBI module
[root@serveroracle postgres]#tar xvzf DBI-1.636.tar.gz
[root@serveroracle postgres]#cd DBI-1.636
[root@serveroracle postgres]#perl Makefile.Pl
[root@serveroracle postgres]#make
[root@serveroracle postgres]#make install
Install DBD-Oracle
[root@serveroracle postgres]# tar xvzf DBD-Oracle-1.75_2.tar.gz
[root@serveroracle postgres]# cd DBD-Oracle-1.75_2/
[root@serveroracle DBD-Oracle-1.75_2]# export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
[root@serveroracle DBD-Oracle-1.75_2]# export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib
[root@serveroracle DBD-Oracle-1.75_2]# perl Makefile.PL
[root@serveroracle DBD-Oracle-1.75_2]# make
[root@serveroracle DBD-Oracle-1.75_2]# make install
Install DBD-Pg
[root@serveroracle postgres]# tar xvzf DBD-Pg-3.6.0.tar.gz
[root@serveroracle postgres]# cd DBD-Pg-3.6.0
[root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL
Configuring DBD::Pg 3.6.0
Path to pg_config? /u01/app/postgres/product/96/db_2/bin/pg_config
[root@serveroracle DBD-Pg-3.6.0]# make
[root@serveroracle DBD-Pg-3.6.0]# make install
When we first run the command perl MakeFile.PL, we got following errors
[root@serveroracle DBD-Pg-3.6.0]# perl Makefile.PL
Configuring DBD::Pg 3.6.0
PostgreSQL version: 90602 (default port: 5432)
POSTGRES_HOME: /u01/app/postgres/product/96/db_2
POSTGRES_INCLUDE: /u01/app/postgres/product/96/db_2/include
POSTGRES_LIB: /u01/app/postgres/product/96/db_2/lib
OS: linux
Warning: prerequisite version 0 not found.
Could not eval '
package ExtUtils::MakeMaker::_version;
no strict;
BEGIN { eval {
# Ensure any version() routine which might have leaked
# into this package has been deleted. Interferes with
# version->import()
undef *version;
require version;
"version"->import;
} }
local $VERSION;
$VERSION=undef;
do {
use version; our $VERSION = qv('3.6.0');
};
$VERSION;
' in Pg.pm: Can't locate version.pm in @INC (@INC contains: t/lib /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at (eval 11) line 16, line 19.
BEGIN failed--compilation aborted at (eval 11) line 16, line 19.
WARNING: Setting VERSION via file 'Pg.pm' failed
at /usr/share/perl5/vendor_perl/ExtUtils/MakeMaker.pm line 619.
Using DBI 1.636 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/local/lib64/perl5/auto/DBI/
Writing Makefile for DBD::Pg
We correct errors by running the command below.
[root@serveroracle DBD-Pg-3.6.0]# yum -y install 'perl(version)'
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package perl-version.x86_64 3:0.99.07-2.el7 will be installed
--> Finished Dependency Resolution
Install ora2pg
[postgres@serveroracle ~]$ tar xvzf ora2pg-18.1.tar.gz
[root@serveroracle postgres]# cd ora2pg-18.1/
[root@serveroracle postgres]# perl Makefile.PL
[root@serveroracle postgres]# make
[root@serveroracle postgres]# make install
[root@serveroracle postgres]# ora2pg -version
Ora2Pg v18.1
[root@serveroracle postgres]#
Now that ora2pg is installed, we can proceed with the migration. The first step is to configure the ora2pg.conf file. We can do a copy of the default template and then modify the file. In our case the configuration file is located in /etc/ora2pg directory.
[root@serveroracle ora2pg]# cp ora2pg.conf.dist ora2pg.conf
[root@serveroracle ora2pg]# vi ora2pg.conf
In our configuration file, following changes where done. We are exporting only the HR schema
ORACLE_DSN dbi:Oracle:host=serveroracle.localdomain;sid=ORCL
ORACLE_USER system
ORACLE_PWD root
SCHEMA HR
TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION
OUTPUT HR_output.sql
ora2pg works by exporting and importing schemas. We can only export one schema at a time if we use the option SCHEMA. If we want to export all schemas we can just comment the option SCHEMA. In this case all non-oracle users will be extracted.
In the documentation we also have the option SYSUSERS
# Allow to add a comma separated list of system user to exclude from
# from Oracle extraction. Oracle have many of them following the modules
# installed. By default it will suppress all object owned by the following
# system users:
# CTXSYS,DBSNMP,EXFSYS,LBACSYS,MDSYS,MGMT_VIEW,OLAPSYS,ORDDATA,OWBSYS,
# ORDPLUGINS,ORDSYS,OUTLN,SI_INFORMTN_SCHEMA,SYS,SYSMAN,SYSTEM,WK_TEST,
# WKSYS,WKPROXY,WMSYS,XDB,APEX_PUBLIC_USER,DIP,FLOWS_020100,FLOWS_030000,
# FLOWS_040100,FLOWS_FILES,MDDATA,ORACLE_OCM,SPATIAL_CSW_ADMIN_USR,
# SPATIAL_WFS_ADMIN_USR,XS$NULL,PERFSTAT,SQLTXPLAIN,DMSYS,TSMSYS,WKSYS,
# APEX_040200,DVSYS,OJVMSYS,GSMADMIN_INTERNAL,APPQOSSYS
# Other list of users set to this directive will be added to this list.
#SYSUSERS OE,HR
Once configuration done, we can run the orap2g command. Note that you can see all options by running ora2pg -help
[root@serveroracle ora2pg]# ora2pg
[========================>] 7/7 tables (100.0%) end of scanning.
[> ] 0/7 tables (0.0%) end of scanning.
[========================>] 7/7 tables (100.0%) end of table export.
[========================>] 0/0 packages (100.0%) end of output.
[========================>] 25/25 rows (100.0%) Table COUNTRIES (25 recs/sec)
[==> ] 25/215 total rows (11.6%) - (0 sec., avg: 25 recs/sec).
[========================>] 27/27 rows (100.0%) Table DEPARTMENTS (27 recs/sec)
[=====> ] 52/215 total rows (24.2%) - (1 sec., avg: 52 recs/sec).
[========================>] 107/107 rows (100.0%) Table EMPLOYEES (107 recs/sec)
[=================> ] 159/215 total rows (74.0%) - (1 sec., avg: 159 recs/sec).
[========================>] 19/19 rows (100.0%) Table JOBS (19 recs/sec)
[===================> ] 178/215 total rows (82.8%) - (1 sec., avg: 178 recs/sec).
[========================>] 10/10 rows (100.0%) Table JOB_HISTORY (10 recs/sec)
[====================> ] 188/215 total rows (87.4%) - (2 sec., avg: 94 recs/sec).
[========================>] 23/23 rows (100.0%) Table LOCATIONS (23 recs/sec)
[=======================> ] 211/215 total rows (98.1%) - (2 sec., avg: 105 recs/sec).
[========================>] 4/4 rows (100.0%) Table REGIONS (4 recs/sec)
[========================>] 215/215 total rows (100.0%) - (3 sec., avg: 71 recs/sec).
[========================>] 215/215 rows (100.0%) on total estimated data (3 sec., avg: 71 recs/sec)
[========================>] 1/1 views (100.0%) end of output.
[========================>] 3/3 sequences (100.0%) end of output.
[========================>] 1/1 triggers (100.0%) end of output.
[========================>] 0/0 functions (100.0%) end of output.
[========================>] 2/2 procedures (100.0%) end of output.
[========================>] 0/0 types (100.0%) end of output.
[========================>] 0/0 partitions (100.0%) end of output.
[root@serveroracle ora2pg]#
Once finished, a file HR_output.sql is generated. This file can be used to load data in the postgresql database. We can also load data directly without using a file. We just have to specify the connection info for the postgresql.
Let’s know prepare the postgresql server to receive our data. First we create the user HR.
[postgres@serveroracle ~]$ psql
psql (9.6.2 dbi services build)
Type "help" for help.
postgres=# c orclpg
You are now connected to database "orclpg" as user "postgres".
orclpg=# create user HR WITH PASSWORD 'root';
And then we can execute the file. The first time we ran the file, we had some constraints errors
orclpg=# i HR_output.sql
ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk"
DETAIL: Key (region_id)=(2) is not present in table "regions".
STATEMENT: COPY countries (country_id,country_name,region_id) FROM STDIN;
psql:HR_output.sql:224: ERROR: insert or update on table "countries" violates foreign key constraint "countr_reg_fk"
DETAIL: Key (region_id)=(2) is not present in table "regions".
orclpg=#
To correct this, we put the option in the configuration file DROP_FKEY to 1
DROP_FKEY 1
With this option all foreign keys will be dropped before all data import and recreate them at the end of the import. After the load was successful.
orclpg=# i HR_output.sql
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE TABLE
COMMENT
SET
SET
SET
SET
SET
BEGIN
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
SET
COPY 107
SET
COPY 19
SET
COPY 10
SET
COPY 23
SET
COPY 4
ALTER TABLE
ALTER TABLE
ALTER TABLE
…
…
COMMIT
We can verify that tables were created and that data were inserted.
orclpg=# d
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------
hr | countries | table | postgres
hr | departments | table | postgres
hr | employees | table | postgres
hr | job_history | table | postgres
hr | jobs | table | postgres
hr | locations | table | postgres
hr | regions | table | postgres
(7 rows)
orclpg=# select count(*) from countries;
count
-------
25
(1 row)
orclpg=#
Conclusion
As we can see ora2pg is a free easy tool to migrate data from oracle to postgresql. In coming blog we will talk about other tools that can be used to move data from oracle to postgresql
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/12/oracle-square.png)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
thomas
11.12.2023Hello Mouhamadou,
we have migrated our first DBs from ORACLE to PostgreSQL using ORA2PG, all on Windows-server. Data, we migrated by creating INSERT-scripts.
Doing it this way, we observe that we need 3 times more diskspace for the PostgreSQL-DB than for the ORACLE-DB.
Any idea why ?
Regards,
Thomas