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

Why does everyone run ancient Postgres versions?

neon.tech

Post reply on HN
Print view View original

181–190 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#181

Post by Netcob »

My personal reason: While I haven't had to deal with a Postgres update at work yet, I've been running a pretty large Postgres 12 database in my homelab for a few years now.

My homelab projects mostly center around a "everything is an MQTT message" idea. Zigbee sensors, Tasmota power readings, OwnTracks locations, surveillance camera events, motion sensors for light switches, currently active app on my PC, status of my 3D printer, whatever my vacuum robots are up to and so on. It all gets recorded into a Postgres db. From there I can use it for data mining experiments, but mostly as a source for Grafana. I tried counting the rows but that query didn't even complete while I was writing this comment.

I like trying out all kinds of dockerized oss services, and I keep them updated using watchtower. I run a gitlab instance which is usually the most annoying service to update because it there's an upgrade path and post-start-migrations. With my Postgres instance, which is isolated from the internet, I'll have to figure out what the fastest way is to move all that data around, not leave a huge gap in the record and so on. Sounds like at least a day of work - and since it's technically all for "fun", it'll have to wait until it actually is that.

Re: Why does everyone run ancient Postgres versions?

#182

Post by ogogmad »

p10_user wrote: post #55
polishdude20 wrote: post #45

Earlier quoted context omitted.

What's the SQL alternative?

JSON - er JSON-based document storage - documents with unique identifiers. and the ability to define and set schemas for the JSON, and ... we're back to a relational database

I think he was talking about the query language.

Re: Why does everyone run ancient Postgres versions?

#183

Post by jeremycarter »

jgb1984 wrote: post #129

I've used postgresql in most of my previous jobs, but using mariadb in my current one, and I must say it's a joy to administer. The replication and high availability that mariadb offers is rock solid, and much more advanced than what postgresql has to offer. It works out of the box, no plugins or third party shenanigans needed, and there is "one obvious way" to do it. Not a dozen options like in the postgresql ecosys…

I've maintained a few large postgres databases and completely agree.

Re: Why does everyone run ancient Postgres versions?

#184

Post by throwaway19972 »

MichaelZuo wrote: post #179
noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?

There are many answers to this question and few are as satisfying as the question implies.

Re: Why does everyone run ancient Postgres versions?

#185

Post by phil21 »

MichaelZuo wrote: post #179
noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?

My opinion is Postgres was designed by software developers for software developers. The split on “which relational database to use” in my career has almost always been perfectly split between SWE vehemently demanding pgsql for the feature set, and the sysadmins having to support maintenance and production availability preferring MySQL.

One of the few things I’ve enjoyed with the move into devops and companies forcing previously “pure” developers into operational roles was their discovery that Postgres was utterly horrible to administer at a systems level. Apparently us lowly sysadmins may have had a point after all.

This is a bit tongue in cheek but really not far from my lived reality. When the focus is on features and “correctness” at the near total expense of sane systems tooling folks can develop some myopia on the subject. So many arguments with devs on my teams over this subject that were utterly horrified to find we were running MySQL for a given service.

Open source projects tend to fix the pain points its contributors experience, and I assume there were not too many contributors wanting to deal with the boring work of making administration and easy task - it’s thankless “sideways” work that won’t result in many accolades or personal satisfaction for most SWEs.

The end users are almost always developers, most of whose experiences in production entail either the equivalent of a docker container level scale system, or are simply given a connection string and the rest is a black box to them. Under those contexts I’d personally prefer Postgres as well and it wouldn’t even be close. When you get into backups, clustering, upgrades, and high availability under extreme load? IMO the story falls apart real fast.

Re: Why does everyone run ancient Postgres versions?

#186

Post by reshlo »

sofixa wrote: post #112
thrdbndndn wrote: post #106

Earlier quoted context omitted.

Forget about something as major as Postgres, I have trouble updating packages (that aren't install via pip/npm/cargo) on Linux all the time as a newbie. The experience is worse than Windows for some reason. Hell, I have a hard time to tell the version of some system build-in binaries. A few months ago, I have trouble to unzip a file which turns out ot be AES-encrypted. Some answers on SO [1] saying I should update my…

> I have trouble updating packages (that aren't install via pip/npm/cargo) on Linux all the time as a newbie. The experience is worse than Windows for some reason If you haven't installed them via your programming language's package manager, you either installed them manually or via the OS package manager. The first one you'd know how to upgrade, and for the second you can ask it what version it is and what version i…

> In Windows you… google and download random .exes?

Windows has an official package manager now too.

Re: Why does everyone run ancient Postgres versions?

#187

Post by Symbiote »

Netcob wrote: post #181

My personal reason: While I haven't had to deal with a Postgres update at work yet, I've been running a pretty large Postgres 12 database in my homelab for a few years now. My homelab projects mostly center around a "everything is an MQTT message" idea. Zigbee sensors, Tasmota power readings, OwnTracks locations, surveillance camera events, motion sensors for light switches, currently active app on my PC, status of m…

A good approach for this is to use pg_upgrade in-place, which should give you a downtime of a few minutes at most. (I have 800GB at work and would expect 1-2 minutes for this.)

I recommend installing PG12 on a temporary VM, duplicating the existing database, and test the upgrade in isolation.

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

A more complicated approach uses replication, and upgrades the standby server before promoting it to the primary server.

Re: Why does everyone run ancient Postgres versions?

#188

Post by reshlo »

thrdbndndn wrote: post #116
sofixa wrote: post #112

Earlier quoted context omitted.

> I have trouble updating packages (that aren't install via pip/npm/cargo) on Linux all the time as a newbie. The experience is worse than Windows for some reason If you haven't installed them via your programming language's package manager, you either installed them manually or via the OS package manager. The first one you'd know how to upgrade, and for the second you can ask it what version it is and what version i…

Not exactly random. It's not hard to tell which website is official 7-zip website. Also choco and scoop exist on Windows. > As good as the worst possible option on Linux. I understand this is not a fair comparison, but in practice, they're not as easy. When using Windows, I usually use it with a proper GUI interface, so popping up a browser and download the newest installer for a software from their official website…

> choco and scoop

And winget.

Re: Why does everyone run ancient Postgres versions?

#189

Post by tgv »

noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

ADd to that: if it works, don't fix it.

Re: Why does everyone run ancient Postgres versions?

#190

Post by WJW »

MichaelZuo wrote: post #179
noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

So the real question is, why is the upgrade process so incompetently designed, and why has no one fixed this?

The answer to both is probably "because maintainer time is scarce and nobody is interested enough".
Post reply on HN