A PostgreSQL Docker container that automatically upgrades your database

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

by justinclift • 3 years ago

198 88 3 years ago

VWWHFSfQ• 3 years ago

Just 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?

justinclift26• 3 years ago

It 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• 3 years ago

justinclift26• 3 years ago

This 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?

Alifatisk2• 3 years ago

I have always wondered how a database and its persistence is handled in a containerized environment.

This covers one of the issues.

justinclift26• 3 years ago

With 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• 3 years ago

Both Docker and Kubernetes can use volumes to provide persistant storage to containers/pods without bind mount.

johannes12343214• 3 years 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.

hotpotamus2• 3 years ago

Saving it on one host's local filesystem doesn't feel particularly production-ready. There is a distributed store system for Kubernetes called "Longhorn" that I've heard good things about, but I haven't really looked into it much myself. I just run a pair of VMs with a manual primary/replica setup and have never needed to fail over to the replica yet, but I can imagine some sort of fully orchestrated container solution in the future.

johannes12343214• 3 years ago

The way you handle persistence is by using storage volumes mounted form the outside. Don't put the data in the container, only the software, which then can be replaced. Then managing is similar to other environments. For updating you don't replace rpm/deb packages and restart, but replace the container using the same volume, which should trigger the dbms-specific update routine. Similarly you do backups similar to outside container world (depending on DBMS and your exact choice, from filesystem snapshot to logical backup (dump) or something more or less smart in between)

Alifatisk2• 3 years ago

So all the containers access the same volume on the host? Does that scale well or will it bottleneck at one point?

Or is that when you have a layer of cache inbetween like Redis?

ndsipa_pomu3• 3 years ago

This 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?

justinclift26• 3 years 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• 3 years ago

Seems def interesting to handle backup, wonder if could just be something simple enough that lets you downgrade if upgrade fails

ndsipa_pomu3• 3 years ago

A default option is to warn about the possibility and then just leave the admins to do their own backups (which they should be doing anyway).

jacooper• 3 years ago

I think following MariaDBs lead is a good idea, thru have an environment variable to enable automatic upgrades, and it creates a backup before every upgrade. Works like a charm.

geenat• 3 years ago

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

justinclift26• 3 years 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

justsomehnguy3• 3 years ago

  mkdir /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.

justinclift26• 3 years ago

Cool, good thinking, thanks heaps. I'll implement that in the morning. :)

javajosh• 3 years ago

This looks like this will use twice the disk.

justinclift26• 3 years ago

Implemented in https://github.com/justinclift/docker-pgautoupgrade/commit/0....

I think I got everything right, and it passes the test harness, but please give it a look over if you have a few minutes. :)

WirelessGigabit• 3 years ago

I 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?

justinclift26• 3 years ago

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

justinclift26• 3 years ago

I'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.

mkarrmann• 3 years ago

Neat :)

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.

justinclift26• 3 years ago

Yeah, a lot of the time I'd agree with you. :)

This container came about for the Redash project (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. :)

cosmotic• 3 years ago

I 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• 3 years ago

yes. i do have a few developer environments this would be great for though.

hamandcheese• 3 years ago

I think the point of this container is to be that explicit admin action.

forsakenharmony• 3 years ago

Changing the docker container version is still explicit? Also, you don't have to worry about having both versions of postgres installed with this

wokkel• 3 years ago

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

alphager2• 3 years ago

This is for the school of "the container is immutable". They don't want to use a years old container and upgrade the binaries inside.

mbreese2• 3 years ago

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

justinclift26• 3 years ago

It's pretty much answered here: https://news.ycombinator.com/item?id=36747498

The upgrade-of-your-data happens automatically when the container starts, prior to starting the database server.

mbreese2• 3 years ago

Right, 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)

Izkata• 3 years ago

Postgres docs do use the word upgrade, for example:

pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades

https://www.postgresql.org/docs/current/pgupgrade.html

lmeyerov• 3 years ago

That'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.

neilv2• 3 years 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• 3 years ago

In 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• 3 years ago

Even RDS can't do in place upgrades.

amluto2• 3 years ago

I 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• 3 years ago

Can you elaborate on what made it crap?

ekidd• 3 years ago

RDS is very useful for companies which are big enough to employ, say, 2 programmers, but still too small to employ a DBA.

The hard part of running a database, in my experience, isn't setting up or running it. The hard part isn't even configuring backups.

The hard part is noticing that your backups have been broken for years, before you actually need to restore from them. Yes, yes, you know how to do this correctly. But you delegated it to the sysadmin, the sysadmin subtly broke the backup scripts, the scripts have been silently doing nothing for 18 months, and then the sysadmin got a new job.

This is the main value proposition of RDS: Your data will be backed up, your backups will be restorable, and most of your normal admin tasks can be performed by pushing a button.

abrookewood• 3 years ago

Agreed ... which is why we test our restores periodically. Feels annoyingly like 'wasted' time, but it's worth it.

spiffytech• 3 years ago

Your data will be backed up, your backups will be restorable

Well, usually...

https://news.ycombinator.com/item?id=13620622

Pretty sure I've read similar stories more recently. Probably still better odds of successful restore with RDS than with a home-rolled setup.

But there's no substitute for verifying backups.

KingOfCoders4• 3 years ago

In my startup (until our exit) I've run Postgres on my own, with streaming backups, daily backup tests (automatic restores and checks), offsite backups and (slow) upgrades.

Data wasn't mission critical and customers could live with 5min downtimes for upgrades once a year.

No problem for years. When we had hosted Mongo, we had more problems.

If I have the money I'll use a managed database. But running Postgres in a startup up to $10m ARR / ~1TB data seems not like a problem.

sharts• 3 years ago

While true, you'd think something as important as a DB would be something worthy of creating some kind of nightly automated testing/verification of a restore procedure (or multiple).

neilv2• 3 years ago

Exactly, on the backups. There was off-the-shelf open source PITR software that I looked at and could've configured, but I couldn't justify spending all the time to test that setup, given that (supposedly) RDS was rock-solid turn-key. There were other engineering and ops things that needed my time more.

user6723• 3 years ago

Mutable containers, what could go wrong?

alphager2• 3 years ago

This 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• 3 years ago

useful, 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• 3 years ago

If you run your DB as a cluster using the Percona Cluster Operator, you can upgrade the whole cluster in place with a single command.

A PostgreSQL Docker container that automatically upgrades your database

Related Stories