
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 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}’