Database migration from Oracle to Postgresql involves a sequence of steps.These are listed below

1. Schema conversion

The database schema including creation of tables, primary key and other constraints should be converted from oracle to postgresql. This was done using the XML framework. DB2XML was used to export oracle database schema into XML format. This XML file is converted into postgresql DDL using XSL.

2. Partition creation

The next big step is creation of partitions for the tables. PostgreSQL supports partitioning via table inheritance. Each partition must be created as a child table of a single parent table. The parent table itself is normally empty; it exists just to represent the entire data set.Table constraints are addedd to the partition tables to define the allowed key values in each partition

3. Foreign Key constraints

There are 4 cases that needs to be taken care of when enforcing FK constraints

1. Non-partitioned tables referencing Non-Partitioned tables
2. Partioned tables referencing Non-Partitioned tables
3. Non-partioned tables referencing Partioned tables
4. Partitioned tables referencing Partioned tables

In case 1, FK constraints can be enforced using the simple SQL statements, For example,

   ALTER TABLE CODE_SYSTEM ADD CONSTRAINT CODE_SYSTEM_FK_AUTHORITY
   FOREIGN KEY(AUTHORITY_NAME) REFERENCES ASSIGNING_AUTHORITY(AUTHORITY_NAME);

will add FK constraint to the table code_system.

In case 2, since the table is partitioned and each partition is a separate table by itself, FK constraints should be added to each child table.

For case 3 and 4, FK cannot be enforced with just the SQL statement since the master table does not contain any records( and is always empty). So we first create a view that contains the primary key values of the referenced table. Then a function is created that checks to see if the new row that is being inserted/updated contains the value in the view. A trigger is created using this function for each partitions that is fired before inserting/updating a row. This enforces foreign key constraint.

For example, To create FK constraints for table Clinical variable referencing medical_order table, we do the following Create a view first

   CREATE VIEW MEDICAL_ORDER_FK_VIEW AS 

   SELECT MEDICAL_ORDER_ID FROM MED_ORDER_1 UNION

   SELECT MEDICAL_ORDER_ID FROM MED_ORDER_2 UNION 

        ......

   SELECT MEDICAL_ORDER_ID FROM MED_ORDER_109

where MED_ORDER_1,MED_ORDE_2 are the partitions(or child tables)

Next we create a function

   CREATE FUNCTION medical_order_fk_function () RETURNS trigger AS '

   declare

      medical_order_id1 numeric(22);

   begin

      select medical_order_id into medical_order_id1 from medical_order_view where medical_order_id=new.medical_order_id;

      if found then return new;

      else raise exception ''insert or update on table  violates foreign key '';

      end if;

      return null;

   end

   ' LANGUAGE 'plpgsql'

The above function shows only one FK constraint. If there are more than 1 FK constraint ,they can be enforced using the same function. A trigger is created for each partition as follows

   CREATE TRIGGER specimen_1_fk_trigger
   AFTER INSERT OR UPDATE ON specimen_1 FOR EACH ROW
   EXECUTE PROCEDURE medical_order_fk_function();

I will put the link to the original code soon.....

4. The values(rows) are inserted from Oracle tables to postgresql tables using jdbc.

5. Index creation

Im still working on creation of indexes. For non-partitioned tables indexes can be created in the usual way using the SQL query

CREATE INDEX .....

The problem arises for indexing partitioned tables since in postgresql the partitions can be indexed separately but there is no global index . This means that for each query that don't involve the partitioned column, each partition needs to be checked. This is a performance bottleneck which needs to be sorted out.

The other issues of this database migration are are backup strategy and online mirroring.I will write more about this when I get there !!!!