Spent time over the weekend upgrading my TT-RSS install to postgres12 (dump & restore) and php7.4 (RC4) under Debian Buster and happy to report everything seems to be working fine after 24+ hours, both in web client and fever API’s.
just sharing in case anyone else was curious.
Not surprised, mine got upgraded to 12 the day after it came out… my DBA consultant friend wanted to experiment. 
why not pg_upgradecluster?
I’ve never done anything besides pg_upgrade.
No reason other than I know how dump & restores works, I’ve done it before when I upgraded from 9.6 > 11. I’ve never used pg_upgradecluster before. Next time though for sure.
I tried pg_upgradecluster, it ate my db. It would seem to be a solution looking for a problem, and at least with dump/restore you have a handy backup.
Isnt pg_upgradecluster just the same as pg_dump and pg_restore under the hood i believe after some reading? Ive also just done plain dump and restore which has worked out great,abit faster though to do it all in one command also thanks for tip aswell
Unless you used the -m switch and also the -k for inplace upgrade i dont think you should loose anything and old cluster should still be present just on another port according to docs,might had to manually start it though
at some point hopefully you’ll grow out of doing tedious things like this manually. who knows, maybe even discover ansible and puppet.
you also get a hot standby of your original cluster on a separate port, instead of a dump you need to waste time loading back in case of problems.
also, if your backup policy is “manual pg_dump before version upgrade” i’m not sure what to tell you other than maybe considering a carreer in a different industry.
According to the documentation, pg_upgradecluster:
“Please note that this program cannot upgrade clusters which use tablespaces. If you use those, you have to upgrade manually.”
So there might be some use for both methods then afterall,though pg-upgradecluster seems like the general most efficient way to upgrade as long as its not using tablespaces that is,nice find!
Yeah, one is the official postgres supported method, the other appears to be a script someone wrote to automate.
For me, using the actual postgresql utilities seems to be the best way… ie, pg_upgrade.
it’s never failed, it’s never even burped, and it’s been used for years now, successfully.
Why use a wrapper that ‘probably’ works instead of a native tool?
(PostgreSQL DBA there)
The surest and most flexible way is a pg_dump / install new pg / pg_restore from your database. It is the slowest too. (But you won’t forget to do a backup before upgrading).
pg_upgrade (from the PostgreSQL community) migrates the files of the database on the same server. It is much quicker, especially with --clone or -k. There is no going back with -k (but you have made a pg_dump backup before, right?). There are some horror stories with it like for any other tool of this kind, but anyway, for most people on a single server without specific extensions this should be okay.
pg_upgradecluster is a Debian-specific script able to do both methods. I have few experience with it, but, as it is used in automated Debian migrations, this should be safe in most situations.
A good DBA is paranoid, so I prefer to use pg_dump/pg_restore (-Fd -j8) if the downtime and available disk space are not a problem; pg_upgrade -k only if necessary.
Did I say that you must do a backup before?
So, from a paranoia standpoint, isn’t the surest method to do a pg_upgrade, without the -k ? that way you’ve still got your entire previous db up and running, and you don’t have as many chances for transcription errors to come in during the dump/restore processes… you’ve still got the old db sitting there, in case there’s anything at all wrong with the upgraded one.
pg_upgrade without -k is safe as is “I have a fallback”. If a pg_upgrade bugs corrupts your database, you may know it much later. A pg_restore is a logical operation that cannot corrupt an instance.
In fact, upgrading to a brand new instance (with pg_restore) is a good opportunity to install it with --data-checksums, that is often forgotten but much safer. (Well, from PG12, checksums can be activated after the creation of the PG instance).