Re: pg_upgrade does not upgrade pg_stat_statements properly

On Thu, Jul 15, 2021 at 8:43 AM Dave Cramer <davecramer(at)gmail(dot)com<mailto:davecramer(at)gmail(dot)com>> wrote:

On Thu, 15 Jul 2021 at 11:29, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:

I’m not familiar with what hoops extensions jump through to facilitate upgrades but even if it was as simple as “create extension upgrade” I wouldn’t have pg_upgrade execute that command (or at least not by default). I would maybe have pg_upgrade help move the libraries over from the old server (and we must be dealing with different databases having different extension versions in some manner…).

Well IMHO the status quo is terrible. Perhaps you have a suggestion on how to make it better ?

To a certain extent it is beyond pg_upgrade's purview to care about extension explicitly - it considers them "data" on the database side and copies over the schema and, within reason, punts on the filesystem by saying "ensure that the existing versions of your extensions in the old cluster can correctly run in the new cluster" (which basically just takes a simple file copy/install and the assumption you are upgrading to a server version that is supported by the extension in question - also a reasonable requirement). In short, I don't have a suggestion on how to improve that and don't really consider it a terrible flaw in pg_upgrade.

I don’t know if this is a terrible flaw in pg_upgrade, it is a terrible flaw in the overall Postgres experience.

We are currently working through various upgrade processes and it seems like the status quo is:

Drop the extension, upgrade and reinstall
OR
Upgrade the cluster then upgrade the extension

The issue is that it often isn’t clear which path to choose and choosing the wrong path can lead to data loss.

I don’t think it is ok to expect end users to understand when it is an isn’t ok to just drop and recreate and often it
Isn’t clear in the extension documentation itself. I’m not sure what core can/should do about it but it is a major pain.

-- Rob