Introduction

One of my customer recently asked me to upgrade its PostgreSQL instance from version 9.6 to version 14.3. The infrastructure is composed of 4 servers :

– DB02-04 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-05 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-06 – Ubuntu 20.04 – supporting PostgreSQL, Patroni, HAProxy and etcd
– DB02-07 – Ubuntu 20.04 – pgBackRest server

I also had to upgrade Patroni from version 2.0.1 to version 2.1.4 for compatibility reason, and I took the opportunity to upgrade pgBackRest from version 2.24 to version 2.39.

Source versionTarget version
PostgreSQL9.6.1814.3
Patroni2.0.22.1.4
pgBackRest2.242.39


The purpose of this blog post is to explain all the required steps to achieve this.

PostgreSQL 14.3 installation

Before installing PostgreSQL 14.3, it is necessary to install some required packages.

[[email protected] ~] $ sudo apt install llvm clang pkg-config liblz4-dev libllvm7 llvm-7-runtime libkrb5-dev libossp-uuid-dev

I’m used to compile and install PostgreSQL from the source code. Once the archive is downloaded and transferred to the server, we have to extract its content.

[[email protected] upgrade] $ tar -xzf postgresql-14.3.tar.gz

[[email protected] upgrade] $ ll postgresql-14.3
total 768
-rw-r--r--  1 postgres postgres    445 May  9 21:14 aclocal.m4
drwxr-xr-x  2 postgres postgres   4096 May  9 21:24 config
-rwxr-xr-x  1 postgres postgres 587897 May  9 21:14 configure
-rw-r--r--  1 postgres postgres  85458 May  9 21:14 configure.ac
drwxr-xr-x 58 postgres postgres   4096 May  9 21:24 contrib
-rw-r--r--  1 postgres postgres   1192 May  9 21:14 COPYRIGHT
drwxr-xr-x  3 postgres postgres   4096 May  9 21:24 doc
-rw-r--r--  1 postgres postgres   4259 May  9 21:14 GNUmakefile.in
-rw-r--r--  1 postgres postgres    277 May  9 21:14 HISTORY
-rw-r--r--  1 postgres postgres  63944 May  9 21:25 INSTALL
-rw-r--r--  1 postgres postgres   1665 May  9 21:14 Makefile
-rw-r--r--  1 postgres postgres   1213 May  9 21:14 README
drwxr-xr-x 16 postgres postgres   4096 May  9 21:25 src
[[email protected] upgrade] $

Then the directory where the binaries will be installed must be created.

[[email protected] upgrade] $ mkdir -p /u01/app/postgres/product/14/db_3

Our standard when installing PostgreSQL from the sources is to create and execute a shell script which will automatically compile and install the binaries.

[[email protected] postgresql-14.3] $ cat compile_from_source.sh
#!/bin/bash

PGHOME=/u01/app/postgres/product/14/db_3
SEGSIZE=2
BLOCKSIZE=8

./configure --prefix=${PGHOME} \
            --exec-prefix=${PGHOME} \
            --bindir=${PGHOME}/bin \
            --libdir=${PGHOME}/lib \
            --sysconfdir=${PGHOME}/etc \
            --includedir=${PGHOME}/include \
            --datarootdir=${PGHOME}/share \
            --datadir=${PGHOME}/share \
            --with-pgport=5432 \
            --with-perl \
            --with-python \
            --with-openssl \
            --with-pam \
            --with-ldap \
            --with-libxml \
            --with-llvm \
            --with-libxslt \
            --with-segsize=${SEGSIZE} \
            --with-blocksize=${BLOCKSIZE} \
            --with-systemd \
            --with-gssapi \
            --with-icu \
            --with-lz4 \
            --with-uuid=ossp \
            --with-system-tzdata=/usr/share/zoneinfo \
            --with-extra-version=" dbi services build"

make -j $(nproc) all
make install
cd contrib
make -j $(nproc) install
[[email protected] postgresql-14.3] $
[[email protected] postgresql-14.3] $ chmod +x compile_from_source.sh
[[email protected] postgresql-14.3] $ ./compile_from_source.sh

Obviously the steps described above have to be performed on all nodes.

