How to Upgrade PostgreSQL, PostGIS and Patroni in Air-Gapped Environments

-

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.

Upgrade Your PostgreSQL: Major Steps and Minor Touches

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.

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.

A Comprehensive Postgresql Upgrade Guide


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.

Comments

Post a Comment

Popular posts from this blog

Using table stats hint to force a direct path read but with a rownum predicate surprise

-
Image
Manipulating table statistics for the statement period On my blog post " Who makes the decisions? I thought it was Optimizer ", I used parallel hint to force a direct path read. This time i will try to make use of table_stats hint to force a direct path read. There is not much documentation about the usage of this hint. Roger Macnicol blog post ( Correct syntax for the table_stats hint  ) offers up some useful information about it. These are the variables play role in direct path read decision.  STT stands for small table threshold = _db_block_buffers * 0,02 =  3875820 * 0,02 = 77516 MTT stands for medium table threshold = _small_table_threshold * 5 = 77516 * 5 = 387580 VLOT stands for Very Large Object Threshold = _db_block_buffers * 5  = 3875820 * 5 = 19379100 I will show how the thresholds effect the direct path decision. We will use nsmtio (non smart IO) event  to trace the direct path or non-direct path/buffered decision.   Before 11.2.0.2 release, the direct path read d
Read more

Oracle Grid Release Update by using Ansible Playbooks

-
Image
Ansible is a friend in need and a friend indeed for DBAs. In this blog post, I will mention about Redhat Ansible and show how you can patch your grid infrastructure by using it. Ansible is an open source, command-line IT automation software application written in Python. It can configure systems, deploy software, and orchestrate advanced workflows to support application deployment, system updates, and more. Ansible's main strengths are simplicity and ease of use. It is a little bit similar to dcli commands in Exadata environments, but much more powerful as you may run playbooks which consist of different tasks. Ansible stands out from other configuration management tools because it doesn't need agents installed on target hosts. Instead, it leverages SSH for connecting to them and executes commands directly through the shell. This means Ansible communicates with remote hosts securely using SSH, eliminating the need for additional software on the managed machines. It's like h
Read more

Oracle Database Release Update by Using Ansible Playbooks

-
Image
Ansible to Rule Them All In the previous post " Oracle Grid Software Patching by using Ansible Playbooks" , I mentioned how Ansible orchestration can be used to make Oracle Grid Software Patching more clean and simple way. Now I will also patch database software by using Ansible Playbooks. In this post,  I will use blt01 host as the ansible host. Target environment is a two node RAC database. (gns01, gns02). I will patch grid software to 19.22 by using out-of-place methodology with no down time.  Although, there are lots of useful blog posts about how you may use Ansible for automating tasks, there is not much about a complete Oracle database software patching.  Ansible is simply installed by "yum install ansible"  from EPEL repository. Add your hosts(gns01, gns02) in the hosts file under /etc/ansible/hosts and create ssh equivalency for oracle and root users. We need:     * OPatch -   p6880880_122010_Linux-x86-64.zip     * 19.22 RU -  p35940989_190000_Linux-x86-64.
Read more