PostgreSQL upgrades are hard
andreas.scherbaum.la
andreas.scherbaum.la
I don't remember any version whose storage format is cannot be read by the next versions (none since 2010). Could you add some more info what issues you had?
OK upon further thought, what you're saying is to use the pg_dump tool that comes with the pg version you are upgrading to.
I read it as "use pg_dump instead of pg_restore"...
I was just trying to simplify the guidance here. :)
If you use -Fc, you can make an SQL file from the dump using pg_restore like this:
pg_restore -f db.sql db.dmp
pg_restore -f - db.dmp | grep "database dump complete"
It is really fast, but might not be practical if your database is too big.
nonetheless, interesting way to validate a backup, though it would probably take too long on most dumps
That would be enough to qualify as "super painful" for me
As far as RDS goes it amazes me that, even with replicas, there are NO options for doing a zero downtime upgrade which is a major gotcha.
By this point they should buffer the writes during the critical section when the switchover happens and make this seamless for users and operators.
Setting up the infrastructure to manually manage your own cluster failover would kinda go against the spirit of using RDS and letting AWS manage infrastructure for you, though.
That has nothing to do with this. All databases are fundamentally the same regardless of data model. The standard process is to start a new version, replicate the existing database, then cutover once the replica is caught up. Advanced systems can seamlessly switch the primary so it'll redirect new queries to the new primary upon that cutoff.
This has been done for decades through external tooling across many systems, including Postgres. However it would be easier if the database included this functionality internally.
[0] https://github.com/alexandrospanagiotidis/postgres-upgrader
There are well tested distro packages for databases which get security updates automatically and none of the weirdness of container environments.
Some people need to realize using the buzzword of the moment isn't necessary where it isn't.
Some people need to realize that distro packages aren't good in all use-cases.
When I finally went to upgrade Ubuntu, do-release-upgrade literally bailed out because Postgres was installed[1]. So my choices were: uninstall Postgres for the upgrade, or do something like containerize it. I decided I had had enough problems with non-standard repos and Ubuntu upgrades (e.g. a ton of pain with upgrades of Unifi Contoller + MongoDB + Ubuntu), and containerized it. In comparison to my upgrade to 20.04, my upgrade to 22.04 was much cleaner.
If you're going to tell me I should have used VMs or some hosted services, yes, thank you, I don't have the hardware to do so at home and I didn't want to spend more money on the cloud.
There are use cases for containers that have nothing to do with chasing buzzwords.
[1] Not my mail, but representative: https://www.postgresql.org/message-id/28768e4f-f6f7-ad78-d0d...
Nowadays my setup is a very minimal server install, setup “raw” services like SSH, basic monitoring, mDNS. Then install Docker and “up” all my compose yamls, pointing to persistent directories on disk
It’s easy enough that migrating from Centos Stream to Ubuntu server took 24hrs for the meat of it
Please edit swipes out of your HN posts. Your comment would be fine without that last sentence.
Oracle upgrades are the real nightmare.
While that's true for a lot of database systems, some more recently designed are much better. [1]
[1]: https://www.cockroachlabs.com/docs/stable/upgrade-cockroach-...
https://engineering.theblueground.com/blog/zero-downtime-pos...
GP claimed upgrading was a piece of cake, not that zero downtime upgrades are a piece of cake. The two claims aren’t interchangeable. The simple upgrade path is always available, though it may have downtime consequences you personally are unwilling to accept. And the complex upgrade path is complex for reasons that have nothing to do with PostgreSQL - it’s just as complex to do a zero downtime upgrade in any data store, because in all cases it requires logical replication.
So if anything it feels like you’re the one being misleading by acting as though GP made a more specific claim than they actually did, and insisting that the hard case is hard because of PG instead of difficulty that’s inherent to the zero downtime requirement.
If you claim that process X is trivial one has to make some assumptions, right? Otherwise I could claim that going to the moon is trivial but leave out "assuming you have a rocket, resources, people and anything else you may require".
Claiming that something is a piece of cake as a broad statement without any details is meaningless at best.
Incredibly bad-faith comparison, this.
Many, many datastore deployments can tolerate 10 minutes of downtime every 4 or 5 years when their PG install finally transitions out of support. Data loss isn’t even in the same universe of problem. It’s reasonable to talk about how easy it is to upgrade if you can tolerate a tiny bit of downtime every few years, since most people can. It’s utterly asinine to compare that to data deletion.
Postgres is a tool. It can do many things, but the tool cannot run your entire business.
this is something majority of the decision makers pretend to don't understand (and sometimes really don't).
It's usually the company leadership that can't tolerate it to have downtime.
And it's fact, they are hit an unplanned with downtime of one service or the other every month or so because of an outage. They are used to it.
So if you plan for it, explain it, and limit the scope and time of it, it usually goes very well unless you are a fortune 500, a hospital or something alike.
I once caused a production outage in a retail company that caused all cash registers to stop working. The team that worked on that had pushed in a last minute change and didn't test if it handed going offline gracefully.
Right now I'm on call for an identity provider used by people in various timezones, including logistics workers in places that operate 24/7. Even when we do weekend upgrades, we still cause quite a bit of collateral damage. 10 minutes of time, multiply by the number of employees affected. It adds up fast.
Indeed, but who cares about the system design anymore? How many companies/ teams can claim honestly they even had a person with proper DBA competency, while features over features were added in sprints doing the minimum required to get the feature shipped out at the soonest possible (usually one DB schema change with feature and then one or more to add index due to performance regressions)? DBA competency is only sought when DB schema has fubar'd to an extent that frequent outages are norms or the version used is EOL'd by a few months at least. And by that time the people who "designed" the system are gone, not having documented ever why a given decision was made.
And that is how my friend...
DB upgrades are hard.
This is sometimes not so easy if the database is huge and you can't have a downtime. The article is also mostly focused on things related to running a replicated setup which makes things a lot harder than pg_upgrade that you "just" run on your production database.
PG upgrades work well and the pg_upgrade tool works well but it's not just something you run on a Friday evening if it's bigger than a side project with 2 users.
It's not very fair to say "upgrades on Postgres are hard" if that's practically always true in your use-case, that's all.
That is just nonsense. If it's hard then it's hard. It's not easier because it's practically always hard.
What's next, it's not fair to say that flying to the moon is hard because flying to LEO is tricky? Oh my.
It would be more fair to say "Zero-downtime database upgrade is hard"
What about replicated and zero-down time (the current thread context)? That's also hard with MySQL.
* efficient binary data clone is a single command
* starting replication with GTID positioning is a single command
* data dictionary / metadata upgrades happen automatically when you start a newer-version mysqld
The hard part of major-version upgrades in MySQL is testing your application and workload, to ensure no deprecations/removals affect you, and checking for queries with performance regressions. Tools like Percona's pt-upgrade and ProxySQL's mirroring feature help a lot with this.
They also introduced binary file format changes even with minor and patchlevel version number changes and downgrading stopped being supported. afaik in that case had to restore from backup.
it's just the exact opposite of postgres' upgrade guarantees.
Realistically, every relational database has corner cases in replication. There are a lot of implementation trade-offs in logical vs physical, async vs sync, single storage engine vs pluggable, etc. Replication is inherently complex. If the corner cases are well-documented, that's a good thing.
I do totally agree the lack of downgrade support in MySQL 8 is problematic.
Postgres is a really great database, don't get me wrong. But no software is perfect, ever. Consider the silent concurrent index corruption bug in pg 14.0-14.3 for example. If something like that ever happened in MySQL, I believe the comments about it here would be much more judgemental!
We pay Microsoft to perform upgrades transparently for us. They have multiple servers and they shuffle transaction logs and traffic between them to ensure availability during outages and upgrades. There are 6 copies of each database in different regions. Not sure how that is relevant, though?
But it is not. It's complex to do properly, just like with most if not all other databases. Claiming that it is easy as is just ignorant and spreading such misinformation is bad.
It's not a weakness in Postgres.
Managing upgrades in a highly available, close to 100% uptime database is hard.
If you want piece of cake, outsource this service and be happy enjoying the database features working as you please.
i am a fan of planning for maintenance. planned downtime is definitely accepted by most of the population. i mean what you gonna do?
much better a planned downtime than an outage. leaving a system to rot just because the architecture is "don't touch it while it's working" is a sure recipe for a disaster of some kind.
Just the security patching of the host OS will likely consume a bunch of those minutes.
Not sure what point you were trying to make apart from that. I am not advocating that people should leave system to rot.
Of course, you've still got to obtain a patch. I usually see the licensing in the name of some senior manager who doesn't know how to use a keyboard and who is also the only person allowed to download patches.
I’ve been burned by this before, and gone as far as to require a 3rd party audit for Oracle installations on a quarterly basis.
Anyway, my comment was simply responding to the parent's:
> Or you could just dump and load the database (maybe even pipe it from one to the next).
I'm looking to select a relational DB for a web application, most of my experience is MS-SQL but I'm looking to go open source/Linux this time.
I find it wrong to call out B-tree indexes -- any index that is (re)indexed concurrently in PG14 <14.4 might have had corruption. Just because PG14 only has a way to detect it for b-trees does not mean it doesn't exist in other index methods.
The author also seems to confuse upgrade complexity with the complexity of verification and decorruption tasks (that you should do if you have run concurrent (re)index in your cluster in PG 14.0-14.3). The thing is that you are not at all required to do that during the upgrade, because you might not run (re)index concurrently, or because fixing the corruption is also possible when still on <14.4, by using non-concurrent reindex, or by doing the manual snapshot control that I detailed in [1].
There's no known impact to brin or gin/gist indexes as these haven't seen major changes in recent versions.
btree is only the default index type, but otherwise unrelated, with the sole exception being that it is easy to verify whether it's corrupted - but that doesn't mean that other index methods are not affected.
Absence of evidence is not evidence of absence.
And then you still have to upgrade extensions and run analyze manually.
At a previous company I had to tend to a large here of MySQL clusters and we did all sorts of zero-downtime stuff there. I've found MySQL replication far easier to work with.
CockroachDB is the biggest one I've been watching but unfortunately it still seems to have some limitations around performance with complex queries that rule it out for a lot of things.
Initial release was in 1996.
And it probably traces actual code back to Ingres in 1982.
Some of the more advanced operators might better handle the upgrade problem? But in general, I've been happy with Kubegres for a ~10GB database with low traffic.
What has changed between now and then where putting postgres into k8s is an option?
Does the Postgres operator handle all the statefullness requirements and scheduling things that might be unique to pg?
At $BIG_CORP our executives became very excited about "The Cloud" and many teams were forced to migrate their applications over. Didn't matter if it was a proper 12 factor application or a monolithic vendor JAR that required 64GB of ram. You were punished for using VMs, so teams spent an inordinate amount of time shoving square pegs into round holes.
For smaller setups and testing no problem in K8s.