Validating Oracle to PostgreSQL Migration

 

How to Migrate Your Oracle Database to PostgreSQL | by Shafiqa Iqbal |  Towards Data Science

With growing popularity of database migration from Oracle to PostgreSQL, the control over migration process becomes even more important to avoid data loss or breaking the database logic. To reduce risk of errors or data corruption during the migration, many database administrators use special software to automate the procedure like this one: 

https://www.convert-in.com/ora2pgs.htm

However, even in this case it is important to make sure that the database has been migrated from Oracle to PostgreSQL properly. This article explains how to check that all database entries have been migrated properly.

First step is to explore what kind of entries must be validated. The list of generic database objects includes table definitions, data, indexes, foreign keys, sequences, views. 

Table Definitions

Oracle allows to get table definition via following statement: DESC table_name

PostgreSQL provide this command for the same purpose: \d table_name

Table definition is migrated correctly when each column has the same properties in both Oracle and PostgreSQL tables. Here is the table of appropriate conversions for each Oracle data type:

OraclePostgreSQL
BFILEVARCHAR(255)
BINARY_FLOATREAL
BINARY_DOUBLEDOUBLE PRECISION
BLOBBYTEA
CHAR(n), CHARACTER(n)CHAR(n), CHARACTER(n)
CLOBTEXT
DATETIMESTAMP
DECIMAL(p,s), DEC(p,s)DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISIONDOUBLE PRECISION
FLOAT(p)DOUBLE PRECISION
INT, INTEGERINT, INTEGER
LONGTEXT
LONG RAWBYTEA
NCHAR(n)CHAR(n)
NCHAR VARYING(n)VARCHAR(n)
NCLOBTEXT
NUMBER(p,0), NUMBER(p), 1 <= p < 5SMALLINT
NUMBER(p,0), NUMBER(p), 5 <= p < 9INT
NUMBER(p,0), NUMBER(p), 9 <= p < 19BIGINT
NUMBER(p,0), NUMBER(p), p >= 19DECIMAL(p)
NUMBER(p,s)DECIMAL(p,s)
NUMBER, NUMBER(*)DOUBLE PRECISION
NUMERIC(p,s)NUMERIC(p,s)
NVARCHAR2(n)VARCHAR(n)
RAW(n)BYTEA
REALDOUBLE PRECISION
ROWIDCHAR(10)
SMALLINTSMALLINT
TIMESTAMP(p)TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONETIMESTAMP(p) WITH TIME ZONE
VARCHAR(n)VARCHAR(n)
VARCHAR2(n)VARCHAR(n)
XMLTYPEXML

 

Data

Validation of migrated data must be started from verifying that Oracle and PostgreSQL tables have equal number of rows. Both database management systems extract rows count through the query: 

SELECT COUNT(*) FROM the_tab_name

Indexes

Verification of indexes migrated from Oracle to PostgreSQL includes check of total number of indexes per table, indexed columns and attributes for each index. To list all indexes in Oracle table, use this query:

SELECT * FROM all_indexes WHERE table_name = ‘the table name’

PostgreSQL exposes description of indexes at the bottom of table definition produced by the statement: \d the_tab_name

Foreign Kyes

Foreign keys validation is handled similar to indexes. Oracle provides this query to extract necessary information about foreign keys:

SELECT a.table_name,a.constraint_name,a.delete_rule,b.column_name FROM user_constraints a, user_cons_columns b WHERE a.constraint_name=b.constraint_name and a.constraint_type=’R’

PostgreSQL exracts foreign keys data from internal table “information_schema” as follows: 

SELECT

    tc.constraint_name, tc.table_name, kcu.column_name, 

    ccu.table_name AS foreign_table_name,

    ccu.column_name AS foreign_column_name 

FROM 

    information_schema.table_constraints AS tc 

    JOIN information_schema.key_column_usage AS kcu

      ON tc.constraint_name = kcu.constraint_name

    JOIN information_schema.constraint_column_usage AS ccu

      ON ccu.constraint_name = tc.constraint_name

WHERE constraint_type = ‘FOREIGN KEY’ AND tc.table_name=’table_name’;

 

Views

Validation of views migrated from Oracle to PostgreSQL requires to compare each SELECT-statement with respect to differences between SQL dialects of the source and destination database management systems. Both Oracle and PostgreSQL list available views through the following query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

Oracle allows to explore each view using the following query:

SELECT text FROM all_views WHERE view_name='{name of the view}’

In PostgreSQL the following query can be used for the same:

SELECT definition FROM pg_views WHERE viewname = ‘{name of the view}’

John Thompson

Peter Thompson: Peter, a futurist and tech commentator, writes about emerging technology trends and their potential impacts on society.