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.

[postgres@db02-04 ~] $ 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.

[postgres@db02-04 upgrade] $ tar -xzf postgresql-14.3.tar.gz

[postgres@db02-04 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
[postgres@db02-04 upgrade] $

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

[postgres@db02-04 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.

[postgres@db02-04 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
[postgres@db02-04 postgresql-14.3] $
[postgres@db02-04 postgresql-14.3] $ chmod +x compile_from_source.sh
[postgres@db02-04 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 .

[postgres@db02-04 ~] $ python3 -m pip install --upgrade pip
[postgres@db02-04 ~] $ python3 -m pip install --upgrade setuptools

Then we can upgrade Patroni.

[postgres@db02-04 ~] $ patronictl version
patronictl version 2.0.2
[postgres@db02-04 ~] $

[postgres@db02-04 ~] $ 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
[postgres@db02-04 ~] $

[postgres@db02-04 ~] $ patronictl version
patronictl version 2.1.4
[postgres@db02-04 ~] $ patroni --version
patroni version 2.1.4

[postgres@db02-04 ~] $

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.

[postgres@db02-04 ~] $ 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 |
+---------+-------------+---------+---------+----+-----------+
[postgres@db02-04 ~] $

The new 14.3 cluster is created in this directory.

[postgres@db02-04] $ mkdir -p /u02/pgdata/14/PROD

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

[postgres@db02-04 ~] $ /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

[postgres@db02-04 ~] $

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

[postgres@db02-04 ~] $ cp /u02/pgdata/96/PROD/pg_hba.conf /u02/pgdata/14/PROD/ 
[postgres@db02-04 ~] $ 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.

[postgres@db02-04 ~] $ 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 - %u@%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'
[postgres@db02-04 ~] $

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.

[postgres@db02-04 ~] $ pg_dumpall -p 5432 -U postgres -l postgres -f /home/postgres/upgrade/dump/prod.dmp

Once done, Patroni can be stopped on all nodes.

[postgres@db02-04 ~] $ sudo systemctl stop patroni
[postgres@db02-05 ~] $ sudo systemctl stop patroni
[postgres@db02-06 ~] $ sudo systemctl stop patroni

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

[postgres@db02-04 ~] $ 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

[postgres@db02-04 ~] $

(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.

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

[postgres@db02-04 ~] $ /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.

[postgres@db02-04 ~] $ 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'
...
...
...

[postgres@db02-04 ~] $

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

[postgres@db02-04 ~] $ 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
[postgres@db02-04 ~]

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

[postgres@db02-04 ~] $ sudo systemctl start patroni
[postgres@db02-05 ~] $ sudo systemctl start patroni
[postgres@db02-06 ~] $ sudo systemctl start patroni

[postgres@db02-04 ~] $ 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 |
+---------+-------------+---------+---------+----+-----------+
[postgres@db02-04 ~] $

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.

[postgres@db02-04 ~] $ 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 | *               |
+---------+-------------+---------+----------+----+-----------+-----------------+
[postgres@db02-04 ~] $

[postgres@db02-04 ~] $ 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 | *               |
+---------+-------------+---------+---------+----+-----------+-----------------+
[postgres@db02-04 ~]

pgBackRest upgrade

Following packages are mandatory before upgrading pgBackRest to version 2.39.

[postgres@db02-04 ~] $ 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.

[postgres@db02-04 upgrade] $ unzip -q pgbackrest-release-2.39.zip

[postgres@db02-04 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
[postgres@db02-04 upgrade] $

And the the installation can be started.

[postgres@db02-04 upgrade] $ cd pgbackrest-release-2.39/src/

[postgres@db02-04 src] $ ./configure && make

[postgres@db02-04 src] $ sudo mv /usr/bin/pgbackrest /usr/bin/pgbackrest_old
[postgres@db02-04 src] $ sudo cp pgbackrest /usr/bin/
[postgres@db02-04 src] $ pgbackrest version
pgBackRest 2.39
[postgres@db02-04 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.

[postgres@db02-04 ~] $ 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
[postgres@db02-04 ~] $

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

[postgres@db02-07 ~] $ 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
[postgres@db02-07 ~] $

Finally, the Stanza must be upgraded.

[postgres@db02-07 ~] $ 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)
[postgres@db02-07 ~] $

Hope it helps !

Post Views: 5,650
Thumbnail [60x60]
by
Joël Cattin