Upgrade to 2.1 fails: extension "pgautofailover" has no update path from version "2.0" to version "2.1"

[Jay2k1]

Today on our Ubuntu-based cluster, we installed updates via apt and our cluster broke.

Logs on the monitor node said

Dec 06 10:19:07 monitor01 pg_autoctl[67687]: 10:19:07 67687 ERROR Error 22023 while running Postgres query: ALTER EXTENSION "pgautofailover" UPDATE TO "2.1":
Dec 06 10:19:07 monitor01 pg_autoctl[67687]: 10:19:07 67687 ERROR ERROR:  extension "pgautofailover" has no update path from version "2.0" to version "2.1"

A bit of research told me that postgresql extension updates require an sql file with the filename <extension>--<old version>--<new version>.sql.

I've looked in the repo and found them here, but a file for 2.0 to 2.1 is indeed missing. Instead there's "2.0 to dummy" at https://github.com/hapostgres/pg_auto_failover/blob/main/src/monitor/pgautofailover--2.1--dummy.sql.

I've looked at the update sql file for 1.6 to 2.0 which didn't contain anything except comments, so I've copied it and named it appropriately:

$ cd /usr/share/postgresql/14/extension
/usr/share/postgresql/14/extension$ cp pgautofailover--1.6--2.0.sql pgautofailover--2.0--2.1.sql

Now the cluster was able to start again. I just don't know if the actual upgrade file would have had important instructions that we have not applied.

[Akkowicz]

Sent out a PR for this, let's see if there are any CI issues :)

[xinferum]

Yes, this is an actual problem.

[xinferum]

@dimitri, thank you for your work! If possible, could you pay attention to this problem in the new 2.1 release, thank you.

[sgrinko]

@DimCitus Firstly, I hasten to thank you for your work! I ask you to add the pgautofailover--2.0--2.1.sql file to the current issue, since errors are made without it when installing the expansion. @Akkowicz released PullRequest - https://github.com/hapostgres/pg_auto_failover/pull/1019 Thank you again a lot!