Upgrading PostgreSQL 12.15 to 14.18 in RHEL 8 Air-Gapped Systems: A Comprehensive Guide – Part-1

Recently, I had the opportunity to upgrade one of my client’s PostgreSQL databases. The environment is a High availability setup with PostgreSQL 12.15, and a three-node repmgr cluster (primary, standby, and witness), where none of the nodes have access to the internet. This is an account of my experience on upgrading the PostgreSQL database from 12.15 to 14.18 on this environment.

Upgrading PostgreSQL in an air-gapped environment, where internet access is restricted or entirely unavailable, presents unique challenges that go far beyond the typical pg_upgrade or dump/restore workflows. This blog post walks you through the practical realities of upgrading from PostgreSQL 12 to PostgreSQL 14 in such environments, using methods and strategies that are equally applicable to any newer version of PostgreSQL.

We’ll explore what it means to operate in an air-gapped context, how to plan your upgrade path, and what tooling and processes you’ll need to successfully transition versions without external dependencies. From dependency packaging to extension handling and data migration, this post serves as a practical field guide for database administrators and DevOps engineers navigating the upgrade process behind the firewall.

Whether you’re upgrading to PG14 or eyeing PG15+ in the future, the principles and techniques covered here will prepare you to handle PostgreSQL upgrades in some of the most restrictive and security-conscious environments.

Why Upgrade to PostgreSQL 14.18?

PostgreSQL 14 brings significant performance improvements, enhanced administration features, and new functionalities.

Below are the enhancements/Release notes in PostgreSQL 14:

https://www.postgresql.org/docs/release/14.0/
https://www.postgresql.org/docs/release/14.18/

Step 1: Prepare for Upgrade

 1.1. System Compatibility Check

• Ensure RHEL 8 is updated with the required kernel, system and client libraries (like libpq, SSL) .
  

Before initiating any upgrade, it’s critical to verify that your operating system, RHEL 8, is up-to-date and possesses all the necessary libraries and components required by PostgreSQL 14. This includes ensuring your kernel and essential system libraries like libpq (PostgreSQL client library) and SSL are compatible with the new PostgreSQL version. An outdated system might lead to unexpected issues or even a failed upgrade.

 1.2. Download Required Packages

For an air-gapped environment (a system completely isolated from external networks), you cannot directly download packages. This step outlines the process to acquire the necessary PostgreSQL 14 RPM packages from a connected system.

• sudo dnf download postgresql14-server postgresql14 postgresql14-libs postgresql14-contrib: This command uses dnf (the package manager for RHEL) to download the PostgreSQL 14 server, core client, libraries, and contributed modules. These packages will be stored locally on the connected system.
• Copy RPMs to air-gapped system using SCP: Once downloaded, the RPM files must be securely transferred to the air-gapped target system. SCP (Secure Copy Protocol) is recommended for this purpose, ensuring data integrity during transfer.

 1.3. Stop application and Backup Everything

This is a critical risk mitigation step. Before any upgrade, all applications that interact with the PostgreSQL database must be stopped to ensure no active transactions interfere with the backup process. Comprehensive backups are essential for recovery in case of an unforeseen issue.

• pg_basebackup: This utility is used to take backup of the PostgreSQL data directory. It creates a complete copy of the database cluster, which is vital for restoring the entire database.
• Validate consistency and retention: After taking the backup, it’s crucial to verify its integrity to ensure it’s not corrupted and can be restored successfully. Additionally, confirm that your backup retention policy is being met.
• Backup postgresql.conf, pg_hba.conf, repmgr.conf, SSL certificates: Beyond the database data, configuration files are equally important. postgresql.conf contains server configuration parameters, pg_hba.conf controls client authentication, repmgr.conf stores the  replication manager configuration, and SSL certificates are vital for secure connections. Backing these up ensures you can restore your previous configuration settings if there ever is a need.

 Step 2: Stop Services and Prepare for Upgrade

This phase involves shutting down the existing PostgreSQL 12 services and, if performing an in-place upgrade, removing the old PostgreSQL 12 installation, while carefully preserving the data directory.

