How to Upgrade PostgreSQL, PostGIS and Patroni in Offline Environment

Upgrade Your PostgreSQL Database: Major Steps and Minor Touches

In this blog post, I will show how a complete PostgreSQL environment major update can be done. Minor upgrades are not a tough task; they are no different from a standard Linux package upgrade. As my production environment serves in an airgapped-environment, I will complete all the major upgrade with no internet connection. I’ve downloaded all the required whl and .rpm packages before upgrade.

Before starting, A short description of major and minor Postgresql upgrades are provided below:

Major Upgrade: Upgrading PostgreSQL to a new major version, such as moving from 10 to 11 or 12 to 13. Major upgrades bring significant changes, introduce new features, and may require thorough testing and planning due to potential incompatibilities. Minor Upgrade: Updating PostgreSQL to a newer minor version within the same major release, like going from 13.1 to 13.2 or 12.5 to 12.6. Minor upgrades focus on bug fixes, security patches, and performance enhancements, typically requiring less planning and downtime than major upgrades.

I will not only perform a PostgreSQL software update, but also update the following extensions: Patroni, pgbackrest, PostGIS, pg_cron, and Oracle_FDW.

My current environment is:

• 2 node patroni cluster – current release – 3.0.1
• Postgresql software – current release – 14.2
• Postgis – current release – 3.2.7
• Pgbackrest – current release – 2.44
• Oracle_fdw – current release – 2.5.0
• Pg_cron – current release – 1.4.2

I will upgrade to :

• Patroni – target release – 3.3.0
• Postgresql software – target release – 16.2
• Postgis – target release – 3.4.2
• Pgbackrest – target release – 2.51
• Oracle_fdw – target release – 2.6.0
• Pg_cron – target release – 1.6.2

First i will upgrade current patroni release on both servers. I have installed patroni with pip3 before and will use pip3 for update.

Here it is.

[root@posvt01 Packages]# pip3 show patroni
  Name: patroni
  Version: 3.0.1
  Summary: PostgreSQL High-Available orchestrator and CLI
  Home-page: https://github.com/zalando/patroni
  Author: Alexander Kukushkin, Polina Bungina
  Author-email: akukushkin@microsoft.com, polina.bungina@zalando.de
  License: The MIT License
  Location: /usr/local/lib/python3.9/site-packages
  Requires: python-dateutil, psutil, ydiff, six, PyYAML, urllib3, click, prettytable
  Required-by: 

[root@posvt01 patroni3.3]# pip3 install --upgrade ./patroni-3.3.0-py3-none-any.whl 
  WARNING: Running pip install with root privileges is generally not a good idea. Try `pip3 install --user` instead.
  Processing ./patroni-3.3.0-py3-none-any.whl
  Requirement already satisfied, skipping upgrade: urllib3!=1.21,>=1.19.1 in /usr/local/lib/python3.9/site-packages (from patroni==3.3.0) (1.26.14)
  Requirement already satisfied, skipping upgrade: PyYAML in /usr/local/lib64/python3.9/site-packages (from patroni==3.3.0) (6.0)
  Requirement already satisfied, skipping upgrade: python-dateutil in /usr/local/lib/python3.9/site-packages (from patroni==3.3.0) (2.8.2)
  Requirement already satisfied, skipping upgrade: psutil>=2.0.0 in /usr/local/lib64/python3.9/site-packages (from patroni==3.3.0) (5.9.4)
  Requirement already satisfied, skipping upgrade: click>=4.1 in /usr/local/lib/python3.9/site-packages (from patroni==3.3.0) (8.1.3)
  Requirement already satisfied, skipping upgrade: ydiff>=1.2.0 in /usr/local/lib/python3.9/site-packages (from patroni==3.3.0) (1.2)
  Requirement already satisfied, skipping upgrade: prettytable>=0.7 in /usr/local/lib/python3.9/site-packages (from patroni==3.3.0) (3.6.0)
  Requirement already satisfied, skipping upgrade: six>=1.5 in /usr/local/lib/python3.9/site-packages (from python-dateutil->patroni==3.3.0) (1.16.0)
  Requirement already satisfied, skipping upgrade: wcwidth in /usr/local/lib/python3.9/site-packages (from prettytable>=0.7->patroni==3.3.0) (0.2.6)
  Installing collected packages: patroni
    Attempting uninstall: patroni
      Found existing installation: patroni 3.0.1
      Uninstalling patroni-3.0.1:
        Successfully uninstalled patroni-3.0.1
  Successfully installed patroni-3.3.0

