Upgrading and updating PostgreSQL

Blog post updated 05.12.2022 People keep asking: “What are the best ways to upgrade and update my PostgreSQL to the latest version?” This blog post covers how you can upgrade or update to the latest PostgreSQL release.

Before we get started, we have to make a distinction between two things:

• Updating PostgreSQL
• Upgrading PostgreSQL

Let’s take a look at both scenarios.

Updating PostgreSQL

The first scenario I want to shed some light on is updating within a major version of PostgreSQL: What does that mean? Suppose you want to move from PostgreSQL 15.0 to PostgreSQL 15.1. In that case, all you have to do is to install the new binaries and restart the database.
There is no need to reload the data. The downtime required is minimal. The binary format does not change; a quick restart is all you need. The same is true if you are running a HA (high availability) cluster solution such as Patroni. Simply restart all nodes in your PostgreSQL cluster and you are ready to go.
The following table contains a little summary:

Upgrading PostgreSQL

Now let’s take a look at upgrades: if you want to move from PostgreSQL 9.6, 10, 13 or some other older version to PostgreSQL 15, an upgrade is needed. To do that, you have various options:

• pg_dumpall: Dump / reload
• pg_upgrade: Copy data on a binary level
• pg_upgrade --link: In-place upgrades

If you dump and reload data, it might take a lot of time. The bigger your database is, the more time you will need to do the upgrade. It follows that pg_dump/pg_dumpall and restore are not the right tools to upgrade a large multi-terabyte database.
pg_upgrade is here to do a binary upgrade. It copies all the data files from the old directory to the new one. Depending on the amount of data, this can take quite a lot of time and cause serious downtime. However, if the new and the old data directory are on the same filesystem, there is a better option: “pg_upgrade --link”. Instead of copying all the files, pg_upgrade will create hard links for those data files. The amount of data is not a limiting factor anymore, because hard links can be created quickly. “pg_upgrade --link” therefore promises close to zero downtime.

What is important to note here is that pg_upgrade is never destructive. If something goes wrong, you can always delete the new data directory and start from scratch.

Preparing a sample database

To show pg_upgrade in action, I have created a little sample database to demonstrate how things work in real life:

test=# CREATE TABLE a AS SELECT id AS a, id AS b, id AS c
       FROM generate_series(1, 50000000) AS id;
SELECT 50000000
test=# CREATE TABLE b AS SELECT *
       FROM a;
SELECT 50000000
test=# CREATE INDEX idx_a_a ON a (a);
CREATE INDEX
test=# CREATE INDEX idx_a_b ON a (b);
CREATE INDEX
test=# CREATE INDEX idx_b_a ON b (a);
CREATE INDEX

This is a fairly small database, but it is already large enough so that users can feel the difference when doing the upgrade:

test=# SELECT pg_size_pretty(pg_database_size('test'));
pg_size_pretty
----------------
7444 MB
(1 row)

7.4 GB are ready to be upgraded. Let’s see how it works.

pg_upgrade in action

To upgrade a database, three steps are needed:

• initdb ...
• copy pg_hba.conf and pg_ident.conf and adapt the new postgresql.conf
• pg_upgrade ...

Let us start with initdb:

iMac:tmp hs$ initdb -D /data/db15

The files belonging to this database system will be owned by user "hs".

This user must also own the server process.

The database cluster will be initialized with locales
       COLLATE: en_US
       CTYPE: UTF-8
       MESSAGES: en_US
       MONETARY: en_US
       NUMERIC: en_US
       TIME: en_US
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

creating directory /data/db15 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Europe/Vienna
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

       pg_ctl -D /data/db15 -l logfile start

Note that pg_upgrade is only going to work in case the encodings of the old and the new database instance match. Otherwise, it will fail.

After adapting the configuration files, we can run pg_upgrade: Basically, we need four pieces of information here: The old and the new data directory as well as the path of the old and the new binaries:

iMac:tmp hs$ time pg_upgrade -d /data/db12/ \
       -D /data/db15 \
       -b /path/pg12/bin/ \
       -B /usr/local/Cellar/postgresql/15.1/bin/

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas                           ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows in the new cluster                        ok
Deleting files from new pg_xact                             ok
Copying old pg_xact to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Restoring database schemas in the new cluster               ok
Copying user relation files                                 ok
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
        ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
        ./delete_old_cluster.sh

real 4m11.702s
user 0m0.255s
sys 0m13.385s

What is worth mentioning here is that the upgrade process takes over four minutes because all the data had to be copied to the new directory. My old Mac is not very fast and copying takes a very long time.

To reduce downtime, we can clean out the directory, run initdb again and add the --link option:

iMac:~ hs$ time pg_upgrade -d /data/db12/ \
       -D /data/db15 \
       -b /path/pg12/bin/ \
       -B /usr/local/Cellar/postgresql/15.1/bin/ \
       --link

Performing Consistency Checks
-----------------------------
Checking cluster versions                                    ok
Checking database user is the install user                   ok

...

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
        ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
        ./delete_old_cluster.sh

real 0m3.538s
user 0m0.198s
sys 0m0.322s

In this case, it took only 3.5 seconds to upgrade. We can start the database instance directly and keep working with the new instance.

pg_upgrade --link under the hood

The --link option can reduce the downtime to close to zero. The question is what happens under the hood? Let’s check out the data files:

-rw------- 2 hs wheel 1073741824 Nov 22 11:55 16388
-rw------- 2 hs wheel 1073741824 Nov 22 11:58 16388.1
-rw------- 2 hs wheel 66576384 Nov 22 12:01 16388.2
-rw------- 2 hs wheel 1073741824 Nov 22 12:12 16391
-rw------- 2 hs wheel 1073741824 Nov 22 12:13 16391.1
-rw------- 2 hs wheel 66576384 Nov 22 12:14 16391.2
-rw------- 2 hs wheel 1073741824 Nov 22 12:01 16394
-rw------- 2 hs wheel 49373184 Nov 22 12:01 16394.1
-rw------- 2 hs wheel 1073741824 Nov 22 12:03 16395
-rw------- 2 hs wheel 49373184 Nov 22 12:03 16395.1
-rw------- 2 hs wheel 1073741824 Nov 22 12:15 16396
-rw------- 2 hs wheel 49373184 Nov 22 12:15 16396.1
-rw------- 1 hs wheel 8192 Nov 24 10:03 174
-rw------- 1 hs wheel 8192 Nov 24 10:03 175

What you see here is that the relevant data files have two entries: “2” means that the same file (inode) shows up as two directories. pg_upgrade creates hard links, and this is exactly what we see here.

That’s why it is super important to make sure that the mount point is /data and not /data/db12. Otherwise, hard links are not possible.

Finally …

pg_upgrade is an important tool. However, there are more ways to upgrade: Logical replication is an alternative method. Check out our blog post about this exciting technology.