7 ms·

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 up

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.

sascha_sl 4y ago
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-version.html https://www.cockroachlabs.com/docs/stable/upgrade-cockroach-...
- nix23 4y ago
  zero-downtime-postgres-migration-done-right: https://engineering.theblueground.com/blog/zero-downtime-postgres-migration-done-right/ https://engineering.theblueground.com/blog/zero-downtime-pos...
  - wutwutwutwut 4y ago
    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 4y ago
      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 4y ago
        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 4y ago
        > 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.
        wutwutwutwut 4y ago
        So if I tell you that upgrading pretty much all databases is a piece of cake but not include the criteria "unless you want to keep your data" you would say that is a fair statement? 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.
        msbarnett 4y ago
        > So if I tell you that upgrading pretty much all databases is a piece of cake but not include the criteria "unless you want to keep your data" you would say that is a fair statement? 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.
- urthor 4y ago
  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 4y ago
    "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 4y ago
  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.
  - sascha_sl 4y ago
    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.
  - fmajid 4y ago
    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.
    - amtamt 4y ago
      "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.
- cersa8 4y ago
  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 4y ago
    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 4y ago
      Can you say more about workloads which suck for either of the two databases at their default settings?
dewey 4y ago
> 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 4y ago
  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 4y ago
    > 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 4y ago
      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 4y ago
        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.
wutwutwutwut 4y ago
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?
- Avamander 4y ago
  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 4y ago
    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 4y ago
      It is relevant, reread and understand the parent comment. Get a managed pg and you'll have the same thing.
      - wutwutwutwut 4y ago
        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 4y ago
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.
- Spooky23 4y ago
  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.
- forinti 4y ago
  Are you referring to updating one node at a time of a RAC cluster or is that some other method?
  - technion 4y ago
    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.
dingleberry420 4y ago
Yeah, super easy. Let me just shut down my database server for 30 hours so I can do a dump and load.
- Axsuul 4y ago
  Have you looked into `pg_upgrade —link`?
  - dingleberry420 4y ago
    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 4y ago

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-version.html https://www.cockroachlabs.com/docs/stable/upgrade-cockroach-...

nix23 4y ago
zero-downtime-postgres-migration-done-right: https://engineering.theblueground.com/blog/zero-downtime-postgres-migration-done-right/ https://engineering.theblueground.com/blog/zero-downtime-pos...
- wutwutwutwut 4y ago
  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 4y ago
    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 4y ago
      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 4y ago
        > 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.
        wutwutwutwut 4y ago
        So if I tell you that upgrading pretty much all databases is a piece of cake but not include the criteria "unless you want to keep your data" you would say that is a fair statement? 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.
        msbarnett 4y ago
        > So if I tell you that upgrading pretty much all databases is a piece of cake but not include the criteria "unless you want to keep your data" you would say that is a fair statement? 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.
urthor 4y ago
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 4y ago
  "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 4y ago
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.
- sascha_sl 4y ago
  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.
- fmajid 4y ago
  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.
  - amtamt 4y ago
    "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.
cersa8 4y ago
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 4y ago
  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 4y ago
    Can you say more about workloads which suck for either of the two databases at their default settings?

dewey 4y ago

> 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 4y ago
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 4y ago
  > 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 4y ago
    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 4y ago
      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.

wutwutwutwut 4y ago

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?

Avamander 4y ago
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 4y ago
  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 4y ago
    It is relevant, reread and understand the parent comment. Get a managed pg and you'll have the same thing.
    - wutwutwutwut 4y ago
      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 4y ago

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.

Spooky23 4y ago
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.
forinti 4y ago
Are you referring to updating one node at a time of a RAC cluster or is that some other method?
- technion 4y ago
  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.

dingleberry420 4y ago

Yeah, super easy. Let me just shut down my database server for 30 hours so I can do a dump and load.

Axsuul 4y ago
Have you looked into `pg_upgrade —link`?
- dingleberry420 4y ago
  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).