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. 😄

[duracell]

Thanks for hiding the warning in the issues...
Why not put it anywhere on the readme.
Saying that you need to adapt is great, but after doing this for hours, breaking stuff and then reading that people already asked you to put a warning about the problems in the readme is really annoying.
If putting one sentance about this in the readme is too much work after you discuss this here, maybe it is better to archive the project =/

[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).

[Alestrix]

I used this upgrade container (thanks a lot for providing it!!) to update our alpine-based postgres database and it seemed to work at first. Only when the application was accessing the database I later saw log messages complaining about wrong collation. To get around this I built an alpine-based upgrade-postgres image.

Since I only needed one for 13-to-17 I didn't bother making this universal. But here's the Dockerfile I used if anyone cares. Not fully tested yet, but it seems to do the trick:

FROM postgres:17-alpine

RUN head -n2 /etc/apk/repositories | sed 's#/alpine/v[0-9.]*/#/alpine/v3.19/#' >> /etc/apk/repositories

RUN set -eux; \
	apk update; \
	apk add postgresql13

ENV PGBINOLD=/usr/bin
ENV PGBINNEW=/usr/local/bin

ENV PGDATAOLD=/var/lib/postgresql/13/data
ENV PGDATANEW=/var/lib/postgresql/17/data

RUN set -eux; \
	mkdir -p "$PGDATAOLD" "$PGDATANEW"; \
	chown -R postgres:postgres /var/lib/postgresql

WORKDIR /var/lib/postgresql

COPY docker-upgrade /usr/local/bin/

ENTRYPOINT ["docker-upgrade"]

# recommended: --link
CMD ["pg_upgrade"]

[Alestrix]

@dargmuesli I saw your PR after I made that Dockerfile. Yours has the issue that older (13 and 14) postgres versions could not be migrated from. See the first RUN line in my Dockerfile above to get around the issue. Other than that your PR is much cleaner and of course universal.

[justinclift]

@Alestrix Out of curiosity, did you take a look at the pgautoupgrade repo images? They support Alpine and shouldn't cause the collation issue you saw.

[Alestrix]

@Alestrix Out of curiosity, did you take a look at the pgautoupgrade repo images? They support Alpine and shouldn't cause the collation issue you saw.

Unfortunately not. I saw pgautoupgrade after I already had migrated our musl postgresses to v17 so didn't have the urge to dive deeper.

[justinclift]

No worries, maybe next time. 😄