Cool post, but this is one major reason I like to use DBs like CRDB. We’ve upgraded maybe 20 times in the last year, including a major version upgrade, with zero downtime planning.
Yes to CRDB! We use some Postgres features that are not (yet) in CRDB (most notably advisory locks), but hopefully this post illustrates the tradeoff between CRDB and having to have so much ceremony around major version upgrades with Postgres.
This has been a problem for Postgres forever. I use it for some personal things where:
I have half a dozen users, so the total amount of data is trivial and a full export and import takes a few seconds.
I don’t care about downtime.
It’s depressing that there isn’t a built-in solution. I guess for big users, you’re running a replication solution and so you just add new replicas and retire old ones, it’s only larger single-instance deployments that suffer.
Oh, I misread the Wikipedia article. I thought it was changed to Apache 2, not from. If it’s BUSL, that’s a non-starter for any problem I’d be likely to have for which it is a solution. Yugabyte DB looks potentially interesting as an alternative. It also claims to have a Postgres-compatible query interface.
I guess for big users, you’re running a replication solution and so you just add new replicas and retire old ones
Actually that where the big problem lies, because the physical replication that your typical cluster manager like Patroni uses only works with the same PostgreSQL major version; and logical replication does not replicate everything.
it’s only larger single-instance deployments that suffer.
For that you actually have a built-in solution by using pg_upgrade, although I don’t know how long it takes on bigger databases.
For that you actually have a built-in solution by using pg_upgrade, although I don’t know how long it takes on bigger databases.
Last time I tried it, pg_upgrade was a bit painful because it required both the old and new versions of Postgres to be installed simultaneously, which wasn’t always possible with the way that they’re packaged. I ended up using the pgdumpall approach instead.
This dump & restore approach would also not work for us due to the required downtime involved, mostly because you need to disconnect all applications from the old database in order to get a reliable database backup.
You don’t need to disconnect clients, pg_dump is always consistent (per database, that is). However, you will have a dump from the time you start it, so if clients keep writing, those writes won’t be in the dump of course. So if you wanted to use this for an “online” upgrade, you’d have to make the database read-only.
True - you could go read-only and then take the backup. I can see that working for a variety of applications, even ours at Knock could be handled that way to a degree (we’d use other queues to back up various work during the outage, but certain read-only endpoints would continue to function as normal).
Cool post, but this is one major reason I like to use DBs like CRDB. We’ve upgraded maybe 20 times in the last year, including a major version upgrade, with zero downtime planning.
Yes to CRDB! We use some Postgres features that are not (yet) in CRDB (most notably advisory locks), but hopefully this post illustrates the tradeoff between CRDB and having to have so much ceremony around major version upgrades with Postgres.
What’s this “CRDB” you’re referring to? Searching for it yields anything but databases.
“CRDB db” yielded CockroachDB for me
Cockroach DB, which aims to be both Postgres compatible and highly available/distributed.
This has been a problem for Postgres forever. I use it for some personal things where:
It’s depressing that there isn’t a built-in solution. I guess for big users, you’re running a replication solution and so you just add new replicas and retire old ones, it’s only larger single-instance deployments that suffer.
Cockroach DB is an interesting option here since it is generally Postgres compatible but also supports rolling upgrades without losing availability.
Thanks for the pointer. It looks as if it was open sourced after I last had a serious requirement for a database. I’ll take a look next time.
It’s source available, uses the BUSL which is not FOSS.
But hearing about this use case makes me think I might want to use it anyway.
Oh, I misread the Wikipedia article. I thought it was changed to Apache 2, not from. If it’s BUSL, that’s a non-starter for any problem I’d be likely to have for which it is a solution. Yugabyte DB looks potentially interesting as an alternative. It also claims to have a Postgres-compatible query interface.
Actually that where the big problem lies, because the physical replication that your typical cluster manager like Patroni uses only works with the same PostgreSQL major version; and logical replication does not replicate everything.
For that you actually have a built-in solution by using pg_upgrade, although I don’t know how long it takes on bigger databases.
Last time I tried it,
pg_upgradewas a bit painful because it required both the old and new versions of Postgres to be installed simultaneously, which wasn’t always possible with the way that they’re packaged. I ended up using thepgdumpallapproach instead.This part is a little misleading:
You don’t need to disconnect clients, pg_dump is always consistent (per database, that is). However, you will have a dump from the time you start it, so if clients keep writing, those writes won’t be in the dump of course. So if you wanted to use this for an “online” upgrade, you’d have to make the database read-only.
True - you could go read-only and then take the backup. I can see that working for a variety of applications, even ours at Knock could be handled that way to a degree (we’d use other queues to back up various work during the outage, but certain read-only endpoints would continue to function as normal).