For me, the greatest pain of postgresql is upgrading between major versions. You need to install both versions to the same db server at the same time and then Tthe data must be copied (using pg_upgrade) between the two clusters, something that leads to downtime (that is proportional to the size of the data ie how much time it needs to copy and run some maintenance commands). If you consider that our cluster hosts databases for like 15 apps and each needs to be changed to display some "maintenance mode" home page you'lln understand my frustration and fear of major upgrades...
I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version, or using logical replication between the clusters and switching to the new version cluster. The problem is that since I am not a full time DBA (and my organisation doesn't have one) I don't trust myself with these techniques and rely to the trusted pg_upgrade method that will leave the old cluster as it was with all its data intact in case anything went wrong!
The only good thing is that postgresql supports each major version for a lot of time (5 years) so such updates don't need to be very frequent :)
> I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version
You need to have a proper backup of the database anyway, so I don't really see the risk in using the --link mode (which makes the downtime pretty much independent of the size of the actual data).
Well the problem is that to actually get the backup (using pgdump) I'd also needs time that is proportional to the size of the database; and during that time the database can't get any writes so the system will again be down!
Also I'd really rather avoid restoring the data from a backup unless there's a real disaster that happened.
pg_basebackup defaults to streaming WAL from whatever server it is taking a copy from, as backups are otherwise inconsistent. There's no need for the source database to be paused while doing it, and even if your backup takes several hours to complete, once it finishes the target will have all the writes that occurred while the backup was being taken.
Thought you might find this useful, as no one should be considering a Postgres backup as a downtime requiring process!
There are other options, though. For upgrades, a virtual machine snapshot is a perfect backup, and with really large databases, you would likely have something using WAL archiving, which provides a continuous backup until the point that you shut down the database for upgrade. Finally, if you have a database replica, that can also functionally act as your backup.
Maybe i didn't express myself clearly (english is not my primary language) but since with pg_upgrade the data will be copied from the old cluster to the new one, doing an upgrade to a 10GB database would need ~ 10 times more than upgrading a 1 GB database, just for the data copying. So the time will be proportional to the size of the data.
I see, that's a good point: I'm used to pg_upgrade in --link mode, but if you want to be safe and have an isolated old cluster around, you're right: you'll still need to copy the data. Thanks for clarifying.
Yes I think this is possible (at least there are some blog posts recommending that way for upgrading without downtime) however configuring (and testing) that mechanism is not something I want to do :|
I know there are some ways that reduce the downtime, like hard linking the data between the clusters instead of version, or using logical replication between the clusters and switching to the new version cluster. The problem is that since I am not a full time DBA (and my organisation doesn't have one) I don't trust myself with these techniques and rely to the trusted pg_upgrade method that will leave the old cluster as it was with all its data intact in case anything went wrong!
The only good thing is that postgresql supports each major version for a lot of time (5 years) so such updates don't need to be very frequent :)