Upgrade postgres for cuirass

Upgrade postgres for cuirass

[Gabriel Wicki]

Aloha, howdy and hello!

This isn't your usual call for help - this is my help to you!  Since
postgres was recently unset all of us running cuirass on default
postgres versions need to take some action.  Since I guess a bunch of
users will be affected, I summarized the necessary steps in hope to
help before you have to ask.  It may contain errors, mistakes or false
promises, use at your own risk.


This is for all of you who know perfectly well that they will figure out
how to do it but still prefer to do some of the tasks on the stack in a
sheepishly follow the tutorial step-by-step fashion.  I know I am like
that with far too many of the tasks piling up on my virtual task-heap.

So here it is, my tiny writeup on how to upgrade postgres for cuirass
to continue working.  It's actually kinda easy.

 1. Stop and disable cuirass (to prevent it from starting and failing
    after a reconfiguration):

    sudo herd stop cuirass && sudo herd disable cuirass

 2. Dump the database contents.  Ever too often (I tend to be somewhat
    dense) I forget how to access postgres privileged content.  Don't
    be like me.

    sudo su - postgres -s /bin/sh -c pg_dumpall > /tmp/pg.dump

 3. Add the postgres service to your system configuration - or alter
    the already existing configuration to reflect your intended
    version upgrade.  Be careful not to upgrade directly to
    postgres-16 -- cuirass service for some reason doesn't like that.
    I had to find and purge and then re-initialize after a failed
    upgrade to postgres 16.

    #+begin_quote
    (service postgresql-service-type
             (postgresql-configuration
              (postgresql (@ (gnu packages databases) postgresql-15))))
    #+end_quote

    If you for some reason didn't read the text here but somewhat
    blindly followed the examples and **did upgrade to 16**, here's
    how you reset the state:

    a) Delete the database instance files; they default to live under
    /var/lib/postgres/data.

    b) Re-initialize:
    sudo su - postgres -s /bin/sh -c 'pg_ctl init -D /var/lib/postgres/data'

 4. Reconfigure your system.
    sudo guix system reconfigure path/to/your/altered/config.scm

 5. Restore database contents:
    sudo su - postgres -s /bin/sh -c 'psql -d postgres -f /tmp/pg.dump'

 6. Enable and start the service(s)
    sudo herd enable cuirass
    sudo herd start cuirass

That's it, fellow guix!


Happy hacking and have a nice weekend!
gabber

Re: Upgrade postgres for cuirass

[Ludovic Courtès]

Hi,

Thanks for this nice writeup!  Would you like to turn it into a section
of the Cuirass (or Guix) manual?

Gabriel Wicki <gabriel@erlikon.ch> skribis:

> So here it is, my tiny writeup on how to upgrade postgres for cuirass
> to continue working.  It's actually kinda easy.
>
>  1. Stop and disable cuirass (to prevent it from starting and failing
>     after a reconfiguration):
>
>     sudo herd stop cuirass && sudo herd disable cuirass
>
>  2. Dump the database contents.  Ever too often (I tend to be somewhat
>     dense) I forget how to access postgres privileged content.  Don't
>     be like me.
>
>     sudo su - postgres -s /bin/sh -c pg_dumpall > /tmp/pg.dump

FWIW, I used the same approach on guix.bordeaux.inria.fr, which has a
relatively small database.

For ci.guix.gnu.org, which has a much bigger database, I used
‘pg_upgrade’ instead:

  https://www.postgresql.org/docs/current/pgupgrade.html

It went like this:

--8<---------------cut here---------------start------------->8---
sudo herd stop cuirass && sudo herd disable cuirass
sudo herd stop postgresql

mv /var/lib/postgresql/data{,.pg13}
sudo su - postgres -s /bin/sh -c "cd $PWD; /gnu/store/cng33vs1zdrqpmiz548kkvavc7x1xpd0-postgresql-15.8/bin/initdb -D /var/lib/postgresql/data --locale=en_US.UTF-8"

sudo su - postgres -s /bin/sh -c "cd $PWD; /gnu/store/cng33vs1zdrqpmiz548kkvavc7x1xpd0-postgresql-15.8/bin/pg_upgrade --old-datadir=/var/lib/postgresql/data.pg13 --new-datadir=/var/lib/postgresql/data --old-bindir=/gnu/store/j9wnb7rvjzxdzghpvq1jmi2zfg4z2d8c-postgresql-13.15/bin --new-bindir=/gnu/store/cng33vs1zdrqpmiz548kkvavc7x1xpd0-postgresql-15.8/bin"
--8<---------------cut here---------------end--------------->8---

The store file names are those of Postgres 13 (the one that was used so
far) and 15 (as returned by ‘guix build postgresql@15’ with the target
Guix revision).

That was a couple of hours ago.  Apparently it went well, though
postgresql is slower than before so I wonder if there are options I
forgot to pass or something.

Ludo’.

Re: Upgrade postgres for cuirass

[Gabriel Wicki]

On Sun, Dec 01, 2024 at 02:23:12PM +0100, Ludovic Courtès wrote:
> Thanks for this nice writeup!  Would you like to turn it into a section
> of the Cuirass (or Guix) manual?
This seems like a nice opportunity!  Since it is somewhat specific to
Guix (in the way we handle packages and services) I thought it probably
fits best in there?  Probably as a cookbook entry?

> That was a couple of hours ago.  Apparently it went well, though
> postgresql is slower than before so I wonder if there are options I
> forgot to pass or something.
Some strangers on the interwebs suggest to REINDEX all schemas - though
I am not an expert and do not know it this actually solves the problem.
Might be worth a try, though ;)

Have a nice day,
g