Upgrade to v5.0.0 broken

[fullpwemium]

Version:

• listmonk: 5.0.0
• OS: docker

** IMPORTANT: Take a backup of the database before upgrading.
continue (y/n)?  2025/04/29 10:35:12.527060 upgrade.go:75: running migration v5.0.0
2025/04/29 10:35:12.528975 v5.0.0.go:13: IMPORTANT: this upgrade might take a while if you have a large database. Please be patient ...
2025/04/29 10:35:13.355466 upgrade.go:77: error running migration v5.0.0: pq: ALTER TYPE ... ADD cannot run inside a transaction block

Can't open list monk anymore

[lamurian]

Hey, thank you for reporting this. I suppose our issues are somewhat related: #2437

[knadh]

@fullpwemium what Postgres version are you using?

@lamurian the issue you described is normal behaviour. Upgrading listmonk requires running ./listmonk --upgrade

[jeff-h]

I have the same outcome as @fullpwemium. I checked, mine's on postgres:11 🙈 I'm going to go out on a limb and say that's the cause 😆

[jeff-h]

I've put in the steps I took in the hopes it might help someone else to move up to v5.

On your VPS docker host:

# Identify the listmonk db container name. Mine's `db`
docker ps

# Dump the database to the docker host filesystem. There's a chance 
# your Postgres user may not be `listmonk`. Check your docker-compose
# file for `POSTGRES_USER=listmonk` or `.env` for `POSTGRES_USER` etc
docker exec db pg_dumpall -U listmonk > backup.sql

Update your docker-compose.yml as per here. You'll also have to either remove the volume your old postgres container used, or modify the compose file's source: listmonk-data to source: listmonk-data17 or such, because (I believe) Postgres 17 cannot read the data directory of an older install. It'll need a new clean volume.

Run docker compose up -d to bring up the new postgres:17-alpine container with its clean-slate data volume.

Then:

# Restore the backup into the new container.
cat backup.sql | docker exec -i db psql -U listmonk

# Upgrade listmonk as per https://listmonk.app/docs/upgrade/#docker
docker compose run --rm app ./listmonk --upgrade

# The above failed for me with auth failure; I ended up having to reset the
# password for the `listmonk` postgres user. GPT had good answers for that.

# Check the listmonk app container is up and running again. May need to
# check a few times, giving it time to start, or even re-run `docker compose up -d` again.
docker ps

• note: I have used docker compose in the notes above, rather than the older docker-compose. Adjust according to the version you are running.
• note 2: also refer to this comment

[fullpwemium]

@fullpwemium what Postgres version are you using?

@lamurian the issue you described is normal behaviour. Upgrading listmonk requires running ./listmonk --upgrade

I'm on postgres:11

[jeff-h]

@fullpwemium mine works fine after having moved to Postgres 17 as per my comment above.

[fullpwemium]

@fullpwemium mine works fine after having moved to Postgres 17 as per my comment above.

Will be a challenge, have a lot of databases on there. Listmonk not support 11 anymore?

[jeff-h]

I'm sure you've considered it, but could you spin up a postgres 17 container just for listmonk (assuming it's all Dockerised)?

[fullpwemium]

I'm sure you've considered it, but could you spin up a postgres 17 container just for listmonk (assuming it's all Dockerised)?

I am dockerised, but on a very resource limited Lightsail instance. Would rather upgrade it all to 17 if nothing goes wrong - have you tried it before?

[jeff-h]

Ah, yes I understand re: resource usage.

As per above I did just today upgrade my postgres 11 to 17 and Listmonk 5.0.0 is running beautifully, but the db container did just have the one Listmonk db and nothing else. In that context, nothing went wrong once I stepped through the process (which honestly wasn't fun, but in my experience Postgres never is).

Actually, my notes in the issue above will (I think) dump every db in the entire container. Are you able to have a crack and just do a trial run (perhaps even try getting it onto your dev machine)? I do guess it will be quite a bit of work though.

[satonotdead]

Previous comment worked for me taking in mind that changing the source: on data-volume needs to be updated also on the bottom:

volumes:
  listmonk-data-17:

And this command needs to use a lowercase i:

cat backup.sql | docker exec -i db psql -U listmonk

[jeff-h]

@satonotdead thanks for the heads-up on the typo. I've fixed it in the original comment and added a reference to yours in case it helps anyone in the future.

[github-actions[bot]]

This issue has been marked 'stale' after 90 days of inactivity. If there is no further activity, it will be closed in 7 days.