Skip to content

PostgreSQL 14 EOL #386

Open
Open

Description

@ignitedPotato

Description

Please update postgres to a never version.
Version 14 will be EOL from 2026-11-12 on, which is in one week and a day.

Epic/Story

No response

Activity

  1. added
    taskA piece of work that isn't necessarily tied to a specific Epic or Story.
    on Nov 4, 2026
  2. hardillb commented on Nov 4, 2026

    @hardillb
    Contributor

    Thanks for the reminder, it has been on my list for a little while.

    The tricky bit will be the upgrade path as jumping major PostgreSQL versions normally requires some dedicated upgrade steps so we can't just flip the version in the docker-compose.yml file.

    So will need to document a process to do the conversion for existing installs.

  3. hardillb commented on Nov 5, 2026

    @hardillb
    Contributor

    Initial process to upgrade

    1. docker compose stop stops but doesn't down the containers
    2. docker run --rm --volumes-from flowfuse-postgres-1 -v $(pwd):/backup ubuntu tar czvf /backup/db-backup.tar.gz /var/lib/postgresql/data creates a back up in db-back.tar.gz in current directory
    3. docker compose down actually down all the containers
    4. docker run --rm -it -u postgres -v flowfuse_db:/var/lib/postgresql/14/data -v flowfuse_db16:/var/lib/postgresql/16/data tianon/postgres-upgrade:14-to-16 /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/data -U root initialises PG 16 empty database
    5. docker run --rm -it -u postgres -v flowfuse_db:/var/lib/postgresql/14/data -v flowfuse_db16:/var/lib/postgresql/16/data tianon/postgres-upgrade:14-to-16 /usr/lib/postgresql/16/bin/pg_upgrade --old-bindir /usr/lib/postgresql/14/bin --new-bindir /usr/lib/postgresql/16/bin --old-datadir /var/lib/postgresql/14/data --new-datadir /var/lib/postgresql/16/data -U root converts old db to new format
    6. docker run --rm -it -u postgres -v flowfuse_db:/var/lib/postgresql/14/data -v flowfuse_db16:/var/lib/postgresql/16/data tianon/postgres-upgrade:14-to-16 cp /var/lib/postgresql/14/data/pg_hba.conf /var/lib/postgresql/16/data/pg_hba.conf ensures that new PG 16 allows network connections
    7. update docker-compose.yml to point to the newly created flowfuse_db16 volume and point to the v16 container.
    8. docker compose up -d restart everyting.

    These are PoC instructions that assume the following:

    1. The docker-compose.yml file is in a directory called flowfuse or the docker compose commands are run with -p flowfuse
    2. System is running v3.2.0 as a starting point

    docker-compose.yml diff after change

    488c488
    <     image: postgres:14
    ---
    >     image: postgres:16
    503c503
    <       - db:/var/lib/postgresql/data
    ---
    >       - db16:/var/lib/postgresql/data
    623c623
    <   db:
    ---
    >   db16:

    It should also leave the original flowfuse_db volume untouched making rolling back easy.

  4. ignitedPotato commented on Nov 5, 2026

    @ignitedPotato
    Author

    Thanks for the steps!

    Is 16 the newest version compatible with FlowFuse or would 18 work aswell?
    Also, is there anything against simply using https://github.com/pgautoupgrade/docker-pgautoupgrade ?
    I've used it a couple of times in the past and it always worked like a charm.

  5. hardillb commented on Nov 5, 2026

    @hardillb
    Contributor

    The earlier comment is just me keeping notes as I try things. This worked and I was going to look at scripting it all for actual release.

    I was trying to limit the version jump size so 16 felt like a reasonable compromise as it's good to end of 2028. There is no reason that FF shouldn't work with PG 18

    As for the tool, I found a couple of options and the https://github.com/tianon/docker-postgres-upgrade version worked (after a little messing about to get the init user right)

    But I have to look at the kubernetes upgrade path next so I will try the other as well as that looks to have some k8s instructions.

    This may not make the next release on the 19th, but will be in the next one just before Christmas so people can apply it in the New Year.

  6. ignitedPotato commented on Nov 5, 2026

    @ignitedPotato
    Author

    Sounds great.
    We'll try to apply the upgrade to 18 with pgautoupgrade with a test instance and I'll let you know if that works as expected, too.

  7. hardillb commented on Nov 5, 2026

    @hardillb
    Contributor

    Updated docker-compose.yml diff

    119a120,121
    >       ESQL
    >       psql -v ON_ERROR_STOP=1 -U root flowforge <<-ESQL
    120a123,124
    >         GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${DB_USER:-forge};
    >         GRANT ALL ON SCHEMA public to ${DB_USER:-forge};
    127a132,133
    >       ESQL
    >       psql -v ON_ERROR_STOP -U root ff-context <<-ESQL
    128a135
    >         GRANT ALL ON SCHEMA public to ${DB_USER:-forge}
    488c495
    <     image: postgres:14
    ---
    >     image: postgres:16
    503c510
    <       - db:/var/lib/postgresql/data
    ---
    >       - db16:/var/lib/postgresql/data
    623c630
    <   db:
    ---
    >   db16:

    The extra changes should only be needed for new installs with PG16

  8. ignitedPotato commented on Nov 6, 2026

    @ignitedPotato
    Author

    I just upgraded from version 14 to version 18 with pgautoupgrade one shot mode. Seems to have worked fine. I used the debian 18-debian image. Before I had to restructure the folders as outlined here: https://hub.docker.com/_/postgres/#pgdata

  9. hardillb commented on Nov 6, 2026

    @hardillb
    Contributor

    Ahh, so good reason for us to stop at jumping to 16 for now

  10. hardillb commented on Nov 11, 2026

    @hardillb
    Contributor

    pgautoupgrade instructions appear simpler

    $ docker run --rm -it --name pgupdate -v flowfuse_db:/var/lib/postgresql/data -e POSTGRES_PASSWORD=password -e POSTGRES_USER=root -e PGAUTO_ONESHOT=yes pgautoupgrade/pgautoupgrade:16-trixie

    Bonus to this image is no need for new volume

  11. hardillb commented on Nov 16, 2026

    @hardillb
    Contributor

    Reopening until this all ships in v3.4.0

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    taskA piece of work that isn't necessarily tied to a specific Epic or Story.

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions