Why does everyone run ancient Postgres versions?
neon.tech
neon.tech
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 maintenance routines in your upgrade path.)
It requires having the previous version of Postgres installed, something which can mess with a number of distro policies (not to mention docker, which is the most popular way to deploy software that will typically rely on Postgres), and unlike most software with that issue, Postgres is software you want to be managed by your distro.
Therefore, most people only upgrade by necessity - when their distro forces the upgrade or the version they're using reaches EOL.
* Upgrades
* Database Protocol
MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time.
The MS SQL Server protocol, TDS, supports native named parameters, executes the T-SQL PL inline with SQL. TDS also supports inline query cancellations because the protocol is framed, and both T-SQL and TDS supports returning multiple result sets from a single query.
But yes, whenever I see PG docs saying, major versions require an export and re-load, I shudder.
I also really miss their query optimizer. It was just so damn smart.
Oracle's DBUA is so much more painful.
However, I agree with OP that SQL Server does have a solid upgrade story and it's generally pain free.
They are just two aspects I really wish Postgres could do better on, and I miss working with those parts of SQL Server.
1. Leave your old database running
2. Start your new database with new version
3. Run logical replication and let it catch up
4. Decide you want to switch over.
5. Using a script: Stop your apps writing to the database (or switch to read only mode), let logical replication catch up one final time, stop logical replication, run a procedure to fix your sequences, and switch your apps over to the new database.
You are now done. Sure, it requires a one-time investment to work out the exact steps needed for your situation, but it's easy to do with minimal downtime.
Of course if you don't care about downtime just shut down your database server, make a backup(!), and then do pg_upgrade.
I think this is the right answer for 99% of companies. Yes, everyone wants zero downtime, but I see incredibly few cases where it is actually worth the cost and effort.
But obviously this is not suitable for all use cases. I don't know why pg doesn't invest in this sector.
If you don't want to use pg_upgrade, you can dump from one cluster and pipe directly into another, with no need for a temporary file. It couldn't be easier.
Can't say the same for Oracle. Even applying a patch can ruin several weekends.
If you have a trivial database, yes.
If you have a large, complicated database that takes hours/days to restore and has an HA SLA, very hell no.
For more complex deployments, cases of backwards-incompatibilities in the data structure, or where I had reason to rebuild many large tables, you set up a replication secondary, do pre-upgrade migrations (e.g. in the case of deprecated functionality), upgrade the secondary, do post-upgrade migrations (e.g. in the case of new functionality), and wait for replication to catch up 100%. Then, after sending traffic to it for a bit to ensure it works correctly, you swap the primary and secondary and do it again.
Upgrading the database server is as simple as shutting down the service, installing new executables and restarting the service. Downtime is measured in minutes.
When upgrading major versions, one might not get full functionality of the new features unless one upgrades the on-disk format (unload-reload), but you can run the new server version with older on-disk format just fine.
Because now it was easy to use hah.
We ended up rolling it back because the hate was so consistent.
(And no, this wasn’t us guessing or making this up - a number of them were quite explicit that we were threatening their roles, and to stop it.)
Every bit of the experience before getting it working, feels like it was designed by an MBA who owns a Postgres hosting service.
Other than that, it's amazing.
https://devcenter.heroku.com/articles/heroku-postgres-mainte...
Why?
For production systems I generally try to avoid using anything not built internally. When it comes to PostgreSQL, for example, if you want to submit benchmarks, or you want to report bugs, an important or sometimes a mandatory component is to provide compilation flags. Also, seeing how every kind of storage can act in a different way, tuning of higher-level storage programs seems inevitable. Even though PostgreSQL has a lot of configurations that can be done to the compiled program, some (eg. block size) are compile-time that can only be changed by compiling the program.
So does using the vendor repos:
* https://apt.postgresql.org/ / https://yum.postgresql.org
* https://dev.mysql.com/downloads/repo/
This way when you upgrade your OS you don't have to worry about suddenly getting a new version (which, in the case of MySQL, may take a long time to convert its database files to the new version format).
At least on Ubuntu (and likely Debian), your existing Postgres version is never dropped until you are ready to manually upgrade yourself.
Don't forget to do this on Friday afternoon! :D
Upgrading OS in production environment... this reminds me of a joke about police academy intake test:
The participants were offered a board with a round and a square hole and two pegs of similar shape. After the test the new recruits were sorted into two groups: very smart and very strong
So, yeah, you ought to be either very smart or very strong to upgrade OS in production environment, and then also discover changes to your database as you go along.
That's not to say that upgrades don't happen at all... but you'd be upgrading in a testing environment ten times before you try that "for real", and at that point you would have probably solved the question of whether you need to (re)install the database and how :)
On all open-source distros, you can look into your distro's source and check their compile flags.
Take fore example the aforementioned block size. Say, you have a storage with 4x block size of the one used by PostgreSQL. If you run a benchmark over such a storage, you'll have insane write amplification. Nobody will consider results of such a "benchmark" because that's simply a misconfiguration of the program you are trying to measure.
More generally, Linux distros will tend to compile distributed binaries with "safe" defaults s.t. run on most h/w users can have, and this means optimizing for the lowest common denominator. Looping back to PostgreSQL, the default for block size was for a long time 4k, and iirc today it's 8k. This is fine, if we are talking about plain SSD / HDD, but with enterprise SDS, these are "rookie numbers", even the maximum supported by PosgreSQL (32k) is still a "rookie number", but it's still four times better than the default!
I keep an eye out for vulnerabilities, and that's about it.
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.
PostgreSQL has prioritized correctness and stability which while it has made certain features more clunky to use or taken longer time for them to be implemented, when you get paged n the middle of the night you get the time you spent back. PostgreSQL also has spent a lot of time on improving DBA experience so mixed DBA/sysadmins also usually prefer PG.
I’ve seen this as well, but when pressed, none of them could articulate what part of its feature set they actually needed to use.
> 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.
Are you (or your devs, rather) actually running your own DBs? If so, respect. My experience has been that they spin up either a hideously under or over-provisioned RDS or Aurora instance, and then never touch it until it breaks, at which point they might ask for help, or they might just make it bigger.
Compare that with Postgres, which seemed very "old school", going so far as to assume it was the only thing a given server was doing. Connecting to postgres authenticated as your own user; creating a user was done with the `createuser` command (or similar, I don't remember what it was actually called), and not some namespaced `pg_createuser` command that would make it clear what it did.
I also remember setting up MySQL replication with almost no effort whatsoever, and then in the same year trying to set up Postgres replication - which it didn't have. I was told by other postgres admins to "just set up a script to rsync the database over and over to the other server; then if your first server dies just start the second server up and it'll recover". This seemed like a wildly cavalier attitude towards uptime and reliability, not to mention generating a ridiculous amount of I/O and network traffic for minimal benefit.
The PostgreSQL team simply does not have the resources to do this. At least not without significantly slowing down development of everything else which there is no political will for. Maybe someone will come up with a genius idea which solves this but I am doubtful. Usually there is no free lunch.
Maybe some core dev will correct me, I am quite familiar with the PostgreSQL project but not one of the core devs. :)
1. The new version of PostgreSQL would need to able to read all old catalog table formats and migrate them.
2. The new version of PostgreSQL would need to support all old versions of the parse tree to migrate views.
3. Likely a bunch of more things that I do not know of. I for example doubt it is trivial to just read an old catalog without having a fully up and running cluster which supports almost everything in that old cluster. The catalog has TOAST tables and indexes for example.
Right now 1 and 2 are implemented in pg_dump plus by having pg_dump call functions in a running old version of PostgreSQL.
So even if it's available these days, the amount of people still subjected to upgrades from older version still leaves an impression that it's really bad.
- whatever made it past the postgres 10 hump is on the most recent version and keeps getting migrated
- whatever is still on 9.x is scheduled to remain that way until the product using it will be EOL'd
1. Most psql deployments are not exposed to the interwebz, they are typically only accessible to the applications that need them by virtue of network setup (firewalls etc). This limits the attack vector to whatever the application does. Good.
2. Distro vendors (RHEL et al) often stick to major psql release for the lifecycle of the OS version. If the OS lives longer than the psql major version, they take on the responsability of backporting critical security issues.
3. While upgrades aren't hard, they're not easy either.
4. Psql is pretty much feature complete for many workloads, and pretty stable in general. For many people, there is little need to chase the latest major version.
To drive this a little further, "latest and greatest" doesn't always apply. I've chosen software - even databases - for greenfield deployments one or two releases behind for their known characteristics.
Stability doesn't imply perfection, but rather, predictability.
And in my experience, apart from ease of use there's also a major trust issue here. If you're upgrading your app server framework/language, it's easy enough to do a rollback. With databases, people are worried that they might not notice errors right away and then you have to merge the data accumulated since the upgrade with the last backup in case of a rollback.
Not saying that this is entirely rational...
Also, new features on the SQL level are hard to sell if all you're doing is lowest common denominator ORM ("New window functions and faster lateral joins? But we're doing all that in our code!").
It requires a lot of work, planned downtime, or some way to smear updates across the estate.
The cost of any failure is very high. The benefit of any major upgrade is also vanishingly small. Unless you need a specific feature, its just not worth it.
…or you're on AWS RDS, which will automatically bump your db cluster if it goes EOL and you ignore the notices for more than a year.
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.
And no, being old is not broken.
Amongst other reasons:
- Performing regular upgrades is an easy and cheap way to maintain a healthy knowledge base about the system. It's always easier to fix a system that is well understood than a black box which nobody has touched in 20 years. Upgrading regularly also leads to people being more comfortable with the upgrade process and it is likely to become a "regular thing" with checklists and other process improvements that make maintenance safer and more reliable.
- Doing preemptive maintenance means YOU get to choose when the system is unavailable and then you can plan accordingly, perhaps by planning it during a period with low usage or even arranging for a redundant system to take up the load. The alternative is leaving it up to chance when a system will fail, and that's frequently at high-load moments when you can least afford it.
- As a corollary to the previous point: a team which is in control of its systems can have more predictable output of feature work, since there will be fewer "unexpected" events.
Not maintaining your systems is only cheaper in the long run if you don't count the engineering time required to fix things when they break or need to be replaced. Ounce of prevention vs pound of cure and all that.
That doesn't mean "upgrade recklessly," but it does mean you should know _why_ you're either upgrading or _NOT_ upgrading. That's your job, much more than the act of upgrading itself.
Unpublished vulnerabilities in old software are not a hypothetical. And very old packages are usually broken, just coped with at the expense of significant lost opportunity cost - or because the failure is a combination of rare and impactful that means once it happens everyone is out of job anyway.
Seriously, I've yet have to encounter a sysadmin using that old, silly adage at me and not later have to admit I was right.
Edit: so no, you don't stay on an ancient version of the database because "it's not broken." You're staying on it because _the upgrade process itself_ is so broken you're terrified of it.
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.
https://en.wikipedia.org/wiki/G._K._Chesterton#Chesterton's_...
It's often lower risk to apply frequent smaller changes than wait years for one enormous "bundled" upgrade.
I guess it depends on scale? I was surprised how easy it was on Ubuntu. There was an automatic migration script, and it worked. Took less than 5 minutes to upgrade.
Sure, there was downtime, but I think most applications out there can live with scheduled downtime of a few minutes.
If you can't have downtime, then nothing is easy.
To others reading, be mindful: database upgrade times depend greatly on the data stored within them/where/who is using them. Your development environment doesn't represent production. If the distinction even exists!
A five minute upgrade can become indefinite with a single lock [row/table, depending on the storage engine/etc]
No matter how easy it is, it takes more effort and thought to do an upgrade than it does to not do an upgrade at all, and for most users the upside is basically invisible if it exists at all.
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 ecosystem, each with it's own quirks and drawbacks.
Also, upgrades are a dream. I did 4 major long term stable release upgrades so far and everything was fully automatic and smooth sailing all around.
All of that with about 10 seconds downtime only for each upgrade, despite being a chunky 6TB database, thanks to the user friendly replication process.
I respect postgresql a lot, but mariadb allows me to sleep much better at night thanks to replication, smooth upgrades and no VACUUM to worry about.
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.
I have suspected for a long time that most people who criticize MySQL have never actually worked with it, or have done so a couple of decades ago. It's often the default choice if you don't need some of the PostgreSQL features (like PostGIS) and can work with either one.
This is the one thing in My/MariaDB that I miss in Pg: Galera. So handy for in-house stuff that needs some kind of HA: either a three-server setup, or a two-server+arbitrator.
Looking at https://mariadb.com/kb/en/standard-replication/, is the one obvious way binlog replication or Galera?
Newer PostgreSQL versions are better. Yet still not quite as robust or easy as MySQL.
At a certain scale even MySQL upgrades can be painful. At least when you cannot spare more than a few minutes of downtime.
I think it boils down to this. We used to constantly be running the obsolete pg version until it became an emergency mostly because upgrading with the tooling available at the time was very painful. Today however, we stay relatively up to date. Once you figure out the data replication, you can almost do blue green deployments on databases with almost no down time.
https://jepsen.io/analyses/mysql-8.0.34 ( MySQL 8.0.34 2023-12-19 )
HN: https://news.ycombinator.com/item?id=38695750
"MySQL is a popular relational database. We revisit Kleppmann’s 2014 Hermitage and confirm that MySQL’s Repeatable Read still allows G2-item, G-single, and lost update. Using our transaction consistency checker Elle, we show that MySQL Repeatable Read also violates internal consistency. Furthermore, it violates Monotonic Atomic View: transactions can observe some of another transaction’s effects, then later fail to observe other effects of that same transaction. We demonstrate violations of ANSI SQL’s requirements for Repeatable Read. We believe MySQL Repeatable Read is somewhat stronger than Read Committed. As a lagniappe, we show that AWS RDS MySQL clusters routinely violate Serializability."
---------------
https://jepsen.io/analyses/postgresql-12.3 ( PostgreSQL 12.3 2020-06-12 )
HN: https://news.ycombinator.com/item?id=23498781
"PostgreSQL is a widely-known relational database system. We evaluated PostgreSQL using Jepsen’s new transactional isolation checker Elle, and found that transactions executed with serializable isolation on a single PostgreSQL instance were not, in fact, serializable. Under normal operation, transactions could occasionally exhibit G2-item: an anomaly involving a set of transactions which (roughly speaking) mutually fail to observe each other’s writes. In addition, we found frequent instances of G2-item under PostgreSQL “repeatable read”, which is explicitly proscribed by commonly-cited formalizations of repeatable read. As previously reported by Martin Kleppmann, this is due to the fact that PostgreSQL “repeatable read” is actually snapshot isolation. This behavior is allowable due to long-discussed ambiguities in the ANSI SQL standard, but could be surprising for users familiar with the literature. A patch for the bug we found in serializability is scheduled for the next minor release, on August 13th, and the presence of G2-item under repeatable read could be readily addressed through documentation."
To be clear, I like both. Postgres has a lot more features, and is far more extensible. But there’s no getting around the fact that its MVCC implementation means that at scale, you have to worry about things that simply do not exist for MySQL: vacuuming, txid wraparound, etc.
That was true in 2012; dunno if it still applies though.
Pretty sure that even in 2012 MySQL had very easy to use replication, which Postgres didn't have well into the late 2010s (does it today? It's been a while since I've ran any databases).
Except that search doesn't work, because all text is in latin1 encoding.
EDIT: my mistake, OPTIMIZE TABLE is an online DDL. I’ve been burned in the past from foreign key constraint metadata locks essentially turning it into a blocking operation.
Postgres is definitely more difficult to administer.
* I'm running a lot of MySQL stuff and such a topic might be of interest to me
https://web.archive.org/web/20230922210124/https://grimoire....
My only conclusion is that the majority of devs don’t actually read documentation, and rely purely on the last blog post they read to influence their infrastructure decisions.
Most devs just go with whatever the influencer du jour says is good.
"Nobody ever got fired for choosing insert_currently_hyped_tech_here"
MySQL was immortalized as the database in every LAMP stack. And just like PHP it improved considerably since then.
(I joined MySQL shortly after that mess, before the Sun acquisition)
I am a database specialist and have worn the DBA had for many years. I have run MySQL and Postgres in production, both self-hosted and using managed services. Postgres wins on every single dimension that matters, every time. Yes MySQL is easier to setup for non-experts. That counts for nothing.
If you are sticking up for MySQL in this thread... I just don't even know, man.
(which may be actually derived from the turkish aslan, for lion, but somehow the animal got mixed up)
https://en.wiktionary.org/wiki/Reconstruction:Proto-Slavic/s...
It really feels like early 1990s vintage Unix software. It's clunky and arcane and it's hard to feel confident doing anything complex with it.
In no particular order, my preference for postgres is driven by:
* Date / time functions that don't suck
* UTF-8 is really UTF-8
* 99% of a backup can be done live with nothing more than rsyncing the data directory and the WAL files
* Really comprehensive documentation
* LTREE and fuzzy string match extensions
* Familiarity from using it for years
I for one have always viewed this as a perk.
* comprehensive transaction model using different modes
* PostGIS and lots of other great extensions
* supports most of the current SQL standard and is clear on interpretation of edge-cases in the documentation
* support for writing stored procedures in any major programming language
* many useful functions regarding dates, sets, ranges, json, xml, ...
* custom datatypes
* extremely thought-out and standardized approach to development: if a feature is included it generally works well in interaction with everything else
* syntax, semantics and performance are all very predictable
* great documentation
Regarding MySQL / MariaDB: MySQL optimized for performance first. Until 2010 the standard-storage-engine MyISAM didn't even support transactions.
PostgreSQL always focused on correctness and stability and then made sure everything performed.
Good in theory. But last time I checked the main libs to connect to pgsql, everything you get back from the database are strings. So you need something in your app to convert those strings to the equivalent data structures.
Yes, more drivers and libraries should support the more expansive data type list, but even just within the database itself there are multiple advantages.
Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES.
This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html. Hardly an "obstical course".
How software "feels" is subjective. Can you be more specific?
\c database
\dt
use database
show tablesJust another anecdote: MySQL lost data for me (2004). I spent some time evaluating the projects and Postgres’ development process seemed much more mature — methodical, careful, and focused on correctness. Boring, which I loved.
I didn’t need whatever perf advantage MySQL had so I switched to Postgres and never looked back. And then the Oracle drama and Monty’s behavior around it — not saying he was wrong or right, but it was the opposite of boring — just reinforced my decision.
I like to play with new tech in various spots of the stack, but for filesystems and databases I go boring all the way.
Real answer: no licensing cost
Most of the software on my machines are "old" because they are part of a Linux distribution that (aside from security issues) was frozen in time a year or two ago so that it could be tested, released, and maintained. I am quite happy to have a system that I know is not going to break (either itself, or my workflow) when I apply security updates.
People who MUST HAVE the latest version of everything I feel either have some deeper FOMO issues to work out, suffer from boredom, or look at their computers as hobbies themselves rather than tools. (Which is fine, just be honest about what it is.)
That said, much of my career has been spent working at companies who got so busy shipping features that upgrading infrastructure never makes it above the fold. You can tell the managers that working around old software adds costs that scale with the age of the infrastructure, but they don't always listen. I currently work at a company that still has loads of CentOS 7 hosts still in production, and only fairly recently began upgrading them to RHEL 8. (Not 9!)
Absolutely...not.
Slow does not mean stable. Slow means the floor is rotting out from under you constantly.
Being prudent about when and where to upgrade is a very active, intentional process that the typical company simply don't have the stomach or skill for.
That platform got SMP in 1964.
Not chasing shiny is important but generally when tech debt builds up this high, life is generally hell in terms of outages, unable to accomplish basic tasks and dealing with a bunch of people who have NIH syndrome.
So for fresh installs yes but existing ones not so much
“Show me the incentive, I’ll show you the outcome”
Significant security vulnerability? Upgrade
Feature you need? Upgrade
All other reasons: Don't upgrade.
Upgrading takes effort and it is risky. The benefits must be worth the risks.
I’ve been there with products that were still internal at the time. I can only imagine how much fun that is with a public product. But then I do have a pretty vivid imagination. We changed to periodic upgrades after that to avoid the obvious problem staring us in the face.
Additionally, actions done frequently are less risky than actions done rarely, since you develop skills in performing that action as an organization - see high deployment frequency as a strategy of managing deployment risk.
This adds up to continuous upgrading being the least risky option in aggregate.
[0] Probably including those passwords you didn't hash, and those credit card numbers you shouldn't be storing in the first place because, what the heck, it meets your needs.
Thank you Amazon!
My timid management forbade an upgrade from Oracle 7.3.4 until 2013. It was agony to remain on that museum piece for as long as we did.
I am upgrade-minded, but my management is not. I always lose.
I am retiring in two years. I will not miss their problems, not at all.
Edit: Oracle 10g was the last release that (for us) brought must-have features. Sure, upgrading to 19 or 23 would be great, but it doesn't bring anything that I really want.
Let me check what I'm on... brb... Postgres 14.
Because it's not automatic I leave it, I leave it until it's so unsupported that I must upgrade the whole system, then I build a new system with a new Postgres and I migrate the old to the new.
I want, so badly, for Postgres to just automatically update itself, that a new binary just works with the data directory of an old version, and that if required it does an in-place upgrade to those data files when it can (i.e. if it can detect the last version was the same major as the current version, upgrade the files transparently to the admin).
My databases are all backed up each night, and these are single server Postgres with no replication or other trickery, an automatic upgrade for a single-server Postgres should be possible.
As it's not done... I assume (incorrectly?) that there be dragons and risks, and I mitigate that by never upgrading, just waiting and migrating. Migrating puts all of the risk on me, human error, and I am definitely fallible, so I can best handle this risk by just not doing it until I must.
Last migration I performed was from Postgres 7 > Postgres 14 in October 2021... I guess I have quite a few years of Postgres 14 ahead of me still. I would take downtime, my systems aren't zero downtime critical, just give me a super simple one-command no-questions upgrade as I really dislike migrations.
Whether you go from v14 to v17 or v14 to v20, I doubt it'll make a difference in migration strategy. You've still got a fair amount of time before EOL. No need to stress or FOMO.
It doesn't yet automatically upgrade people's PG extensions, but that's on the ToDo list and has initial code in a PR. So that'll likely start happening in a few weeks too. :)
I like pointing it at an S3 bucket so I have another full backup repo (with its own retention strategy) away from my homelab, as well as to my local NAS.
The consequence is that things in database-land tends to move slower than other types of software. This I think is the major reason why we still use SQL.
A dump/reload of the database or use of the pg_upgrade application is required for major upgrades.
Now, seems one can mostly use pg_upgrade, which only rewrites the system tables[2] so is fairly quick. But if on-disk format has changed it seems you're forced to dump and reload.
At work we've mainly been using SQLAnywhere, which would just disable new functionality for databases using old on-disk format. So upgrading major versions has usually been fairly painless and quick.
I don't think this is done automatically when you simply install a new postgres version, but I'm not certain of that.
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 upgrade and it's mission critical..
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.
You can hardly blame PostgreSQL for that.
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.
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.
And since I have backups, I might not even need the testing step, considering the low risk. Might do it anyway just out of curiosity at how long it would take to duplicate.
So, by "ancient", this author means possibly not even two years old, as Postgres 15 is barely that old (https://www.postgresql.org/about/news/postgresql-15-released...).
Meanwhile, I've had to make posts this year to tell people no, seriously, stop using Python 2.7.
And meanwhile in my package repo:
$ apt show postgresql
Package: postgresql
Version: 14+238
Priority: optional
Section: database
Source: postgresql-common (238)
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
The author's expectations are incredibly unrealistic.
1. Set up logical replication to a new database server. We used https://github.com/2ndQuadrant/pglogical, but maybe you don't need that any more with newer versions of postgres?
2. Flip a feature flag that pauses all database queries and wait for the queue of queries to complete.
3. Wait for the query queue to drain and for replication to catch up.
4. Flip a feature flag that switches the connection from the old db to the new db.
5. Flip the flag to resume queries.
It helped that we were written in OCaml. We had to write our own connection pooling, which meant that we had full control over the query queue. Not sure how you would do it with e.g. Java's Hikari, where the query queue and the connection settings are complected.
We also had no long-running queries, with a default timeout of 30 seconds.
It helped to over-provision servers during the migration, because any requests that came in while the migration was ongoing would have to wait for the migration to complete.
In that post we walk through all the steps we took to go from Postgres 11.9 to 15.3.
Suppose you join a project and see that the PostgreSQL version used is pretty old.
Do you:
A) convince people to migrate it for some nebulous benefits (features that aren't currently used, performance improvements that will be difficult to measure in lieu of tooling and aggregated statistics) while also taking on the risks of breaking everything or even worse, data loss (since you won't always be able to provision multiple instances and backup restore might take a while and you could still mess that up)
B) or just leave it as it is and focus on anything else
Containers and bind mounts make all of this way easier, but then again, many places don't use containers.
That way you can update the database version when you want, while still installing the base system updates quite frequently. Plus, I think it’s really nice to separate the runtime from the persistent data, which such setups make obvious.
From the same blog:
> Sep 26, 2024 - Postgres 17 is Now Available
3 weeks....for a new major release...and we are asking ourselves why people haven't updated?
(Granted, we have rather small DBs and simple applications where ON UPDATE SKIP LOCKED is about the most fancy feature we use.)
postgres 1958 0.0 0.0 247616 26040 ? S Jul21 3:03 /usr/lib/postgresql/11/bin/postgres
postgres 1085195 0.0 0.0 249804 24740 ? Ss Aug19 2:01 /usr/lib/postgresql/13/bin/postgres
postgres 1085196 0.0 0.0 223240 27900 ? Ss Aug19 1:59 /usr/lib/postgresql/15/bin/postgres
That way you can have multiple versions of the same package.
1. Sandstorm apps need to always come back cleanly from a raw SIGKILL at any moment.
2. Sandstorm apps must be able to always upgrade ANY previous version of stored data, with no intervention, automatically, when newer versions are deployed.
These are tough technical constraints and have huge implications on the whole design. For example, the client/protocol layer needs to possibly be multi-version aware for clean rollouts, client connection failover needs to be built in, etc. But the reality is if you do not have these two constraints, your software will never really work in a turnkey forget-about-it-way way where someone just runs an instance and then actually forgets about it, and it can be safe and secure. This kind of stuff is imperative to every actual computer user who isn't a programming nerd.
This is why slapping Docker on a lot of existing pieces of software like databases doesn't really work in the grand scheme. Yes, it gets you started faster, which people value to a high degree. But it often lacks or has the same problems as other solutions on day 2, day 3, ... etc. So, you need to solve all the same problems anyway just in a different form (or externalize them onto some other solution.)
Realistically, the solutions have to be designed in from the ground up. That's very difficult to do but necessary.
Someone else mentioned in here that SQL Server always 100% works when upgraded in place from an old version. That's also my (limited, homelab-ish) experience and a good reason to like it.
Ironically, Sandstorm itself is still stuck running on Mongo version 2.6 (a decade old!) because Mongo can't necessarily be updated in an unattended way. Of course, we could never ask every Sandstorm user to do some manual process to upgrade it.
Meanwhile, newer versions of the Mongo Node.js client library don't support such old Mongo, which means Sandstorm cannot actually update that package nor any of the packages that depend on it anymore. And this is why Sandstorm is now stuck in time and no longer receiving updates. :(
It was clearly a big mistake for Sandstorm to use Mongo. We chose it because we built the UI on Meteor, which was really only designed to work wing Mongo. In retrospect I wish we'd used SQLite, whose backwards-compatibility guarantee has now reached twenty years.
Testing all the apps that use it, not so much.
Trying to setup a logical replica of a much larger, high write volume database is an entirely different story with its own set of challenges. In some cases it’s not even possible to do even with tricks like dropping and restoring indexes.
Logical still struggles to keep up with high write loads. When something like vacuum freezes kicks off it’s not uncommon to see logical replication lag for significant periods.
Then there are things like lack of DDL replication. While this can be mostly worked around - it adds complexity. And remember DB user management is DDL - so if you change a DB password on the primary it won’t replicate to the logical replica.
When you have CDC systems using logical decoding from the systems you are upgrading you have to deal with resync conditions related to the fact that the logical replication slot will not be replicated and the new replica will lose its place in the replication stream.
Most non-trivial instances have multiple streaming replicas for read offloads which need to be coordinated at cutover. While not difficult it increases the complexity.
In addition - there are no guarantees of application compatibility. While this is rarely an issue in my experience- PG 14 (IIRC) changed the function signatures of a bunch of array functions which was particularly painful.
That said - Postgres is improving the story here. PG 17’s ability to convert a streaming replica to a logical one and be able to upgrade it will be a game changer for standing up the logical replica. If they can get DDL replication into logical it will improve things even more.
That said - it’s still way behind how modern databases like CockroachDB and Yugabyte handle things here.
We are constantly making changes to our schemas (adding tables, columns, etc). Its never an issue on physical standby's, it just gets created, but logical replication, we have to manually run the changes on the subscriber.
We have lots of instances where we create a new table for a feature coming, and alter another to add a column.
If those get missed on the logical subscriber, you don't even know until someone tries to write data to that new table or new column.
I know logical is supposed to be flexible, but I wish there was a setting to have a replica via logical, so I can handle upgrades easier.
If I run my application/code v1 right now, I generate data. I expect that if I move to application/code v2, I can leave my data in place and it will automatically apply changes to my data.
I do not get that with postgres. If I am on postgres 16, and I want to upgrade to postgres 17, I want to leave my data folder untouched. When I then start postgres 17, it should just work (tm).
It should also work with code that assumes postgres 16, so I can upgrade my database separate from my application. I can not wait 10 days for a large database to be migrated from 16 to 17 without being able to run it. However, I can wait 10 days before updating my code to support features in 17.
The current upgrade process does not give me such confidence in restoring data and uptime. So I don't upgrade until I really have to.
I hope you have a backup somewhere else, not on the Mac Mini. One option would be to restore it on another machine on Postgres 14, follow the upgrade path to 17 (idk if you can jump directly but a few Google searches will cover this), verify the data, back up the new db, somehow upgrade Posgtres on your Mac, and restore the new back up. Done.
brew install postgresql@17
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 `unzip` to newer version but I can't find any updates for my distro, and I have no idea (still no, so feel free to teach me) to update it manually to make my `unzip` supporting AES. And all the versions, the good and the bad, all say they're "version 6.0.0" despite they behavior obviously differently.
[1] https://stackoverflow.com/questions/60674080/how-to-open-win...
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 is available to upgrade to (for compatibility reasons it might not be the latest, or latest major, unless you use the software vendor's own package manager repositories).
It's actually much easier than in Windows, because you have a piece of software (package manager) that is your one stop shop to knowing what is installed, update it, check versions available, etc. unless you've manually installed stuff.
In Windows you... google and download random .exes? Cool. As good as the worst possible option on Linux.
Windows has an official package manager now too.
> 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 would take me less than 1 min.
Doing similar for my Linux VPS with only a terminal is much more complicated.
I’ve run multiple variants of PostgreSQL on my MacBook under MacPorts. Can’t even remember the last time I thought about it, to be honest.
% brew search postgres
==> Formulae
check_postgres postgresql@12 postgresql@15 postgrest
postgresql@10 postgresql@13 postgresql@16 qt-postgresql
postgresql@11 postgresql@14 postgresql@17 postgis
My experience has been that most people (including devs) hardly think about their database at all. Everyone just takes the database for granted until a dev runs a bad query that locks a table. Devs let their ORM manage everything for them and don't take the time to think for themselves.
Also I rarely see teams with a culture that prioritizes maintenance, even for their own software. It's common for teams to become wary of upgrading because they do it infrequently. Like most things, the more often you perform an upgrade the easier it is. Smaller changes are easier to revert. The more often you do something, the better at it you get. Delaying maintenance often just increases the work later.
Once the database runs you are inclined to let it keep on running until you must do an upgrade (Bugfixes, EOS) . Upgrading for new features/ improved performance is nice but can be a pain and is prone to cause downtime. PostgreSQLs upgrade tolling story is also a bit painful but it has proven to be an awesome way to semi-regularly test my backup infrastructure.
It's been a long time since I worked with v9.x in anger, so I could well be forgetting things though
https://www.postgresql.org/docs/current/ddl-partitioning.htm...
But it wasn't really a nuisance. Postgresql doesn't give a DBA much to worry about.
Any needed updates to databases are done by the new exe, automatically. Obviously backup everything before you do the upgrade. I've never had one fail.
You can check this article about Uber migrating its Mysql from v5 to v8 posted here 3 days ago [1]. Among other things, they observed a "~94% reduction in overall database lock time." The before/after graph is pretty impressive. It also gave them window functions and better JSON support, which are two very big features.
[1] https://www.uber.com/en-JO/blog/upgrading-ubers-mysql-fleet
There's no option where the nodes all accept writes.
The classic example is advertising a new improved fishing reel in a fishing magazine. People buy the magazine (well, 20 years ago they did) because they want to know about things like new improved fishing reels.
It's a world away from the overwhelming avalanche of bullshit that is modern advertising/spam. There's nothing at all weird about hating advertising in general but being ok with permission marketing.
If you follow this idea further you'll find that very few people, even the most vocal, genuinely hate advertising. We all want to know about useful products and services. We just don't want to see a million ads a day for Apple, Coke, Pepsi, Nike, erectile dysfunction, fake single women in your area, Nigerian princes...
Because when it reaches a certain scale, and when too many psychological tricks are being played, and everything is always, BRIGHT, BIG, hyper-sexualized, when you can't walk down any street, watch anything, read anything, without seeing people richer, smarter, younger, sexier, happier than you, it goes far beyond just advertising. It's brainwashing. It has to stop because it's extremely unhealthy for our societies, our mental health, our children.
But when I'm watching a YouTube video, having the video cut mid-sentence to some hyper-annoying and unrelated noisy ad simply angers me and makes me look for an ad-blocker.
The RDBMS has not fundamentally changed much since the 80's. It's the same basic design inside and out, with a shitload of tweaks and optimizations. Don't get me wrong - you can get very far with tweaks and optimizations. But the foundations root you to a particular model, and some things in that model will always be painful.
The important question to me isn't why don't people upgrade. It's why do people run Postgres?
Is there no other kind of database? No, there are hundreds of different databases. Is it because Postgres does things fundamentally different and better than anything else? No, lots of things work somewhat like Postgres, with pros and cons. Is it because you can't do things without Postgres? No, there are always alternatives that you can make work. Is it because it's impossible for it to keep working without upgrading? No, any database with long-term support could continue working without upgrading.
So why use Postgres at all? Answer: it's the incumbency, stupid.
When a big fucking honking giant "thing" sucks up all the air in the room, provides for most of the use cases, and is accepted by all the users, then it's extremely hard to justify not using it. Incumbents usually win, even if they're old, disreputable, buggy, and annoying. Even if they're missing some obvious features other things have. Even if people loudly complain that they want change. It's just more annoying not to use them.
We're used to them. We've adapted. Trying to do something else is going to be annoying and hard. So we stick to what we know. But that doesn't mean we have to stroke the balls while working the shaft. Upgrading isn't exactly fun. It doesn't even buy us anything, other than the promise of "support", or "compatibility" with whatever else will eventually require it. So we upgrade, eventually, when we must.
But the constant mind-numbing march away from entropy isn't a fantastic reason to keep using the thing. When you have to convince yourself to stick with it, it's likely you're in an toxic relationship. If you're honest with yourself, you'll agree that it's time for a change. You deserve better.
But switching is full of unknowns. Ominous. Time-consuming. Hard. You know you want to, maybe even need to. But there's too much to lose. So you stick to the familiar, to what you can handle. Maybe something better will come down the pike soon. You even tell yourself you're lucky to be here. But deep down you know you're just comforting yourself. You wish you had something else. Something that brings you joy. Something better. Something... right. But that something isn't here right now. So until Mr. Right gets here, you'll stick with Mr. Right Now.
Time to get ready for the next upgrade..... sigh...
No. It's been released in September 2024. That's not "quite a bit".
Now as to why people aren't all on 17 and not even on 16 yet, here's an acronym for you: LTS [1]
Debian 11 Bullseye is the current LTS. It came out in 2021.
Debian has Stable. That's it.