Five tips for a smooth migration to PostgreSQL

Leading organisations are always looking to find different solutions for high-performance data replication and secure storage environments. A popular platform is PostgreSQL, an open source database that offers critical maturity and enterprise features for high scalability and availability. Given its ranking as DBMS of the Year 2020, it’s no surprise that a growing number of organisations want to move to Postgres. In addition, during the global pandemic, many companies have had time to review systems that were continuously used before the pandemic (think airline ticketing systems). This assessment of critical systems has sparked a new dimension of migrations, upping the demand for Postgres and pushing towards a more efficient spend of budget to ensure a sharper competitive edge.

That said, migrating to a new database can present many challenges. To prevent performance issues and to ensure a smooth migration to PostgreSQL, IT teams should plan the move ahead of time by following a few simple steps:

1. Test, test and test

Testing is critical to prevent unforeseen issues and downtime. Before migrating from one database type to another, be sure to test in a non-production environment that closely mirrors the production environment, and test the Procedural Language for SQL (PL/SQL) functions to make sure they’re ported properly from the existing to the new database. Once migration has been tested in a non-production environment and all issues have been resolved, the application can be taken into production with much more confidence.

SUSE CEO: avoiding disruption with open source and how her focus has changed

The SUSE CEO explains why an open source model has led to non-existent disruption and how her focus has changed in the wake of coronavirus. Read here

2. Share the work

Developers can spend a lot of time building brilliant and complex systems with Postgres, but without sharing this work with the wider DevOps team, all this hard work can be lost when the individual transitions out of the company. To avoid having to start from scratch, IT departments should allocate this work to a small group of developers, to ensure that more than one person is immersed in the implementation of the system. Additionally, by using modern techniques such as running Postgres on Kubernetes, it gets much easier and more reliable to run repeatable unit tests of the work that has been done on a migration.

3. Analyse compatibility

Often, the choice of which database to start with is totally random, based on usage, or based on the size of the database. Deciding based on size or usage is better than nothing, but this can be misleading. The smallest databases may be the ones with logic or features that don’t have a 1:1 correlation between the existing and target database.

To avoid this mistake, there are tools available that assesses the compatibility of an existing database with Postgres. Once a company has established which of their databases are most and least compatible with Postgres, it helps to start with the one that looks easiest. This allows the IT team to gain experience that they can fall back on when the time comes for the more challenging cases.

Why a new strategy is necessary to untapping the potential of your data

From SMBs to multinationals, every business has to deal with data on a day-to-day basis. Read here

4. Ask experts for help

Companies that don’t have sufficient Postgres expertise often resort to extensive research to figure out the best practices. This can be very time consuming, not to mention that the advice isn’t tailored to the organisation’s individual needs. Organisations that don’t have at least a couple of people well-versed in Postgres who are available to plan an approach should consider reaching out for help from PostgreSQL experts and professionals. This will speed the company along its Postgres journey, and will help to avoid costly mistakes that will need correcting a year from now.

5. Make it a priority

Like many infrastructure improvements companies want to make, sometimes a transition to Postgres is ‘prioritised,’ but never makes it to the top of the list. To get stuff done quickly, companies should think about their goals and what they want to get out of Postgres. For example, a company that is user-focused above all else can benefit from decreased transaction times and improved data integrity. On the other hand, a company more focused on time savings could consider moving two or more types of databases to Postgres, to minimise the number of database types they use. Cutting down on databases that take a lot of time to maintain could help to reduce troubleshooting and context switching. By focusing on a handful of key goals, companies can see tangible results much more quickly.

With all of the possible mistakes that could be made, migrating to a new database can seem like a daunting task. But because PostgreSQL adheres to both industry standards and to the rules set by Relational Database Theory, as well as to all of the external support that is available, it doesn’t need to be. By planning the move ahead of time, testing the production environment and asking relevant experts for their support, companies can ensure a smooth and easy transition to Postgres and reap the many benefits of this open source platform.

Written by Jan Karremans, director of sales engineering EMEA at EDB

Editor's Choice

Editor's Choice consists of the best articles written by third parties and selected by our editors. You can contact us at timothy.adler at