Overview

PostgreSQL's latest major version is 12. That being said, it is fairly easy to migrate between versions even major ones. There are couple ways to upgrade, depending on how much you want to risk to compromise with. Practically there are 2 ways of Upgrade in any database:

In-Place: You upgrade on the same host

Out-Of-Place: You upgrade using 2nd host, most often via Replication or data migration directly to the new release.

We will discuss both methods in this section. So let's get started:

In-Place

Important to note here is that even the In-place upgrade isn't so risky as with other databases (Oracle / SQL / DB2 and so on). The steps to perform In-Place upgrade in PostgreSQL between major version are pretty straight-forward. So let's get going: In this case, we will also, upgrade from 9.6 → 10.

Install the new Release

You can install the new version by either compiling the source or using package manager (in my case yum).

The new version, will come in it's own binary and data locations:

Create Database base

After that, we have to create a database base:

It is very good idea to check for possible compatability problems between the two versions in order to play it safe:

From this point, we have two ways we can go.

Ask pg_upgrade to copy the datafiles from old → new location. The downtime depends the size of the DB

Ask pg_upgrade to LINK the datafiles (hard links). The downtime is minimal.

Since we want to have the least downtime, we will use 2nd option

Stop the databases

Since we didn't start the new version. Stop the old database version:

Once stoppped, let's upgrade:

Link the new Version

Start the new Cluster

After that, we can delete the old cluster and start the one. Please don't be afraid that the upgrade is via links, in that case links mean that both files point to the same piece of chunk in the hard disk. Meaning as long as we have one pointer (the new version) we can delete the old one (9.6). These AREN'T SYMBOLIC links, these are hard links to the hard disk.

Out-Of-Place

Out of place of place upgrade usually is done using one of the following:

Complete installation of the new PostgreSQL on 2nd host and using pg_dumpall, pg_dump and psql to restore the data

Using logical replication to minimize the downtime to almost 0.

We have two environments:

PostgreSQL 9.1 on Linux 5, which should be migrated to: PostgreSQL 10 on Linux 7

To migrate from such an old version there are two options

Upgrade the current version to 10 and simply migrate the data using backup/restore

Backup the current version 9.1 using (pg_dumpall and pg_dump) and restore it (pg_restore) to the new version 10

Since the first path will take waaay longer and since we are lazy AF, we will be using the second path

Backup

In order to backup the data in universal format we have to:

Dump the globals: Roles & Tablespaces using pg_dumpall

Dump each database (we will use the custom format “-Fc” since it is more flexible) using pg_dump -Fc

So let's get going:

Dump Globals