5 Tips for a Smooth Migration to PostgreSQL

PostgreSQL Migration illustartion

Jan Karremans, Director of Sales Engineering, EMEA, EDB, advises us on how to successfully migrate to PostgreSQL, a popular platform for high-performance data management.

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 the world’s fourth most popular database, it’s no surprise that many organisations wish to make the move to Postgres.

That said, migrating to a new database can present many challenges, and according to Gartner, 83 percent of data migrations fail or exceed their budgets and schedules. 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.

 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.

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.

Jan Karremans

Relational database person, Oracle ACE Alumni, EDB Postgres Certified Professional and PostgreSQL community member