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

This is the part 2, continuation of the blog Upgrading PostgreSQL 12.15 to 14.18 in RHEL 8 Air-Gapped Systems: A Comprehensive Guide – Part-1

This guide will walk you through the remaining process of upgrading from PostgreSQL 12.15 to 14.18, highlighting key security considerations and potential challenges you might encounter, such as the shift from MD5 to SCRAM-SHA-256 for password encryption, and the impact on SSL certificates and high-availability solutions like repmgr.

 Step 5: Update Configuration Files

PostgreSQL 14 introduces changes and enhancements that require updates to your configuration files, particularly regarding authentication and SSL. This is the one of the important checks before starting the server. PostgreSQL 14 introduced SCRAM_SHA_256. You need to alter postgresql users’ passwords. Because passwords  were stored in md5 format in the earlier versions; an alter command will change the passwords to SCRAM-SHA-256 format.

• Authentication Changes
  • md5 → scram-sha-256: PostgreSQL 14 defaults to scram-sha-256 for password authentication, which is a more secure method than md5.
    • In pg_hba.conf, change:
      host all all 0.0.0.0/0 scram-sha-256
      You need to update your pg_hba.conf file (which controls client authentication) to specify scram-sha-256 as the authentication method for connections. This line allows all users from any IP address to connect using SCRAM-SHA-256.
    • Ensure users have passwords that support SCRAM:
      ALTER ROLE user_name PASSWORD 'password';
      For existing users, their passwords might still be stored in an md5 compatible format. To enable SCRAM-SHA-256 authentication for these users, you must reset their passwords. This command demonstrates how to update a user’s password, which will now be encrypted using the SCRAM-SHA-256 algorithm. .
• SSL Considerations
  • PostgreSQL 14 enforces stronger TLS settings: PostgreSQL 14 prioritizes security and  allows you to enforce more stringent TLS (Transport Layer Security) protocols and cipher suites than the earlier Postgres versions.
  • Renew expired or outdated certs (server.crt, server.key): If your SSL certificates are old, expired, or use weaker cryptographic algorithms, it’s an opportune time to renew them. server.crt is your public certificate, and server.key is your private key.
  • Review:
    • ssl = on: This setting in postgresql.conf enables SSL connections.
    • ssl_cert_file = ‘server.crt’: Specifies the path to your SSL certificate file.
    • ssl_key_file = ‘server.key’: Specifies the path to your SSL private key file.
    • ssl_min_protocol_version = ‘TLSv1.3’: This important setting dictates the minimum TLS protocol version that PostgreSQL will accept for connections. For enhanced security, it’s recommended to set this to TLSv1.3 or higher, as older versions like TLSv1.0 and TLSv1.1 are considered insecure.

Step 6: Repmgr Considerations

If you are using repmgr for managing PostgreSQL replication, special attention is needed to update its configuration and potentially rebuild standbys.

• Update repmgr.conf
  • The repmgr.conf file contains paths to PostgreSQL binaries and other configuration details. Since you’ve upgraded to PostgreSQL 14, these paths need to be updated to point to the new PostgreSQL 14 binaries.
• Rebuild HA setup
  • Recommended: re-initialize using repmgr cluster: It is highly recommended to re-initialize your HA cluster after a major version upgrade of the postgresql.
    On primary
    repmgr -f /etc/repmgr.conf primary register on primary

    On standby nodes
    repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run
repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run
    
    Check on primary
    SELECT * FROM pg_stat_replication;

    Check on standby
    SELECT * FROM pg_stat_wal_receiver;
repmgr -f /etc/repmgr.conf standby register
repmgr -f /etc/repmgr.conf cluster show
    
  • Check PostgreSQL versions across nodes: All nodes in your repmgr cluster (primary and standbys) must be running the same major version of PostgreSQL. Inconsistent versions can lead to replication failures and data corruption. You could check the version of Postgres by running the following command in psql on each of the instances within the repmgr cluster.
    
    select version();

Step 7: Cleanup

This final step involves removing old components and enabling the newly upgraded PostgreSQL 14 service.

• Delete old PostgreSQL 12 binaries and data if not needed:
  • /usr/pgsql-14/bin/delete_old_cluster.sh

    The pg_upgrade utility generates a script called delete_old_cluster.sh in the new binary directory. This script automates the removal of the old PostgreSQL 12 binaries and data directory after a successful upgrade and verification. Only run this script after you are absolutely certain that your PostgreSQL 14 cluster is fully operational and stable.
• Re-enable services:
  • systemctl enable postgresql-14

    This command configures the PostgreSQL 14 service to start automatically upon system boot.
    
  • systemctl start postgresql-14

    This command immediately starts the PostgreSQL 14 service.

Additional Considerations for Air-Gapped Environments

These are specific best practices when performing upgrades in environments with no internet connectivity.

• Package dependency resolution via dnf deplist on connected systems: Before downloading packages, use dnf deplist <package_name> on a connected system to list all dependencies for the required PostgreSQL 14 packages. This ensures you download all necessary RPMs, preventing dependency issues on the air-gapped system.
• Test in isolated dev/test VM first: It is highly recommended, especially for air-gapped production environments, to perform the entire upgrade process in an isolated development or testing virtual machine (VM) that mirrors your production setup. This allows you to identify and resolve any potential issues or unexpected behaviors without impacting live operations.

Final Checklist

Task

Backup database and configs
Download and transfer RPMs
Stop the old service and install the new version
Perform pg_upgrade
Update configs (pg_hba.conf, postgresql.conf)
Update repmgr.conf and re-register
Validate SSL certs
Run tests and cleanup

Run tests and cleanup

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.