How to Migrate from Oracle to PostgreSQL
Here is described a case of migration from Oracle server to PostgreSQL server for large supplier of goods. This article covers each stage of migration, potential issues, and ways to solve them.
In most cases, companies switch from Oracle to PostgreSQL to reduce costs. Oracle licenses are paid and quite expensive, but PostgreSQL is an open source DBMS that is free to use and known for its ease of use, rich features, and strong security.
When switching to PostgreSQL, many questions may arise:
- Does it meet business requirements?
- How to choose the right migration tool?
- How quickly will the migration occur?
- What challenges may arise during the migration?
- How to minimize risks and costs for the business?
Advantages of PostgreSQL
Each DBMS has its own strengths. PostgreSQL has been actively developing recently and has become one of the most reliable, productive and functional DBMS. Let's see why PostgreSQL is strong:
1. PostgreSQL is open-source. A large community of developers acts as a vendor and keeps the software up to date. Thanks to this, there are many useful third-party extensions for Postgres.
2. Overall costs are usually less than in Oracle.
3. Sufficient functionality, flexibility, and scalability.
4. Technical support: the community provides a high level of support and regular updates.
5. Postgres has no limitations on the database size of and the number of rows in tables. Therefore, it can work with a large amount of data. There is a table size limit of 32 TB, but this is more than enough to organize the storage of any data.
Migration Features
When transferring data from one platform to another, there is always a possibility of encountering features that can complicate the data transfer during migration. For example:
- Incompatible data types.
- Specific data storage formats.
- Computed columns.
- External data structures.
- Temporary tables.
- Utilization of RAC (Real Application Clusters) and Data Guard.
Addressing these features requires careful planning and consideration to ensure a smooth and successful migration process. It involves mapping and transforming data types, handling unique storage formats, managing computed columns, accommodating external data structures, addressing temporary table usage, and adapting to the specific features of RAC and Data Guard if they are in use. Thorough testing and validation are essential to mitigate potential issues during the migration process.
Therefore, research and planning are the first steps to implement any project.
To implement this project successfully, we divided the process into several steps (stages), and the implementation itself took 3 weeks.
Here is a brief specification of the original database:
- Database server: Oracle 12g
- Database contains 130 tables, 70 GB of data in total.
These details provide a foundation for understanding the scope and scale of the migration project, allowing a systematic and well-prepared approach to ensure a smooth migration from Oracle to PostgreSQL.
Implementation Stages:
1. Identify and analyze all Oracle-specific methods for storing and processing data in the source database and determine the scope of usage.
2. Choose the most appropriate tool for schema and data migration. In our case, we opted for DBConvert Studio.
3. Configure the tool based on the requirements of this migration.
4. Run the data migration process that performs all transformations required for the Postgres database.
5. Run performance and functionality tests, detailed configuration of the resulting database.
In case of using DBConvert Studio, we suggest doing a test migration of part of the data and viewing the results in the destination database.The test/demo version has no speed limitations, so you can calculate the approximate migration speed to understand the acceptable downtime of your production database. The only limitation in the trial is the special TRIAL TEXT, which will be added to the data.
It is worth considering that the conversion results may differ from the test results due to non-optimal tool customization. In this case, we recommend to contact us and we will provide you with free support to set up the conversion tool for your project.
To facilitate the seamless transfer of data from the source database on an automated, periodic basis while migrating the entire infrastructure from one database to another, you can use another our product named DBSync for Oracle & PostgreSQL or the synchronization option in DBConvert Studio. This way, all added or modified data will automatically sync with the PostgreSQL destination database.
It's important to understand that the larger the volume of data, the longer the migration process will take. If you have a large amount of data, a Personal license may not be sufficient, as support for parallel migration at high speeds is available in Business and Enterprise licenses.
In any way, it is important to achieve high speed and reliability of data migration. For this, you need to:
- try to increase network bandwidth
- improve the performance of servers where the migration tool, source and target databases are running
- use parallel migration
Migration of Incompatible Data Types
There are some data types in Oracle that do not have a direct equivalent in Postgres.
One such type is DATE, which contains both date and time information.
PostgreSQL supports the following:
- time: the time of day without a date, with the option to specify a time zone
- date: only the date without a time component
- timestamp: date and time with an additional time zone specification
- interval: a time interval
There are two options to map Oracle dates to PostgreSQL: either use TIMESTAMP as the closest equivalent to DATE in Oracle, or configure the Orafce extension to use the Oracle-style date type oracle.date.
Another example of type mismatch is the SDO_GEOMETRY built-in type in Oracle, which allows working with geometry. For PostgreSQL, installing PostGIS is required to work with spatial data types.
Differences in Data Storage Formats
For numerical types, it is essential to consider your specific needs. If precision is a priority, then using the NUMERIC type is recommended. However, if performance is crucial, you may opt for so-called "inexact types" like REAL and DOUBLE PRECISION, as they offer faster processing speeds.
Oracle CLOB and LONG character large objects are converted to TEXT type in PostgreSQL. However, it is important to note that in TEXT, the maximum size of the stored single column value should not exceed 1 GB.
For large binary data types like BLOB, RAW, or LONG RAW in Oracle, you should use Bytea in PostgreSQL. Nevertheless, the stored value for this type cannot exceed 1 GB as well. Alternatively, you can use the large object (lo) type for handling large binary data.
The table below shows the mapping between Oracle and PostgreSQL types:
Our software has the possibility to set up mapping both for the entire database and for individual fields in the tables you need.
Data, tables, fields, indexes and relationships can be converted using this tool. To convert other database objects such as views, stored procedures, functions, you will need to use other tools for partial automation and manual post-processing. Unfortunately, there is no tool that can convert all objects automatically.
Conclusion
When migrating from Oracle to PostgreSQL, automation is the key to a successful migration with minimal time and risk. To transfer your database from Oracle to PostgreSQL, you can try DBConvert Studio, or stand-alone DBConvert for Oracle & PostgreSQL or DBSync for Oracle & PostgreSQL. These tools offer a wide range of settings to ensure the highest quality migration. Your choice of product may depend on the individual characteristics of your project, your budget and, of course, your personal needs.
In any case, these tools are fully automated and, therefore, do not require any programming skills from your side.
To reduce your business downtime when migrating from one database server to another, don't forget to optimize your source and target database, server, and network settings.