Select Page

4 Success Tips for Migrating from Oracle to PostgreSQL

Will Fike | | April 25, 2019

Should your organization migrate from Oracle to PostgreSQL? There’s no single right or wrong answer to this question. The right database solution depends on your budget, use cases, and resources.

Oracle is a leading option for enterprise-grade solutions due to its maturity, rich features, and capabilities. However, these capabilities come at a cost which is prohibitive for many small and mid-sized organizations.

For some organizations, a migration from Oracle to an open-source alternative like PostgreSQL is the right choice. According to popularity data from DB Engines Rankings, interest in PostgreSQL has soared nearly 20 percent in the past 12 months.

Should You Migrate from Oracle to PostgreSQL?

Large enterprises with an extensive technology budget or strong support staff resources may not have a strong use case for a PostgreSQL migration. However, if your business is struggling with the costs or maintenance burden of Oracle, you may be a candidate to make the switch. Some of the most common reasons organizations choose to migrate include:

  • PostgreSQL basic version is open-source and free for business use, though some companies choose to partner with a value-added reseller to develop an enterprise-grade solution. PostgreSQL is supported by major cloud computing services such as Amazon web services, Microsoft Azure, and Google cloud.
  • PostgreSQL does not have licensing fees. The solution isn’t strictly free due to support and maintenance costs, but data indicate that the cloud costs of a PostgreSQL database are approximately one-tenth the cost of a similar Oracle solution.
  • PostgreSQL has some differences from Oracle, however, according to tech columnist Matt Asay, the solutions share “all important concepts.”

4 Success Tips for Switching from Oracle to PostgreSQL

If your organization has a strong use case for PostgreSQL adoption, you could realize immediate cost savings or other benefits, such as simpler database operations. While PostgreSQL is open-source and has a strong user community, migrations aren’t always simple. Create a groundwork for success by preparing for the migration with these four tips.

  1. Test Schema Migration

    The right time to test schema migration is prior to your go-live date. Tools like ora2pg can allow you to test Oracle to PostgreSQL server migration and discover any potential issues which can occur during a live migration. Discovering these issues can enable you to make a plan for procedure code which cannot be automatically converted. When helping one client with a complex Oracle migration with several hundred gigabytes of data, Datavail manually converted the views, materialized views, and stored procedure code which did not correctly migrate from Oracle or automatically convert using the ora2pg tool. Datavail  has also successfully utilized tools that include Schema conversion Tool (SCT) and Database Migration services offered by Amazon Web Services for the schema conversion and migration.


  3. Do a Test Export

    After migrating schema using an automated tool, export your Oracle database data and load it into the respective PostgreSQL server tables to uncover issues such as and not limited to incompatibilities between the two database character sets or non-printable values embedded in text data.


  5. Perform a Test Conversion

    Carefully review your stored procedures to identify which stored procedures and features your organization is using are specific to Oracle and could present an issue with the PostgreSQL conversion. During this aspect of the migration preparation process, you should carefully document these discoveries. This will give your organization a realistic idea of how much time and resources to dedicate to manual code migrations and testing.

    If you decide to partner with a vendor for your migration, it can streamline this process. In many cases, such as the instance of the recent Datavail client, the syntatic differences between these two relational database solutions require manual correction. Large or complex statements are likely to require manual testing to avoid issues such as views or stored procedures which fail at runtime.


  7. Convert Code to SQL Standard

    To streamline and automate your conversion, perform work before your go-live date to convert Oracle-specific codes and conventions to SQL standards. Review how your organization approaches date and time issues within Oracle, which can be one common source of compatibility issues.


There is no such thing as a one-size-fits-all solution. While many organizations are choosing to migrate to PostgreSQL to realize benefits such as cost savings or simplicity, Oracle is a better choice for some organizations with the budget and resources to manage it. If you’re interested in a switch to PostgreSQL, download our white paper to get the expertise you need to make the move, Going Open-Source: Why and How to Migrate from Oracle to PostgreSQL.”

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.

Vijay Muthu | February 4, 2021

12c Upgrade Bug with SQL Tuning Advisor

Learn the steps to take on your Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Best RAID For SQL Server | RAID 0, RAID 1, RAID 5, RAID 10

Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.

Eric Russo | June 8, 2015

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.