My Oracle to PostgreSQL Migration: The 7 Tools That Made It Possible

I have a confession to make: I'm a huge fan of Oracle. I absolutely adore the company and its technology. Admittedly, I'm not fond of their pricing practices, but overall, they offer excellent products. As an Oracle DBA for a major energy infrastructure company in California, I've spent years mastering Oracle databases. However, a recent decision from our executive team prompted a transition to PostgreSQL. This shift transformed me from an experienced Oracle DBA into a newcomer in the world of PostgreSQL. Having navigated through this migration, I feel compelled to share the insights I've gained along the way. This article details my journey, the challenges encountered, and the seven indispensable tools that facilitated this transition. My hope is that sharing these experiences will make your journey to PostgreSQL smoother.

Overview of the Migration Process

Migrating from Oracle to PostgreSQL isn't just a flip of a switch. It's a journey with a bunch of steps like schema conversion, data migration, application migration, and performance tuning. Each stage had its own hiccups, and I needed a toolbox of solutions to handle them.

Ora2Pg

Ora2Pg was my first ally. It's an open-source tool that converts Oracle database schemas into PostgreSQL format.

As Ora2Pg is an open source project, you can see the popularity of the tool:

	Stars ⭐	Commits	Number of releases	Used By	License	Lead contributor 👏
Ora2Pg	890	2,775	22	n/a	GPL-3.0 license	Gilles Darold

Pros:

• Can handle a ton of Oracle objects
• Configurable through configuration files

Limitations:

• Complex PL/SQL conversions might need manual intervention
• Large databases can take a long time to convert

For us, we had a massive Oracle table that was the backbone of our daily operations. Converting it manually would've been like climbing Everest. But with Ora2Pg, it was as easy as running the command:

ora2pg -t TABLE -o table.sql -b /output/directory -c /path/to/config/file

We also had a bunch of sequences in our Oracle database. Ora2Pg handled them like a champ too:

ora2pg -t SEQUENCE -o sequence.sql -b /output/directory -c /path/to/config/file

Just like that, I had PostgreSQL tables and sequences ready to go.

AWS Database Migration Service (DMS)

Next up is AWS DMS, a cloud service that helped me migrate our data with minimal downtime. In an industry where every minute of downtime can cost a fortune, this was a big win.

We had one database that was absolutely critical to our operations. With DMS, I set up continuous replication, which meant the database was still available during the migration.

I also found the task monitoring feature of DMS really handy. With a simple AWS CLI command, I was able to keep tabs on the migration status:

aws dms describe-replication-tasks --filters Name=replication-task-id,Values=task1

we migrated from on-prem to AWS, but if you migrate to Google Cloud you can use Google Cloud Database Migration Service (DMS) that provides a similar functionality and already integrated with Ora2Pg.

Pros:

• Supports both homogeneous and heterogeneous migrations
• Allows for continuous replication, reducing downtime

Limitations:

• It's a paid service, unless you have credits or funds from AWS.
• Not all data types are supported for all source and target databases

pgLoader

pgLoader is a data loading tool for PostgreSQL that uses the COPY command to load data super fast. It's like a moving truck, helping to haul our data from Oracle to PostgreSQL.

When I first tried to move our data, it was slow going. But with pgLoader, I just ran this command:

pgloader oracle://user@localhost/dbname postgresql:///dbname

We also had a bunch of CSV files that needed to be loaded into PostgreSQL. pgLoader took care of those too:

pgloader --type csv --field 'column1,column2,column3' --with 'header=true' csv_file.csv postgresql:///dbname

Pros:

• Fast data loading
• Can load data from various sources including flat files and other databases

Limitations:

• Does not provide options for transforming data during migration
• Limited to data loading, does not handle schema or code migration

Foreign Data Wrappers (FDW)

Foreign Data Wrappers, or FDWs, are this neat feature in PostgreSQL that let you manage data in other databases as if they were local PostgreSQL tables.

For example there were times when I needed to query Oracle data directly from PostgreSQL. With FDWs, I was able to set up a foreign table and run queries like they were on local data:

CREATE EXTENSION oracle_fdw;
CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//hostname/dbname');

Later, when I needed to pull in data from an external MySQL database, I used the mysql_fdw:

CREATE EXTENSION mysql_fdw;
CREATE SERVER mysql_server FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'mysqlhost', port '3306');

Pros:

• Simplifies data integration
• Supports different data sources

Limitations:

• Some FDWs are read-only
• Performance can be slower than local queries

pg_dump, pg_restore and additional built-in PG features

PostgreSQL comes with its own toolbox. Two tools I found super useful were `pg_dump` and `pg_restore` for data backup and restore.

In our migration, once all our data was in PostgreSQL, I wanted to make sure we had solid backups. So I used these commands to dump and restore our database:

pg_dump -U username -W -F t dbname > dbname.tar
pg_restore -U username -W -F t -d dbname dbname.tar

We also had a couple of large tables that needed to be backed up individually. The `pg_dump` tool came in handy again:

pg_dump -U username -W -F t -t large_table dbname > large_table.tar

