[HN Gopher] PostgreSQL upgrades are hard
___________________________________________________________________
PostgreSQL upgrades are hard
Author : omnibrain
Score : 210 points
Date : 2022-06-25 06:32 UTC (16 hours ago)
(HTM) web link (andreas.scherbaum.la)
(TXT) w3m dump (andreas.scherbaum.la)
| olavgg wrote:
| I upgraded my development database from 12 to 15-beta this week.
| It was super painful, even with pg_upgrade, I cant understand why
| newer Postgres release can include compatibility for older
| storage formats. This is the number 1 weakness in Postgresql. I
| prefer to not upgrade major version for years because its so
| painful.
| mattashii wrote:
| That seems strange, the only incompatibilities between major
| versions are generally only the catalogs.
|
| 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?
| 411111111111111 wrote:
| In not the person you've responded to but I've never been
| able to get `pg_dump -Fc` and restore to work across
| versions. It always errors out for me, saying it's an
| invalid/broken backup. Super annoying because now the
| workflow is 1. Start old version just to restore, remove old
| version and start new version for upgrade.
| forinti wrote:
| I've used dumps as backups and never experienced this
| issue, although they would stop prematurely from time to
| time. The solution was just to check the string at the end
| and repeat the dump if it wasn't there.
| 411111111111111 wrote:
| Considering that -Fc is a binary format, how would i
| check a string at the end of it?
| forinti wrote:
| If you do an SQL dump, it will end with "-- PostgreSQL
| database dump complete".
|
| 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
|
| Or straight to stdout like this:
| pg_restore -f - db.dmp | grep "database dump complete"
|
| Although I guess if it's corrupted pg_restore won't work.
|
| It is really fast, but might not be practical if your
| database is too big.
| 411111111111111 wrote:
| My issue was indeed the mishmash of versions, as the
| restore succeeded with the mentioned steps (installing
| old version, restoring, upgrading).
|
| nonetheless, interesting way to validate a backup, though
| it would probably take too long on most dumps
| castorp wrote:
| You need to use pg_dump from the new version, not from the
| old version.
| jasfi wrote:
| I think you found a common stumbling block. The tutorial
| people follow may say to run pg_dump and many people
| would assume you're running it from the old version.
| rapind wrote:
| Wait, what? Explain please.
|
| 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"...
| mh- wrote:
| Yes. Put concisely: Use the client tools from the newest
| version involved for _all_ steps.
| oblio wrote:
| Isn't that impractical for most Linux distros? You'd need
| to manually install the new client.
| mh- wrote:
| Probably? Certainly if you rely on distro packaging for
| it, yes.
|
| I was just trying to simplify the guidance here. :)
| BGZq7 wrote:
| On Debian (and probably most of its derivatives), the
| package manager will not remove any running PostgreSQL
| versions and will let you have multiple versions side by
| side. It also has its own tool pg_upgradecluster, which
| can perform the upgrade using pg_dump+pg_restore or
| pg_upgrade (optionally with link or clone options).
| forinti wrote:
| I'm curious as to what could have gone wrong. A few weeks ago I
| upgraded from 9 to 14 using pg_upgrade and had no issue
| whatsoever.
| Macha wrote:
| Doesn't this entail going 9.4-10, 10-11, 11-12, 12-13 and
| 13-14? (and possibly across various 9.x versions)
|
| That would be enough to qualify as "super painful" for me
| forinti wrote:
| No. To be precise, it was 9.6.6 or 9.5.3 (I don't recall
| which one exactly because both were installed) to 14.0. And
| then I upgraded to 14.4, but that only required a
| compilation.
| nicoburns wrote:
| I think the issue is if you want zero-downtime (or close to
| zero downtime). pg_upgrade can take upwards of 10 minutes to
| run even for relatively small databases. It can potentially
| be hours for large ones.
| anpanman wrote:
| Some time ago I wanted to update my self-hosted PostgreSQL
| containers (for stuff like Nextcloud, and Gitea) and did not find
| a good solution to running pg_upgrade in such an environment. To
| that end I created a little container [0] that tries to upgrade
| the database from one version to another. Maybe this is useful to
| some of you :)
|
| [0] https://github.com/alexandrospanagiotidis/postgres-upgrader
| mekster wrote:
| What's the point of running a database in a container?
|
| 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.
| forbiddenlake wrote:
| I wanted <feature> (now lost to the mists of time), but was
| on Ubuntu 18.04, which only has postgresql-10. So I installed
| Postgres (13 or 14, I believe) from a PPA and happily used
| it.
|
| 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...
| dang wrote:
| > Some people need to realize using the buzzword of the
| moment isn't necessary where it isn't.
|
| Please edit swipes out of your HN posts. Your comment would
| be fine without that last sentence.
|
| https://news.ycombinator.com/newsguidelines.html
| Gigachad wrote:
| Unbundling OS updates from service updates is a huge benefit.
| No one wants to have to juggle updating the OS and migrating
| databases at the same time.
| Axsuul wrote:
| Consistency with dev and prod and getting scheduling and
| management for free with Nomad. I've been running a 2 TB
| Postgres database that has an average of 2000 QPS+ in a
| container for many years now with no issues, what kind of
| weirdness are you referring to?
| itsthejb wrote:
| For one thing, the isolation. I also run a "home lab", and
| started off as a newbie running everything on bare metal.
| Quickly ran into dependency issues, and recreating the setup
| was painful, even if I had used something like a ansible
|
| 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
| Avamander wrote:
| It's easier to run multiple clusters, to do testing,
| development and more elaborate networking.
|
| Some people need to realize that distro packages aren't good
| in all use-cases.
| mattashii wrote:
| > A good example is the recent release of PostgreSQL version
| 14.4. It is a minor upgrade, so no new features. But it requires
| extensive work on B-tree indexes.
|
| 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].
|
| [1] https://news.ycombinator.com/item?id=31688332
| smileysteve wrote:
| The concurrent reindex operation only impacted breed indexes.
|
| There's no known impact to brin or gin/gist indexes as these
| haven't seen major changes in recent versions.
| mattashii wrote:
| The corruption issue is with concurrent (re)index operations,
| which can be done for all index types. That is, the
| corruption is best described as 'some table rows may skipped
| when scanning the table during reindex, so the index can be
| incomplete', and unrelated to index methods, as any index can
| be reindexed concurrently.
|
| 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.
| smileysteve wrote:
| Brin was implemented in a lazy fashion already. So
| concurrent reindexing isn't a thing
| mattashii wrote:
| BRIN lazily summarizes blocks _at the end of the table_.
| When you build the index (by either creating or
| reindexing, concurrently or not) it will generate
| summaries for all full block ranges of the table. As
| such, it is possible that even a BRIN index was corrupted
| by concurrent reindex in PG14 < 14.4 because it didn't
| receive all tuples that it should have summarized.
| tbrock wrote:
| The upgrade path using logical replication is critical for
| minimizing downtime. The others should not be considered for
| production workloads.
|
| 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.
| shadowgovt wrote:
| It's basically impossible to do a zero-downtime upgrade with an
| RDS. Systems that have zero-downtime upgrade "solve" this
| problem by not being relational (because it pushes the fault-
| tolerance to the query developer when they have to build their
| own relations across systems that are defined by API contract
| to not always be available).
| CWuestefeld wrote:
| Right now as we speak, our DBAs are applying patches to our
| SQL Servers. SQL Always On seems to solve the problem pretty
| well. Granted, this is a minor upgrade, but even so, we've
| not had any database downtime for any of the updates we've
| applied in the last year+ since we got this set up.
| manigandham wrote:
| > _" solve this problem by not being relational"_
|
| 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.
| dub wrote:
| Hypothetically it should be possible to make an entirely new
| RDS cluster as a replica at a new version and fail over to
| it, with a similar error rate to a normal replica failover.
|
| 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.
| rkeene2 wrote:
| As a counter example there's COMDB2 which has zero downtime
| upgrades, because the client (library) maintains the
| transaction state and can replay it when the server comes
| back online.
| jacooper wrote:
| I'm currently a casual user, I use Postgres for personal docker
| services, and I wish it would auto upgrade itself when there is a
| new release, like how MariaDB does it.
| jlkuester7 wrote:
| Same. Of all the containerized apps that I run, Postgres is
| probably the most painful to upgrade. Everyone else (eg
| Nextcloud) has invested the time to figure out how to auto-
| trigger an upgrade when starting a container with a new
| version. Why can't Postgres do the same???
| megous wrote:
| Ask the author of whatever container you're using?
| HeckFeck wrote:
| I see quite a few opinions here detailing the pain of PgSQL
| upgrades. From those in the know, if you've time to bestow: how
| does it compare with the main rival relational db engine, MySQL,
| or its offshoot MariaDB? (I know I _could_ ddg but a few
| anecdotes always sweeten the deal)
|
| 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.
| danielvaughn wrote:
| I don't know about how postgres compares to others, but if
| you're looking for an easy-to-manage relational DB, there are a
| handful of new cloud-native databases that have come out in the
| last few years. The ones I know of are Planetscale and
| Cockroach DB.
|
| https://planetscale.com/
|
| https://www.cockroachlabs.com/
| newaccount74 wrote:
| CockroachDB has so many changes in each version that updates
| are bound to break something. PostgreSQL is much more stable
| in that regard.
| davidgerard wrote:
| Postgres is great, but frankly this is where Amazon RDS shines
| for us. Outsourcing the pain of maintenance, upgrades, mirroring
| ...
| raffraffraff wrote:
| Aurora still can't do zero downtime upgrades though, which
| surprised me. You get closer with vanilla RDS PostgreSQL. And
| if you use RDS proxy or pgbouncer it's better.
|
| 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.
| nikita2206 wrote:
| It still doesn't do zero downtime upgrades though, does it?
| aeyes wrote:
| Major version upgrades on RDS are even more of a pain because
| you can't reliably plan the downtime you'll need for them to
| finish. You push a button and then you pray that it doesn't
| take much longer than your test runs with restored snapshots...
|
| And then you still have to upgrade extensions and run analyze
| manually.
| booi wrote:
| And realistically.. they always take longer because your
| production database storage is fragmented from use
| polote wrote:
| Upgrades are the biggest pain of Postgres in my opinion.
| Especially when you want to do zero-downtime upgrades.
| api wrote:
| I hate PostgreSQL. It's overly arcane and feels like something
| from the 1980s or early 1990s. Everything is clunky and manual
| and "mainframey." (Is that a word?) Unfortunately there's not a
| lot that's that much better. The commercial options are not even
| much better really. Maybe a little more polished.
|
| 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.
| vbezhenar wrote:
| I wonder what do people think of Kubernetes operators for
| Postgres? They promise painless 0-downtime upgrades.
| tempest_ wrote:
| I don't keep up to date on all the k8s changes but a couple
| years ago wasnt the recommendation not to have your databases
| in k8s at all?
|
| 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?
| richbell wrote:
| StatefulSets were added to handle things like this. It always
| seemed like an afterthought to me, appeasing people trying to
| run things that Kubernetes was never designed to handle.
|
| 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.
| brainzap wrote:
| Personally I tried, but decided to have the production Postgres
| in dedicated VM. This allows to give more attention, more
| resources and less risks.
|
| For smaller setups and testing no problem in K8s.
| yjftsjthsd-h wrote:
| Any chance you have enough experience to suggest a good option?
| This is something I guess I'm going to have to deal with soon
| perrygeo wrote:
| https://www.kubegres.io/ has worked well for me. You can use
| custom images, provided they follow the conventions of the
| official postgres image. I haven't tried a major-version
| upgrade yet - my impression is that it will probably be a bit
| painful and involve some downtime and manual ops.
|
| 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.
| CameronNemo wrote:
| I used this one recently and it worked pretty well:
| https://github.com/zalando/postgres-operator
| vbezhenar wrote:
| I did some research and most popular ones seem to be zolando
| and crunchy. I have zero experience, but going to deploy it
| for test environment to see how it goes.
| forinti wrote:
| Postgresql upgrades are a piece of cake. pg_upgrade just works.
| Or you could just dump and load the database (maybe even pipe it
| from one to the next). Minor upgrades are even simpler: forgot to
| compile a module? make and move the. so to lib/.
|
| Oracle upgrades are the real nightmare.
| dewey wrote:
| > Or you could just dump and load the database
|
| 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.
| Avamander wrote:
| If you "can't" have any downtime at all then no matter if
| it's Postgres of MariaDB, it's going to require a replicated
| setup. You can't ry on a single instance.
|
| 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.
| wutwutwutwut wrote:
| > 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.
| rmbyrro wrote:
| I guess the point they're trying to make is that phrasing
| as "Postgres upgrade is hard" is not fair because it
| gives the impression it's a weakness in Postgress, but
| the use case would be hard in any database.
|
| It would be more fair to say "Zero-downtime database
| upgrade is hard"
| wutwutwutwut wrote:
| Meh. That's an argumentation which is just a waste of
| time. This is a post about Postgres. Thinking about
| whether or not the title is "fair" towards the software
| itself is ridiculous and a waste of everyone's time. The
| title is correct and the software won't feel bad about
| it. Move on.
| tinus_hn wrote:
| I wouldn't say that MySQL is a better database but
| upgrades with it are very easy: you backup and then you
| upgrade the packages and the new packages run the upgrade
| command.
| djbusby wrote:
| Yea, exactly as on PG.
|
| What about replicated and zero-down time (the current
| thread context)? That's also hard with MySQL.
| evanelias wrote:
| It's actually not too difficult in MySQL, in terms of the
| mechanical steps required. MySQL's built-in replication
| has always been logical replication, and they've made
| major ease-of-use improvements in recent years, for
| example:
|
| * 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.
| minusf wrote:
| haven't used mysql in a couple of years but its
| replication (all methods) used to have a whole page of
| gotchas and idiosyncrasies with various corner cases.
|
| 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.
| evanelias wrote:
| It's hard to respond without any specifics, but in my
| personal view it's a pretty solid replication
| implementation in modern versions. Sure, there are some
| gotchas, but they're not common.
|
| 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!
| yjftsjthsd-h wrote:
| I thought mysql could do replication across a major
| version? If so, that's big differentiator
| mwarkentin wrote:
| As can Postgres: https://www.cybertec-
| postgresql.com/en/upgrading-postgres-ma...
| wutwutwutwut wrote:
| So postgresql upgrades are a piece of cake if your users
| doesn't care if your system is available to them. Is this some
| paradoy of the older claim that MongoDb is a great system if
| your user doesn't care if their data is lost?
| minusf wrote:
| even four 9's give 50 minutes a year to do maintenance work.
|
| 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.
| wutwutwutwut wrote:
| > even four 9's give 50 minutes a year to do maintenance
| work.
|
| 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.
| Avamander wrote:
| Do you actually have such an uptime requirement or do you
| think you have such an uptime requirement? How are you
| conducting it with other databases?
| wutwutwutwut wrote:
| Yes, if the software I work on doesn't work for 5 minutes
| we have a ton of tickets from customer. We have tens of
| thousands of customers who pay for it. Not being able to
| shut down your system for an hour isn't exactly a unique
| requirement. Technically our SLA is 10 minutes to perform
| jobs but our customers don't wait that long before creating
| tickets.
|
| 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?
| mrelectric wrote:
| It is relevant, reread and understand the parent comment.
| Get a managed pg and you'll have the same thing.
| rmbyrro wrote:
| That's the point.
|
| 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.
| wutwutwutwut wrote:
| I believe you are the one who need to reread the thread.
| The person I replied to claimed that "Postgresql upgrades
| are a piece of cake."
|
| 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.
| technion wrote:
| Not that I want to defend Oracle but I've been through the
| process of installing a new version, starting it up whilst
| loading the same database files currently being served by an
| older patch level, and seeing it fail over to the new version
| without dropping in flight queries. They've always done pretty
| well in this space in my view.
|
| 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.
| forinti wrote:
| Are you referring to updating one node at a time of a RAC
| cluster or is that some other method?
| technion wrote:
| Yes that is what I'm referring to. But note Oracle has the
| concept of a single node cluster, so you can have a
| completely standalone deployment and still perform this
| technique hot adding a new "node" when it's the same
| server.
| Spooky23 wrote:
| You have to do that because Oracle has a nasty habit of
| changing what's in the download and then changing the
| licensing scheme.
|
| 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.
| dingleberry420 wrote:
| Yeah, super easy. Let me just shut down my database server for
| 30 hours so I can do a dump and load.
| Axsuul wrote:
| Have you looked into `pg_upgrade --link`?
| dingleberry420 wrote:
| I know it, and it's the way to go after making backups.
| It's not that hard, but not trivial either. You have to
| install the old & new postgres versions alongside each
| other, shut the database down, etc. I've done it many
| times, it's not hard, but I wouldn't call it a "piece of
| cake". And it does require some downtime.
|
| 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).
| sascha_sl wrote:
| They're hard if you can't tolerate downtime, which most
| production users can't.
|
| 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-...
| nix23 wrote:
| zero-downtime-postgres-migration-done-right:
|
| https://engineering.theblueground.com/blog/zero-downtime-
| pos...
| wutwutwutwut wrote:
| Would you consider that a piece of cake? I have doubts. The
| page you linked to describes that if you follow the
| instructions on many other blogs you'll have data loss. Not
| a symptom of a piece of cake process
| nix23 wrote:
| If you need zero downtime, you already in a field where
| NOTHING is a piece of cake. Not your network, not your
| computing, not your storage, and i haven't even talked
| about the human aspect of "zero downtime".
| wutwutwutwut wrote:
| Alright. But the GP comment claimed upgrading PG was a
| piece of cake. You claim it is not a piece of cake. So it
| sounds like you agree that the claim that upgrading PG
| was a piece of cake was misleading.
| msbarnett wrote:
| > the GP comment claimed upgrading PG was a piece of
| cake. You claim it is not a piece of cake. So it sounds
| like you agree that the claim that upgrading PG was a
| piece of cake was misleading.
|
| 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.
| cersa8 wrote:
| CockroachDB and others come with huge downsides in
| performance and features. I keep trying to see of these
| databases are a good fit for me but just the sheer time it
| takes to import my database makes me shudder. I've used the
| Aws cloud hosted free tier of CockroachDB but ran into
| transaction timeouts on very moderate loads. There is a
| reason these databases aren't seeing massive uptake despite
| their lofty promises.
| billythemaniam wrote:
| Yeah CockroachDB suffers from the same problem as PG: the
| defaults kinda suck for certain workloads. CockroachDB has
| an additional problem that their documentation isn't as
| detailed as PG so it can be hard to know what to tune. TiDB
| and Spanner defaults are much better for a wider range of
| workloads.
| vvern wrote:
| Can you say more about workloads which suck for either of
| the two databases at their default settings?
| urthor wrote:
| Blue green deployment is, while it requires a professional,
| is not particularly complex or unusual.
|
| Postgres is a tool. It can do many things, but the tool
| cannot run your entire business.
| amtamt wrote:
| "Postres 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).
| BiteCode_dev wrote:
| My experience is the opposite. Most productions users can
| tolerate downtime.
|
| 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.
| fmajid wrote:
| I'd say a system not designed to support maintenance is not
| properly architected. You will need maintenance windows for
| an individual subsystem to perform OS upgrades, DB upgrades
| are no different and Postgres upgrades using pg_upgrade are
| relatively painless, provided you test them first in CI or
| against production snapshots, something the author's
| company seemingly lacks.
| hoffs wrote:
| How happy are you when your global bank has downtime in
| middle of the day?
| fmajid wrote:
| You design the complete system so it does not have
| dependencies on a single component, that way each
| subsystem can have proper maintenance schedules. It takes
| a lot more up-front work, along with defining what levels
| of service degradation are acceptable during a window,
| but that's the difference between a professionally run
| service and one run by amateurs. Look up Google's SRE
| methodology and their concept of error budget.
| PhineasRex wrote:
| You wouldn't schedule downtime for the middle of the day.
| You schedule downtime during your period of least usage.
| amtamt wrote:
| "a system not designed to support maintenance is not
| properly architected"
|
| 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.
| omnibrain wrote:
| You mentioned hospitals yourself. There are also alarm
| management solutions for alarm receiving centres, and
| security and emergency services. Can't really have downtime
| there too. Of course there are less busy hours, but an
| alarm can come at any moment and needs to be handled right
| then.
| sascha_sl wrote:
| I think you really underestimate the amount of dependencies
| (documented and otherwise) that exist in even medium sized
| company.
|
| 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.
___________________________________________________________________
(page generated 2022-06-25 23:01 UTC)