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:
- 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.
- 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.”
