Migrating to PostgreSQL

In Chapter 12, Troubleshooting PostgreSQL,of this book, I showed you 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 I have tried to provide.

The final chapter of this book is about moving from other databases to PostgreSQL. Many readers might still be suffering from the pain caused by commercial database license costs. I want to give all those users out there a way out and show how data can be moved from some proprietary system to PostgreSQL. Moving to PostgreSQL not only makes sense from a financial point of view, but also makes sense if you are looking for more advanced features and more flexibility. PostgreSQL has so much to offer and new features are being added as we speak. The same applies to the number of tools available...

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. It basically allows us to parameterize a join and execute everything inside the LATERAL clause more than once. Here is a simple example:

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

Moving from Oracle to PostgreSQL

So far, you 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. To help people make Oracle a thing of the past, I have included a special section here.

Using the oracle_fdw extension to move data

One of my preferred methods to move people from Oracle to PostgreSQL is Laurenz Albe's oracle_fdw extension (https://github.c</span>om/laurenz...

Moving from MySQL or MariaDB to PostgreSQL

In this chapter, you have already learned some valuable lessons about how to move from databases such as Oracle to PostgreSQL. Migrating both MySQL and MariaDB database systems to PostgreSQL is fairly easy. The reason for that is that Oracle might be expensive and a bit cumbersome from time to time. The same applies to Informix. However, both Informix and Oracle have one important thing in common: CHECK constraints are honored properly and data types are properly handled. In general, you can safely assume that the data in those commercial systems is somewhat correct and doesn't violate the most basic rules of data integrity and common sense.

Our next candidate is different. Many things you know about commercial databases are not true in MySQL. The term NOT NULL does not mean much to MySQL (unless you explicitly use strict mode)...

Summary

In this chapter, you learned how to move to PostgreSQL. Migration is an important topic and more and more people are adopting PostgreSQL as we speak.

PostgreSQL 10 has many new features such as built-in partitioning, the ability to create more statistics (CREATE STATISTICS), and logical replication features. In future, we will see many more developments in all areas of PostgreSQL, especially to allow users to scale out more and to run queries even faster. We are yet to see what the future has in store for us.