search

tianon / docker-postgres-upgrade

a PoC for using "pg_upgrade" inside Docker -- learn from it, adapt it for your needs; don't expect it to work as-is!
https://hub.docker.com/r/tianon/postgres-upgrade/
MIT License
1.09k stars 116 forks source link

Break database made with Alpine-based container #71

Open TheFrenchGhosty opened 1 year ago

TheFrenchGhosty commented 1 year ago

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 commented 1 year ago

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

E1k3 commented 1 year ago

@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 commented 1 year ago

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

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 commented 1 year ago

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 commented 1 year ago

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. :smile:

duracell commented 7 months ago

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 commented 1 month ago

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

tianon commented 1 month ago

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