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

171–180 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#171

Post by benoitg »

sudhirj wrote: post #142
benoitg wrote: post #127

Earlier quoted context omitted.

Not really, the innodb_file_per_table variable has been set to 1 for a long time. Running OPTIMIZE TABLE frees up the disk space in this case.

Is this process materially different from a vacuum? Does it manage to optimise without a write lock?

I don't know how VACUUM works, I couldn't tell you about the differences.

The OPTIMIZE works almost exclusively with online DDL statements. There's only a brief table lock held during table metadata operations, but I haven't found that to be a problem in practice. (https://dev.mysql.com/doc/refman/8.4/en/optimize-table.html#...)

Re: Why does everyone run ancient Postgres versions?

#172

Post by kstrauser »

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…

To be fair to PostgreSQL, it has multiple replication setups because they have different inherent properties. For instance, it comes with built in streaming and logical replication setups. Which should you use? It’s impossible to say without knowing your own exact use case.

By analogy: should a database cluster optimize consistency or availability? Answer: there’s no way its author can guess which is more important to how you want to use it.

Re: Why does everyone run ancient Postgres versions?

#173

Post by KronisLV »

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…

> Doing similar for my Linux VPS with only a terminal is much more complicated.

Debian/Ubuntu:

  sudo apt update
  sudo apt upgrade
Fedora/RHEL:

  sudo dnf update
Arch:

  sudo pacman -Syu
Alpine Linux:

  apk update
  apk add --upgrade apk-tools
  apk upgrade --available
Of course, if the package you need isn't available in the standard repos, then you'll need to look elsewhere (e.g. PPAs or third party repos). There's also options like Flatpak and AppImage if you want something that's a bit closer to how you'd choose to install new releases on Windows.

If I wanted to update all of the installed software I have on my Windows install, there'd basically be no way for me to do this, outside of shady update manager software.

At the same time, I get the appeal of being able to just download a new release and install it, both AppImage on Linux and the way you install software on macOS (just drag the file into Applications) seem similarly pleasant to me in that regard.

To expand on the latter (the response got deleted), you can very much do something like https://peazip.github.io/peazip-linux.html (I wish 7-Zip was available on Linux natively, but as far as GUI software goes, PeaZip is pretty nice) however that's not the most common approach. You should generally prefer using the package manager when you can.

Re: Why does everyone run ancient Postgres versions?

#174

Post by Dalewyn »

atoav wrote: post #164
Dalewyn wrote: post #128

Earlier quoted context omitted.

One of the first laws of the universe that a good engineer learns is: Do not fix what is not broken. And no, being old is not broken.

This is solid advice, however I would caveat that you can't know if it is broken if you are not checking. E.g. if your old as hell database has a known vulernability that can be expoited, unless you can rule out that it can be used in your setup it is broken by all definitions of the word.

It's not a caveat since checking is a prerequisite to the law that shall not be violated.

If you are "fixing" a Schroedinger's Cat, my dude the Supreme Court of the Universe will not be kind.

Re: Why does everyone run ancient Postgres versions?

#175

Post by x-_-x »

webprofusion wrote: post #94

Lol, try upgrading old MongoDB stuff. Database engines (every single one) are notorious for incompatibilities between major versions, upgrading mission critical stuff means updating and re-testing entire applications, which in some cases can be a multi-million dollar process, before going into production. Even if you deeply know/think that there's no problem upgrading, if something does fail in production after an up…

This. I was tasked with upgrading Postgresql from a very old version (I think 9?) to one that was still supported a couple of years ago. Backwards compatibility is paramount and from my experience upgrading MySQL/MariaDB I know that changes in versions can break it.

For this reason, I chose to upgrade to version 11 because it was only a couple of versions apart and still had repositories available at the time.

So the first thing I do is stop the VM and take a snapshot. Then I start it back up and go check for database corruption before I dump them... wait there's no utility to check for corruption...? Yep that's right! You basically have to YOLO the whole thing and hope it works. OK...

So I dump the databases and back up the directory. I shut down the old version and then install the new version from the repo. I start to import the databases and notice in the scrolling logs that there's some incompatibility... Oh F*$&. I google the error and spend a good hour trying to figure it out. Apparently there are external plugins for postgres that were installed in the old version. I search for the plugins online and they are long discontinued. OK, so let's just copy them over from the old version. I stop postgres, copy them over, and start it back up. It starts up ok. I reimport the databases and no more errors. Yay! I start the applicationsand pray to the SQL gods. So far so good, everything seems to work.

Thankfully the applications tested well and worked post upgrade.

All of this was done for a critical multi-million dollar healthcare platform. There were no official guides. Nothing. I had to find some random admin's blog for guidance. MySQL on the other hand has full documentation on just about every aspect of everything. The whole process was super hackish and not having any way to check database integrity would have been a show stopper for me had I designed this configuration.

Re: Why does everyone run ancient Postgres versions?

#176

Post by [deleted] »

KronisLV wrote: post #173
thrdbndndn wrote: post #116

Earlier quoted context omitted.

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…

> Doing similar for my Linux VPS with only a terminal is much more complicated. Debian/Ubuntu: sudo apt update sudo apt upgrade Fedora/RHEL: sudo dnf update Arch: sudo pacman -Syu Alpine Linux: apk update apk add --upgrade apk-tools apk upgrade --available Of course, if the package you need isn't available in the standard repos, then you'll need to look elsewhere (e.g. PPAs or third party repos). There's also options…

[deleted]

Re: Why does everyone run ancient Postgres versions?

#177

Post by thrdbndndn »

MaKey wrote: post #168
thrdbndndn wrote: post #116

Earlier quoted context omitted.

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…

> Doing similar for my Linux VPS with only a terminal is much more complicated. sudo apt-get install p7zip-full

I'm replying to the "the worst possible option on Linux", i.e. when the said software is not available in package manager. 7-zip is just a (bad) example; since you can install 7-zip using `choco install 7zip.install` on Windows too.

I meant to say when you can't find the software you want in package manager, it's easier to download it manually and install it on Windows than (again, unfair comparison) a terminal-only Linux server.

Re: Why does everyone run ancient Postgres versions?

#178

Post by thrdbndndn »

KronisLV wrote: post #173
thrdbndndn wrote: post #116

Earlier quoted context omitted.

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…

> Doing similar for my Linux VPS with only a terminal is much more complicated. Debian/Ubuntu: sudo apt update sudo apt upgrade Fedora/RHEL: sudo dnf update Arch: sudo pacman -Syu Alpine Linux: apk update apk add --upgrade apk-tools apk upgrade --available Of course, if the package you need isn't available in the standard repos, then you'll need to look elsewhere (e.g. PPAs or third party repos). There's also options…

Sorry I wasn't very clear, it's totally on me.

On average, the experience of upgrading/managing packages is obviously much better than Windows.

I meant to say in certain cases (like the `unzip` example I mentioned above), when the system's build-in package manager fails, I seem to not be able to find alternatives like what I did on Windows (just find the piece of binary I want and manually install it). I to this day still can't find a way to update `unzip` to a version that supports AES on my Debian VPS.

Re: Why does everyone run ancient Postgres versions?

#179

Post by MichaelZuo »

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?

Re: Why does everyone run ancient Postgres versions?

#180

Post by vbezhenar »

Dalewyn wrote: post #128
atoav wrote: post #123

Earlier quoted context omitted.

Also: 5. If your IT department is spread thin already and that old version is running fine, the incentive to potentially create more work for yourself is not gigantic.

One of the first laws of the universe that a good engineer learns is: Do not fix what is not broken. And no, being old is not broken.

It leads to a lot of old software which is not going to be upgraded ever. Then the entire project dies and gets rewritten from the scratch, because nobody wants to work with Windows 2003 server running Delphi 7, Java 1.4 and Oracle 9i in 2020 (personal experience).

Old software is not necessarily broken, but it is always a tech debt. And you can't live in debt forever, our IT does not work this way.

Post reply on HN