{"id":17632,"date":"2022-06-22T16:03:03","date_gmt":"2022-06-22T14:03:03","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=17632"},"modified":"2022-06-22T16:30:30","modified_gmt":"2022-06-22T14:30:30","slug":"major-postgresql-version-upgrade-in-a-patroni-cluster","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/","title":{"rendered":"Major PostgreSQL version upgrade in a Patroni cluster"},"content":{"rendered":"\n

Introduction<\/h2>\n\n\n\n

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 : <\/p>\n\n\n\n

– 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<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

<\/td>Source version<\/strong><\/td>Target version<\/strong><\/td><\/tr>
PostgreSQL<\/strong><\/td>9.6.18<\/td>14.3<\/td><\/tr>
Patroni<\/strong><\/td>2.0.2<\/td>2.1.4<\/td><\/tr>
pgBackRest<\/strong><\/td>2.24<\/td>2.39<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n


The purpose of this blog post is to explain all the required steps to achieve this.<\/p>\n\n\n\n

PostgreSQL 14.3 installation<\/h2>\n\n\n\n

Before installing PostgreSQL 14.3, it is necessary to install some required packages.<\/p>\n\n\n\n

[postgres@db02-04 ~] $ sudo apt install llvm clang pkg-config liblz4-dev libllvm7 llvm-7-runtime libkrb5-dev libossp-uuid-dev<\/code><\/pre>\n\n\n\n
I’m used to compile and install PostgreSQL from the source code<\/a>. Once the archive is downloaded and transferred to the server, we have to extract its content.<\/p>\n\n\n\n
[postgres@db02-04 upgrade] $ tar -xzf postgresql-14.3.tar.gz\n\n[postgres@db02-04 upgrade] $ ll postgresql-14.3\ntotal 768\n-rw-r--r--  1 postgres postgres    445 May  9 21:14 aclocal.m4\ndrwxr-xr-x  2 postgres postgres   4096 May  9 21:24 config\n-rwxr-xr-x  1 postgres postgres 587897 May  9 21:14 configure\n-rw-r--r--  1 postgres postgres  85458 May  9 21:14 configure.ac\ndrwxr-xr-x 58 postgres postgres   4096 May  9 21:24 contrib\n-rw-r--r--  1 postgres postgres   1192 May  9 21:14 COPYRIGHT\ndrwxr-xr-x  3 postgres postgres   4096 May  9 21:24 doc\n-rw-r--r--  1 postgres postgres   4259 May  9 21:14 GNUmakefile.in\n-rw-r--r--  1 postgres postgres    277 May  9 21:14 HISTORY\n-rw-r--r--  1 postgres postgres  63944 May  9 21:25 INSTALL\n-rw-r--r--  1 postgres postgres   1665 May  9 21:14 Makefile\n-rw-r--r--  1 postgres postgres   1213 May  9 21:14 README\ndrwxr-xr-x 16 postgres postgres   4096 May  9 21:25 src\n[postgres@db02-04 upgrade] $<\/code><\/pre>\n\n\n\n
Then the directory where the binaries will be installed must be created.<\/p>\n\n\n\n
[postgres@db02-04 upgrade] $ mkdir -p \/u01\/app\/postgres\/product\/14\/db_3<\/code><\/pre>\n\n\n\n
Our standard when installing PostgreSQL from the sources is to create and execute a shell script which will automatically compile and install the binaries.<\/p>\n\n\n\n
[postgres@db02-04 postgresql-14.3] $ cat compile_from_source.sh\n#!\/bin\/bash\n\nPGHOME=\/u01\/app\/postgres\/product\/14\/db_3\nSEGSIZE=2\nBLOCKSIZE=8\n\n.\/configure --prefix=${PGHOME} \\\n            --exec-prefix=${PGHOME} \\\n            --bindir=${PGHOME}\/bin \\\n            --libdir=${PGHOME}\/lib \\\n            --sysconfdir=${PGHOME}\/etc \\\n            --includedir=${PGHOME}\/include \\\n            --datarootdir=${PGHOME}\/share \\\n            --datadir=${PGHOME}\/share \\\n            --with-pgport=5432 \\\n            --with-perl \\\n            --with-python \\\n            --with-openssl \\\n            --with-pam \\\n            --with-ldap \\\n            --with-libxml \\\n            --with-llvm \\\n            --with-libxslt \\\n            --with-segsize=${SEGSIZE} \\\n            --with-blocksize=${BLOCKSIZE} \\\n            --with-systemd \\\n            --with-gssapi \\\n            --with-icu \\\n            --with-lz4 \\\n            --with-uuid=ossp \\\n            --with-system-tzdata=\/usr\/share\/zoneinfo \\\n            --with-extra-version=\" dbi services build\"\n\nmake -j $(nproc) all\nmake install\ncd contrib\nmake -j $(nproc) install\n[postgres@db02-04 postgresql-14.3] $<\/code><\/pre>\n\n\n\n
[postgres@db02-04 postgresql-14.3] $ chmod +x compile_from_source.sh\n[postgres@db02-04 postgresql-14.3] $ .\/compile_from_source.sh<\/code><\/pre>\n\n\n\n
Obviously the steps described above have to be performed on all nodes.<\/p>\n\n\n\n
Patroni upgrade<\/h2>\n\n\n\n
Before upgrading Patroni to the latest version, it is important to upgrade pip<\/em> and setuptools <\/em>.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ python3 -m pip install --upgrade pip\n[postgres@db02-04 ~] $ python3 -m pip install --upgrade setuptools<\/code><\/pre>\n\n\n\n
Then we can upgrade Patroni.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ patronictl version\npatronictl version 2.0.2\n[postgres@db02-04 ~] $\n\n[postgres@db02-04 ~] $ python3 -m pip install --upgrade --user patroni[etcd]\nRequirement already satisfied: patroni[etcd] in .\/.local\/lib\/python3.8\/site-packages (2.0.2)\nCollecting patroni[etcd]\n  Downloading patroni-2.1.4-py3-none-any.whl (225 kB)\n     \u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501\u2501 225.0\/225.0 kB 5.9 MB\/s eta 0:00:00\nRequirement already satisfied: python-dateutil in .\/.local\/lib\/python3.8\/site-packages (from patroni[etcd]) (2.8.1)\nRequirement already satisfied: urllib3!=1.21,>=1.19.1 in \/usr\/lib\/python3\/dist-packages (from patroni[etcd]) (1.25.8)\nRequirement already satisfied: prettytable>=0.7 in .\/.local\/lib\/python3.8\/site-packages (from patroni[etcd]) (2.1.0)\nRequirement already satisfied: ydiff>=1.2.0 in .\/.local\/lib\/python3.8\/site-packages (from patroni[etcd]) (1.2)\nRequirement already satisfied: click>=4.1 in \/usr\/lib\/python3\/dist-packages (from patroni[etcd]) (7.0)\nRequirement already satisfied: psutil>=2.0.0 in .\/.local\/lib\/python3.8\/site-packages (from patroni[etcd]) (5.8.0)\nRequirement already satisfied: PyYAML in \/usr\/lib\/python3\/dist-packages (from patroni[etcd]) (5.3.1)\nRequirement already satisfied: six>=1.7 in \/usr\/lib\/python3\/dist-packages (from patroni[etcd]) (1.14.0)\nRequirement already satisfied: python-etcd<0.5,>=0.4.3 in .\/.local\/lib\/python3.8\/site-packages (from patroni[etcd]) (0.4.5)\nRequirement already satisfied: wcwidth in .\/.local\/lib\/python3.8\/site-packages (from prettytable>=0.7->patroni[etcd]) (0.2.5)\nRequirement 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)\nInstalling collected packages: patroni\n  Attempting uninstall: patroni\n    Found existing installation: patroni 2.0.2\n    Uninstalling patroni-2.0.2:\n      Successfully uninstalled patroni-2.0.2\nSuccessfully installed patroni-2.1.4\n[postgres@db02-04 ~] $\n\n[postgres@db02-04 ~] $ patronictl version\npatronictl version 2.1.4\n[postgres@db02-04 ~] $ patroni --version\npatroni version 2.1.4\n\n[postgres@db02-04 ~] $\n<\/code><\/pre>\n\n\n\n
Again, we have to do this on all nodes.<\/p>\n\n\n\n
New cluster creation<\/h2>\n\n\n\n
Below steps have to be performed on the Leader node only. The following command can be used to check who is the Leader.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ patronictl list\n+ Cluster: DEMO (6938400030986650439) -----------------------+\n| Member  | Host        | Role    | State   | TL | Lag in MB |\n+---------+-------------+---------+---------+----+-----------+\n| db02-04 | 10.0.148.31 | Leader  | running | 15 |           |\n| db02-05 | 10.0.148.32 | Replica | running | 15 |         0 |\n| db02-06 | 10.0.148.33 | Replica | running | 15 |         0 |\n+---------+-------------+---------+---------+----+-----------+\n[postgres@db02-04 ~] $\n<\/code><\/pre>\n\n\n\n
The new 14.3 cluster is created in this directory.<\/p>\n\n\n\n
[postgres@db02-04] $ mkdir -p \/u02\/pgdata\/14\/PROD<\/code><\/pre>\n\n\n\n
To create it, we use the initdb<\/em> utility provided by the new PostgreSQL binaries.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ \/u01\/app\/postgres\/product\/14\/db_3\/bin\/initdb -D \/u02\/pgdata\/14\/PROD\nThe files belonging to this database system will be owned by user \"postgres\".\nThis user must also own the server process.\n\nThe database cluster will be initialized with locale \"en_US.UTF-8\".\nThe default database encoding has accordingly been set to \"UTF8\".\nThe default text search configuration will be set to \"english\".\n\nData page checksums are disabled.\n\nfixing permissions on existing directory \/u02\/pgdata\/14\/PROD ... ok\ncreating subdirectories ... ok\nselecting dynamic shared memory implementation ... posix\nselecting default max_connections ... 100\nselecting default shared_buffers ... 128MB\nselecting default time zone ... Etc\/UTC\ncreating configuration files ... ok\nrunning bootstrap script ... ok\nperforming post-bootstrap initialization ... ok\nsyncing data to disk ... ok\n\ninitdb: warning: enabling \"trust\" authentication for local connections\nYou can change this by editing pg_hba.conf or using the option -A, or\n--auth-local and --auth-host, the next time you run initdb.\n\nSuccess. You can now start the database server using:\n\n    \/u01\/app\/postgres\/product\/14\/db_3\/bin\/pg_ctl -D \/u02\/pgdata\/14\/PROD -l logfile start\n\n[postgres@db02-04 ~] $<\/code><\/pre>\n\n\n\n
Following files have to be taken from the old cluster to the new one.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ cp \/u02\/pgdata\/96\/PROD\/pg_hba.conf \/u02\/pgdata\/14\/PROD\/ \n[postgres@db02-04 ~] $ cp \/u02\/pgdata\/96\/PROD\/patroni.dynamic.json \/u02\/pgdata\/14\/PROD\/<\/code><\/pre>\n\n\n\n
The file patroni.dynamic.json<\/em> contains a dump of the DCS options. It will be read during a later stage.<\/p>\n\n\n\n
In order to apply our best practices for PostgreSQL 14, the following instance parameters are applied to the new cluster.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ cat \/u02\/pgdata\/14\/PROD\/postgresql.conf\nlisten_addresses = '10.0.148.31'\nport=5432\nlogging_collector = 'on'\nlog_truncate_on_rotation = 'on'\nlog_filename = 'postgresql-%a.log'\nlog_rotation_age = '1440'\nlog_line_prefix = '%m - %l - %p - %h - %u@%d - %x'\nlog_directory = 'pg_log'\nlog_min_messages = 'WARNING'\nlog_autovacuum_min_duration = '60s'\nlog_min_error_statement = 'NOTICE'\nlog_min_duration_statement = '30s'\nlog_checkpoints = 'on'\nlog_statement = 'ddl'\nlog_lock_waits = 'on'\nlog_temp_files = '0'\nlog_timezone = 'Europe\/Zurich'\nlog_connections=off\nlog_disconnections=off\nlog_duration=off\ncheckpoint_completion_target=0.9\ncheckpoint_timeout='5min'\nclient_min_messages = 'WARNING'\nwal_level = 'replica'\nhot_standby_feedback = 'on'\nmax_wal_senders = '10'\ncluster_name = 'PROD'\nmax_replication_slots = '10'\nshared_buffers=128MB\nwork_mem=8MB\neffective_cache_size=512MB\nmaintenance_work_mem=64MB\nwal_compression=on\nshared_preload_libraries='pg_stat_statements'\nautovacuum_max_workers=6\nautovacuum_vacuum_scale_factor=0.1\nautovacuum_vacuum_threshold=50\nautovacuum_vacuum_cost_limit=3000\narchive_mode='on'\narchive_command='pgbackrest --stanza=PROD archive-push %p'\nwal_log_hints='on'\npassword_encryption='scram-sha-256'\ndefault_toast_compression='lz4'\n[postgres@db02-04 ~] $<\/code><\/pre>\n\n\n\n
Upgrade<\/h2>\n\n\n\n
Due to corruption on  some data files (invalid page checksums), I was not able to use pg_upgrade<\/a><\/em> to perform the upgrade from 9.6 to 14.3.  Therefore, I had no choice to use pg_dumpall<\/a><\/em> to move the data.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ pg_dumpall -p 5432 -U postgres -l postgres -f \/home\/postgres\/upgrade\/dump\/prod.dmp<\/code><\/pre>\n\n\n\n
Once done, Patroni can be stopped on all nodes.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ sudo systemctl stop patroni\n[postgres@db02-05 ~] $ sudo systemctl stop patroni\n[postgres@db02-06 ~] $ sudo systemctl stop patroni<\/code><\/pre>\n\n\n\n
Bonus : we use the following systemd service definition for Patroni.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ cat \/etc\/systemd\/system\/patroni.service\n#\n# systemd integration for patroni\n# Put this file under \/etc\/systemd\/system\/patroni.service\n#     then: systemctl daemon-reload\n#     then: systemctl list-unit-files | grep patroni\n#     then: systemctl enable patroni.service\n#\n\n[Unit]\nDescription=dbi services patroni service\nAfter=etcd.service syslog.target network.target\n\n[Service]\nUser=postgres\nGroup=postgres\nType=simple\nExecStartPre=\/usr\/bin\/sudo \/sbin\/modprobe softdog\nExecStartPre=\/usr\/bin\/sudo \/bin\/chown postgres \/dev\/watchdog\nExecStart=\/u01\/app\/postgres\/local\/dmk\/bin\/patroni \/u01\/app\/postgres\/local\/dmk\/etc\/patroni.yml\nExecReload=\/bin\/kill -s HUP $MAINPID\nKillMode=process\nRestart=no\nTimeoutSec=30\n\n[Install]\nWantedBy=multi-user.target\n\n[postgres@db02-04 ~] $<\/code><\/pre>\n\n\n\n
(Of course, the ExecStart<\/em> parameter must be adapted to you environment.)<\/p>\n\n\n\n
It’s now time to start the new cluster and to import the dump.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ \/u01\/app\/postgres\/product\/14\/db_3\/bin\/pg_ctl -D \/u02\/pgdata\/14\/PROD -l logfile start\n\n[postgres@db02-04 ~] $ \/u01\/app\/postgres\/product\/14\/db_3\/bin\/psql postgres < \/home\/postgres\/upgrade\/dump\/prod.dmp<\/code><\/pre>\n\n\n\n
Once the import is done, we must change the parameters data_dir<\/em> and bin_dir<\/em> of the Patroni configuration file in order to match to the new cluster.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ cat \/u01\/app\/postgres\/local\/dmk\/etc\/patroni.yml\n...\n...\n...\npostgresql:\n  listen: 10.0.148.31:5432\n  connect_address: 10.0.148.31:5432\n  data_dir: \/u02\/pgdata\/14\/PROD\/\n  bin_dir: \/u01\/app\/postgres\/product\/14\/db_3\/bin\n#  config_dir:\n  pgpass: \/u01\/app\/postgres\/local\/dmk\/etc\/pgpass0\n  authentication:\n    replication:\n      username: replicator\n      password: *****\n    superuser:\n      username: postgres\n      password: *****\n  parameters:\n    unix_socket_directories: '\/tmp'\n...\n...\n...\n\n[postgres@db02-04 ~] $<\/code><\/pre>\n\n\n\n
Before restarting Patroni, the previous configuration information must be removed from the DCS.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ patronictl remove PROD\n+ Cluster: PROD (6946441255879209913------------+\n| Member | Host | Role | State | TL | Lag in MB |\n+--------+------+------+-------+----+-----------+\n+--------+------+------+-------+----+-----------+\nPlease confirm the cluster name to remove: PROD\nYou are about to remove all information in DCS for PROD, please type: \"Yes I am aware\": Yes I am aware\n[postgres@db02-04 ~] \n<\/code><\/pre>\n\n\n\n
Then, Patroni can be restarted on all nodes and the replicas will be built automatically on db02-05 and db02-06.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ sudo systemctl start patroni\n[postgres@db02-05 ~] $ sudo systemctl start patroni\n[postgres@db02-06 ~] $ sudo systemctl start patroni\n\n[postgres@db02-04 ~] $ patronictl list\n+ Cluster: PROD (7109360479587211872) ------+----+-----------+\n| Member  | Host        | Role    | State   | TL | Lag in MB |\n+---------+-------------+---------+---------+----+-----------+\n| db02-04 | 10.0.148.31 | Leader  | running |  2 |           |\n| db02-05 | 10.0.148.32 | Replica | running |  2 |         0 |\n| db02-06 | 10.0.148.33 | Replica | running |  2 |         0 |\n+---------+-------------+---------+---------+----+-----------+\n[postgres@db02-04 ~] $<\/code><\/pre>\n\n\n\n
That’s it ! The PostgreSQL cluster and Patroni have been successfully upgraded.<\/p>\n\n\n\n
Switchover test<\/h2>\n\n\n\n
An important thing to do on the Patroni side is to test the switchover of the new cluster.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ patronictl switchover\nMaster [db02-04]: db02-04\nCandidate ['db02-05', 'db02-06'] []: db02-05\nWhen should the switchover take place (e.g. 2022-06-15T14:23 )  [now]: now\nCurrent cluster topology\n+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+\n| Member  | Host        | Role    | State   | TL | Lag in MB | Pending restart |\n+---------+-------------+---------+---------+----+-----------+-----------------+\n| db02-04 | 10.0.148.31 | Leader  | running |  2 |           | *               |\n| db02-05 | 10.0.148.32 | Replica | running |  2 |         0 | *               |\n| db02-06 | 10.0.148.33 | Replica | running |  2 |         0 | *               |\n+---------+-------------+---------+---------+----+-----------+-----------------+\nAre you sure you want to switchover cluster PROD, demoting current master db02-04? [y\/N]: y\n2022-06-15 13:23:28.83611 Successfully switched over to \"db02-05\"\n\n+ Cluster: PROD (7109360479587211872) ------------+-----------+-----------------+\n| Member  | Host        | Role    | State    | TL | Lag in MB | Pending restart |\n+---------+-------------+---------+----------+----+-----------+-----------------+\n| db02-04 | 10.0.148.31 | Replica | stopping |    |   unknown | *               |\n| db02-05 | 10.0.148.32 | Leader  | running  |  2 |           | *               |\n| db02-06 | 10.0.148.33 | Replica | running  |  2 |         0 | *               |\n+---------+-------------+---------+----------+----+-----------+-----------------+\n[postgres@db02-04 ~] $\n\n[postgres@db02-04 ~] $ patronictl list\n+ Cluster: PROD (7109360479587211872) -----------+-----------+-----------------+\n| Member  | Host        | Role    | State   | TL | Lag in MB | Pending restart |\n+---------+-------------+---------+---------+----+-----------+-----------------+\n| db02-04 | 10.0.148.31 | Replica | running |  3 |         0 |                 |\n| db02-05 | 10.0.148.32 | Leader  | running |  3 |           | *               |\n| db02-06 | 10.0.148.33 | Replica | running |  3 |         0 | *               |\n+---------+-------------+---------+---------+----+-----------+-----------------+\n[postgres@db02-04 ~] <\/code><\/pre>\n\n\n\n
pgBackRest upgrade<\/h2>\n\n\n\n
Following packages are mandatory before upgrading pgBackRest to version 2.39.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ sudo apt install libpq-dev libyaml-dev libbz2-dev<\/code><\/pre>\n\n\n\n
I have compiled and installed pgBackRest from the source code<\/a>. 
Once the archive is downloaded and transferred to the server, we have to extract its content.<\/p>\n\n\n\n
[postgres@db02-04 upgrade] $ unzip -q pgbackrest-release-2.39.zip\n\n[postgres@db02-04 upgrade] $ ll pgbackrest-release-2.39\ntotal 80\n-rw-r--r--  1 postgres postgres 10374 May 16 12:46 CODING.md\n-rw-r--r--  1 postgres postgres 37765 May 16 12:46 CONTRIBUTING.md\ndrwxr-xr-x  6 postgres postgres  4096 May 16 12:46 doc\n-rw-r--r--  1 postgres postgres  1168 May 16 12:46 LICENSE\n-rw-r--r--  1 postgres postgres  9607 May 16 12:46 README.md\ndrwxr-xr-x 11 postgres postgres  4096 May 16 12:46 src\ndrwxr-xr-x  7 postgres postgres  4096 May 16 12:46 test\n[postgres@db02-04 upgrade] $<\/code><\/pre>\n\n\n\n
And the the installation can be started.<\/p>\n\n\n\n
[postgres@db02-04 upgrade] $ cd pgbackrest-release-2.39\/src\/\n\n[postgres@db02-04 src] $ .\/configure && make\n\n[postgres@db02-04 src] $ sudo mv \/usr\/bin\/pgbackrest \/usr\/bin\/pgbackrest_old\n[postgres@db02-04 src] $ sudo cp pgbackrest \/usr\/bin\/\n[postgres@db02-04 src] $ pgbackrest version\npgBackRest 2.39\n[postgres@db02-04 src] $\n<\/code><\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
[postgres@db02-04 ~] $ cat \/etc\/pgbackrest.conf \n[global]\nbackup-host=DB02-07\nbackup-user=postgres\nlog-level-file=detail\n\n[PROD]\npg1-path=\/u02\/pgdata\/14\/PROD\npg1-socket-path=\/tmp\npg1-user=postgres\n[postgres@db02-04 ~] $\n<\/code><\/pre>\n\n\n\n
The configuration file of the pgBackRest server must be adapted as well.<\/p>\n\n\n\n
[postgres@db02-07 ~] $ cat \/etc\/pgbackrest.conf\n[global]\nrepo1-path=\/networkshare\/pgbackrest\nrepo1-cipher-pass=IUlCfTExDg1x7WBTsl83rrwINn7eCKRMDyi5SsPHUjj+ywULThyRtCWMd5GVZXR4\nrepo1-cipher-type=aes-256-cbc\nlog-level-console=info\nlog-level-file=debug\ncompress-level=3\nrepo1-retention-full=2\nrepo1-retention-diff=7\nrepo1-type=cifs\narchive-timeout=10000\n\n[PROD]\npg1-path=\/u02\/pgdata\/14\/PROD\npg1-port=5432\npg1-host=DB02-04\npg1-socket-path=\/tmp\npg1-host-user=postgres\npg1-user=postgres\npg2-path=\/u02\/pgdata\/14\/PROD\npg2-port=5432\npg2-host=DB02-05\npg2-socket-path=\/tmp\npg2-host-user=postgres\npg2-user=postgres\npg3-path=\/u02\/pgdata\/14\/PROD\npg3-port=5432\npg3-host=DB02-06\npg3-socket-path=\/tmp\npg3-host-user=postgres\npg3-user=postgres\n[postgres@db02-07 ~] $\n<\/code><\/pre>\n\n\n\n
Finally, the Stanza must be upgraded.<\/p>\n\n\n\n
[postgres@db02-07 ~] $ pgbackrest stanza-upgrade --stanza=PROD\n\n2022-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\n2022-06-15 08:21:38.903 P00   INFO: stanza-upgrade for stanza 'PROD' on repo1\n2022-06-15 08:21:39.501 P00   INFO: stanza-upgrade command end: completed successfully (4194ms)\n[postgres@db02-07 ~] $\n<\/code><\/pre>\n\n\n\n
Hope it helps !<\/h2>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
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 – […]<\/p>\n","protected":false},"author":30,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[83],"tags":[1543,570,2602,2601],"type_dbi":[],"class_list":["post-17632","post","type-post","status-publish","format-standard","hentry","category-postgresql","tag-patroni","tag-postgres","tag-postgresql-2","tag-upgrade-2"],"acf":[],"yoast_head":"\nMajor PostgreSQL version upgrade in a Patroni cluster - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Major PostgreSQL version upgrade in a Patroni cluster\" \/>\n<meta property=\"og:description\" content=\"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 – […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-06-22T14:03:03+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-06-22T14:30:30+00:00\" \/>\n<meta name=\"author\" content=\"Jo\u00ebl Cattin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jo\u00ebl Cattin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/\"},\"author\":{\"name\":\"Jo\u00ebl Cattin\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2c774f00321ee734515f0c2f6a96b780\"},\"headline\":\"Major PostgreSQL version upgrade in a Patroni cluster\",\"datePublished\":\"2022-06-22T14:03:03+00:00\",\"dateModified\":\"2022-06-22T14:30:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/\"},\"wordCount\":607,\"commentCount\":0,\"keywords\":[\"Patroni\",\"postgres\",\"postgresql\",\"upgrade\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/\",\"name\":\"Major PostgreSQL version upgrade in a Patroni cluster - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-06-22T14:03:03+00:00\",\"dateModified\":\"2022-06-22T14:30:30+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2c774f00321ee734515f0c2f6a96b780\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/major-postgresql-version-upgrade-in-a-patroni-cluster\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Major PostgreSQL version upgrade in a Patroni cluster\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/2c774f00321ee734515f0c2f6a96b780\",\"name\":\"Jo\u00ebl Cattin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g\",\"caption\":\"Jo\u00ebl Cattin\"},\"description\":\"Jo\u00ebl Cattin has more than three years of experience in databases management. He is specialized in Oracle solutions such as Data Guard and RMAN and has a good background knowledge of Oracle Database Appliance (ODA), Real Application Cluster (RAC) and applications development on APEX. Jo\u00ebl Cattin\u2019s experience includes other RDBMS, such as PostgreSQL and MySQL. He is Oracle Database 12c Administrator Certified Professional, EDB Postgres Advanced Server 9.5 Certified Professional, RedHat Certified System Administrator and ITILv3 Foundation for Service Management Certified. Jo\u00ebl Cattin holds a degree from the \u00c9cole Sup\u00e9rieure d\u2019Informatique de Gestion (ESIG) in Del\u00e9mont and a Federal Certificate of Proficiency in Computer Science (Certificat f\u00e9d\u00e9ral de Capacit\u00e9 \u2013 CFC).\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/joel-cattin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Major PostgreSQL version upgrade in a Patroni cluster - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/","og_locale":"en_US","og_type":"article","og_title":"Major PostgreSQL version upgrade in a Patroni cluster","og_description":"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 – […]","og_url":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/","og_site_name":"dbi Blog","article_published_time":"2022-06-22T14:03:03+00:00","article_modified_time":"2022-06-22T14:30:30+00:00","author":"Jo\u00ebl Cattin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jo\u00ebl Cattin","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/"},"author":{"name":"Jo\u00ebl Cattin","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780"},"headline":"Major PostgreSQL version upgrade in a Patroni cluster","datePublished":"2022-06-22T14:03:03+00:00","dateModified":"2022-06-22T14:30:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/"},"wordCount":607,"commentCount":0,"keywords":["Patroni","postgres","postgresql","upgrade"],"articleSection":["PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/","url":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/","name":"Major PostgreSQL version upgrade in a Patroni cluster - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-06-22T14:03:03+00:00","dateModified":"2022-06-22T14:30:30+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/major-postgresql-version-upgrade-in-a-patroni-cluster\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Major PostgreSQL version upgrade in a Patroni cluster"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/2c774f00321ee734515f0c2f6a96b780","name":"Jo\u00ebl Cattin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4271811924694263d4de5a469f8bd4a90b14d3d90e6ad819b9e2e5ac035a2dc?s=96&d=mm&r=g","caption":"Jo\u00ebl Cattin"},"description":"Jo\u00ebl Cattin has more than three years of experience in databases management. He is specialized in Oracle solutions such as Data Guard and RMAN and has a good background knowledge of Oracle Database Appliance (ODA), Real Application Cluster (RAC) and applications development on APEX. Jo\u00ebl Cattin\u2019s experience includes other RDBMS, such as PostgreSQL and MySQL. He is Oracle Database 12c Administrator Certified Professional, EDB Postgres Advanced Server 9.5 Certified Professional, RedHat Certified System Administrator and ITILv3 Foundation for Service Management Certified. Jo\u00ebl Cattin holds a degree from the \u00c9cole Sup\u00e9rieure d\u2019Informatique de Gestion (ESIG) in Del\u00e9mont and a Federal Certificate of Proficiency in Computer Science (Certificat f\u00e9d\u00e9ral de Capacit\u00e9 \u2013 CFC).","url":"https:\/\/www.dbi-services.com\/blog\/author\/joel-cattin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17632","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/30"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17632"}],"version-history":[{"count":52,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17632\/revisions"}],"predecessor-version":[{"id":17726,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17632\/revisions\/17726"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17632"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17632"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17632"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=17632"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}