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

61–70 of 93 posts

Re: A PostgreSQL Docker container that automatically upgrades your database

#61

Post by amluto »

neilv wrote: post #54

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

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#62

Post by ekidd »

neilv wrote: post #54

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

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#63

Post by abrookewood »

ekidd wrote: post #62
neilv wrote: post #54

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

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

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#64

Post by spiffytech »

ekidd wrote: post #62
neilv wrote: post #54

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

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

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#65

Post by justinclift »

rcfox wrote: post #53
justinclift wrote: post #43

Earlier quoted context omitted.

Is there better wording that's concise? I'm just going with what I thought of, but am happy to adjust things as makes sense. :)

"A PostgreSQL Docker container that automatically upgrades your database" Still uses "upgrade", but highlights the fact that you're changing the artifact and not just the software.

Thanks, that sounds good. Looks like I can't edit the title for this any more now though.

I'll email the HN mods and ask them if they can do it. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#66

Post by justinclift »

WirelessGigabit wrote: post #30

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

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

Re: A PostgreSQL Docker container that automatically upgrades your database

#67

Post by justinclift »

justinclift wrote: post #66
WirelessGigabit wrote: post #30

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

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…

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#68

Post by cortesoft »

awinter-py wrote: post #59

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

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.

Re: A PostgreSQL Docker container that automatically upgrades your database

#69

Post by justinclift »

justinclift wrote: post #31
ndsipa_pomu wrote: post #29

Earlier quoted context omitted.

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.

Thanks. I'll make that update in the morning too. :)

Warning added in https://github.com/justinclift/docker-pgautoupgrade/commit/e....

Thanks heaps. :)

Re: A PostgreSQL Docker container that automatically upgrades your database

#70

Post by javajosh »

justinclift wrote: post #23
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. :)

This looks like this will use twice the disk.
Post reply on HN