Break database made with Alpine-based container

[TheFrenchGhosty]

I tried to update a postgres database running on postgres:12.13-alpine3.17 to postgres:14.6-alpine3.17 (or postgres:14.6-bulleyes).

Everything worked, however I noticed that the software using it couldn't commit anything to it anymore.
This error appears every time it tries to write to the database:
ERROR: duplicate key value violates unique constraint "[REDACTED]" SQL error updateCachedValue: ERROR: current transaction is aborted, commands ignored until end of transaction block (a ROLLBACK; didn't solve it)

My guesses:

After looking at your Dockerfile it looks like you're running the "updater" on a Debian container.

Database made with an Alpine-based container are known to not be compatible with Debian-based container (and vice versa), it looks like it's the problem here.

A solution that might work is to have new containers where the "updater" run on Alpine. For now I think you should add a warning that says that this project isn't compatible with database made with alpine-based container.

[tianon]

Ah yep, for Alpine-based builds you'll probably want to use an approach more similar to #13

[E1k3]

@tianon Please add a big fat warning to the README instead of just pinning this issue.
Otherwise people only find out about this after they broke their database and have to roll back everything.

[tianon]

I'm not sure how much more clear I could make that users should not blindly use this project as-is 😕

This is the very first line of the project readme (with a similar notice in the repository description):

This is a PoC for using pg_upgrade inside Docker -- learn from it, adapt it for your needs; don't expect it to work as-is!

I don't actually actively maintain this project, and I certainly don't get paid to do so.

There hasn't been a single functional change to anything here in roughly seven years (the last change to the docker-upgrade script). I've got automation that bumps all the versions, but even that's entirely best effort and if it ends up becoming more work or more fragile than it is today, I'll likely stop maintaining it and archive the repository instead.

[E1k3]

Sorry, I didn't mean to offend.
The way things broke for me just caused a lot of confusion, because it seamed to just "work as-is" despite your warning and collation warnings only popped up later with no clear indication that it may be a libc vs musl (or debian vs alpine) problem - none of which is your fault, of course.

Its just that I know several people that are only drawn towards postgres because there is an official alpine option, so I expected these problems to be a fairly common occurrence when upgrading using your method, hence the proposed warning.
Thank you anyways for this PoC, its an elegant solution to the problem of upgrading postgres containers.

[justinclift]

For people that run Alpine based PostgreSQL images, this "automatic upgrade" container might be of interest:

https://github.com/pgautoupgrade/docker-pgautoupgrade

---

2024.09.30 Update - There are Debian (Bookworm) based ones in that repo now too, not just Alpine. Those will work for users of Debian based containers. 😄

[dargmuesli]

I've opened #108 to kick of an alpine version for this tool! Please give it a review :)

[tianon]

To be clear, I do not believe Alpine Linux is actually "supported" by PostgreSQL upstream (and the support for it in Docker Official Images is more of a "it builds and mostly works" kind of thing, just like Alpine support in many of the official images there; there's even a big Alpine variant caveat in that image's readme on Docker Hub).