Migrating to PostgreSQL

In Chapter 12, Troubleshooting PostgreSQL, we learned how to approach the most common issues related to PostgreSQL troubleshooting. The important thing is to have a systematic approach to tracking down problems, which is exactly what is provided here.

The final chapter of this book is about moving from other databases to PostgreSQL. Many of you may still be suffering from the pain caused by commercial database license costs. I want to give all of you out there a way out and show you how data can be moved from a proprietary system to PostgreSQL. Moving to PostgreSQL makes sense not only from a financial point of view but also makes sense if you are looking for more advanced features and flexibility. PostgreSQL has so much to offer, and, at the time of writing, new features are being added daily. The same applies to the number of tools that are available to migrate to PostgreSQL. Things are getting better and better, and developers are publishing more and better...

Migrating SQL statements to PostgreSQL

When moving from a database to PostgreSQL, it makes sense to take a look and figure out which database engine provides which kind of functionality. Moving the data and the structure itself is usually fairly easy. However, rewriting SQL might not be. Therefore, I decided to include a section that explicitly focuses on various advanced features of SQL and their availability in today’s database engines.

Using LATERAL joins

In SQL, a LATERAL join can basically be seen as some sort of loop. This allows us to parameterize a join and execute everything inside the LATERAL clause more than once. Here is a simple example of this:

test=# SELECT *
   FROM generate_series(1, 4) AS x,
        LATERAL (SELECT array_agg(y)
                  FROM generate_series(1, x) AS y
     ...

Moving from Oracle to PostgreSQL

So far, we have seen how the most important advanced SQL features can be ported or used in PostgreSQL. Given this introduction, it is time to take a look at migrating Oracle database systems in particular.

These days, migrating from Oracle to PostgreSQL has become really popular due to Oracle’s new license and business policy. Around the world, people are moving away from Oracle and adopting PostgreSQL.

Using the oracle_fdw extension to move data

One of my preferred methods to move users from Oracle to PostgreSQL is Laurenz Albe’s oracle_fdw extension (https://github.com/laurenz/oracle_fdw). It is a foreign data wrapper (FDW) that allows you to represent a table in Oracle as a table in PostgreSQL. The oracle_fdw extension is one of the most sophisticated FDWs and is rock solid, well-documented, free, and open source.

Installing the oracle_fdw extension requires you to install the Oracle client library. Fortunately, there are...

Summary

In this chapter, we understood how to migrate SQL statements to PostgreSQL and learned to move a basic database from another system to PostgreSQL. Migration is an important topic, and more and more people are adopting PostgreSQL every day.

PostgreSQL 15 has many new features, such as improved built-in partitioning and a lot more. In the future, we will see many more developments in all the areas of PostgreSQL, especially ones that allow users to scale out more and run queries even faster. We are yet to see what the future has in store for us.