Oracle to PostgresSQL Migration

PostgreSQL is one of the most powerful and used open source rdbms. Performed a simple testing on migration of a Oracle 12c to Postgres 11.4 running in OEL ( Linux ) platform .


In this post, I would like to share postgres installation steps & migrate a schema from Oracle database to postgres database


1. Install PostgresSQL

1.1. Create a os level postgres account ( postgres:postgres/oinstall )


1.2 Download postgresql-11.4.tar.gz binary from postgres download site /engg site & run the below commands


tar xvf postgresql-11.4.tar
cd postgresql-11.4
./configure
make
make install ( as root)



1.3. Verify installation directory


ls -l /usr/local/pgsql/
[postgres@oelunixserver0101 postgres]$ ls -l /usr/local/pgsql/
total 6
drwxr-xr-x 2 root root 1024 Jul 16 10:39 bin
drwx------ 19 postgres postgres 1024 Jul 16 10:41 data
drwxr-xr-x 6 root root 1024 Jul 16 10:39 include
drwxr-xr-x 4 root root 2048 Jul 16 10:39 lib
drwxr-xr-x 6 root root 1024 Jul 16 10:39 share



1.4.Create postgres data directory ( login as postgres account)
Ensure you have enough space to accomodate the data space.
mkdir /usr/local/pgsql/data



1.5. Initilaise data Dierctory ( login as postgres)
postgres@oelunixserver0101 ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... GB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start



1.6. Verify data directory


ls -l /usr/local/pgsql/data
total 53
drwx------ 7 postgres oinstall 1024 Jul 19 00:21 base
drwx------ 2 postgres oinstall 1024 Jul 19 03:23 global
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_commit_ts
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_dynshmem
-rw------- 1 postgres oinstall 4513 Jul 16 10:41 pg_hba.conf
-rw------- 1 postgres oinstall 1636 Jul 16 10:41 pg_ident.conf
drwx------ 4 postgres oinstall 1024 Jul 19 03:27 pg_logical
drwx------ 4 postgres oinstall 1024 Jul 16 10:41 pg_multixact
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_notify
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_replslot
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_serial
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_snapshots
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_stat
drwx------ 2 postgres oinstall 1024 Jul 19 06:20 pg_stat_tmp
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_subtrans
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_tblspc
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_twophase
-rw------- 1 postgres oinstall 3 Jul 16 10:41 PG_VERSION
drwx------ 3 postgres oinstall 1024 Jul 16 10:41 pg_wal
drwx------ 2 postgres oinstall 1024 Jul 16 10:41 pg_xact
-rw------- 1 postgres oinstall 88 Jul 16 10:41 postgresql.auto.conf
-rw------- 1 postgres oinstall 23874 Jul 16 10:41 postgresql.conf
-rw------- 1 postgres oinstall 59 Jul 16 10:41 postmaster.opts
-rw------- 1 postgres oinstall 89 Jul 16 10:41 postmaster.pid

1.7. start postgres database
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1
7. create a database
[postgres@oelunixserver0101 ~]$ /usr/local/pgsql/bin/createdb test

1.8. connect to test database and list all the databases
[postgres@oelunixserver0101 ~]$ /usr/local/pgsql/bin/psql test
psql (11.4)
Type "help" for help.

test=#
test=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

2. Migrate Oracle Install PostgressSQL using ora2pg tool
2.1 Pre-requiste ( install required binaries)
Download and install the following binaries
-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
installation steps
tar xvf <file>
cd <main_folder>
perl Makefile.Pl
make
make install ( root)
Verify ora2pg version
[postgres@oelunixserver0101 ~]$ /data/oracle/product/agent12c/core/12.1.0.5.0/perl/bin/ora2pg -version
Ora2Pg v18.1

2.2 Create config ora2pg.conf with the following entries


ORACLE_HOME /data/oracle/product/12.2.0.2
ORACLE_DSN dbi:Oracle:host=172.17.80.138;service_name=postg1;port=1721
ORACLE_USER hr
ORACLE_PWD <password>
EXPORT_SCHEMA 1
SCHEMA HR
OUTPUT output.sql
TYPE TABLE PACKAGE COPY VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TABLESPACE TYPE PARTITION

2.3 export the HR schema data into output.sql
/data/oracle/product/agent12c/core/12.1.0.5.0/perl/bin/ora2pg -c /home/postgres/ora2pg.conf
/data/oracle/product/agent12c/core/12.1.0.5.0/perl/bin/ora2pg -c /home/postgres/ora2pg.conf
[========================>] 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%) - (1 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%) - (2 sec., avg: 89 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.
[postgres@oelunixserver0101 ~]$

2.4 create a HR_database
/usr/local/pgsql/bin/createdb HR_database

2.5 connect to HR database
[postgres@oelunixserver0101 ~]$ /usr/local/pgsql/bin/psql -d HR_database
psql (11.4)
Type "help" for help.
HR_database=# \l
HR_database | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres

HR_database=#


2.6 load the data ( output.sql contains create schema command)
HR_database=# \i output.sql
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE TABLE
COMMENT
COMMENT
COMMENT
COMMENT
ALTER TABLE
CREATE TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
CREATE INDEX
ALTER TABLE
CREATE TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
CREATE INDEX
CREATE INDEX
CREATE INDEX
ALTER TABLE
CREATE TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
CREATE INDEX
...
...
...
...
...
ALTER TABLE
COMMIT

2.7 List the tables

HR_database=# \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)

2.8 query data
HR_database=# select * from hr.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
CN | China | 3
DE | Germany | 1
DK | Denmark | 1
EG | Egypt | 4
FR | France | 1
HK | HongKong | 3
IL | Israel | 4
IN | India | 3
IT | Italy | 1
JP | Japan | 3
KW | Kuwait | 4
MX | Mexico | 2
NG | Nigeria | 4
NL | Netherlands | 1
SG | Singapore | 3
UK | United Kingdom | 1
US | United States of America | 2
ZM | Zambia | 4
ZW | Zimbabwe | 4
(25 rows)

HR_database=#

2.9. create HR_schema ( just testing purpose


HR_database=# create schema HR_schema
SCHEMA


2.10 List the schemas


HR_database=# \dn
List of schemas
Name | Owner
-----------+----------
hr | hr
hr_schema | postgres
public | postgres
(3 rows)

HR_database=#