What to consider before performing a major upgrade of PostgreSQL

There are several ways to perform a major upgrade of PostgreSQL, each with its pros and cons, and each limited by the origin and destination version of PostgreSQL (usually the latest stable). This article addresses EnterpriseDB's preferred paths.

When approaching a major upgrade of PostgreSQL, we end up facing two relevant dilemmas:

• Should I perform an upgrade on the same server or a different one?
• Should I perform an upgrade when the database is off-line or on-line?

Everyone would instinctively choose to upgrade on-line on the same machine, with no downtime whatsoever.

However, based on our experience, the recommended approach is an on-line upgrade on a different server, using logical replication to transfer data from the origin server to the destination one on the major release.

Whether you are using physical machines or virtual machines, there are quite a few advantages with upgrading on a new server. If you follow leading DevOps concepts such as immutable infrastructure paradigm with Linux containers and Kubernetes (not yet part of Production Support), that is actually the only viable option.

In case of physical machines, you rely on new hardware, with an extended maintenance, warranty and support period.

You can also take advantage of a new Linux Distribution, with, even in this case, an extended support period, including security and bug fixes.

Being a different and separated server, you can take advantage of a pre-production environment for testing, including (in case of physical machines) deep benchmarking, stress tests and validation processes. It is important to measure the maximum performance of the operating system and the storage before we actually use it for database purposes, to know the limits of the system.

Once that is done, you can install the desired PostgreSQL version, ideally the latest stable release available.

As a next step, test the applications and make sure they work with the version of PostgreSQL chosen to upgrade too.

Finally, measure how long it takes to synchronise the content of the database from the origin server to the new environment, and simulate cut-over. This can be repeated several times and automated, in order to provide reliable estimations of the time required to perform such an operation. This is extremely important as it allows technical people to provide plans and schedules to the business department and reduce risks of failure and abort.

There are essentially three ways to migrate database content from the origin server to the destination one:

• using pg_dump and pg_restore (off-line)
• using PgLogical (on-line)
• using pg_upgrade (off-line)

The general idea is to take a backup of the origin server (e.g. PostgreSQL 9.5) using pg_dump executed from the target server (e.g. PostgreSQL 12). Then use pg_restore to restore the content in the new PostgreSQL server.

While during test and before production, the dump operation can be taken with the system on-line, the final migration must be executed when the system is off-line (at least in terms of write operations).

In pre-production, it is important to measure the time taken by pg_dump to export the database, and by pg_restore to restore the database, as the overall time represents the total down-time of your application. Based on this you can decided whether this extraordinary operation is OK for your business or not (in that case, proceed with on-line upgrade as suggested below).

The process should be automated (bash or Ansible, for example) and properly versioned in Git repository (you will see that you will end up reusing these scripts within a few years when you will be performing the next upgrades).

Due to the simplicity of this procedure, we always recommend to start with an off-line upgrade and then evaluate at a later time depending on the above results. Also, don't forget you are not alone in this decision, as it requires input and cooperation from other departments (infrastructure and applications, for example). Having such an environment, will allow your enlarged team to perform application level tests and benchmarks before upgrading.

You could even think about Docker based environments for prototyping and initial testing, so to involve operations and developers in the process, before moving to the pre-production environment with physical or virtual machines.

The procedure for off-line upgrades is thoroughly described in the knowledge article "Major PostgreSQL upgrades offline". The procedure applies to all PostgreSQL versions.

If the time required for off-line upgrade described above is not appropriate for your business, you can decide to reduce the down-time to the so called cutover time, that is the time required by your applications to be re-routed to the new database.

The main idea is that, once all testing is performed, we re-initialise the target cluster, recreate the PostgreSQL global objects and database schema, then install pglogical. Pglogical will synchronise the content and when all tables are replicated and being synchronised, the business can decide when to switch to the new server (cutover).

The cutover process is usually almost instantaneous (normally less than a minute, if not a few seconds).

Pglogical requires that the origin database server has PostgreSQL 9.4 or higher. Also, if you have several databases in the cluster, you will need to perform the replication process for each of the databases.

Another approach is to use pg_upgrade to convert the database block files at physical level (not logical as the methods described above).

There are several approaches you can take but, for the same reasons discussed above, we suggest to perform the operation on a separate server. In order to test the procedure and the applications, you can use a physical backup you have, and run pg_upgrade on it.

For example, if you have Barman, you can recover the latest backup on the new machine, start PostgreSQL (using the binaries of the old versions) and stop the instance once you have verified that the recovery was successful.

Then install the binaries of the new version of PostgreSQL on that machine, run pg_upgrade.

For more information, refer to the PostgreSQL documentation on pg_upgrade.

It is important to note that, due to the specificity and openness of the major upgrade procedure, this kind of activities must be performed outside of support as part of consulting engagements. Rest assured that EnterpriseDB has more than a decade of experience in major online upgrades of business and mission critical environments, and has been leading and innovating in this area with cutting-edge solutions such as BDR, pglogical and Londiste.