Validating Oracle to PostgreSQL Migration

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:

Oracle PostgreSQL
BFILE VARCHAR(255)
BINARY_FLOAT REAL
BINARY_DOUBLE DOUBLE PRECISION
BLOB BYTEA
CHAR(n), CHARACTER(n) CHAR(n), CHARACTER(n)
CLOB TEXT
DATE TIMESTAMP
DECIMAL(p,s), DEC(p,s) DECIMAL(p,s), DEC(p,s)
DOUBLE PRECISION DOUBLE PRECISION
FLOAT(p) DOUBLE PRECISION
INT, INTEGER INT, INTEGER
LONG TEXT
LONG RAW BYTEA
NCHAR(n) CHAR(n)
NCHAR VARYING(n) VARCHAR(n)
NCLOB TEXT
NUMBER(p,0), NUMBER(p), 1 <= p < 5 SMALLINT
NUMBER(p,0), NUMBER(p), 5 <= p < 9 INT
NUMBER(p,0), NUMBER(p), 9 <= p < 19 BIGINT
NUMBER(p,0), NUMBER(p), p >= 19 DECIMAL(p)
NUMBER(p,s) DECIMAL(p,s)
NUMBER, NUMBER(*) DOUBLE PRECISION
NUMERIC(p,s) NUMERIC(p,s)
NVARCHAR2(n) VARCHAR(n)
RAW(n) BYTEA
REAL DOUBLE PRECISION
ROWID CHAR(10)
SMALLINT SMALLINT
TIMESTAMP(p) TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE TIMESTAMP(p) WITH TIME ZONE
VARCHAR(n) VARCHAR(n)
VARCHAR2(n) VARCHAR(n)
XMLTYPE XML

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 table_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 = ‘table name’

PostgreSQL exposes description of indexes at the bottom of table definition produced by the statement: \d table_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 view}’

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

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