[postgres@posvt01 ~]$  pip3 show patroni
  Name: patroni
  Version: 3.3.0
  Summary: PostgreSQL High-Available orchestrator and CLI
  Home-page: https://github.com/zalando/patroni
  Author: Alexander Kukushkin, Polina Bungina
  Author-email: akukushkin@microsoft.com, polina.bungina@zalando.de
  License: The MIT License
  Location: /usr/local/lib/python3.9/site-packages
  Requires: ydiff, psutil, python-dateutil, prettytable, click, PyYAML, urllib3
  Required-by: 

Upgrading pgbackrest is similar with a standard Linux package upgrade.

Prior to installing the new PostgreSQL release, I’ll update the current PostGIS version to the latest one. PostGIS updates can be a bit complicated. Since two different GDAL libraries cannot be installed simultaneously, I utilized the –allowerasing flag. The provided command reflects the setup in my environment. Next, I’ll proceed to install PostgreSQL 16 release along with PostGIS 3.4.2 for PostgreSQL 16.

Now i will install the Postgresql 16 release and postgis 3.4.2 for PostgreSQL 16.

At this point, i will proceed too deploy oracle_fdw and pg_cron extensions for PostgreSQL 16.

Up to this point, we’ve updated Patroni, pgbackrest, and PostGIS to their latest versions and installed PostgreSQL 16 along with the PostGIS 3.4.2 packages. Following the installation of PostgreSQL 16, I’ve compiled the pg_cron and Oracle_FDW extensions for the newer PostgreSQL version. 

At this stage, it’s essential to pay extra attention to a few key points.

We’ll begin by stopping Patroni and the PostgreSQL database. From this point onward, downtime commences. Since our database is relatively small in size, we’ve opted to utilize the pg_upgrade method for the upgrade process. In cases where the database size exceeds 2 TB, the –link or –clone flags may be employed. Alternatively, pg_dump is also a viable option. For situations where no large objects are stored in the database, upgrading with a logical streaming method is feasible. However, it’s important to note that as of PostgreSQL 16, large objects are not replicated due to Logical replication restrictions. Unfortunately, there is no workaround for this limitation other than storing data in normal tables.

We’ll initiate the creation of a new PostgreSQL cluster database using ‘initdb’ from the newly installed PostgreSQL 16 directory, ensuring it conforms to the current locale and WAL segment size settings. Following the setup of the new data directory, it’s crucial to carefully configure the new PostgreSQL database. During the upgrade phase, it’s essential to use a different port for the new version since both databases will be operational simultaneously. Additionally, parameters related to the data directory (such as hba_file and ident_file) need to be verified.

Moreover, it’s important to monitor the available memory resources on your server. You may need to adjust the shared_buffers to a lower value temporarily during the upgrade phase to accommodate the operation of two different PostgreSQL cluster databases simultaneously.

The upgrade process begins with running ‘pg_upgrade’ with the check flag to ensure all necessary checks are passed. Once verified, the ‘pg_upgrade’ process will be executed. Upon completion of the upgrade phase, ‘update_extensions.sql’ will be executed, followed by a vacuum analyze operation to generate fresh statistics for the new cluster.

[postgres@posvt01 pg_cron-main]# mkdir -p /mnt/postgres/pgdata/16
[postgres@posvt01 pg_cron-main]# mkdir -p /mnt/postgres/pg_wal/16

[root@posvt01 ~]# systemctl stop patroni
[postgres@posvt01 ~]$ /usr/pgsql-16/bin/initdb  -U postgres --data-checksums -E 'UTF8' -W --wal-segsize='32' \
--waldir='/mnt/postgres/pg_wal/16' --lc-ctype='tr_TR.UTF-8' --lc-collate='tr_TR.UTF-8' --pgdata='/mnt/postgres/pgdata/16' 

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 this locale configuration:
  provider:    libc
  LC_COLLATE:  tr_TR.UTF-8
  LC_CTYPE:    tr_TR.UTF-8
  LC_MESSAGES: en_US.utf8
  LC_MONETARY: en_US.utf8
  LC_NUMERIC:  en_US.utf8
  LC_TIME:     en_US.utf8
