4 Success Tips for Migrating from Oracle to PostgreSQL

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.

2. 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.

3. 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.

4. 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.

Conclusion

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