Skip to content

main/postgresql: allow parallel installation of multiple major versions

Jakub Jirutka requested to merge jirutka/aports:postgres-split into master

NOTE: I wrote this description in hurry, it’s a incomplete in some parts, but I hope it will give some insights into the changes I made – for others and my future self… ;)

PostgreSQL package (from upstream) provides at least three distinct, but on some level coupled, components:

  1. PostgreSQL server – currently provided by package postgresql.
  2. PostgreSQL interfaces – libpq (PostgreSQL client library, dependency of many packages that use PostgreSQL) and libecpg (embedded SQL C preprocessor – not very common) – currently provided by packages libpq and libecpg (this one is new).
  3. PostgreSQL client programs – currently provided by package postgresql-client.

Goals

  1. Allow upgrading PostgreSQL databases from one major version to the next one using pg_upgrade tool, without the need to spawn another VM/container or performing full dump and restore (the current state).
  2. Allow running an older, but supported, major version of PostgreSQL server on a recent Alpine Linux release; when the user cannot upgrade the database at the moment (e.g. cannot afford the downtime needed to migrate).

These goals are general, supported by other major distros, and in-sync with the user request !24221 (closed).

Non-Goals

  1. Out-of-box support for running multiple major versions of PostgreSQL server in parallel on a single system (but it should be possible with some minimal effort).
  2. Providing multiple versions of PostgreSQL interfaces (libpq and libecpg), one for each major version of PostgreSQL. I don’t know about any use case where it would be needed (certainly not for the named Goals) and it would cause problems and headaches.
  3. At this moment, building all third-party PostgreSQL extensions for multiple PostgreSQL versions. The extensions will be provided for the latest version only. This decision may be changed in the future (and probably will be, but based on the real needs of the users).

Considerations

  1. How many major versions of PostgreSQL should we provide? The minimum is 2 (to satisfy Goal 1), maximum is number of versions with EOL less or equal EOL of a particular Alpine release (see PostgreSQL Versioning). Alpine v3.15 is gonna have EOL 2024-05-01, this intersects with PostgreSQL 14, 13, and 12.
    • I’d start with supporting up to 2 versions, so 14 and 13 for Alpine v3.15.
  2. Should we provide also multiple versions of the client programs?
    • For example, Debian and Void Linux does it. I don’t think it’s necessary (they should be backward compatible), but for now I kept multiple versions. Maybe I will reconsider it one more before merging.

Prior-Art

Debian provides comprehensive support for multiple major versions of PostgreSQL. It’s the most comprehensive, but also the most complex, of all the distributions I have reviewed. They have source packages for selected major versions of PostgreSQL (e.g. postgresql-14), each provides versioned server and client components (e.g. postgresql-14, postgresql-client-14). It seems that only the latest one provides libpq and libecpg packages (as libpq5, libpq-dev, …). And there’s also package postgresql-common. This one provides many custom Perl scripts for managing not only multiple major versions of PostgreSQL server, but also multiple instances (clusters) of one version. It also provides PostgreSQL client commands in /usr/bin – symlinked to their pg_wrapper that calls the right version of the tool based on the provided options.

Fedora seems to be halfway there. They have single PostgreSQL spec that provides the latest PostgreSQL server and at the same time one major version back (postgresql.spec). libpq and libecpg are provided by separate specs (libpq.spec and libecpg.spec). Some patches are duplicated between all these specs. Both versions of PostgreSQL server are built against shared libpq.

Arch Linux (officially) provides just the latest version of PostgreSQL.

Gentoo has a generic mechanism for installing multiple versions (called “slots”) and since it’s source-based, the situation is quite different there in general.

Also proposal !24221 (closed).

(This section is not complete; I don’t have enough time to write-up all the information I’ve gathered.)

Solution for Alpine

I took inspiration from the Prior-Art, but end-up with a bit different solution for simplicity, consistency with the current practices in Alpine (mainly for naming) and also some compatibility with the current state.

Aports and packages

  • One aport for each major version of PostgreSQL – currently postgresql14 and postgresql13 – and postgresql-common providing helper script pg_versions and init scripts.
  • Only one (typically the latest one) aport provides libpq and libecpg, in the same named subpackages.

The postgresql<majorver> aports provides the following (sub)packages:

  • postgresql<majorver> – server programs and libraries
  • postgresql<majorver>-dev – headers etc. for server and client components
  • postgresql<majorver>-client – client programs
  • postgresql<majorver>-jit – JIT support (library and bitcode files)
  • postgresql<majorver>-contrib – contrib extensions
  • postgresql<majorver>-plperl
  • postgresql<majorver>-plperl-contrib
  • postgresql<majorver>-plpython3
  • postgresql<majorver>-plpython3-contrib
  • postgresql<majorver>-pltcl
  • postgresql<majorver>-contrib-jit – JIT support (bitcode files) for contrib extensions
  • postgresql<majorver>-doc – all man pages etc.
  • postgresql<majorver>-openrc – just a virtual package that depends on postgresql-common-openrc