The default text search configuration will be set to "turkish".

Data page checksums are enabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /mnt/postgres/pgdata/16 ... ok
fixing permissions on existing directory /mnt/postgres/pg_wal/16 ... 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/Istanbul
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
initdb: hint: 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:

    /usr/pgsql-16/bin/pg_ctl -D /mnt/postgres/pgdata/16 -l logfile start

[postgres@posvt01 14]$ cp /mnt/postgres/pgdata/14/pg_hba.conf /mnt/postgres/pgdata/16/
[postgres@posvt01 14]$ cp /mnt/postgres/pgdata/14/pg_ident.conf /mnt/postgres/pgdata/16/
[postgres@posvt01 14]$ cp /mnt/postgres/pgdata/14/postgresql.conf /mnt/postgres/pgdata/16/
[postgres@posvt01 14]$  cp /mnt/postgres/pgdata/14/postgresql.base.conf /mnt/postgres/pgdata/16/postgresql.base.conf

-- Check for parameters
[postgres@posvt01 14]$ vi /mnt/postgres/pgdata/16/postgresql.conf 
...
port = '3131'
hba_file = '/mnt/postgres/pgdata/16/pg_hba.conf'
ident_file = '/mnt/postgres/pgdata/16/pg_ident.conf'
unix_socket_directories = '/tmp'
...

-- An upgrade check
[postgres@posvt01 tmp]$ /usr/pgsql-16/bin/pg_upgrade --check -U postgres -b /usr/pgsql-14/bin/ -B /usr/pgsql-16/bin/ \
-d /mnt/postgres/pgdata/14/ -D /mnt/postgres/pgdata/16/ -p 3531 -P 3131 -o "-c config_file=/mnt/postgres/pgdata/14/postgresql.conf" \
-O "-c config_file=/mnt/postgres/pgdata/16/postgresql.conf" --socketdir='/tmp/'
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

*Clusters are compatible*


[postgres@posvt01 ~]$ /usr/pgsql-16/bin/pg_upgrade  -U postgres -b /usr/pgsql-14/bin/ -B /usr/pgsql-16/bin/ \
-d /mnt/postgres/pgdata/14/ -D /mnt/postgres/pgdata/16/ -p 3531 -P 3131 -o "-c config_file=/mnt/postgres/pgdata/14/postgresql.conf" \
-O "-c config_file=/mnt/postgres/pgdata/16/postgresql.conf" --socketdir='/tmp/'
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 system-defined composite types in user tables    ok
Checking for reg* data types in user tables                   ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for incompatible "aclitem" data type in user tables  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
Checking for new cluster tablespace directories               ok

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

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
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 oldest XID for new cluster                            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 delete old cluster                         ok
Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/pgsql-16/bin/vacuumdb -U postgres --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

[postgres@posvt01 ~]$ /usr/pgsql-16/bin/pg_ctl -D /mnt/postgres/pgdata/16 -l logfile start
    
[postgres@posvt01 ~]$ psql -f update_extensions.sql 
[postgres@posvt01 ~]$ /usr/pgsql-16/bin/vacuumdb -U postgres --all --analyze-in-stages

Once the upgrade phase is successfully completed, we may proceed to remove the current Patroni cluster configuration. Before starting Patroni, it’s essential to update the current pgbackrest configuration to align with the new pgdata directories, and the stanza should also be upgraded accordingly. After ensuring all preceding steps are confirmed to be completed successfully, we can initialize Patroni after verifying the configuration (version specific directory related parameters) in the patroni.yml file on both the master and standby servers. 

I’ve provided a sample of a comprehensive PostgreSQL environment update. Choosing to upgrade your major version annually offers a dependable solution. Approximately every quarter, a new minor version becomes available. It’s recommended to apply these updates without delay, as they primarily focus on critical bug fixes and are notably easier to install compared to the major release upgrades.

Hope it helps.