Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A PostgreSQL Docker container that automatically upgrades your database

github.com

Post reply on HN
Print view View original

11–20 of 93 posts

Re: A PostgreSQL Docker container that automatically upgrades your database

#11

Post by johannes1234321 »

justinclift wrote: post #8
Alifatisk wrote: post #5

I have always wondered how a database and its persistence is handled in a containerized environment. This covers one of the issues.

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.

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#12

Post by justinclift »

bheadmaster wrote: post #10
justinclift wrote: post #8

Earlier quoted context omitted.

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.

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

Cool, thanks all. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#13

Post by hotpotamus »

justinclift wrote: post #8
Alifatisk wrote: post #5

I have always wondered how a database and its persistence is handled in a containerized environment. This covers one of the issues.

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.

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#14

Post by justinclift »

hotpotamus wrote: post #13
justinclift wrote: post #8

Earlier quoted context omitted.

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.

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 soluti…

Heh Heh Heh

I'm just pointing out how it's commonly done. Of course people add things like replication, distributed filesystems, (etc) to the mix to suit their needs. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#15

Post by ndsipa_pomu »

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?

Re: A PostgreSQL Docker container that automatically upgrades your database

#16

Post by Kab1r »

hotpotamus wrote: post #13
justinclift wrote: post #8

Earlier quoted context omitted.

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.

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 soluti…

I've had bad luck with longhorn, but I have heard good things about using Rook with Ceph for PVCs

Re: A PostgreSQL Docker container that automatically upgrades your database

#17

Post by geenat »

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#18

Post by justinclift »

ndsipa_pomu wrote: post #15

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 aut…

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#19

Post by justinclift »

geenat wrote: post #17

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.

> 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

Re: A PostgreSQL Docker container that automatically upgrades your database

#20

Post by justsomehnguy » 

  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.
Post reply on HN