Postgresql minor version control

[jefferypalmer]

Bug description

The ansible scripts don't seem to have the ability to build the package string necessary for dnf to install a minor version. Minor version control is quite important for production, so a quick question is whether there are OS's that do support minor version control?

I have tried a variety of postgresql_version settings but the issue is that dnf expects the version information to be appended to the end of the package name and not the middle. For example installing postgresql server v17.5-1:

Works (manually)

dnf install postgresql17-server-17.5-1PGDG.rhel9

Fails (autobase ansible)

dnf install postgresql17-17.5-1PGDG.rhel9-server

TASK [vitabaks.autobase.packages : Install PostgreSQL packages] *******************************************************************************************************
failed: [10.x.x.x] (item=postgresql17.5-1) => {"ansible_loop_var": "item", "attempts": 3, "changed": false, "failures": ["No package postgresql17.5-1 available."], "item": "postgresql17.5-1", "msg": "Failed to install some of the specified packages", "rc": 1, "results": []}

I have tried setting a variety of version strings but these fail

postgresql_version: 17.5
postgresql_version: 17.5-1
postgresql_version: 17-17.5-1PGDG.rhel9

Output from Rocky 9.6

# dnf --showduplicates list postgresql17
Last metadata expiration check: 0:04:13 ago on Mon 04 Aug 2025 04:42:06 AM UTC.
Available Packages
postgresql17.x86_64                                                          17.0-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.0-2PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.1-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.2-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.3-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.4-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.5-1PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.5-2PGDG.rhel9                                                           pgdg17
postgresql17.x86_64                                                          17.5-3PGDG.rhel9                                                           pgdg17

Expected behavior

Postgres minor version control on Rocky 9.6

Steps to reproduce

1. Use Rocky 9.6
2. Set postgresql minor version

Installation method

Command line

System info

Rocky Linux 9.6

Additional info

No response

[vitabaks]

For RHEL-based systems (like Rocky Linux), the PGDG repository provides multiple minor versions of PostgreSQL.

https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-9-x86_64/

However, the situation is different on Debian-based systems, where typically only the latest minor version is available for each major release.

https://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-17/

Because Autobase aims to support both platforms uniformly, it currently installs PostgreSQL based on the major version only (e.g. 17), which always pulls the latest available minor version.

This approach avoids deployment failures (especially for Debian-based), where attempting to pin to a specific minor version (like 17.3) may break if that version has been removed from the repository.

More granular control over minor versions (e.g. locking to 17.4 instead of 17.5) will become possible once support for Docker-based deployments is introduced — this is planned for Autobase 3.0.

[vitabaks]

@jefferypalmer try replace the package name from postgresql{{ postgresql_version }} to postgresql17-17.5-1PGDG.rhel9 in the postgresql_packages variable.

[jefferypalmer]

Good points, definitely challenging to keep it agnostic across all platforms. And each option will be slightly different (deb/redhat/docker)

One thought that came up is to create three variables for the versioning (major/minor/release) and just pick from those based on the deployment, for example:

# Define version components
PG_MAJOR=17
PG_MINOR=5
PG_RELEASE=1

# Construct full version
PG_VERSION="${PG_MAJOR}.${PG_MINOR}-${PG_RELEASE}"

# Package name pattern
PG_PKG_NAME="postgresql-${PG_MAJOR}"

install_postgresql() {
    if [[ "$OS_FAMILY" == "debian" || "$OS_FAMILY" == "ubuntu" ]]; then
        echo "Installing PostgreSQL on Debian/Ubuntu"

        # Always updates to the latest minor (e.g., 17.x), no old minors
        echo "⚠️ Debian does not support installing exact minor versions from apt.postgresql.org"
        apt-get update
        apt-get install -y ${PG_PKG_NAME} || {
            echo "Failed to install ${PG_PKG_NAME}. Likely because minor version ${PG_MINOR} is no longer available."
            echo "Installing latest available version of postgresql-${PG_MAJOR} instead."
            apt-get install -y "postgresql-${PG_MAJOR}"
        }

    elif [[ "$OS_FAMILY" == "rhel" || "$OS_FAMILY" == "centos" || "$OS_FAMILY" == "fedora" ]]; then
        echo "Installing PostgreSQL on RHEL-based system"

        # Use the PGDG repo RPM, then install with version-lock or direct version
        yum install -y https://download.postgresql.org/pub/repos/yum/${PG_MAJOR}/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

        yum -y install "postgresql${PG_MAJOR//./}${PG_MINOR//./}-${PG_VERSION}" || {
            echo "Exact version ${PG_VERSION} not found, falling back to latest postgresql${PG_MAJOR}."
            yum -y install "postgresql${PG_MAJOR}"
        }

    else
        echo "Unsupported OS: $OS_FAMILY"
        exit 1
    fi
}

[jefferypalmer]

@jefferypalmer try replace the package name from postgresql{{ postgresql_version }} to postgresql17-17.5-1PGDG.rhel9 in the postgresql_packages variable.

There's more than 1 package to set the version number on, so I can do this in my vars.yml:

postgresql_os_specific_packages:
  RedHat:
    - postgresql{{ postgresql_version }}-17.4-1PGDG.rhel9
    - postgresql{{ postgresql_version }}-server-17.4-1PGDG.rhel9
    - postgresql{{ postgresql_version }}-contrib-17.4-1PGDG.rhel9
    - postgresql{{ postgresql_version }}-devel-17.4-1PGDG.rhel9

But then it causes the ansible repo config to fail here:

autobase/automation/roles/add_repository/tasks/main.yml

Lines 106 to 109 in bfc0c67

	vars:
	pg_devel_package: "postgresql{{ postgresql_version | string | replace('.', '') }}-devel"
	when:
	- pg_devel_package in postgresql_packages

Because the string no longer ends in "-devel"

Something like this that matches the first portion of the string could resolve but maybe you have a better implementation:

vars:
  pg_devel_prefix: "postgresql{{ postgresql_version | string | replace('.', '') }}-devel"

when:
  - postgresql_packages | select('startswith', pg_devel_prefix) | list | length > 0

[vitabaks]

Try to deploy without devel package

[jefferypalmer]

Try to deploy without devel package

That's a great idea, and it worked, but then the postgis package fails afterwards as it also depends on the "dnf config-manager --set-enabled crb" command which is failing because of the above issue.

[vitabaks]

Okay, I'll try to add support for minor versions for RHEL.

[jefferypalmer]

Okay, I'll try to add support for minor versions for RHEL.

Fantastic, I will give it a good test when you're ready!

[vitabaks]

PR: #1188