Preparation / decision making
The first part of the process is high level research. You should identify Oracle features that are not supported by PostgreSQL and find parts of your application that don't need to be migrated.
The point of this exercise is to make a yes/no decision on whether this project is worth your time, energy, and money.
Identify non-compatible features
Many mature Oracle deployments make use of some Oracle feature that isn't available in PostgreSQL. Common examples of these features include:
- PL/SQL
- Advanced Queuing
- Flashback
Also, watch out for table and column names that conflict with PostgreSQL's reserved keywords!
We have not yet encountered a case where PostgreSQL couldn't replace Oracle, but there are definitely use cases that require some of these features. Let's talk a little bit about each of these features, and how you might replace them.
Oracle PL/SQL replacements
This is an easy decision, even if it isn't easy to implement. You should replace any procedural SQL code with PL/PGSQL (PostgreSQL's procedural language). Generally speaking, PL/PGSQL is a valid replacement for any other RDBMS' procedural language and will have some way of doing whatever you need to do. Of course, doing this is easier said than done, but at least the choice is clear.
Oracle Advanced queuing (AQ) replacements
An underappreciated Oracle feature is it's message queuing functionality. PostgreSQL offers SIMPLE/NOTIFY commands that may be able to serve basic use cases, but it isn't on the same level as AQ. I would personally skip right over using PostgreSQL to implement a queue and look into using something like Redis for simple message queue functionality, or Kafka for more advanced use cases.
However, your team may want to avoid adopting new tools and could potentially give PostgreSQL's basic functionality a try.
Oracle Flashback replacements
PostgreSQL offers the ability to restore your database to a point in time, but Oracle goes a step further and allows you to run SELECT statements as if they are in a certain point in time, without rolling your entire database back to that state.
As an aside, I can only imagine what a headache that code was to write :-)
This is probably the one deal breaker on the list. If you have mission-critical code that relies on this feature and don't have the appetite to roll your own change tracking mechanism in PostgreSQL, you may be better served by remaining on Oracle.
Give your app a haircut
There are almost always schemas, tables, and sometimes even entire databases that are no longer in use. For example, a recent client of ours claimed they needed to migrate 25 databases, but upon further investigation, less than 20 were actually used in production.
We use a few ad-hoc SQL scripts to determine how often database objects are actually used - stored procedures, tables, etc. It is essential to do that homework ahead of time so you don't waste effort moving things that aren't needed.
It is especially helpful to identify stored procedures that are not in use anymore - translating and testing complex stored procedures is probably the most time consuming part of a schema translation process.
Here is one of the many SQL scripts we use to discover stored procedure usage:
SELECT * FROM (
SELECT
sql_text, executions, buffer_gets, disk_reads, sort_executions, cpu_time, elapsed_time
FROM v$sqlarea
WHERE sql_text LIKE 'BEGIN %' OR sql_text LIKE 'CALL %'
ORDER BY executions DESC
)
WHERE ROWNUM <= 10;
Schema translation
Translating a schema from Oracle to PostgreSQL isn't that hard. In fact, there are automated services (such as Amazon DMS) that can help you with the process.
The advent of advanced AI systems has also drastically lessened the effort required to translate SQL from one syntax to another. It is truly amazing what the latest LLMs can do. We make use of them all the time, and are always testing new LLMs to see which ones are the most accurate for our use case.
Side note: We never train LLMs on customer DDL code, or enter customer DDL code into LLMs (ChatGPT) that will train on it.
However, translating a schema well is still a labor intensive, detail oriented process. The cost of making a mistake in your database can be very high. In all cases we've seen, there are hard decisions to be made and trade offs to be considered.
At the end of the day, you need to export your schema's DDL code and translate it to PostgreSQL syntax. The primary deliverable is a working SQL file that creates a schema which is compatible with the original database. The primary objects you need to convert are:
- Databases
- Schemas
- Tables
- Views
- Columns
- Constraints
- Indexes
- Stored procedures
- Functions
- Triggers
- Sequences
- Custom types
Ideally, someone with professional experience working with both Oracle and PostgreSQL will build the new SQL file, potentially with the help of automated tools. Each RDBMS has it's own unique way of doing things, and even if you make something that "works", it may not be optimal.
Our process for translating these objects is simple:
- Spin up a dev environment (usually a single VM with 16 GB RAM in the customer's VPC).
- Put a backup file of Oracle on the dev instance, as well as a DDL file with the DB's schema.
- Deploy Oracle and PostgreSQL via Docker Compose.
- Create a SQL file that you will work on to create a compatible schema for PostgreSQL.
Now it's time to work through the Oracle DDL file, translating objects in the following order:
- Schemas
- (Optional) types / sequences
- Tables / columns (without constraints)
- Views
- Indexes
- Constraints
- Stored procedures
- Functions
- Triggers
The process isn't complex, you just have to do the work. It is a long and detail-oriented process, so be sure to constantly re-compile the PostgreSQL database with your in-progress DDL file to ensure that you haven't made any mistakes.
Sample Docker Compose file
Here is a Docker Compose file that will spin up a PostgreSQL and Oracle instance, as well as transfer SQL files from your local machine to the Docker images for initial DB setup. Unfortunately, you will have to build the Oracle Docker file first - they do not offer a pre-built image. You can find out how to do that in Oracle's official Docker image repo.
Working this way with Docker, if either of the databases get into a weird state because of testing you're doing on them, you can just delete the entire database and start from scratch.
version: '3.8'
services:
oracle:
image: sqlpipe/oracle:21.3.0
container_name: oracle
ports:
- "1521:1521"
environment:
- ORACLE_PWD=Mypass123
- NLS_CHARACTERSET=AL32UTF8
volumes:
- ./setup-sql/oracle.sql:/opt/oracle/scripts/startup/setup.sql
deploy:
resources:
limits:
memory: 16G
postgresql:
image: postgres
container_name: postgresql
ports:
- 5432:5432
environment:
POSTGRES_PASSWORD: YourPass456
volumes:
- ./POSTGRESQL-SETUP-FILE.sql:/docker-entrypoint-initdb.d/setup.sql
deploy:
resources:
limits:
memory: 4G
Oracle to PostgreSQL type mappings
While translating tables, you will need to translate columns from Oracle types to PostgreSQL types. Please view our article Oracle to PostgreSQL Data Type Mappings for an in depth guide on how to translate your column types.
Data Movement
We offer a free Oracle to PostgreSQL data migration tool that can migrate all of the user data in an Oracle database to a PostgreSQL database with just a few clicks. Visit our database migration products page for more information.
We also offer a paid tool that allows you to pull the initial load of a database from an Oracle snapshot, then replicate data changes to PostgreSQL in near real time. This allows you to lessen the impact on your production systems and minimize downtime.
Final Changeover
The final changeover is when you finally point all of your applications (or point load at new, modified applications) at your new database. This is an extremely high leverage moment that requires the utmost preparation and care.
You should have rehearsed this changeover process at least once in a staging or dev environment and must make a contingency plan to roll back to your original environment in case some unacceptable errors appear once the new tech stack goes live.
It is impossible to give specific advice for this stage without detailed knowledge of your given environment. Every organization's applications, deployment methods, and downtime tolerance is different. We offer consulting services that can help you prepare for this part of the process.
Wrapping up
We hope this article helped you understand how to migrate from Oracle to PostgreSQL at a high level. If you would like help with such a project, please send us a message on our contact page, we would be happy to assist!
