Upgrading to Postgres 18: possible loss of data

[paskn]

Hi!

This is not exactly a bug report but more of a notice about a breaking change in Postgres 18. The side-effect that I got:

My user data and all datasets are lost. Luckily, I got this on a testing instance :)

I should add: you lose data not because of updating containers but due to fixing the compose.yml to accommodate Postgres 18. 4cat will just not work with Postgres 18, and this is good.

To avoid it:

1. Before upgrading containers, check if your .env has POSTGRES_TAG=latest
2. If it does, change it to 16 or 17.
3. Only then start pulling the newer images.

I see that you also noticed this in https://github.com/digitalmethodsinitiative/4cat/commit/93bb522e8856719588d054cbdfc6f0ce069786c6

What I figured out:

Starting version 18 Postgress no longer works with - 4cat_db:/var/lib/postgresql/data as all 4cat documentation suggest. Because of this:

https://github.com/docker-library/postgres/pull/1259

To make it work:

1. Add PGDATA=/var/lib/postgresql/data into environment subsection of the db container
2. Change volume mouting:

    volumes:
      - 4cat_db:/var/lib/postgresql

Here is an example of the db section that will enable you to use 4cat with Postgres 18:

    container_name: 4cat_db
    image: postgres:latest
    restart: unless-stopped
    environment:
      - POSTGRES_USER=${POSTGRES_USER}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=${POSTGRES_DB}
      - POSTGRES_HOST_AUTH_METHOD=${POSTGRES_HOST_AUTH_METHOD}
      - PGDATA=/var/lib/postgresql/data
    volumes:
      - 4cat_db:/var/lib/postgresql
    healthcheck:
      test:
        - CMD-SHELL
        - pg_isready -U $${POSTGRES_USER}
      interval: 5s
      timeout: 5s
      retries: 5

Unfortunately, this will give you a fresh and empty 4cat instance without your data. I plan to look into if its possible to recover. And I do not know yet of a way of how move my data between the versions.

[dale-wahl]

Did you actually lose data? Downgrading to 17 should work fine so long as you do not delete the volume.

I will have to check next week but I think you have a mismatch in your volume mount vs PGDATA. Fixing that may work with 18 but I have not tested.

[paskn]

Hi Dale!

Thanks for the quick reply.

With this config:

...
- PGDATA=/var/lib/postgresql/data
    volumes:
      - 4cat_db:/var/lib/postgresql/data
...

The Postgres 18 container will become "unhealthy" and won't work. And, yes, I think, the the data is still there - I will need to look into it.

Please let me know how I can help.

Update: I just tried to manually pin Postgres version to 17 instead of "latest" to see if it will automatically recover the old data. It did not work but the container has "healty" status.

docker container logs 4cat_db reports a collation version mismatch:

2025-11-21 16:31:31.998 UTC [1021] WARNING:  database "fourcat" has a collation version mismatch
2025-11-21 16:31:31.998 UTC [1021] DETAIL:  The database was created using collation version 2.36, but the operating system provides version 2.41.
2025-11-21 16:31:31.998 UTC [1021] HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE fourcat REFRESH COLLATION VERSION, or build PostgreSQL with the right library 
version.

[robbydigital]

I'm having the same issue - I rebuilt the containers with the updated docker-compose.yml and env files and ran the upgrade command (docker compose down && docker compose pull && docker compose up --detach) and this is what I see:

✔ Network 4cat_default Created 0.0s ✘ Container 4cat_db Error 1.9s ✔ Container 4cat_memcached Healthy 10.9s ✔ Container 4cat_backend Created 0.1s ✔ Container 4cat_frontend Created 0.1s dependency failed to start: container 4cat_db is unhealthy

Any suggestions on the best fix?

[dale-wahl]

Hey @paskn, thank you for the update. Please try the following command (assuming you did not change the name of your postgres docker volume (DOCKER_DB_VOL=4cat_4cat_db in your .env file) otherwise update 4cat_4cat_db to the name used): docker run --rm -it -v 4cat_4cat_db:/var/lib/postgresql/data alpine cat /var/lib/postgresql/data/PG_VERSION

That should read the PG_VERSION file that was used to originally create your 4CAT postgres database. That version can then be used for POSTGRES_TAG in your .env file (e.g. POSTGRES_TAG=15 if the previous command returned 15). Then Docker should pull the correct postgres version. So long as you did not delete and recreate your volume, your data should be there.

@robbydigital this should help you as well. Run above docker run... command -> update POSTGRES_TAG= in your .env file -> rerun the docker down, pull, up commands.

We have bound the postgres version to 17 and will have to evaluate if and how we support future postgres versions. Let us know if this resolves your issues and we will update our documentation for other users to walk them through this. Thank you for the help and your patience.

Note: you could do something like docker run --rm -it -v 4cat_4cat_db:/var/lib/postgresql/data alpine sh to mount the volume, connect to the container, and explore it further. You do not need to use alpine either; just some container that works with the volume and has the commands you are interested in running. Warning: data is live and you can edit it permanently so backing up is always recommended.