• Unregister all the nodes in repmgr
  • If you are using repmgr for replication, it’s important to unregister all participating nodes (master and standbys) before stopping the PostgreSQL service. This prevents repmgr from attempting to connect to a non-existent or a non-running database and avoids potential issues during the upgrade process.
  • To unregister a repmgr node, use the repmgr node unregister command, specifying the node ID. For example, to unregister a standby node with ID 3, you would use repmgr standby unregister –node-id=3. If the node is the primary, use repmgr primary unregister –node-id=3.
• sudo systemctl stop postgresql-12
  • This command uses systemctl (the system and service manager for RHEL) to gracefully stop the running PostgreSQL 12 database service. Stopping the service ensures no data is being written or read during the upgrade, thereby preventing data corruption.

 Step 3: Install PostgreSQL 14.18

This step focuses on the installation of the new PostgreSQL 14 packages using the RPMs previously downloaded.

• sudo rpm -ivh postgresql14-libs-.rpm postgresql14-.rpm

This command uses rpm (the Red Hat Package Manager) to install the PostgreSQL 14 packages. The -i flag indicates installation, -v provides verbose output, and -h displays hash marks as the package installs, giving a log progress indicator. You’ll install the libraries first, followed by the main server and client packages.

 Step 4: Use pg_upgrade for Upgrade

This is the main upgrade process, where the pg_upgrade utility is used to migrate data from the PostgreSQL 12 cluster to the newly installed PostgreSQL 14 cluster.

• Create new data directory:
  • sudo mkdir -p /var/lib/pgsql/14/data
    This command creates the new data directory for PostgreSQL 14. The -p flag ensures that parent directories are created if they don’t already exist.
  • sudo chown postgres:postgres /var/lib/pgsql/14/data
    This command changes the ownership of the newly created data directory to the postgres user and group. This is crucial as the PostgreSQL server runs as the postgres user and needs appropriate permissions to access its data.
• Run upgrade (as postgres user):
  • /usr/pgsql-14/bin/initdb -D /var/lib/pgsql/14/data
    Before running pg_upgrade, you need to initialize a new PostgreSQL 14 data directory. This command sets up the basic directory structure and creates the necessary system catalogs for the new cluster.
  • /usr/pgsql-14/bin/pg_upgrade \ –old-datadir=/var/lib/pgsql/12/data \ –new-datadir=/var/lib/pgsql/14/data \ –old-bindir=/usr/pgsql-12/bin \ –new-bindir=/usr/pgsql-14/bin \ –check
    This command executes pg_upgrade in “check” mode. It performs a series of compatibility checks between the old and new clusters without actually performing the upgrade. This is highly recommended to identify potential issues (e.g., incompatible extensions, custom data types) before committing to the upgrade.
    • –old-datadir: Specifies the data directory of the old PostgreSQL 12 cluster.
    • –new-datadir: Specifies the newly created data directory for the PostgreSQL 14 cluster.
    • –old-bindir: Specifies the binary directory of the old PostgreSQL 12 installation.
    • –new-bindir: Specifies the binary directory of the new PostgreSQL 14 installation.
    • –check: This flag tells pg_upgrade only to perform compatibility checks.
  • If the check passes:
    • /usr/pgsql-14/bin/pg_upgrade \ –old-datadir=/var/lib/pgsql/12/data \ –new-datadir=/var/lib/pgsql/14/data \ –old-bindir=/usr/pgsql-12/bin \ –new-bindir=/usr/pgsql-14/bin \ –link
      If the –check phase completes without errors, you can proceed with the actual upgrade. The –link option (highly recommended for speed) creates hard links between the old and new data directories for unchanged files, significantly reducing the disk space and time required for the upgrade. It’s crucial to ensure there is sufficient free space for any new or modified files.

Summary

Upgrading PostgreSQL in air-gapped environments requires meticulous planning to account for any dependency issues in the absence of access to the internet on the host machine.. With PostgreSQL 14 bringing in authentication and replication changes, it’s important to update configurations, and test thoroughly, in order to  ensure compatibility in HA setups  that rely on repmgr or identical tools.

upgrading the environment to postgresql14 is completed in the step 4. This blog post is part 1 of the 2 part series of postgresql upgradation. In the next part we will continue with the configurations of postgresql 14 and replication related steps according to this specific environment and architecture.