Pros:

• Can handle both full and partial backups
• Allows for parallel backup and restore

Limitations:

• Restoring from a dump can be slower than the initial backup
• Not designed for large-scale data migrations

EverSQL, for Post Migration Tuning

After we migrated, our queries needed some tuning to run efficiently on PostgreSQL. That's where EverSQL came in. This tool took our SQL queries and optimized them for PostgreSQL.

One of our Oracle queries was running slower on PostgreSQL. I popped it into EverSQL, and it spat out an optimized version that ran 19x faster. I also used EverSQL's Index Advisor feature to get recommendations on which indexes to add

Pros:

• Automated SQL query optimization, with automatic rewrite.
• Provides index optimization recommendations

Limitations:

• Most features are on the free tier, but advanced features require a paid subscription.

Npgsql

Finally, there's Npgsql. It's a .NET data provider for PostgreSQL, letting our .NET applications talk to PostgreSQL.

We had a .NET application that needed to connect to our new PostgreSQL database. With Npgsql, it was as simple as:

var connString = "Host=myserver;Username=mylogin;Password=mypass;Database=mydatabase";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();

// Perform database operations
}

There was also this time when I had to call a PostgreSQL stored procedure from our .NET application. Npgsql made it easy:

using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
using (var command = new NpgsqlCommand("stored_procedure_name", conn))
{
command.CommandType = CommandType.StoredProcedure;
var result = command.ExecuteNonQuery();
}
}

Pros:

• Fully managed .NET data provider for PostgreSQL
• Supports modern .NET features like async and Entity Framework Core

Limitations:

• Only works with .NET applications
• May require changes in application code to switch from Oracle data providers

Common Pitfalls in Migrating from Oracle to PostgreSQL

Migrating from Oracle to PostgreSQL is not without its hurdles. Here are some of the common issues that you might run into during your migration journey, along with real-life examples from my own experience.

1. Differences in SQL Syntax and Functionality

Oracle and PostgreSQL SQL syntax and functionality aren't always a one-to-one match. This can lead to issues during the migration, especially with complex queries and stored procedures.

Example: In Oracle, I used to rely heavily on the `CONNECT BY` clause for hierarchical queries. PostgreSQL doesn't support `CONNECT BY`. I had to rewrite those queries using recursive common table expressions (CTEs), which are the PostgreSQL way of handling hierarchical data.

2. Transaction Behavior

Oracle and PostgreSQL handle transactions differently. In Oracle, DDL statements are treated as autonomous transactions and are immediately committed. In contrast, PostgreSQL treats DDL statements as regular transactions.

Example: I once tried to execute a series of DDL and DML statements in a single transaction in PostgreSQL, expecting them to behave like in Oracle. This resulted in some unexpected behavior because the changes weren't immediately committed.

3. Case Sensitivity

By default, Oracle is case-insensitive for column and table names, while PostgreSQL is case-sensitive. This can lead to issues if your Oracle schema uses mixed-case identifiers.

Example: I had a table in Oracle with mixed-case columns. When I migrated it to PostgreSQL, I ran into issues because PostgreSQL was treating `myColumn` and `mycolumn` as two different columns.

4. Sequences and Auto-Incrementing

In Oracle, you create a sequence and then use a trigger to auto-increment a column. PostgreSQL has a built-in feature for auto-incrementing columns using `SERIAL` or `IDENTITY`, but migrating Oracle sequences and triggers to this new paradigm can be tricky.

Example:* In Oracle, I had a table with a sequence and a trigger for auto-incrementing the primary key. When migrating to PostgreSQL, I had to replace these with a `SERIAL` primary key.

5. Data Types

Oracle and PostgreSQL have different sets of data types. While many of them map directly, some Oracle data types have no equivalent in PostgreSQL.

Example: I had a table in Oracle that used the `NUMBER` data type. PostgreSQL doesn't have a `NUMBER` type, so I had to carefully map it to an appropriate numeric type in PostgreSQL.

6. NULLs and Empty Strings

Oracle treats NULLs and empty strings the same way, which is different from PostgreSQL where NULL and an empty string are distinct. If your Oracle database or applications rely on this behavior, you'll need to carefully handle this during the migration.

Example: I once had a bug in our application after migration because it was relying on Oracle's treatment of empty strings as NULL. I had to update the application code to treat NULL and empty strings separately in PostgreSQL.

7. Date and Time Types

Oracle has a single DATE type that includes both date and time, unlike PostgreSQL, which separates date and time into DATE, TIME, and TIMESTAMP types. This can lead to confusion if your Oracle database uses the DATE type to store times.

Example: We had a table in Oracle with a DATE column that was being used to store both date and time. When I migrated it to PostgreSQL using the DATE type, we lost the time information. I had to go back and change it to the TIMESTAMP type in PostgreSQL.

Conclusion

Migrating from Oracle to PostgreSQL was a wild ride, but these tools made it a lot easier. Whether you're a seasoned Oracle DBA or a newbie to PostgreSQL, I hope my journey and these tools can help make your migration a little smoother. Good luck, and happy migrating!