On Thu, Jul 29, 2021 at 9:28 AM Jan Wieck <jan@wi3ck.info> wrote:
On 7/29/21 12:00 PM, David G. Johnston wrote: > Ok, looking at the flow again, where exactly would the user even be able > to execute "CREATE EXTENSION" meaningfully? The relevant databases do > not exist (not totally sure what happens to the postgres database > created during the initdb step...) so at the point where the user is > "installing the extension" all they can reasonably do is a server-level > install (they could maybe create extension in the postgres database, but > does that even matter?). > > So, I'd propose simplifying this all to something like: > > Install extensions on the new server
Extensions are not installed on the server level. Their binary components (shared objects) are, but the actual catalog modifications that make them accessible are performed per database by CREATE EXTENSION, which executes the SQL files associated with the extension. And they can be performed differently per database, like for example placing one and the same extension into different schemas in different databases.
pg_upgrade is not (and should not be) concerned with placing the extension's installation components into the new version's lib and share directories. But it is pg_upgrade's job to perform the correct catalog modification per database during the upgrade.
That is exactly the point I am making. The section is informing the user of things to do that the server will not do. Which is "install extension code into the O/S" and that mentioning CREATE EXTENSION at this point in the process is talking about something that is simply out-of-scope.
> Any extensions that are used by the old cluster need to be installed > into the new cluster. Each database in the old cluster will have its > current version of all extensions migrated to the new cluster as-is. > You can use the ALTER EXTENSION command, on a per-database basis, to > update its extensions post-upgrade.
That assumes that the extension SQL files are capable of detecting a server version change and perform the necessary (if any) steps to alter the extension's objects accordingly.
Off the top of my head I don't remember what happens when one executes ALTER EXTENSION ... UPGRADE ... when it is already on the latest version *of the extension*. Might be an error or a no-op.
And to make matters worse, it is not possible to work around this with a DROP EXTENSION ... CREATE EXTENSION. There are extensions that create objects, like user defined data types and functions, that will be referenced by end user objects like tables and views.
These are all excellent points - but at present pg_upgrade simply doesn't care and hopes that the extension author's documentation deals with these possibilities in a sane manner.