Are you considering migrating from Oracle to PostgreSQL? Many experts have noted that migrating all databases to Postgres costs less than paying for a year’s worth of Oracle maintenance. However, making a smooth transition between databases can be challenging for many. This article will teach you how to migrate from Oracle to PostgreSQL successfully and what mistakes to avoid.

Reasons to migrate from Oracle to PostgreSQL can be many, from the need to cut costs, improve performance, or benefit from the support of the open-source community. Whatever the main reason for the change, there is no doubt that PostgreSQL is a strong player in the market, offering a robust and feature-rich alternative to Oracle.

This is very much confirmed by the latest statistics. According to a Stack Overflow survey conducted in May 2023 by more than 90,000 developers from around the world, Postgres was named the most admired database, beating out 31 other databases such as Oracle, the MySQL database, Microsoft SQL Server, and MongoDB, among others.  71% of nearly 76,000 respondents said they had used Postgres in the past year and intend to continue using it. In contrast, 42% of the percent of developers who do not currently use Postgres plan to use it in the next year.

In this article, we will focus on understanding the need for migration. We will also look at what risks are involved and how to avoid them, from assessing Oracle’s current infrastructure to designing, transferring data, and testing the new system.

Why migrate from Oracle database to PostgreSQL?

Both Oracle and PostgreSQL are powerful relational database management systems, but there are several reasons why organizations choose to migrate relational databases from Oracle to PostgreSQL.

• Costs

One of the main factors is high licensing costs. Oracle has developed a business model of eliminating database products, which were popular with smaller customers with limited budgets, in favor of application development and infrastructure solutions designed for large organizations for which cost is not an issue. As Oracle licenses become increasingly expensive, many companies are opting for a robust solution that focuses solely on a relational database.

On the other hand, PostgreSQL is an open-source database, which means it is free and has lower operating costs. It’s designed for ease of use and deployment, focusing solely on operating the database (without using resources to manage additional IT environments). This is one of the first advantages customers notice after migrating from Oracle to Postgres: instead of spending hours studying complex technology on how an Oracle database works, Postgres makes it easy to perform many of the same functions as Oracle in a matter of minutes.

What do developers say about the cost of both technologies?

“If excessive cost is your main concern, choose PostgreSQL or MariaDB, which can do more than 90% of what OracleDB can do in a simpler way and without expensive licenses.”

The following graphics from DB engines service data show the decline in popularity of Oracle and the rise of Postgres over the 2018-2024 period.

Source: https://db-engines.com/en/ranking_trend

• Performance

PostgreSQL has grown tremendously in recent years, offering comparable performance to Oracle. It has a robust query optimizer and supports advanced indexing techniques, making it a viable choice for high-performance applications. In addition, PostgreSQL’s ability to handle large data sets and complex queries makes it a suitable option for organizations dealing with large data sets.

• Community

Moreover, PostgreSQL has a vibrant open-source community, providing regular updates, bug fixes and feature enhancements. This allows users to quickly find solutions to their technical problems, and continuous development influences the high technical level of the solution.

• High scalability

PostgreSQL can handle large amounts of data and parallel users without sacrificing performance. Scalability is especially important for rapidly growing organizations or those dealing with large data sets.

• Compliance with ACID (Atomicity, Consistency, Isolation, and Durability)

PostgreSQL ensures data integrity and reliability. It supports advanced transaction management features, including multi-version concurrency control (MVCC), which allows concurrent read and write operations without compromising data integrity.

• Support for a wide range of data types

PostgreSQL also provides a wide range of indexing options, making it flexible and adaptable to different use cases. It supports both structured and unstructured data, allowing organizations to store and analyze different types of information. It also allows for the definition of custom data types, operators and functions, enabling solutions to be tailored to specific requirements.

• High level of security

PostgreSQL provides role-based access control, allowing administrators to define detailed permissions and restrictions for database services, and offers various authentication methods to ensure that only authorized users have access to the database.

Challenges and issues in migrating from Oracle to PostgreSQL

Migrating from Oracle to PostgreSQL is not without its challenges. It requires careful planning and consideration of whether migration is possible and, if so, how to ensure a smooth transition.

System customization

If the architecture of the system from the beginning was not specifically planned under the Oracle database system but just generally uses the mechanisms of the SQL standard, then it is easy to migrate to PostgreSQL. On the other hand, if the system was created by an Oracle specialist, using technical innovations or characteristic features that are only in Oracle, then migration is much more difficult and sometimes impossible. What do programmers say about this?

“I once worked on a project that used a lot of Oracle mechanisms (implemented specific implementations) and it was impossible to migrate to another database because this system ran on specific Oracle mechanisms that were not present in other databases. The solution was not designed to be portable and practically had to be rewritten from scratch.” (Marcin Żak, Full-stack developer )

Differences in SQL syntax and dialects between Oracle and PostgreSQL.

Oracle has its own proprietary SQL syntax and features that may not be compatible with PostgreSQL. Therefore, it is important to carefully analyze your existing Oracle code base and identify any potential syntax compatibility issues.

There are also some differences in data types that need to be taken into account during migration. For example, the NUMBER data type in Oracle is mapped to NUMERIC or DOUBLE PRECISION in PostgreSQL, depending on precision and scale requirements.

