Migrating an Oracle Schema to PostgreSQL with Partitioning Table(s) in an On-Premises Data Center

by KarlaS | Jul 15, 2020 | Best Practices, open source, Oracle, PostgreSQL

Many companies are looking for open source database solutions to help shrink the IT budget by minimizing or avoiding support costs. One such solution would be migrating an Oracle database to PostgreSQL. PostgreSQL is a widely used open source RDBMS, and the PostgreSQL database model is very similar to Oracle. All PostgreSQL features are available at no cost or minimal cost. Also PostgreSQL professionals are less expensive to hire than Oracle DBAs. 

However, migrating an Oracle database to PostgreSQL is not a straightforward task in an on-premises data center. In particular, it is challenging to migrate an entire Oracle schema with partitioning tables. This technical blog shares a simple method to migrate the Oracle schema to a PostgreSQL database using open source tools. It does not cover installation and configuration of PostgreSQL.

Caution: Every environment is different, so test this in a non-production environment before working with production.

Versions used: Oracle 12.2.0.1, PostgreSQL 12.3
Operating System: CentOS Linux 8.
Tools and utilities used: SQLines Data, SQLine Converter, and PGAdmin 4.x

Pre-migration considerations:

1. There are quite a few data type differences between Oracle and PostgreSQL. Commonly used types (Oracle –> PostgreSQL) include: VARCHAR2–>VARCHAR, CHAR –>CHAR, CLOB –>TEXT, BLOB RAW \–>BYTEA (1 GB limit) or Large object, DATE–>TIMESTAMP(0), TIMESTAMP WITH LOCAL TIME ZONE –> TIMESTAMPTZ, NUMBER–> NUMERIC, NUMBER(4) –>SMALLINT, NUMBER(9) –> INT, NUMBER(18) –> BIGINT.
2. No package stored procedure is available in PostgreSQL. Consider converting packages into procedures and functions. There are several differences in built-in functions between Oracle and PostgreSQL. Please find the equivalent function in PostgreSQL user manual.

How To Migrate Oracle To PostgreSQL

I have used the SQLines Data open source tool to do this migration. Once you create all required users (roles) and tablespaces and privileges in PostgreSQL, do the initial load using SQLines Data and exclude all partition tables.

 Synatax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -texcl=partitiontables -smap=<Oracleschema>:<PostgresSchema> 

Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -t=payroll.* –texcl=employee_pay_tab -smap=payroll:payroll  

Truncate all PostgreSQL tables (* truncates only tables in PostgreSQL). 

Get the partition table creation script without partitions from the table DDL and change the data types as appropriate to PostgreSQL.

Example: 

CREATE TABLE payroll.employee_pay_tab (

Pay_period_id numeric(6),

Pay_period  text COLLATE pg_catalog.”default”,

Emp_id numeric(6),

Base_sal numeric(10,2),

fed_tax  numeric(10,2),

state_tax  numeric(10,2),

medicare  numeric(10,2),

ssn_ded numeric(10,2),

ret_ded numeric(10,2),

tot_pay numeric(10,2)

CONSTRAINT XPK_empployee_pay_tab PRIMARY KEY (Pay_period_id,Emp_id) USING INDEX TABLESPACE PAY_INDEX)

PARTITION BY LIST (Pay_period_id);

ALTER TABLE payroll.empployee_pay_tab  OWNER to payroll;

ALTER TABLE payroll.empployee_pay_tab  SET TABLESPACE PAY_DATA;

CREATE TABLE payroll.empployee_pay_tab_P_TEMP PARTITION OF payroll.empployee_pay_tab DEFAULT TABLESPACE PAY_DATA;

Get a partition creation script from the Oracle partition table from the database.

set linesize 150

set pages 0

set feedback on

set serveroutput on buffer 999999999;

BEGIN

    FOR r IN (select ‘CREATE TABLE ‘||table_owner||’.’|| partition_name ||’ PARTITION OF ‘||table_owner||’.’||TABLE_NAME || ‘ FOR VALUES IN (‘ as col1 , HIGH_VALUE  , ‘) TABLESPACE PAY_DATA;’ as col2 from dba_tab_partitions where table_owner = ‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ order by 1) LOOP

        IF r.HIGH_VALUE != ‘-1’ THEN

           DBMS_OUTPUT.PUT_LINE(

              r.col1|| r.HIGH_VALUE || r.col2

              );

        END IF;

     END LOOP;

  END;

 /

Get a partition index creation script from the Oracle partition table from the database.

set pages 0

set linesize 150

select ‘CREATE INDEX ‘ ||index_name||’ ON payroll.employee_pay_tab (‘||column_name||’) TABLESPACE CE_INDEX;’  from  (select  index_name ,   listagg (column_name, ‘,’) WITHIN GROUP (ORDER BY column_name) column_name FROM dba_ind_columns  where table_owner=‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ GROUP BY  index_name order by index_name)
;

Create the partition table, partitions and indexes from above scripts in PostgreSQL. Then load the Oracle schema into PostgreSQL, including partition tables, using the SQLine Data tool.

Syntax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -smap=<Oracleschema>:<PostgresSchema> 

Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -smap=payroll:payroll  -topt=truncate

Verify data and create functions, procedures, views and sequences. Get the Sequences scripts from Oracle:

set linesize 150

select ‘CREATE SEQUENCE ‘||SEQUENCE_NAME||’ MINVALUE ‘||MIN_VALUE||’ MAXVALUE ‘||MAX_VALUE||’ INCREMENT BY ‘||INCREMENT_BY||’ START WITH ‘||LAST_NUMBER||’;’  from dba_sequences where SEQUENCE_OWNER=’PAYROLL’;

Get the views, procedure and packages using an Oracle DDL generation command. Use the SQLine converter to convert Oracle code into PostgreSQL. Then get table and column comments scripts from Oracle and run on PostgreSQL.

set heading off

set linesize 300

set feedback off

set verify off

set pages 100

col table_name format a30

col column_name format a30

spool  tab_col_comments.sql

select ‘COMMENT ON COLUMN ‘||table_name||’.’||column_name||’  IS ”’||comments||”’;’ from DBA_COL_COMMENTS where owner=‘PAYROLL’ and comments is not null;

select ‘COMMENT ON TABLE ‘||table_name||’ IS ”’||comments||”’;’ 

 from DBA_TAB_COMMENTS   where owner=‘PAYROLL’ and comments is not null;

spool off

Now run tab_col_comments.sql in PostgreSQL.

Finally, test your application in a non-production environment.

If you’re thinking of migrating your existing database to an open source platform, contact Buda Consulting for a free consultation on best practices and “lessons learned.”