Running service migrations during upgrades

Running service migrations during upgrades

[Marius Bakke]

[-- Attachment #1: Type: text/plain, Size: 2097 bytes --]

Hello Guix,

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

For the former, running 'mysql_upgrade' in-place is generally sufficient
and fairly safe.

For the latter, the procedure is more involved, and requires making a
full backup, as well as access to the old _and_ new PostgreSQL
packages.

There is a patch to update MariaDB here:

  https://issues.guix.gnu.org/43355

Users of mysql-service-type will need to run 'mysql_upgrade' afterwards.

I have been considering adding an AUTO-UPGRADE? parameter of
mysql-service-type that runs 'mysql_upgrade' as part of the activation
script.

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.

How do other distros deal with this?

On a related note, a 'herd backup' action might be a prerequisite for
doing such potentially dangerous operations...

I will try to add a 'mysql_upgrade' pass to 'mysql-service-type' in time
for the MariaDB upgrade, but tips regarding backups, potential pitfalls,
and especially Postgres welcome.

PS: I'm mostly busy for some time still, but can be reached on #guix or
by private email if you want my feedback on something.  :-)

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 487 bytes --]

Re: Running service migrations during upgrades

[Efraim Flashner]

[-- Attachment #1: Type: text/plain, Size: 2736 bytes --]

On Sun, Sep 20, 2020 at 02:39:45PM +0200, Marius Bakke wrote:
> Hello Guix,
> 
> Some services require administrator interaction when they are updated.
> The most prominent examples here are MySQL/MariaDB and PostgresQL.
> 
> For the former, running 'mysql_upgrade' in-place is generally sufficient
> and fairly safe.
> 
> For the latter, the procedure is more involved, and requires making a
> full backup, as well as access to the old _and_ new PostgreSQL
> packages.
> 
> There is a patch to update MariaDB here:
> 
>   https://issues.guix.gnu.org/43355
> 
> Users of mysql-service-type will need to run 'mysql_upgrade' afterwards.
> 
> I have been considering adding an AUTO-UPGRADE? parameter of
> mysql-service-type that runs 'mysql_upgrade' as part of the activation
> script.
> 
> 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.
> 
> How do other distros deal with this?
> 

It looks like Debian has a whole package named "postgres-common" to deal
with updating postgres databases and clusers to newer versions.
https://sources.debian.org/src/postgresql-common/

> On a related note, a 'herd backup' action might be a prerequisite for
> doing such potentially dangerous operations...
> 
> I will try to add a 'mysql_upgrade' pass to 'mysql-service-type' in time
> for the MariaDB upgrade, but tips regarding backups, potential pitfalls,
> and especially Postgres welcome.
> 
> PS: I'm mostly busy for some time still, but can be reached on #guix or
> by private email if you want my feedback on something.  :-)



-- 
Efraim Flashner   <efraim@flashner.co.il>   אפרים פלשנר
GPG key = A28B F40C 3E55 1372 662D  14F7 41AA E7DC CA3D 8351
Confidentiality cannot be guaranteed on emails sent or received unencrypted

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 833 bytes --]

Re: Running service migrations during upgrades

[Alex Sassmannshausen]

[-- Attachment #1: Type: text/plain, Size: 2652 bytes --]

Hi Marius,

Your work and thoughts on the SQL upgrades are very interesting.

After the last FOSDEM I wrote up some thoughts about service states and
their backups that you may find interesting.

https://lists.nongnu.org/archive/html/guix-devel/2020-02/msg00002.html

Unfortunately I have not found the time to work on any of this
since. But feel free to steal freely any content that proves useful!

Best wishes,

Alex

Marius Bakke <marius@gnu.org> writes:

> Hello Guix,
>
> Some services require administrator interaction when they are updated.
> The most prominent examples here are MySQL/MariaDB and PostgresQL.
>
> For the former, running 'mysql_upgrade' in-place is generally sufficient
> and fairly safe.
>
> For the latter, the procedure is more involved, and requires making a
> full backup, as well as access to the old _and_ new PostgreSQL
> packages.
>
> There is a patch to update MariaDB here:
>
>   https://issues.guix.gnu.org/43355
>
> Users of mysql-service-type will need to run 'mysql_upgrade' afterwards.
>
> I have been considering adding an AUTO-UPGRADE? parameter of
> mysql-service-type that runs 'mysql_upgrade' as part of the activation
> script.
>
> 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.
>
> How do other distros deal with this?
>
> On a related note, a 'herd backup' action might be a prerequisite for
> doing such potentially dangerous operations...
>
> I will try to add a 'mysql_upgrade' pass to 'mysql-service-type' in time
> for the MariaDB upgrade, but tips regarding backups, potential pitfalls,
> and especially Postgres welcome.
>
> PS: I'm mostly busy for some time still, but can be reached on #guix or
> by private email if you want my feedback on something.  :-)


[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 227 bytes --]

Re: Running service migrations during upgrades

[Ludovic Courtès]

Hi!

Marius Bakke <marius@gnu.org> skribis:

> There is a patch to update MariaDB here:
>
>   https://issues.guix.gnu.org/43355
>
> Users of mysql-service-type will need to run 'mysql_upgrade' afterwards.
>
> I have been considering adding an AUTO-UPGRADE? parameter of
> mysql-service-type that runs 'mysql_upgrade' as part of the activation
> script.
>
> 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.

Running upgrades in the activation snippet sounds reasonable.  If
‘mysql_upgrade’ detects what version it is upgrading from, that’s
perfect.  However, the activation snippet runs upon reconfigure even if
the service is not restarted; so you could find yourself running version
N but you’ve just upgraded the database to N+1.  The safest way would be
to do that in the ‘start’ method.

But you can also add an ‘upgrade’ action to the service, just like
‘mcron’ has a ‘schedule’ action.

> 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.

One way would be to determine the previous generation number of
/run/current-system and use, say, /var/guix/profiles/system-42-link/bin
as the bindir.  Not pretty.

A nicer way would be to add a ‘bindir’ and a ‘datadir’ action to the
postgresql service; the activation snippet could use it to find the
right values.

Thoughts?

Thanks,
Ludo’.

Re: Running service migrations during upgrades

[Christopher Baines]

[-- Attachment #1: Type: text/plain, Size: 3033 bytes --]


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

[-- Attachment #2: signature.asc --]
[-- Type: application/pgp-signature, Size: 987 bytes --]