7 ms·
A PostgreSQL Docker container that automatically upgrades your database
- VWWHFSfQ 3y agoJust to be clear, this isn't for actually running postgres to serve your database, right? This is just more or less a tool to upgrade your data files to a new postgres version?
- justinclift 3y agoIt does both. As part of the startup process it first checks if the data files are for an old version, and upgrades them first (if needed). Then it starts PostgreSQL 15.3 as per normal. It's intended as being a drop-in replacement for existing (alpine based) PostgreSQL containers. That being said, it's still pretty new so don't use it on production data until you've tested it first (etc). -- Btw, it uses the "--link" option when it runs pg_upgrade so should be reasonably suitable even for databases of a fairly large size. That option means it processes the database files "in place" to avoid needing to make a 2nd copy. At the moment it only process a single data directory, so if you have multiple then it's not yet suitable.
- develatio 3y agoRelated: https://github.com/tianon/docker-postgres-upgrade https://github.com/tianon/docker-postgres-upgrade
- justinclift 3y agoThis one seems like a good proof of concept, but appears to need running as a separate task for upgrading rather than being an "automatic upgrade before starting the database server" thing?
- Alifatisk 3y agoI have always wondered how a database and its persistence is handled in a containerized environment. This covers one of the issues.
- justinclift 3y agoWith PostgreSQL containers used in docker-compose, the common approach is to use a bind mount so the database files are persisted on the host. I've not done stuff with kubernetes yet though, so I have no idea how it's done there.
- bheadmaster 3y agoBoth Docker and Kubernetes can use volumes to provide persistant storage to containers/pods without bind mount.
- justinclift 3y agoCool, thanks all. :)
- johannes1234321 3y ago> I've not done stuff with kubernetes yet though, so I have no idea how it's done there. Essentially the same, except that K8s gives you a wide variety of storage backend integrations (Storage classes + storage providers) which can attach "anything" (local volumes on the node, NFS, NAS, Cloud Volumes, ...) depending on your local environment and needs.
- tempest_ 3y agoAre people putting their databases in K8s now? I thought the old rule of thumb was don't do that but perhaps it has changed. Feels like the database would take a massive performance hit using network backed storage unless the software is aware of that fact.
- mschuster91 3y agoIf you're running in the cloud, say on AWS EKS, it makes sense to use in-cluster databases for development environments, and only use RDS DB's for production/integration to save on hosting costs.
- deleted 3y ago[deleted]
- ndsipa_pomu 3y agoThis looks useful. I've had issues in the past with PostgreSQL as a backend for NextCloud (all running in docker) and blindly pulling the latest PostgreSQL and then wondering why it didn't work when the major version jumped. (It's easy enough to fix once you figure out why it's not running - just do a manual export from the previous version and import the data into the newer version). However, does this container automatically backup the data before upgrading in case you discover that the newer version isn't compatible with whatever is using it?
- justinclift 3y ago> However, does this container automatically backup the data before upgrading ... Nope. This container is the official Docker Postgres 15.3-alpine3.18 image + the older versions of PostgreSQL compiled into it and some pg_upgrade scripting added to the docker entrypoint script to run the upgrade before starting PostgreSQL. It goes out of it's way to use the "--link" option when running pg_upgrade, to upgrade in-place and therefore avoid making an additional copy of the data. That being said, this is a pretty new project (about a week old on GitHub), and having some support for making an (optional) backup isn't a bad idea. I'll have to think on a good way to make that work. Probably needs to check for some environment variable as a toggle or something, for the people who want it... (unsure yet).
- tough 3y agoSeems def interesting to handle backup, wonder if could just be something simple enough that lets you downgrade if upgrade fails
- justinclift 3y agoYeah. Probably going to need to add a bunch more error catching and handling-of-specific-situations. Edge cases being a thing and all. :)
- westurner 3y ago
- geenat 3y agoFor the uninitiated: Fairly certain this can only handle minor version upgrades... Which is generally as easy as moving your data folder beside the new binary and starting postgres.. (minor version upgrades are already trivial..) Major versions still require a pg_dump (or a scary migration using postgres' anemic logical replication) unless some advancement has happened on the postgres side I'm unaware of.
- justinclift 3y ago> Beware: Fairly certain this can only handle minor version upgrades ... Nope. It's entirely for upgrading between major PostgreSQL versions. :) It uses the PostgreSQL "pg_upgrade" utility to do the data upgrade behind the scenes: https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
- justsomehnguy 3y agomkdir /var/lib/postgresql/data/old mv -v /var/lib/postgresql/data/* /var/lib/postgresql/data/old/ mkdir /var/lib/postgresql/data/new You should create the both dirs first, then check if they do really exist and only then move the files. Shit happens and it's better be safe than sorry. Also I would replace ../old and ../new with $OLD and $NEW to have a bit less clutter in the next block with the explicit upgrade calls, ie: /usr/local/bin/pg_upgrade --link -d $OLD -D $NEW -b /usr/local-pg9.5/bin -B /usr/local/bin Overall it's nice idea, just need some more safety checking before starting the process.
- justinclift 3y agoCool, good thinking, thanks heaps. I'll implement that in the morning. :)
- javajosh 3y agoThis looks like this will use twice the disk.
- justinclift 3y agoNah. The concept of verifying that the mkdir's worked is sound and also easy to do. They just need doing individually in the script, as doing both in the same spot would muck up the wildcard a bit later on. Using variable names better ($OLD, $NEW) is a good idea too. Should cut down any potential typo risk as well. :)
- justsomehnguy 3y agoIf you prefer to work with the dirs under pgdata (well it makes sense...) you can just make the list of files (maybe even write it to the file, maybe even write a batch file which would move them) and use it for moving the data from pgdata to old. This saves an unnecessary move. Add: are sure about "${NEW}"/* in this? 444 mv -v "${NEW}"/* "${PGDATA}"
- minus10eng 3y ago[dead]
- mkarrmann 3y agoNeat :) I personally feel like upgrading a database should be an explicit admin process, and isn't something I want my db container entrypoint automagically handling.
- justinclift 3y agoYeah, a lot of the time I'd agree with you. :) This container came about for the Redash project (https://github.com/getredash/redash https://github.com/getredash/redash), which had been stuck on PostgreSQL 9.5 (!) for years. Moving to a newer PostgreSQL version is easy enough for new installations, but deploying that kind of change to an existing userbase isn't so pretty. For people familiar with the command line, PostgreSQL, and Docker then its no big deal. But a large number of Redash deployments seem to have been done by people not skilled in those things. "We deployed it from the Digital Ocean droplet / AWS image / (etc)". For those situations, something that takes care of the database upgrade process automatically is the better approach. :)
- deleted 3y ago[deleted]
- cosmotic 3y agoI disagree. It is (or was, last time I tried a year or two ago) hard to upgrade postgres because you need both the old and new binary. Package manager tries its best to allow just one copy. You end up copying from one image to another, then run into tiny breaking differences. I agree it should be explicitly invoked and not automated, for something almost everyone needs to do, it sure is a hard task.
- rc_mob 3y agoyes. i do have a few developer environments this would be great for though.
- hamandcheese 3y agoI think the point of this container is to be that explicit admin action.
- WirelessGigabit 3y agoI pin Postgres to vMajor. But I was bitten in the rear by Wallabag the other day. Didn't work anymore. Ended up having to do a doctrine upgrade. Then it all worked. Same concept I guess. No automatic upgrade does allow you to pause and evaluate what you're doing, and take backups. Now I say it allows you. Whether you (or I) do so... after all, that is what automatic backups are for... Now when did I last validate those?
- justinclift 3y agoReading through the responses, it seems like a good idea to have pgautoupgrade containers pinned to vMajor too. So (for example): pgautoupgrade/pgautoupgrade:15-alpine3.8 That one would upgrade the database to PostgreSQL 15, and not further. Simultaneously, there would be other other versions available too. For example: pgautoupgrade/pgautoupgrade:16-alpine3.8 This one would upgrade the database to PostgreSQL 16, and not further. That should be pretty straight forward to do. :)
- justinclift 3y agoI've just pushed an initial build with a tag for PostgreSQL 15: pgautoupgrade/pgautoupgrade:15-alpine3.8-v1 The "-v1" text fragment on the end is a version number because I'm still improving the scripting for the upgrade part. So that'll likely increment over time (-v2, -v3, -v4, etc) until it stabilises.
- wokkel 3y agoSo we come full circle it seems. Apt update/upgrade already does this and has been doing this for a few years now. So if i drop docker and just run a container with Debian I get this with a few years of testing behind it.
- alphager 3y agoThis is for the school of "the container is immutable". They don't want to use a years old container and upgrade the binaries inside.
- mbreese 3y agoThis is a very confusing title and project at first glance. It took me a minute to figure out what you’re doing… But just to be clear — the point of this is to migrate an existing Postgres DB from the original version to a newer version? This is about the data volume itself, not automatically upgrading the Postgres binary when a new version is released? So, you’d basically pass a flag that says --migrate-db when starting the container to kick start changing the data on disk? So when you start a new Postgres 15 container, you could pass it a volume with Postgres 13 data and it would auto update the on disk DB data.
- justinclift 3y agoIt's pretty much answered here: https://news.ycombinator.com/item?id=36747498 https://news.ycombinator.com/item?id=36747498 The upgrade-of-your-data happens automatically when the container starts, prior to starting the database server.
- mbreese 3y agoRight, but you’re not actually automatically migrating a Postgres 13 program to Postgres 15 in a running container. That was what I first thought when I read the title. You’re using the work “upgrade” differently than how I normally think about it when talking about version changes. You’re talking about data-on-disk, not just the program version. (I realize that when talking about Postgres, the two are linked, but that’s not the case for most programs)
- justinclift 3y agoIs there better wording that's concise? I'm just going with what I thought of, but am happy to adjust things as makes sense. :)
- deleted 3y ago[deleted]
- rcfox 3y ago
- lmeyerov 3y agoThat's great. We have been maintaining a manual migration script for our docker users for the DB part, while the app part does migrations automatically already (Django), so making the db part more built-in makes a lot of sense. Our case doesn't really need live/fully auto, just an easy mode for admins during upgrade cycles, and our scripts were pretty generic, so a project like this makes a lot of sense. There are a few modes we support - local / same-server, cross-node, etc - so am curious.
- neilv 3y ago> It's whole purpose in life is to automatically detect the version of PostgreSQL used in the existing PostgreSQL data directory, and automatically upgrade it (if needed) to the latest version of PostgreSQL. In a small startup... * If the data is mission-critical and constantly changing, PostgreSQL is a rare infra thing for which I'd use a managed service like AWS RDS, rather just Debian Stable EC2 or my own containers. The first time I used RDS, the company couldn't afford to lose an hour of data (could destroy confidence in enterprise customer's pilot project), and without RDS, I didn't have time/resources to be nine-9s confident that we could do PITR if we ever needed to. * If the data is less-critical or permits easy sufficient backups, and I don't mind a 0-2 year-old stable version of PG, I'd probably just use whatever PG version Debian Stable has locked in. And just hold my breath during Debian security updates to that version. (Although I think I saw AWS has a cheaper entry level of pricing for RDS now, which I'll have to look into next time I have a concrete need. AWS pricing varies from no-brainers to lunacy, depending on specifics, and specifics can be tweaked with costs in mind.)
- tomlue 3y agoIn my experience boot strapping a startup, RDS is really expensive. Definitely a nice thing, but if you're a one person show trying to get to ramen profitable, RDS might not really make sense. If you are VC backed then go ahead!
- nhumrich 3y agoEven RDS can't do in place upgrades.
- amluto 3y agoI tried to move a production MySQL database to RDS. Hours later, I decided that I had wasted hours on a crappy product, and I gave up. RDS, at least for MySQL, is, in my opinion, crap.
- kevinventullo 3y agoCan you elaborate on what made it crap?
- user6723 3y agoMutable containers, what could go wrong?
- alphager 3y agoThis is the opposite. The container is not mutable, but the datastore is. This alleviates the need to run Postgres X and Postgres X+1 to export from X and import into X+1.
- awinter-py 3y agouseful, but it's surprising that docker has not standardized upgrade / migration the closest thing to this is helm which like everything in the k8s ecosystem is either a useful tool or a single feature wrapped in a cursed configuration language, depending on your perspective not hard to imagine a single command to snapshot the volume, boot the new version, run a migration command, revert if something fails
- cortesoft 3y agoIf you run your DB as a cluster using the Percona Cluster Operator, you can upgrade the whole cluster in place with a single command.