Marius Bakke <marius@gnu.org> writes:

> Some services require administrator interaction when they are updated.
> The most prominent examples here are MySQL/MariaDB and PostgresQL.

...

> Another approach is adding a 'herd upgrade' Shepherd action along with a
> news entry describing what to do.  Of course it is possible to do both,
> having 'auto-upgrade?' _and_ a Shepherd action for manual upgrades.
>
> Thoughts?
>
> While that works for MariaDB, I'm not sure what to do about Postgres.
> For those unfamiliar, the procedure for upgrading from PostgreSQL 10
> (current default) to 11 (available in Guix) is roughly:
>
>   sudo cp -a /var/lib/postgresql/data /var/lib/postgresql/data10
>   sudo -u postgres $(guix build postgresql)/bin/pg_upgrade \
>     --old-bindir=$(guix build postgresql@10)/bin \
>     --new-bindir=$(guix build postgresql)/bin \
>     --old-datadir=/var/lib/postgresql/data10 \
>     --new-datadir=/var/lib/postgresql/data
>
> In order to automate it, we need to somehow preserve the "previous"
> version of PostgreSQL so that we can reach it when the major version
> changes.  Or add an 'upgrade-from' parameter to
> postgresql-service-type.

Hi!

I'm really glad you're thinking about this Marius, I've got a few
PostgreSQL clusters running with postgresql@10 that I'd like to upgrade,
so this is very interesting.

Reading through the documentation on pg_upgrade, it suggests the new
cluster should be initialised as part of the process [1]. Maybe leaving
the old files in place is sometimes/always sufficient, I'm unsure?

1: https://www.postgresql.org/docs/11/pgupgrade.html#id-1.9.5.11.7

One approach I have in mind:

 - Make the postgresql package explicit in the configuration

   - This avoids the current situation where changing the major version
     of the postgresql package would cause PostgreSQL not to start when
     reconfigured.

 - Somehow include the major version in the data directory name

   - So rather than /var/lib/postgresql/data it should be
     /var/lib/postgresql/10, /var/lib/postgresql/data/10 or something
     like that.

 - Add a upgrade-from field to the postgresql service configuration,
   this takes a postgresql package, which is used for the old-bindir
   when doing the upgrade

 - Add a shepherd action to call pg_upgrade

I think if all the above things are done, you could end up with a
process like:

(service postgresql-service-type
         (postgresql-configuration
          (postgresql postgresql-10)))

 ->

(service postgresql-service-type
         (postgresql-configuration
          (postgresql postgresql-11)
          (upgrade-from postgresql-10)))

Assuming the data directory default includes the major version, when
reconfiguring you'd go from running version 10, to an empty version 11.

Then running herd upgrade postgres would stop the service, and run
pg_upgrade.

I started sketching out some patches to at least make the postgresql
package explicit in the configuration [2.

2: https://issues.guix.info/43771


Thanks again,

Chris