Each of this package declares provides=postgresql-<subname> and provider_priority=<majorver>, so other aports can (and should) depend on unversioned postgresql-dev and users can install the latest PostgreSQL version via unversioned package names as before.

The latest (controlled by variable _default_ver) aport provides also:

  • libpq
  • libpq-dev
  • libecpg
  • libecpg-dev

The postgresql-common aport provides:

  • postgresql-common – provides pg_versions script * and also hooks and trigger that runs pg_versions
  • postgresql-common-openrc – provides init script and init config

* This is basically a poor man’s “alternatives” replacement specifically for PostgreSQL (since we don’t have anything like that now). I’ll probably replace it with some generic “alternatives” mechanism in the future.

Installation Paths

I used the Debian approach to install the interface libraries and headers in /usr/lib and /usr/include/postgresql, respectively, and server-related libraries and headers in versioned /usr/lib/postgresql<majorver> and /usr/include/postgresql/<majorver>/server, respectively.

Installation paths:

  • /etc/postgresql<majorver>/ – configs
  • /usr/lib – interfaces (libpq + libpgtypes, libecpg)
  • /usr/lib/postgresql<majorver>/ – server libraries
  • /usr/libexec/postgresql<majorver>/ – both server and client programs (including pg_config provided by postgresql<majorver>-dev)
  • /usr/share/postgresql<majorver>/ – data files for server
  • /usr/share/postgresql<majorver>/man/ – all manual pages
  • /usr/share/doc/postgresql<majorver>/ – docs
  • /var/log/postgresql/ – logs
  • /var/lib/postgresql/<majorver>/data/ – data, no change here
  • /usr/bin/pg_config – pg_config provided by libpq-dev (which is provided by the latest postgresql aport) – this is not a symlink into /usr/libexec

Symlinks managed by pg_versions script (provided by aport postgresql-common):

  • /etc/postgresql -> /etc/postgresql<default-version>
  • /usr/bin/<name> -> /usr/libexec/postgresql/<name>
  • /usr/libexec/postgresql -> /usr/libexec/postgresql<default-version>
  • /usr/share/postgresql -> /usr/share/postgresql<default-version>
  • /usr/share/man/man<N>/<name> -> /usr/share/postgresql<default-version>/man/man<N>/<name>

These symlinks are automatically created after installing any postgresql<majorver> or postgresql<majorver>-client and updated after (de)installing any package that writes into /usr/libexec/postgresql* or /usr/share/postgresql*. I refer the version selected by this script as default version (or <default-version>). If the default version does not provide postgres program (i.e. the server package) and the newly installed postgres package does, the default version is automatically changed to the other one. Otherwise, unless the default version is uninstalled, it’s not automatically changed. The user can always change the default version using pg_versions set-default <majorver>. The symlinks are ofc removed after uninstalling all PostgreSQL versions.

Init script

There’s still only one init script (and associated init config file) named postgresql. It starts the default version, but the user can override it via variable pg_version in the init config (/etc/conf.d/postgresql). The user can also create symlinks for this init script (i.e. create another services), each with its own config and different pg_version.

Upgrading

I need to add some pre/post deinstall/upgrade notices and also test some more scenarios.

Impact on Other Aports

Changes I already made two weeks ago:

  • aports that link just against libpq (or libecpg) use libpq-dev (or libecpg-dev) instead of postgresql-dev in makedepends. However, there are a few aports that are not PostgreSQL extensions, but need full postgresql-dev for some reason.
  • PostgreSQL extensions (typically packages with postgresql- prefix) should have postgresql-dev in makedepends – this will install the latest postgresql<majorver>-dev package, unless there’s a dependency on a specific postgresql version in the dependency graph (which shouldn’t be).

New change (implemented in this MR):

Each aport providing a PostgreSQL extension must explicitly depend on specific postgresql<majorver> package, not postgresql provider. That’s because extensions compiled against one version of PostgreSQL are not guaranteed to be compatible with another major version, and they are installed into versioned directory. However, since they are linked against “private” PostgreSQL libraries (they don’t set SONAMEs and are not in /usr/lib, /lib etc.), abuild doesn’t track them.

I solved this problem by adding the following into the package function (!) in each aport that provides PostgreSQL extension:

depends="postgresql$(pg_config --major-version)"

So we don’t have to explicitly declare the target major version and edit it on each upgrade to a new major version. It’s implicitly in-sync with postgresql-dev provider. We have to just bump/rebuild them (this was always needed).

This MR supersedes !24221 (closed)

Edited by Jakub Jirutka

Merge request reports