Patroni upgrade

Before upgrading Patroni to the latest version, it is important to upgrade pip and setuptools .

[[email protected] ~] $ python3 -m pip install --upgrade pip
[[email protected] ~] $ python3 -m pip install --upgrade setuptools

Then we can upgrade Patroni.

[[email protected] ~] $ patronictl version
patronictl version 2.0.2
[[email protected] ~] $

[[email protected] ~] $ python3 -m pip install --upgrade --user patroni[etcd]
Requirement already satisfied: patroni[etcd] in ./.local/lib/python3.8/site-packages (2.0.2)
Collecting patroni[etcd]
  Downloading patroni-2.1.4-py3-none-any.whl (225 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 225.0/225.0 kB 5.9 MB/s eta 0:00:00
Requirement already satisfied: python-dateutil in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (2.8.1)
Requirement already satisfied: urllib3!=1.21,>=1.19.1 in /usr/lib/python3/dist-packages (from patroni[etcd]) (1.25.8)
Requirement already satisfied: prettytable>=0.7 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (2.1.0)
Requirement already satisfied: ydiff>=1.2.0 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (1.2)
Requirement already satisfied: click>=4.1 in /usr/lib/python3/dist-packages (from patroni[etcd]) (7.0)
Requirement already satisfied: psutil>=2.0.0 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (5.8.0)
Requirement already satisfied: PyYAML in /usr/lib/python3/dist-packages (from patroni[etcd]) (5.3.1)
Requirement already satisfied: six>=1.7 in /usr/lib/python3/dist-packages (from patroni[etcd]) (1.14.0)
Requirement already satisfied: python-etcd<0.5,>=0.4.3 in ./.local/lib/python3.8/site-packages (from patroni[etcd]) (0.4.5)
Requirement already satisfied: wcwidth in ./.local/lib/python3.8/site-packages (from prettytable>=0.7->patroni[etcd]) (0.2.5)
Requirement already satisfied: dnspython>=1.13.0 in ./.local/lib/python3.8/site-packages (from python-etcd<0.5,>=0.4.3->patroni[etcd]) (2.1.0)
Installing collected packages: patroni
  Attempting uninstall: patroni
    Found existing installation: patroni 2.0.2
    Uninstalling patroni-2.0.2:
      Successfully uninstalled patroni-2.0.2
Successfully installed patroni-2.1.4
[[email protected] ~] $

[[email protected] ~] $ patronictl version
patronictl version 2.1.4
[[email protected] ~] $ patroni --version
patroni version 2.1.4

[[email protected] ~] $

Again, we have to do this on all nodes.

New cluster creation

Below steps have to be performed on the Leader node only. The following command can be used to check who is the Leader.

[[email protected] ~] $ patronictl list
+ Cluster: DEMO (6938400030986650439) -----------------------+
| Member  | Host        | Role    | State   | TL | Lag in MB |
+---------+-------------+---------+---------+----+-----------+
| db02-04 | 10.0.148.31 | Leader  | running | 15 |           |
| db02-05 | 10.0.148.32 | Replica | running | 15 |         0 |
| db02-06 | 10.0.148.33 | Replica | running | 15 |         0 |
+---------+-------------+---------+---------+----+-----------+
[[email protected] ~] $

The new 14.3 cluster is created in this directory.

[[email protected]] $ mkdir -p /u02/pgdata/14/PROD

To create it, we use the initdb utility provided by the new PostgreSQL binaries.

[[email protected] ~] $ /u01/app/postgres/product/14/db_3/bin/initdb -D /u02/pgdata/14/PROD
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /u02/pgdata/14/PROD ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
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:

    /u01/app/postgres/product/14/db_3/bin/pg_ctl -D /u02/pgdata/14/PROD -l logfile start

[[email protected] ~] $

Following files have to be taken from the old cluster to the new one.

[[email protected] ~] $ cp /u02/pgdata/96/PROD/pg_hba.conf /u02/pgdata/14/PROD/ 
[[email protected] ~] $ cp /u02/pgdata/96/PROD/patroni.dynamic.json /u02/pgdata/14/PROD/

The file patroni.dynamic.json contains a dump of the DCS options. It will be read during a later stage.

In order to apply our best practices for PostgreSQL 14, the following instance parameters are applied to the new cluster.

[[email protected] ~] $ cat /u02/pgdata/14/PROD/postgresql.conf
listen_addresses = '10.0.148.31'
port=5432
logging_collector = 'on'
log_truncate_on_rotation = 'on'
log_filename = 'postgresql-%a.log'
log_rotation_age = '1440'
log_line_prefix = '%m - %l - %p - %h - %[email protected]%d - %x'
log_directory = 'pg_log'
log_min_messages = 'WARNING'
log_autovacuum_min_duration = '60s'
log_min_error_statement = 'NOTICE'
log_min_duration_statement = '30s'
log_checkpoints = 'on'
log_statement = 'ddl'
log_lock_waits = 'on'
log_temp_files = '0'
log_timezone = 'Europe/Zurich'
log_connections=off
log_disconnections=off
log_duration=off
checkpoint_completion_target=0.9
checkpoint_timeout='5min'
client_min_messages = 'WARNING'
wal_level = 'replica'
hot_standby_feedback = 'on'
max_wal_senders = '10'
cluster_name = 'PROD'
max_replication_slots = '10'
shared_buffers=128MB
work_mem=8MB
effective_cache_size=512MB
maintenance_work_mem=64MB
wal_compression=on
shared_preload_libraries='pg_stat_statements'
autovacuum_max_workers=6
autovacuum_vacuum_scale_factor=0.1
autovacuum_vacuum_threshold=50
autovacuum_vacuum_cost_limit=3000
archive_mode='on'
archive_command='pgbackrest --stanza=PROD archive-push %p'
wal_log_hints='on'
password_encryption='scram-sha-256'
default_toast_compression='lz4'
[[email protected] ~] $

Upgrade

Due to corruption on some data files (invalid page checksums), I was not able to use pg_upgrade to perform the upgrade from 9.6 to 14.3. Therefore, I had no choice to use pg_dumpall to move the data.

[[email protected] ~] $ pg_dumpall -p 5432 -U postgres -l postgres -f /home/postgres/upgrade/dump/prod.dmp

Once done, Patroni can be stopped on all nodes.

[[email protected] ~] $ sudo systemctl stop patroni
[[email protected] ~] $ sudo systemctl stop patroni
[[email protected] ~] $ sudo systemctl stop patroni

Bonus : we use the following systemd service definition for Patroni.

[[email protected] ~] $ cat /etc/systemd/system/patroni.service
#
# systemd integration for patroni
# Put this file under /etc/systemd/system/patroni.service
#     then: systemctl daemon-reload
#     then: systemctl list-unit-files | grep patroni
#     then: systemctl enable patroni.service
#

[Unit]
Description=dbi services patroni service
After=etcd.service syslog.target network.target

[Service]
User=postgres
Group=postgres
Type=simple
ExecStartPre=/usr/bin/sudo /sbin/modprobe softdog
ExecStartPre=/usr/bin/sudo /bin/chown postgres /dev/watchdog
ExecStart=/u01/app/postgres/local/dmk/bin/patroni /u01/app/postgres/local/dmk/etc/patroni.yml
ExecReload=/bin/kill -s HUP $MAINPID
KillMode=process
Restart=no
TimeoutSec=30

[Install]
WantedBy=multi-user.target

[[email protected] ~] $

(Of course, the ExecStart parameter must be adapted to you environment.)

It’s now time to start the new cluster and to import the dump.

[[email protected] ~] $ /u01/app/postgres/product/14/db_3/bin/pg_ctl -D /u02/pgdata/14/PROD -l logfile start

[[email protected] ~] $ /u01/app/postgres/product/14/db_3/bin/psql postgres < /home/postgres/upgrade/dump/prod.dmp

Once the import is done, we must change the parameters data_dir and bin_dir of the Patroni configuration file in order to match to the new cluster.

[[email protected] ~] $ cat /u01/app/postgres/local/dmk/etc/patroni.yml
...
...
...
postgresql:
  listen: 10.0.148.31:5432
  connect_address: 10.0.148.31:5432
  data_dir: /u02/pgdata/14/PROD/
  bin_dir: /u01/app/postgres/product/14/db_3/bin
#  config_dir:
  pgpass: /u01/app/postgres/local/dmk/etc/pgpass0
  authentication:
    replication:
      username: replicator
      password: *****
    superuser:
      username: postgres
      password: *****
  parameters:
    unix_socket_directories: '/tmp'
...
...
...

[[email protected] ~] $

Before restarting Patroni, the previous configuration information must be removed from the DCS.

[[email protected] ~] $ patronictl remove PROD
+ Cluster: PROD (6946441255879209913------------+
| Member | Host | Role | State | TL | Lag in MB |
+--------+------+------+-------+----+-----------+
+--------+------+------+-------+----+-----------+
Please confirm the cluster name to remove: PROD
You are about to remove all information in DCS for PROD, please type: "Yes I am aware": Yes I am aware
[[email protected] ~] 

Then, Patroni can be restarted on all nodes and the replicas will be built automatically on db02-05 and db02-06.

[[email protected] ~] $ sudo systemctl start patroni
[[email protected] ~] $ sudo systemctl start patroni
[[email protected] ~] $ sudo systemctl start patroni

[[email protected] ~] $ patronictl list
+ Cluster: PROD (7109360479587211872) ------+----+-----------+
| Member  | Host        | Role    | State   | TL | Lag in MB |
+---------+-------------+---------+---------+----+-----------+
| db02-04 | 10.0.148.31 | Leader  | running |  2 |           |
| db02-05 | 10.0.148.32 | Replica | running |  2 |         0 |
| db02-06 | 10.0.148.33 | Replica | running |  2 |         0 |
+---------+-------------+---------+---------+----+-----------+
[[email protected] ~] $

That’s it ! The PostgreSQL cluster and Patroni have been successfully upgraded.

Switchover test

An important thing to do on the Patroni side is to test the switchover of the new cluster.

[[email protected] ~] $ patronictl switchover
Master [db02-04]: db02-04
Candidate ['db02-05', 'db02-06'] []: db02-05
When should the switchover take place (e.g. 2022-06-15T14:23 )  [now]: now
Current cluster topology
+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+
| Member  | Host        | Role    | State   | TL | Lag in MB | Pending restart |
+---------+-------------+---------+---------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Leader  | running |  2 |           | *               |
| db02-05 | 10.0.148.32 | Replica | running |  2 |         0 | *               |
| db02-06 | 10.0.148.33 | Replica | running |  2 |         0 | *               |
+---------+-------------+---------+---------+----+-----------+-----------------+
Are you sure you want to switchover cluster PROD, demoting current master db02-04? [y/N]: y
2022-06-15 13:23:28.83611 Successfully switched over to "db02-05"

+ Cluster: PROD (7109360479587211872) ------------+-----------+-----------------+
| Member  | Host        | Role    | State    | TL | Lag in MB | Pending restart |
+---------+-------------+---------+----------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Replica | stopping |    |   unknown | *               |
| db02-05 | 10.0.148.32 | Leader  | running  |  2 |           | *               |
| db02-06 | 10.0.148.33 | Replica | running  |  2 |         0 | *               |
+---------+-------------+---------+----------+----+-----------+-----------------+
[[email protected] ~] $

[[email protected] ~] $ patronictl list
+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+
| Member  | Host        | Role    | State   | TL | Lag in MB | Pending restart |
+---------+-------------+---------+---------+----+-----------+-----------------+
| db02-04 | 10.0.148.31 | Replica | running |  3 |         0 |                 |
| db02-05 | 10.0.148.32 | Leader  | running |  3 |           | *               |
| db02-06 | 10.0.148.33 | Replica | running |  3 |         0 | *               |
+---------+-------------+---------+---------+----+-----------+-----------------+
[[email protected] ~] 

pgBackRest upgrade

Following packages are mandatory before upgrading pgBackRest to version 2.39.

[[email protected] ~] $ sudo apt install libpq-dev libyaml-dev libbz2-dev

I have compiled and installed pgBackRest from the source code.
Once the archive is downloaded and transferred to the server, we have to extract its content.

[[email protected] upgrade] $ unzip -q pgbackrest-release-2.39.zip

[[email protected] upgrade] $ ll pgbackrest-release-2.39
total 80
-rw-r--r--  1 postgres postgres 10374 May 16 12:46 CODING.md
-rw-r--r--  1 postgres postgres 37765 May 16 12:46 CONTRIBUTING.md
drwxr-xr-x  6 postgres postgres  4096 May 16 12:46 doc
-rw-r--r--  1 postgres postgres  1168 May 16 12:46 LICENSE
-rw-r--r--  1 postgres postgres  9607 May 16 12:46 README.md
drwxr-xr-x 11 postgres postgres  4096 May 16 12:46 src
drwxr-xr-x  7 postgres postgres  4096 May 16 12:46 test
[[email protected] upgrade] $

And the the installation can be started.

[[email protected] upgrade] $ cd pgbackrest-release-2.39/src/

[[email protected] src] $ ./configure && make

[[email protected] src] $ sudo mv /usr/bin/pgbackrest /usr/bin/pgbackrest_old
[[email protected] src] $ sudo cp pgbackrest /usr/bin/
[[email protected] src] $ pgbackrest version
pgBackRest 2.39
[[email protected] src] $

The pg1-path parameter of the pgBackRest Stanza configuration must be adapted on each node in order to perform the backups against the new cluster.

[[email protected] ~] $ cat /etc/pgbackrest.conf 
[global]
backup-host=DB02-07
backup-user=postgres
log-level-file=detail

[PROD]
pg1-path=/u02/pgdata/14/PROD
pg1-socket-path=/tmp
pg1-user=postgres
[[email protected] ~] $

The configuration file of the pgBackRest server must be adapted as well.

[[email protected] ~] $ cat /etc/pgbackrest.conf
[global]
repo1-path=/networkshare/pgbackrest
repo1-cipher-pass=IUlCfTExDg1x7WBTsl83rrwINn7eCKRMDyi5SsPHUjj+ywULThyRtCWMd5GVZXR4
repo1-cipher-type=aes-256-cbc
log-level-console=info
log-level-file=debug
compress-level=3
repo1-retention-full=2
repo1-retention-diff=7
repo1-type=cifs
archive-timeout=10000

[PROD]
pg1-path=/u02/pgdata/14/PROD
pg1-port=5432
pg1-host=DB02-04
pg1-socket-path=/tmp
pg1-host-user=postgres
pg1-user=postgres
pg2-path=/u02/pgdata/14/PROD
pg2-port=5432
pg2-host=DB02-05
pg2-socket-path=/tmp
pg2-host-user=postgres
pg2-user=postgres
pg3-path=/u02/pgdata/14/PROD
pg3-port=5432
pg3-host=DB02-06
pg3-socket-path=/tmp
pg3-host-user=postgres
pg3-user=postgres
[[email protected] ~] $

Finally, the Stanza must be upgraded.

[[email protected] ~] $ pgbackrest stanza-upgrade --stanza=PROD

2022-06-15 08:21:35.308 P00   INFO: stanza-upgrade command begin 2.39: --exec-id=34516-c2b709b7 --log-level-console=info --log-level-file=debug --pg1-host=DB02-04 --pg2-host=DB02-05 --pg3-host=DB02-06 --pg1-host-user=postgres --pg2-host-user=postgres --pg3-host-user=postgres --pg1-path=/u02/pgdata/14/PROD --pg2-path=/u02/pgdata/14/PROD --pg3-path=/u02/pgdata/14/PROD --pg1-port=5432 --pg2-port=5432 --pg3-port=5432 --pg1-socket-path=/tmp --pg2-socket-path=/tmp --pg3-socket-path=/tmp --pg1-user=postgres --pg2-user=postgres --pg3-user=postgres --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/networkshare/pgbackrest --repo1-type=cifs --stanza=PROD
2022-06-15 08:21:38.903 P00   INFO: stanza-upgrade for stanza 'PROD' on repo1
2022-06-15 08:21:39.501 P00   INFO: stanza-upgrade command end: completed successfully (4194ms)
[[email protected] ~] $

Hope it helps !