Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will tell you How To Migrate Data From Oracle To PostgreSQL Using Ora2pg.
Before migrating data, I will explain ora2pg installation.
Ora2pg
It is a tool used to switch from Oracle database to Postgresql.
First of all, we will install Oracle 19C Client on Oracle Linux 7. For this, we need to download the rpm packages required and put them on our server. After installing the rpm packages on our server, we run the following commands.
1 2 3 4 | [root@testdb]# rpm -ivh /tmp/oracle-instantclient19.11-basic-19.11.0.0.0-1.x86_64.rpm [root@testdb]# rpm -ivh /tmp/oracle-instantclient19.11-devel-19.11.0.0.0-1.x86_64.rpm [root@testdb]# rpm -ivh /tmp/oracle-instantclient19.11-jdbc-19.11.0.0.0-1.x86_64.rpm [root@testdb]# rpm -ivh /tmp/oracle-instantclient19.11-sqlplus-19.11.0.0.0-1.x86_64.rpm |
We install the required perl packages.
1 | [root@testdb]# yum install perl-DBD-Pg perl perl-devel perl-DBI perl-CPAN -y |
We download the DBI module required for Perl by following the steps below.
1 2 3 4 5 6 7 8 | [root@testdb]#mkdir install [root@testdb]#cd install [root@testdb install]# wget https://www.cpan.org/modules/by-module/DBI/DBI-1.643.tar.gz [root@testdb install]# tar xvzf DBI-1.643.tar.gz [root@testdb install]# cd DBI-1.643 [root@testdb DBI-1.643]#perl Makefile.PL [root@testdb DBI-1.643]#make [root@testdb DBI-1.643]#make install |
We install the Oracle database driver for the DBI module by following the steps below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | [root@testdb ~]# export ORACLE_HOME=/usr/lib/oracle/19.11/client64/bin [root@testdb ~]# export LD_LIBRARY_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib [root@testdb ~]# export PATH=$PATH:$ORACLE_HOME/bin [root@testdb ~]# perl -MCPAN -e shell . . . . . . Would you like me to configure as much as possible automatically? [yes] . . . . . . cpan[1]> get DBD::Oracle . . . . . . cpan[2]> quit [root@testdb ~]# cd ~/.cpan/build/DBD-Oracle-1.80-D5Y0lj/ [root@testdb DBD-Oracle-1.80-D5Y0lj]# perl Makefile.PL [root@testdb DBD-Oracle-1.80-D5Y0lj]# make [root@testdb DBD-Oracle-1.80-D5Y0lj]# make install |
We install the Postgres database driver for the DBI module. (DBD-Pg)
1 2 3 4 5 6 7 8 | [root@testdb ~]# cd install [root@testdb install]# rm -rf * [root@testdb install]# wget https://cpan.metacpan.org/authors/id/T/TU/TURNSTEP/DBD-Pg-3.14.2.tar.gz [root@testdb install]# tar xvzf DBD-Pg-3.10.0.tar.gz [root@testdb install]# cd DBD-Pg-3.10.0 [root@testdb DBD-Pg-3.14.2]# perl Makefile.PL [root@testdb DBD-Pg-3.14.2]# make [root@testdb DBD-Pg-3.14.2]# make install |
After completing the above steps, we can proceed to the Ora2pg installation. We will follow the steps below to install Ora2pg.
1 2 3 4 5 6 7 8 | [root@testdb ~]# cd install [root@testdb install]# rm -rf * [root@testdb install]# wget https://sourceforge.net/projects/ora2pg/files/21.1/ora2pg-21.1.tar.bz2 [root@testdb install]# bzip2 -d ora2pg-20.0.tar.bz2 [root@testdb install]# tar xvf ora2pg-20.0.tar [root@testdb install]# cd ora2pg-20.0 [root@testdb ora2pg-21.0]# perl Makefile.PL [root@testdb ora2pg-21.0]# make && make install |
After the installation is complete, we need to make some changes to the ora2pg.conf file. For this, we first backup the ora2pg.conf file and make the changes. For example, since I will carry the HR chart, I will write HR in the SCHEMA section.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | [root@testdb ~]# cd /etc/ora2pg/ [root@testdb ora2pg]# cp ora2pg.conf.dist ora2pg.conf --ora2pg.conf dosyasında gerekli ayarlamalar yapılır. [root@testdb ora2pg]# vi ora2pg.conf ORACLE_HOME /u01/app/oracle/product/19.0.0/dbhome_1 #If you use SID ORACLE_DSN dbi:Oracle:host=testdb.localdomain;sid=orcl;port=1521 #If you use service name ORACLE_DSN dbi:Oracle://localhost:1521/orcl ORACLE_USER <username> ORACLE_PWD <password> TYPE TABLE VIEW GRANT COPY OUTPUT output.sql # Oracle schema/owner to use SCHEMA HR PG_SCHEMA hr # Export Oracle schema to PostgreSQL schema EXPORT_SCHEMA 1 |
We run the following command to see that the installation has completed successfully.
1 2 | [root@testdb ora2pg]# ora2pg -t SHOW_VERSION -c ora2pg.conf Oracle Database 19c Enterprise Edition Release 19.3.0.0 |
Data Migration from Oracle to Postgresql:
After completing the configuration, we create the database and schema that we will transfer in postgres.
1 2 3 4 5 6 7 | -bash-4.2$ psql postgres=# CREATE DATABASE orcl; CREATE DATABASE postgres=# \c orcl You are now connected to database "orcl" as user "postgres". orcl=# CREATE SCHEMA hr; CREATE SCHEMA |
After creating the database and schema, we create the output files to move the data in the Oracle database to the postgres database by running the following command.
1 | [root@testdb ora2pg]# ora2pg -d |
After the process is finished, data is transferred to the Postgres database using output files.
1 2 3 4 | -bash-4.2$ psql -U postgres -d orcl < TABLE_output.sql -bash-4.2$ psql -U postgres -d orcl < GRANT_output.sql -bash-4.2$ psql -U postgres -d orcl < VIEW_output.sql -bash-4.2$ psql -U postgres -d orcl < COPY_output.sql |
After the data migration is complete, we can connect to the orcl database and query the tables in the hr schema.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | -bash-4.2$ psql postgres=# \c orcl orcl=# \dt 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) ****************************** orcl=# select * from countries; country_id | country_name | region_id ------------+--------------------------+----------- AR | Argentina | 2 AU | Australia | 3 BE | Belgium | 1 BR | Brazil | 2 CA | Canada | 2 CH | Switzerland | 1 (6 rows) |
Hi Melek
For practice purpose on my own laptop, can I do this migration without VM? Please suggest.
Yes you can