The article provides a comprehensive guide for those considering data migration from Oracle to PostgreSQL. It highlights the benefits of using Postgres as a database system, such as cost-effectiveness, flexibility, and customizability. The migration process is broken down into several phases, including assessment, schema migration, functional testing, performance testing, and data migration, with detailed step-by-step instructions for each phase. The article also identifies key differences and incompatibilities between the two database systems to help users avoid common mistakes. In addition, it offers alternative migration strategies and lists free data migration tools that can be helpful in the process. When migrating to Postgres, the process involves selecting the appropriate schema, performing compatibility checks, converting incompatible objects, conducting functional and performance testing, migrating data, and performing post-migration checks.
There are lot of reasons to migrate from Oracle to PostgreSQL. Here are just a few key advantages of the migration:
- Cost. Aside from the licensing fees for Oracle, there are also additional costs associated with using its databases, such as high availability and partitioning. These costs can quickly accumulate. On the other hand, Postgres, an open-source database system, is free to install and use.
- Customization. PostgreSQL open-source nature allows for a wide range of extensions and add-ons that can significantly enhance database performance, many of which are available for free. Oracle, in contrast, often charges for similar features, leading to added expenses.
- Flexibility. PostgreSQL is licensed under an open-source model and can be easily obtained from public cloud providers like AWS. Choosing Postgres mitigates the risk of vendor lock-in.
Although PostgreSQL offers numerous advantages over Oracle databases in terms of application programming, authentication, extensibility, languages, localization, performance, and scalability, it’s important to note that migrating from Oracle to Postgres can be a challenging and time-consuming process due to the differences in their relational database management systems (RDBMS) structure and data types. It’s crucial to approach the migration process with the right tools and follow the recommended steps to ensure a successful migration.
Database Migration Steps
Oracle to PostgreSQL database migration can be arranged into the following steps or phases: assessment, schema migration, intermediate testing, data migration, final testing
Assessment. The initial stage of planning a migration from Oracle to Postgres involves conducting an assessment of the application to determine the level of difficulty involved in the migration process. This stage requires a comprehensive analysis of technology-related factors such as client, application server, data access, and database features to evaluate compatibility.
Schema Migration. In Oracle, a user’s schema shares the same name as the user itself, and by default, each user has their own schema. Conversely, in PostgreSQL, the user and schema are not synonymous, and newly created objects will be placed in the public schema by default if a schema is not explicitly specified. This setup in PostgreSQL offers the advantage of enabling a user to create multiple schemas without creating separate users, as well as the ability to grant permissions to others for creating objects in those schemas.
Intermediate Testing. Before proceeding with the migration, it is crucial to conduct thorough testing of the converted schema using a sample dataset. It is recommended to load sample data into Postgres from a development or testing environment where there is production sample data, and set up an application connection using appropriate data access drivers.
To verify the accuracy, load the same fragment of data in both the Oracle and PostgreSQL databases, query it to confirm that the SQL results are identical. Any issues revealed during the functional testing phase should be reviewed and addressed accordingly.
Performance testing is also critical during the migration phase as some of the Oracle built-in transactions or features functionality may differ slightly in PostgreSQL, and the application may experience some variance. This phase is essential for identifying any differences and fixing them at the application, data access (drivers), and database level through proper tuning.
Oracle to PostgreSQL Data Migration.
There are multiple data migration approaches and tools available in the market, which can generally be classified into three types: Snapshot, Snapshot in Parallel, and Change Data Capture (Replication). The first two approaches involve moving data from Oracle to Postgres in a one-time transfer, while the third approach continuously loads data. This leads to smaller downtime windows. It’s essential to choose the right approach that fits the downtime window.
Let’s discuss these three data migration strategies in more detail:
- Snapshot. In this approach, the source database state is captured as a snapshot and applied to the target database all at once. The data is moved from Oracle to Postgres in one shot. During the snapshot process, no WRITE operations are allowed on the source database, resulting in application downtime. However, it’s a cleaner and simpler method of data migration.
Pros: Data movement is completed in one go, no data type challenges, applications can start accessing the target database immediately after the snapshot. No special configuration is required, and it’s easy to manage. Read-only users on the source database can use this method.
Cons: application downtime is required during the snapshot, if the snapshot is interrupted it needs to be restarted.
- Snapshot – piecewise (parallel). Parallel piecewise is a variation of the snapshot approach, where data objects are split into chunks, and snapshots are taken in parallel. There are two ways to do a piecewise snapshot: table by table or split large data into small fragments using primary keys or any unique row identifiers. Most tools support snapshot and the process is invoked in parallel. This approach significantly reduces the snapshot duration and downtime window. Good scripting skills are required to prepare data migration tools for table or large table migration.
Pros: data is moved at one time with less downtime, data is moved in parallel – table by table or a large table in small sets.
Cons: application downtime is still required (less than the Snapshot approach). For large tables split into small sets, primary key or unique row identifiers are mandatory. Scripting is required to adjust the parallel approach. If the snapshot is interrupted, it needs to be restarted.
- Change Data Capture (CDC). In the CDC approach, the software captures and tracks the data that has changed in real-time on the source database and replays it on the target database. Database migration software implementing CDC method is in high demand because it distributes data between heterogeneous databases with low-latency, reliable, and scalable data. Trigger-based and Transaction Log-based are the most common CDC approaches for migrating Oracle to PostgreSQL databases.
Pros: data is loaded continuously to the target database after the initial snapshot, the user can access the source database while data loads into the target database, data sync control is available – and it can be resumed if interrupted.
Cons: replication software is required, in trigger-based CDC there could be a slight performance overhead, no Large Objects support. Only commercial tools are available, no open source. Partial to small application downtime is required during switchover time.
Database Migration Tools
There are a number of free and commercial migration tools available that can help users automate Oracle to PostgreSQL database migration
Ora2pg is a free Perl script. It connects to Oracle server, extracts schemas, indexes, constraint and data, generates SQL scripts that create all the extracted entries in PostgreSQL database.
AWS Schema Conversion Tool (AWS SCT) is a commercial tool provided by Amazon that automates schema analysis and conversion. It can also recognize embedded SQL statements and convert them as part of a database-schema-conversion project.
Oracle-to-PostgreSQL is a commercial database migration software provided by Intelligent Converters. It has intuitive wizard-style interface and can automate overall Oracle to PostgreSQL migration within a few clicks.
EDB Migration Portal is a web-based service for migrating from Oracle to the vendor’s manufactured fork of PostgreSQL called EDB Advanced Server. The tool provides Assessment, Schema conversion and Compatibility reporting features.
The comparison matrix below gives an integrated picture of features, cons and pros of all these Oracle to PostgreSQL database migration tools.
| Migration of Objects | Migration of Code | Migration of Data | License | |
| Ora2Pg | 75% | 25% | Snapshot | Freeware |
| AWS SCT | 75% | 50% | Snapshot Piecewise | Commercial |
| Oracle-to-PostgreSQL | 98% | 75% | Snapshot Piecewise | Commercial |
| EDB Migration Portal | 95% | 75% | Subscription |