Migrating data from Oracle to PostgreSQL database Copy

Database Migration is the process of moving data from one database to another database. This process also involves moving database objects i.e index, view, sequences from one platform to another, and making application changes. Data in the old database system is transferred to a new database system using tools by analyzing patterns. Mapping patterns contain data extracting and loading activities. The design acts as a translator between the formats of data in an old and new database system.

Data migration is needed to perform many firms because the database becomes obsolete after some. Since many features, functionality, and support get deprecated with a new release. Nowadays, many companies are switching to PostgreSQL. It the most popular advanced database system in the world right now. There are many benefits of using PostgreSQL, some of them are as listed below:

  • Cost-effective
  • Continuous support
  • Functionality front
  • Scalability
  • Compatibility
  • Automation
  • Seamless deliveries

Data migration is a tedious task that would require a lot of human resources to complete the activity manually. Hence, the task is done by using automated tools. Programmatic data migration comprises phrases like extract data from the old system, load data to the new system, and data verification to ensure if data is migrated accurately.

Because of the complexity of the typical migration process and the divergence of information systems today there is no elaborated methodology for data migration. Below are the major steps and phases needed to migrate databases from Oracle to PostgreSQL.

  1. Preliminary Check
  1. Schema Migration
  1. Mapping data types
  1. Data Migration
  1. Functional Testing
  1. Performance Testing

1.Preliminary Check

The migration from Oracle to PostgreSQL is a costly and time-consuming task. So, it is important to know which part is to migrate. Below are some things to consider.

  • Do not waste time on migrating objects that are no longer required.
  • Check for historic data
  • Do not replicate data that is not required for example backup data and temporary table from past maintenance.

2. Schema migration

A schema is also known as a “User” in Oracle and has the same name as the user. The default is for each Oracle user to have their own schema. In PostgreSQL, these are not the same, and if you do not explicitly specify a schema new objects will go to a public schema by default. There are several migration tools available that can helps automate schema conversion Some of the popular tools are as follows:

  • Ora2pg tool
  • Ora_migrator
  • Orafce
  • Full convert
  • EDB Migration Portal

When preparing for schema conversion we need to pay special attention to the following differences between Oracle and PostgreSQL.

• Columns

• Constraints

• Identifiers

• Indexes

• Partitions

• Tablespaces

• Data types

3. Mapping data types

Data types in PostgreSQL have some differences when compared to Oracle data types. So conversions are required for some data-types. Below is a comparison table that can be used for reference:

Along with data types, there are other technical differences between Oracle and Postgres that we should know. Some of the important differences you need to account for during the migration process are:

  • Empty Strings and NULL: In PostgreSQL, the IS NULL operator yields a FALSE when used on an empty string, whereas Oracle produces TRUE
  • Dual Table: Oracle databases require a FROM clause for each SELECT statement, so a DUAL table is used for such statements. In PostgreSQL, on the other hand, FROM clauses (and, by extension, DUAL tables) are not required.
  • DELETE Statement: DELETE statements in PostgreSQL only work with a FROM clause, unlike in Oracle.
  • SUBSTR: This function yields different results in Oracle and PostgreSQL which can potentially lead to issues.
  • SYSDATE: PostgreSQL doesn’t have an equivalent to Oracle’s SYSDATE function which provides date and time on execution.
  • Hierarchical queries: Postgres does not support hierarchical query syntax like START WITH, CONNECT BY i. Postgres instead uses WITH RECURSIVE.

4. Data Migration

Data migration is a process of selecting, preparing, extracting, transforming, and applying the data from one database to another database. This includes migration of Data, Sequences, Speeding up, CSV dump, Cross-database replication, etc.

5. Functional Testing

It is done after schema migration. A recommended approach for functional testing is to load some sample data into PostgreSQL from a source database development or testing environment where there’s production sample data and then set up an application connection using appropriate data access (drivers). After the application has connected to the database, allow it to do full functional testing on the converted objects with DMLs. Following are some scenarios that must be tested to ensure smooth performance.

  • Make sure all objects are correctly converted.
  • Check whether all the DMLS are working properly.
  • Load some sample data in both databases to check the results. The SQL output from both databases should be identical.
  • Assess the DML performance and work on it if necessary.

6. Performance Testing

Performance testing is important in the migration phase because some of the Oracle built-in transactions or features functionality might be slightly different in PostgreSQL and the application might see some difference. In this phase, we can capture all those differences and fix them at the application, data access(drivers), and database level with proper tuning.

I am the Author

Share on facebook
Share on twitter
Share on linkedin

Related Articles

Recent Posts

Like us on Facebook


This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor

This is the heading

Lorem ipsum dolor sit amet consectetur adipiscing elit dolor