There is a SUBSTR (str, start_pos, len) function in the Oracle database that returns a fragment of a character string. It can be replaced by the SUBSTRING (str, start_pos, len) function in PostgreSQL. However, in Oracle, the start_pos argument can be a negative number. Then the characters are counted from the end of the string (from right to left), instead of from the beginning (from left to right).

In PostgreSQL, the argument takes only positive values. So it is easy to transfer the code from Oracle to PostgreSQL, just by changing the function name. But if the argument is negative, you will get different results, and catching such an error is very difficult. That’s why migration requires familiarity with both environments and thorough comparison tests. (For accuracy: In PostgreSQL there is a function RIGHT() which returns characters counting from the end of the string).

Below you can see the differences in data types, the full source of the differences in functions, the multiple data types and object definitions can be found here–> https://www.sqlines.com/oracle-to-postgresql

Different approaches to handling constraints and indexes.

It is important to review and modify the existing schema to ensure compatibility and optimal performance. Migration of complex constraints, such as check constraints or other unique constraints, may require manual intervention (index or trigger rewriting may be needed) and careful validation.

Migrating such an existing Oracle solution to Postgres introduces the risk that functions will be used incorrectly and errors will appear that are not apparent at first.  The program will compile, so it appears that all functions are correct. Also, the first tests may show the apparent correctness of the results, but we are not sure if a few months later it will not turn out, however, that some data was lost or functions were implemented incorrectly.

Therefore, assessing the impact of migration on existing applications and database systems is essential. Any changes in database structure or syntax may require updates to application code, stored procedures, and queries.  This is especially important given the existence of sensitive or financial data that must be securely implemented. It is critical to thoroughly test the codebase to ensure compatibility and functionality.

Planning the migration process

So we are convinced that we want to migrate our database. How then to go about it? Careful planning is key. What steps does this involve?

1.     Thoroughly evaluate the existing Oracle infrastructure and determine the scope of the migration.

To do this, we need to analyze the database size, schema complexity, and any dependencies on external systems or applications.

2.     Define migration goals and objectives.

This may include reducing costs, improving performance, or using specific PostgreSQL features. By clearly defining goals, it is easier to prioritize tasks and allocate resources accordingly.

3.     Create a detailed migration plan.

This plan should specify the order of tasks, schedules, and responsibilities. It should also include a contingency plan for unforeseen problems or delays.

Choosing a data migration strategy and tool

One of the most critical aspects of the migration process is moving data from Oracle to PostgreSQL. Several strategies and tools are available to facilitate this process.

 One common approach is to use the ETL (Extract, Transform, Load) method. It involves extracting data from an Oracle database, transforming it into a PostgreSQL-compatible format, and loading it into a new database. Several ETL tools, both open-source and commercial, are available that can automate this process and handle complex data transformations.

 Another option is to use built-in PostgreSQL features, such as the Foreign Data Wrapper (FDW) extension. FDW allows PostgreSQL to directly access and query data from remote Oracle databases. This approach eliminates the need for data migration, but requires additional configuration and can affect performance.

Testing and validation of migrated data

 Regardless of the chosen strategy, it is crucial to thoroughly test the data migration process. This includes checking the integrity and consistency of the transferred data, as well as verifying that the migrated data behaves as expected in the new PostgreSQL environment.

It is recommended to create a separate test environment that closely resembles the production environment. This allows comprehensive testing of migrated data and applications without affecting the running system.

 During testing, it is essential to check the integrity and consistency of the data. This includes:

 – Checking for missing or incorrect data,

 – Verification that the relationships between the tables have been maintained,

 – Conduct functional tests to ensure that the migrated applications and queries work properly.

Tasks after migration

Once the data migration is complete, several tasks and issues must be addressed, including:

 1 Update applications and systems to connect to the new PostgreSQL database. This may include modifying connection strings, updating configuration files or rewriting SQL queries to conform to PostgreSQL syntax.

 2 Re-evaluate performance and optimization strategies for the new PostgreSQL environment. This includes reviewing and adjusting database configuration, indexing strategies and query optimization techniques.

 3 Data backup and disaster recovery. PostgreSQL provides several data backup mechanisms, including physical backups, logical backups and continuous archiving.

Case Study – database migration from Oracle to PostgreSQL

For a client working in the logistics industry, we migrated data from Oracle to PostgreSQL. The client wanted to reduce licensing costs. Previously, he had an Oracle-based solution but wasn’t quite sure if other open-source database solutions were robust and stable enough for production use. Thanks to the migration performed by VM.PL’s developers, he was able to successfully implement PostgreSQL, which works as a serious database, working in large commercial projects.

Conclusion

While both Oracle and PostgreSQL offer robust data security solutions, the choice ultimately depends on your organization’s specific requirements, resources, and long-term goals. Careful consideration of these factors will ensure that you choose the database management system that best meets your organization’s data security needs.

If you decide to migrate from Oracle to PostgreSQL then with careful planning, thorough testing and validation of the migrated data it is sure to be a successful process. If you would have questions in this regard and would like advice from experienced database experts, please contact us. We will be happy to help you select the right solutions for your organization.

---