A PostgreSQL Docker container that automatically upgrades your database • Hacker News Board

Re: A PostgreSQL Docker container that automatically upgrades your database

#21

Post by tough » Sun, Jul 16, 2023, 2:40 PM UTC

justinclift wrote: post #18

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…

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#22

Post by tempest_ » Sun, Jul 16, 2023, 2:40 PM UTC

johannes1234321 wrote: post #11

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.

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

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#23

Post by justinclift » Sun, Jul 16, 2023, 2:41 PM UTC

justsomehnguy wrote: post #20

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…

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#24

Post by justinclift » Sun, Jul 16, 2023, 2:45 PM UTC

tough wrote: post #21

justinclift wrote: post #18

Earlier quoted context omitted.

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

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

Yeah. Probably going to need to add a bunch more error catching and handling-of-specific-situations. Edge cases being a thing and all. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#25

Post by ndsipa_pomu » Sun, Jul 16, 2023, 2:56 PM UTC

justinclift wrote: post #18

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…

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#26

Post by justinclift » Sun, Jul 16, 2023, 2:57 PM UTC

ndsipa_pomu wrote: post #25

justinclift wrote: post #18

Earlier quoted context omitted.

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

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

What do you reckon the right place(s) to warn people would be?

Re: A PostgreSQL Docker container that automatically upgrades your database

#27

Post by mschuster91 » Sun, Jul 16, 2023, 2:59 PM UTC

tempest_ wrote: post #22

johannes1234321 wrote: post #11

Earlier quoted context omitted.

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

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

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#28

Post by johannes1234321 » Sun, Jul 16, 2023, 3:00 PM UTC

tempest_ wrote: post #22

johannes1234321 wrote: post #11

Earlier quoted context omitted.

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

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

There is a huge push for doing that. Whether it is abstract the right thing can be questioned, but many IT departments decided to standardize around Kubernetes for all datacenter management and push that way and in some environments (5G networking) it's part of the specified stack.

Re: A PostgreSQL Docker container that automatically upgrades your database

#29

Post by ndsipa_pomu » Sun, Jul 16, 2023, 3:06 PM UTC

justinclift wrote: post #26

ndsipa_pomu wrote: post #25

Earlier quoted context omitted.

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

What do you reckon the right place(s) to warn people would be?

Probably in the project readme. You could put a warning in the logs as well (e.g. on container startup), but that wouldn't be as visible.

Re: A PostgreSQL Docker container that automatically upgrades your database

#30

Post by WirelessGigabit » Sun, Jul 16, 2023, 3:06 PM UTC

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?