The Complete Oracle to Postgres Migration Guide: Tools, Schema, and Data
Learn about migrating from Oracle to Postgres–from data migration and Oracle vs. Postgres features comparisons to instructions for efficient Oracle transition.
For anyone on the fence or intimidated by the data migration process, there are several advantages Postgres has over Oracle as a database system, namely cost, flexibility and customizability. This blog breaks down the migration process into individual phases (assessment, schema migration, functional testing, performance testing and data migration) and provides step-by-step instructions.
Key differences and incompatibilities between the two database systems are itemized and weighed to help users avoid common mistakes. Alternate migration strategies and a list of helpful free data migration tools are also taken into consideration. For any current Oracle users considering Postgres but concerned about the complexity of the move, this guide is tailor-made for you.
Database migration refers to the process of transferring data from one database to another, which becomes necessary when an organization seeks to upgrade to a new database system, consolidate databases, move to a cloud-based database, or change the database schema.
When considering migration to Postgres, there are several crucial steps involved:
Assessment and Planning | Evaluating the source and target databases, understanding the data and planning the migration strategy. |
Schema Migration | Transfering the database schema (tables, indexes, constraints) from the source to the target database. |
Data Migration | Moving the actual data from the source database to the target database. This can be done in a bulk transfer or incrementally. |
Data Transformation | If necessary, transform the data to fit the new schema or to meet the new system’s requirements. |
Testing and Validation | It is vital to test the migrated data to ensure accuracy and completeness and to validate if the target database is functioning as expected. |
Deployment and Monitoring | Implementing the target database into the production environment and monitor for any issues. |
Here are several advantageous benefits when you choose to migrate from Oracle to Postgres:
Cost: In addition to Oracle license costs, using Oracle databases incurs additional fees for features like partitioning and high availability, where expenses can add up quickly. Open source Postgres on the other hand, is free to install and use.
Flexibility: Postgres has open source licensing and is easily available from public cloud providers, including AWS. With Postgres, you’re not at risk of vendor lock-in.
Customizability: There are countless extensions and add-ons that can improve database performance markedly due to Postgres’ open source nature, and many of them are free to use. With Oracle, similar features quickly add up in cost.
That’s not to say that migrating from Oracle to Postgres is not an involved process. Since the data migration is between two relational database management systems (RDBMS), it can be a challenging and time-consuming process due to heterogeneous structure/data types. As such, you want to be sure it’s tackled and handled with the right tools, such as migration software or tailor-made Oracle migration tools. Follow the steps below and you’ll be well on your way to achieving a developer-friendly oracle to Postgres migration.
- Application Programming
- Oracle and Postgres both provide an application API for database communication. However, as Postgres is open source, developers can access any Postgres component simply by including the header file in their project.
- Authentication
- Oracle has a built-in authentication system. Postgres relies on host-based authentication and can support a wide range of authentication methods, providing greater flexibility for authentication and the option to delegate the process.
- Extensibility
- Oracle has a mostly proprietary plug-in system, whereas the general community supports Postgres' extension system, enabling access to thousands of available plug-ins.
- Languages
- While Oracle has a built-in programming language called PL/SQL, Postgres has PL/pgSQL in addition to many others and an extension system that allows users to create additional procedural languages as plug-ins and bindings for even more programming languages.
- Localization
- Oracle offers globalization support tools, including a globalization development kit and unicode character support. Postgres' localization system services are built-in to provide automatic character encoding and collation support.
- Performance
- Because Postgres can create an unlimited number of nodes in a read cluster, the cost of any particular read operation can be reduced to close to nothing. And because of that, you can tune it differently for every workload. You can do this in Oracle, too, but each node has an additional cost.
- Scalability
- Oracle has b vertical read scalability. Still, Postgres can create a virtually unlimited number of nodes in a read cluster, depending on the resources you have available to dedicate to it.
There are five steps or phases when dealing with Oracle to Postgres migration:
1. Assessment
What are the Oracle to Postgres pre-migration assessment steps and phases? This process involves assessment, compatibility assessment, application code assessment, architecture assessment, and cleanup and schema assessment.
The first step in planning the migration and analyzing the application to estimate how easy or difficult the Oracle to Postgres migration can be. In this phase, a thorough analysis should be conducted on technology-related issues to evaluate the compatibility of the client, application server, data access, and database features.
One concern that can easily be overlooked when considering a move to Postgres is confirming that, if you do not control your application, the packaged software application you use certifies for Postgres. If not, you must either convince your application’s vendor to add Postgres support or choose another application.
After verifying the source and target database compatibility, the following prerequisites should be met for data migration:
- Server resources (memory/disk space/network ports opened between source and destination)
- Operating system
- Data migration software and related drivers installed and configured
It should go without saying that your target server resources are large enough and have the scalability to handle the volume of data they will be receiving. If the volume of data is substantial, then a purely online migration may not be advisable, and you should consider an export-and-reload approach. It may also be wise to follow a migration strategy that divides the migration into parts (see the section on migration strategies below).
The more your application code relies on Oracle-specific frameworks instead of open classes, the more intricate your migration becomes. The amount of adjustment you will need to make depends on your application architecture and database connection layer. The conversion should be relatively easy if you have Java code that uses generic JDBC classes rather than Oracle-specific ones. It should be easy to use object-relational mapping (ORM), such as Hibernate or JCA. Switching the dialect from Oracle to Postgres could be trivially easy, though some adjustments will still need to be made.
The ease or difficulty of a migration can be significantly impacted by the setup and architecture of your database and its contents. Migration presents an excellent opportunity to clean up your architecture and database contents. Deprecate objects you no longer need, such as old temporary tables or backup copies of data—they’re not worth migrating if no one cares about them. If you store large files, like images or PDFs, in your database, consider separating them into a lower-cost storage option to reduce database size and resources needed for backup and restores. You may want to purge static historical data or move into an archival store option.
Moving from Oracle to Postgres also opens the possibility of separating online transaction processing (OLTP) and analytics into different warehouses, which can improve both responsiveness and analytics capabilities
Following the assessment, the next step in the migration process is identifying and addressing schema and data formatting differences between Oracle and Postgres. Making these adjustments before the data migration is crucial to avoid frustrating and time-consuming errors in Postgres.
Postgres supports ANSI SQL standard SQL syntax and data types, whereas Oracle does not support the same standard; additionally, it includes some non-ANSI SQL syntaxes. With the help of Oracle migration tools, unsupported objects can be identified and converted manually with Postgres-supported syntax or feature workarounds.
2. Schema Migration
What is schema in Oracle and Postgres?
A schema is also known as a “user” in Oracle and has the same name as the user. The default is for each Oracle user to have their schema. In Postgres, these are not the same and if you do not explicitly specify a schema, new objects will go to a public schema by default.
One concern that can easily be overlooked when considering a move to Postgres is confirming that, if you do not control your application, the packaged software application you use certifies for Postgres. If not, you must either convince your application’s vendor to add Postgres support or choose another application.
After verifying the source and target database compatibility, the following prerequisites should be met for data migration:
- Create a user and schema with the same name
- The first component in the schema search_path is $user, by default
One advantage of the setup in Postgres is that a user can create multiple schemas without creating separate users and grant permissions to others for creating objects in those schemas.
What are the Oracle to Postgres schema migration tools?
Several migration tools are available that can help users automate schema conversion. Below are the tools you can try for free to convert Oracle objects to Postgres.
- Ora2pg: a robust migration tool that connects to an Oracle database, extracts schemas and tables, and generates SQL scripts that can be loaded into Postgres.
- Ora_migrator: an extension that uses an oracle_fdw foreign data wrapper to extract data from an Oracle database.
- Orafce: this extension allows you to implement many Oracle functions in Postgres. It also provides support for Oracle date formatting and additional Oracle data types.
- EDB Migration Portal: a web-based service for migrating from Oracle to EDB Postgres Advanced Server that features Assessment, Schema conversion, and Compatibility reporting.
Take a quick look at how EDB Migration Portal works in this tutorial.
In addition, there are a number of commercial conversion tools available, including Amazon’s AWS Schema Conversion Tool (AWS SCT).
Oracle to Postgres migration tools comparison matrix
The below tools comparison matrix will give you a high-level picture of what they can do and how far they get you in the migration exercise to a specific target.
Assessment | Migration of data objects | Migration of code objects | Migration Data | Approach | Target | |
---|---|---|---|---|---|---|
EDB Migration Portal | Use EDB MTK | Native + Transformation | Postgres Advanced Server | |||
EDB MTK | Native Only | Postgres Advanced Server | ||||
AWS SCT | Transformation Only | PostgreSQL | ||||
Ora2Pg | Transformation Only |
The key schema differences to take note of before Oracle to Postgres migration
Consider the following differences between Oracle and Postgres when preparing for schema conversion:
Columns
Until version 12, Postgres had no equivalent to virtual columns, so users were encouraged to change these to views when migrating. Now, Postgres offers generated columns, which share many traits with Oracle’s virtual columns.
Constraints
The Primary and Foreign Key, Check, Not-Null, and Unique constraints operate similarly in both database systems.
Identifiers
Oracle converts names of schema, tables, columns, and functions to uppercase unless the name is given in quotes, while Postgres converts them to lowercase unless given in quotes. You should be safe as long as the application consistently quotes or does not quote the identifiers.
- B-tree and descending indexes should function in Postgres
- Reverse key, bitmap, and join indexes are not currently supported.
- Global index is not supported in Postgres
Partitions
Hash, List, and Range partitions should all work in Postgres following migration.
CREATE TABLE is mostly compatible, with the following exceptions:
- Postgres lacks global temporary tables. Use temporary tables (LOCAL TEMP) instead.
- Partitioning: Use Inheritance, Triggers, and CHECK Constraints for partition clauses.
- Storage clause parameters (INITRANS, MAXEXTENTS) are not recognized in Postgres and should be removed.
- For the Oracle PCTFREE parameter, replace it with Postgres’ fillfactor.
Tablespaces
There are differences between Oracle and Postgres’ versions of tablespaces, but they serve the same purpose and should work.
Data types
The following chart lists notable differences between Oracle and Postgres data types.
Oracle | Postgres | EDB Postgres Advanced Server | Comment |
---|---|---|---|
VARCHAR2(n) | VARCHAR(n) | VARCHAR2(n), VARCHAR(n) |
Be careful not to confuse ‘n’ in Oracle and Postgres data types. In Oracle it stands for the size in bytes; in Postgres it stands for the number of characters. |
NVARCHAR, NVARCHAR2 | VARCHAR or TEXT | NVARCHAR, NVARCHAR2, VARCHAR or TEXT |
|
CHAR(n), NCHAR(n) | CHAR(n) | CHAR(n), NCHAR(n) |
Be careful not to confuse ‘n’ in Oracle and Postgres data types. In Oracle it stands for the size in bytes; in Postgres it stands for the number of characters. |
NUMBER(n, m) | NUMERIC(n,m) | NUMERIC(n,m) NUMBER(n, m) |
NUMBER type can be converted to NUMERIC, which is unlimited in size. However, the SMALLINT, INT BIGINT, REAL, and DOUBLE PRECISION data types offer better performance. |
NUMBER(4) | SMALLINT | NUMBER(4) SMALLINT |
|
NUMBER(9) | INT | NUMBER(9), INT |
|
NUMBER(18) | BIGINT | NUMBER(18), BIGINT |
|
NUMBER(n) | NUMERIC(n) | NUMBER(n), NUMERIC(n) |
If n>=19 |
BINARY_INTEGER, BINARY_FLOAT | INTEGER, FLOAT | BINARY_INTEGER, INTEGER, FLOAT |
|
DATE | TIMESTAMP(0) | DATE, TIMESTAMP(0) |
In Oracle the DATE type returns both date and time, whereas in Postgres the DATE type returns the date without the time. |
TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMPTZ | TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMPTZ |
Oracle has both TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types. Postgres' TIMESTAMPTZ is equivalent to TIMESTAMP WITH LOCAL TIME ZONE. If these are confused it can introduce errors. |
CLOB, LONG | TEXT | CLOB, LONG TEXT |
Postgres' TEXT type is capable of storing up to 1 GB of text data. |
BLOB, RAW(n), LONG RAW |
BYTEA(1 GB limit) Large object |
BLOB, RAW(n), LONG RAW BYTEA(1 GB limit) Large Object |
In Oracle,the BLOB datatype is used for unstructured binary data and essentially has no size limit (up to 128 terabytes of binary data). Postgres' BYTEA data type can store up to 1 GB of binary data. Above that limit use Large Object (these are stored in a separate table). |
What are the challenges while migrating from Oracle to Postgres?
In this section, we discuss some of the challenges we might face while migrating from Oracle to Postgres. To address these challenges and limitations identified in Postgres, those objects should be rewritten manually with a workaround if they exist.
1. Constraints
While Oracle allows users to disable and enable constraints as often as you want, this is not generally recommended practice for any RDBMS, because it can lead to data corruption if not performed with proper care.
In Postgres, constraints are instead created as deferrable, and the SET CONSTRAINTS command can be used to defer them. The deferrable setting indicates the default time for activating the constraint. If the constraint in Oracle is not deferrable, it will need to be dropped and re-created as deferrable, though it is sometimes possible to alter the constraint without having to drop it. Note: to avoid potential errors or bad data, place the commands for dropping and re-creating the constraint in a transaction, marked with a BEGIN/COMMIT block, which will lock the tables during the transaction.
2. DELETE
The FROM clause for specifying the table is required with DELETE statements in Postgres but not in Oracle.
Oracle:
DELETE mytable WHERE column_name = 'column_amount';
Postgres:
DELETE FROM mytable WHERE column_name = 'column_amount';
3. Dropping database objects
In Postgres, permission to drop objects is restricted to only a database table’s owner or a super user. It is not a grantable privilege, although membership in the role that owns the object can be granted. If an action in Oracle depends on this ability, it may need to be rewritten or reconfigured.
4. Dual Table
Because the FROM clause is mandatory in Oracle for every SELECT statement, FROM DUAL is used for SELECT statements where the table name is not necessary. Postgres does not require the FROM clause, so FROM DUAL is not necessary and can usually be omitted. If the Dual table is needed in Postgres, it can be generated as a view.
5. Empty Strings and NULL
In Oracle, empty strings have NULL values, but they are not considered NULL in Postgres.
In Oracle, you can check whether a string is empty or not using the IS NULL operator, but in Postgres, it would return FALSE for an empty string (and TRUE for a NULL one).
6. Federation to Foreign Data Wrappers
Oracle’s Federation feature allows users to treat tables from other databases as local data. Postgres' foreign data wrappers are more versatile and allow you to connect to a wider range of data.
7. GRANT
The GRANT command behaves similarly in Oracle and Postgres. There are two basic variants—it can be used to grant privileges on a database object and to grant membership to a role.
Not all privileges that are grantable in Oracle are grantable in Postgres. For example, granting the trigger privilege to a table allows users to create triggers; but, as opposed to Oracle, only the owner of the table can drop triggers.
8. Hierarchical queries
Postgres does not support the START WITH . . . CONNECT BY syntax that Oracle uses for hierarchical queries. Instead, Postgres uses WITH RECURSIVE.
Oracle:
SELECT
business_name,
city_name
FROM
businesses bs
START WITH bs.city_name = 'BOSTON'
CONNECT BY PRIOR bs.business_name = bs.city_name;
Postgres:
WITH RECURSIVE tmp AS (SELECT business_name, city_name
FROM businesses
WHERE city_name = 'BOSTON'
UNION
SELECT t.business_name, t.city_name
FROM businesses t
JOIN tmp ON tmp.business_name = t.city_name)
SELECT business_name, city_name FROM tmp;
9. Joins with (+)
Oracle has a special shorthand (+) operator for performing left and right outer joins. Postgres lacks this feature, so the JOIN command would need to be supplied.
Oracle:
SELECT table1.firstname, table2.lastname
FROM table1, table2
WHERE table1.customer = table2.customer (+);
Postgres:
SELECT table1.firstname, table2.lastname
FROM table1
LEFT OUTER JOIN table2 ON table1.customer = table2.customer;
10. NOT NULL checking
To determine which columns in an Oracle table are NOT NULL, you would use the command CHECK (
Postgres instead has a NOT NULL constraint column named attnotnull in pg_attribute, the systems catalog where information about table columns is stored.
11. Packages
Postgres does not have packages, but, using schema architecture, functions, and procedures can be grouped. Use the “orafce” migration tool library, which supports some of the standard packages, or EDB Postgres Advanced Server, which has built-in Packages.
12. PL/SQL to PL/pgSQL Conversion
Postgres' PL/pgSQL procedural language is similar in many respects to Oracle’s PL/SQL. Both are block-structured, imperative languages, with similar formats for assignments, loops, and conditionals. The Postgres documentation has a thorough run-down of considerations that need to be made when porting from PL/SQL to PL/pgSQL.
13. Remote objects
To access remote objects, the DBLINK module or Foreign Data Wrapper(Oracle_fdw) can be used to access any other database.
ROWID, CTID and Identity columns
Postgres does not have an exact equivalent to the ROWID pseudocolumn in Oracle, which provides the address of a row in a table. CTID in Postgres is similar, except that its value changes every time VACUUM is performed. Instead, you can use identity columns, whose value is self-generated when a row is created and never changes. The value can be specified to be GENERATED ALWAYS or GENERATED BY DEFAULT. GENERATED BY DEFAULT allows the user the option to insert or update a value rather than use the system-generated value.
14. Sequences
Sequences have a different syntax in Oracle and Postgres and will need to be updated either manually or using a script.
Oracle:
Mysequence.nextval;
Postgres:
nextval(‘mysequence’);
15. SUBSTR
The SUBSTR function behaves differently in Oracle and Postgres. In Oracle the statement SELECT SUBSTR(‘ABC’,-1) FROM DUAL; returns ‘C’, while the equivalent SELECT SUBSTR('ABC',-1); in Postgres would return ABC. The Orafce migration tool includes a SUBSTR function that returns the same result in both databases.
16. Synonyms
Postgres does not support synonyms. In place of Oracle’s CREATE SYNONYM for accessing remote objects, Postgres has you use SET search_path to include the remote definition.
Oracle:
CREATE SYNONYM abc.mytable FOR xyz.mytable;
Postgres:
SET search_path TO 'abc.mytable';
17. SYSDATE
Oracle's SYSDATE function returns date and time (in the server’s timezone). Postgres does not have a corresponding function, but there are a range of methods for retrieving the date and time for different purposes: statement_timestamp() gives current date and time from the beginning of the current statement; now() and transaction_timestamp() give the date and time from the beginning of the current transaction; and clock_timestamp() gives current date and time from the execution of the function.
18. TO_DATE
The to_date() function in both Oracle and Postgres return the date data type. However, Postgres' date data type provides the date (year, month, day), while Oracle’s date data type value provides the date and time (year, month, day, hour, minute, second). To avoid this incompatibility, use Postgres' to_timestamp().
The solution for this incompatibility is to convert TO_DATE() to TO_TIMESTAMP(). If you use Orafce tool then it is not necessary to change anything because Orafce implemented this function so we get the same result as Oracle.
Oracle:
SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;
Postgres:
SELECT TO_TIMESTAMP ('20180314121212', 'yyyymmddhh24miss' )::TIMESTAMP(0);
19. Transactions
Oracle always uses transactions, but in Postgres, they must be activated. In Oracle, executing any statement starts a transaction, and it ends with the COMMIT statement. In Postgres, the transaction starts with the BEGIN statement, and it also ends with the COMMIT statement. Transaction isolation levels are the same in Postgres and Oracle, and Read Committed is the default isolation level for both.
Postgres is built to facilitate transaction management and error handling, with full ACID support and isolation levels. It is also capable of handling run-time errors and provides reliable error codes and messages to PL/pgSQL or application code. These are handled differently from Oracle, though, so changes still need to be made. Here are some tips for optimizing error handling for Postgres:
- Transaction control inside of PL/pgSQL is not permitted: you cannot commit or roll back a transaction inside a stored procedure. Commits and roll backs must be called from the application, so the application that calls the stored procedure must perform the transaction management—starting and committing or rolling back. The stored procedure executes within that calling transaction context. Obviously, if your existing database code has transaction management in procedures, it must be modified.
- When there is a run-time exception during a transaction, the transaction must be rolled back before you can execute another statement, because the transaction is aborted when it encounters the error. The application log will show the following error message:
ERROR: current transaction is aborted, commands ignored until end of transaction block.
Be sure that you have an exception handler set up where errors might be occurring and either to a savepoint or close the connection before trying another database operation.
- Use a BEGIN…EXCEPTION…END block for exception handling so your code catch any errors that occur. This automatically sets a savepoint before the block and rolls back to it when it encounters an exception. Keep in mind that because exception blocks create a savepoint, they are expensive, so add them carefully.
- Map the error codes and exception types from Oracle to Postgres. While some error codes are the same in both, others are different. Your programming language affects this as well. For example, Oracle-specific JDBC exceptions need to be replaced with either generic cross-database exceptions or Postgres-specific ones.
- Ensuring your Postgres database handles transactions and errors correctly is a critical part of the migration process and usually requires a careful review of the database and application code.
For enterprises looking to retain some oracle functionality in Postgres and wanting to migrate faster without many code rewrites an option is to move to EDB Postgres Advanced Server. EDB Postgres Advanced server is Oracle compatible Postgres with native PL/SQL compatibility. The below video demonstrates how the Advanced Server handles Oracle queries:
Take a quick look at Oracle compatibility with EDB Postgres Advanced Server.
3. Functional Testing
Functional testing after scheme migration from Oracle to Postgres
Before proceeding further, testing the converted schema on a sample dataset is vital. A recommended approach is to load some sample data into Postgres from a source database development or testing environment with production sample data and then set up an application connection using appropriate data access (drivers). After the application has connected to the database, allow it to do complete functional testing on the converted objects with DMLs.
It is advisable to load the same sample dataset in the Oracle and Postgres databases and then test both to ensure the SQL results are identical. Review and address any issues revealed by the functional tests.
4. Postgres Performance Testing After Migration
Performance testing is essential in the migration phase because some Oracle built-in transactions or feature functionality might differ slightly in Postgres, and the application might see some differences. In this phase, we can capture and fix all those differences at the application, data access (drivers), and database level with proper tuning.
5. Postgres Performance Testing After Migration
There are different approaches for data migration and tools available in the market. Usually, they are classified in three ways:
- Snapshot: Data moved at once
- Snapshot in Parallel: Data moved in chunks (schema or table)
- Change Data Capture (Replication): Data loaded continuously.
For approaches 1 and 2, we need application downtime because data is being written one time from Oracle to Postgres, whereas in approach 3 data is loaded continuously, and there is a smaller downtime window. Pick the right data migration approach that fits in the downtime window.
Breaking down the strategies
Let's discuss these three data migration strategies in a little more detail.
Snapshot (One Big Bang)
In this approach, a snapshot of the source database state is taken and applied on the target database. Data is moved from Oracle to Postgres all at once. During the snapshot process, no WRITE operations are allowed on the source database. It's one of the cleaner and easier methods of data migration.
Pros | Cons |
---|---|
|
|
Snapshot - Parallel in Chunks (Trickle)
Parallel in chunks is another type of snapshot approach where data objects are broken into chunks and snapshots are taken in parallel. Most of the tools support snapshot and the process are invoked in tandem. There are two ways to perform a snapshot in chunks: 1) table by table or 2) a large table split into small sets using primary keys or any unique row identifiers. In this approach, the snapshot duration and downtime window is greatly reduced. Good scripting skills required to prepare data migration tools for table or large table migration.
Pros | Cons |
---|---|
|
|
Change Data Capture (CDC Data Sync)
Different traditional Change Data Capture (CDC) approaches have been available for decades. In the CDC model data migration, the software is designed to determine/track/capture the data that has changed on the source database in real time and replay the same on the target database. Currently, CDC model software is in high demand because it distributes data between heterogeneous databases with low-latency, reliable, and scalable data. The most common CDC approaches for migrating Oracle to Postgres databases are:
- Trigger-based (CDC)
A remote trigger is created to capture DMLs (insert/update/delete) before or after and the sequence of transaction events is captured in changelog (shadow tables). They are processed by the software to replay on the target database. - Transaction Log-based (CDC)
Every database will have transaction logs (redos) to store all transactions/events sequentially and be used in case of database crash recovery. The transactions (DMLs) can be captured to change logs with filters, transforms, and aggregations using native database transaction log plug-ins. Later, the sequence of captured DMLs will be replicated to the target database.
Both CDC approaches can be used to replicate data from Oracle to Postgres. However, each has its merits and limitations. Depending on the requirements, we can choose one of the available approaches. There are excellent software options available to support both CDC approaches.
Pros | Cons |
---|---|
|
|
There are open and free to use tools available for data migration in three different categories we have discussed in the blog. Below are the set of tools that we came across under those categories:
Snapshot | Snapshot - Parallel in chunks | Large table in sets | Change Data Capture |
---|---|---|---|
|
|
|
|
Snapshot | Snapshot in Parallel | Change Data Capture | Target | |
---|---|---|---|---|
EDB Replication Server | Postgres Advanced Server/PostgreSQL | |||
EDB MTK | Postgres Advanced Server/PostgreSQL | |||
AWS SCT | PostgreSQL | |||
Ora2Pg | PostgreSQL | |||
Ora_FDW (extension) | PostgreSQL | |||
Dblink_ora (extension) | Postgres Advanced Server |
Oracle to Postgres Migration Content and Resources
Additional resources to help you get the most out of your oracle migration
Explore what a comprehensive strategy for migrating from Oracle to PostgreSQL looks like using DevOps practices, GitHub, and specialized EDB tools to streamline the process.
Discover how Nibble IT successfully migrated from Oracle to PostgreSQL, including Oracle Forms, with their comprehensive strategy and innovative tools like VisionX, in this compelling case study
Looking for the best tools and solutions to make your Oracle to Postgres migration seamless for your developers?
Businesses may eventually outgrow the capabilities of their Oracle database, and might require a solution that's more extensible, feature-rich, and supports a greater range of data types or languages. Some might also leave to avoid vendor lock-in or expensive licensing costs.
It depends on what a user or business expects from their database, and the costs or complexity they are willing to bear as they scale the database to meet future demands. While there are many reasons to remain with Oracle, businesses might decide to switch to PostgreSQL if they want better control and agility over their database.
PostgreSQL also has a growing ecosystem of solution providers that offer a range of enterprise-grade services, such as fully managed Postgres, cloud migrations, and 24/7 support, all of which makes PostgreSQL a viable alternative to Oracle.
For starters, PostgreSQL has zero licensing fees due to its open source licensing, which allows both the individual and enterprise to access, modify, and distribute the code freely. PostgreSQL also has numerous other advantages over Oracle, namely:
Extensibility Support
PostgreSQL is catalog-driven like most relational databases, but unlike its counterparts, a wider range of information is stored within PostgreSQL catalogs. Users can easily access and modify information within PostgreSQL catalogs, making it more extensible than other databases.
Higher concurrency
MVCC (Multi-Version Concurrency Control) is a concurrency control system in PostgreSQL, which allows for multiple concurrent read and write transactions, while also maintaining transaction isolation to ensure data isn't rewritten and remains consistent for all users.
Feature-rich flexibility
PostgreSQL comes with a wider range of features, such as the abovementioned catalog-driven operations, concurrency control, layered transactions, and dynamic loading, to name a few. It's also ACID compliant, which ensures high data integrity and fault tolerance.
In terms of transactions-per-second, both databases are equal in terms of performance. Both databases can handle large data volumes and concurrent users. Both are also equal in terms of scalability, however, businesses will encounter licensing and cost complications with Oracle - a hurdle they won't have with PostgreSQL due to its open source and extensible nature.
PostgreSQL supports ANSI SQL standard SQL syntax and data types, whereas Oracle does not support the same standard; additionally, it includes some non-ANSI SQL syntaxes. Using tools, unsupported objects should be identified and then converted manually with Postgres-supported syntax or feature workarounds.
Some best practices to consider before you migrate from Oracle into PostgreSQL are:
Get organizational buy-in
Ensure the entire business understands and agrees to the need to migrate to PostgreSQL. Establish migration goals, timelines, and contingencies.
Determine what needs to be moved
Compile an exhaustive list of all data or applications that need to be moved over, eliminate old data the business no longer needs, and identify any compatibility issues that might occur.
Test and test again
Perform stability and performance tests on any data that have been migrated over to the new PostgreSQL to identify potential issues before performing the actual migration.
Establish new security and observability measures
Migrating to PostgreSQL provides businesses with the opportunity to leverage its security and monitoring capabilities to minimize data security and outage risks.
There are many differences between both databases, namely data types, syntax, procedural language, and a host of potential compatibility issues for applications that are migrated over.
It’s challenging to convert schemas in Oracle to PostgreSQL without the right set of tools. For a list of free online conversion tools, see our list here. While these tools are freely available, they can be challenging to use without the right expertise. If you’re converting a large database filled with thousands of query lines, it’s advisable to use an enterprise grade solution instead.
You’ll need the right solution and tools to run PostgreSQL in Oracle compatible mode. EDB Postgres Advanced Server is the only Oracle compatible solution in the industry that allows users to run Postgres in Oracle Compatibility mode.
EDB has created EDB Postgres Advanced Server (EPAS), which is an enhanced version of PostgreSQL that includes implementations of many Oracle specific data types, object types, keywords, procedural language constructs, and other features. We continually evaluate migration challenges and introduce new compatibility features in each major version of EPAS. The solution is designed to mimic Oracle code, drivers, and queries while providing support for certain Oracle features or workloads. These features could potentially reduce code and application rewrites by up to 80%. Additionally, EDB Postgres Advanced Server has robust application security to minimize migration risks and delays.
Our fully managed, multi-cloud Postgres solution will allow you to focus on building and scaling your business, while we fully undertake the burden of database deployment, administration, and operational tasks. We'll also ensure high availability and backups of your mission-critical data and provide 24/7 professional assistance for any issues you may encounter.
We'll first look to gain visibility over your entire database estate and map applications to related data schema for assessment purposes. We'll then define which databases are part of scope, define ROI that meets the migration strategy, and determine a prioritized migration backlog. For full insight into our iterative approach, click here.
Our professional services team will conduct a free migration assessment of your schema and data to ensure that your database qualifies for a migration to PostgreSQL.
Ready to Move to PostgreSQL
And Unlock Greater Possibilities with Your Data?
Reach out to us with your migration requirements and let’s get you started towards a more flexible, cost-efficient, and scalable database management solution