Post-upgrade things we should include

[justinclift]

Looking at the Release Notes for today's PostgreSQL point release, it says people using BRIN indexes may need to run a re-index:

If you use BRIN indexes to  look up `NULL` values, you will need to reindex 
them after upgrading to this release. On PostgreSQL 12 and above, you can use
`REINDEX CONCURRENTLY` to avoid blocking writes to the affected index and table,
for example:

REINDEX INDEX CONCURRENTLY your_index_name;

Previous PostgreSQL releases (both major and minor) will probably also have similar things that need doing.

It would make sense for us to try and detect situations where these post-upgrade tasks need doing. At the very least we could attempt to alert the database admin of the need (for their database), though preferably we'd automate the task itself to keep this being "automatic".

[justinclift]

And yep, this is likely opening a can of worms. 😉

[justinclift]

Checking back over the older PG 15.x announcements:

• PostgreSQL 15.1 Announcement
  • No post-upgrade actions seem to be needed
• PostgreSQL 15.2 Announcement
  • No post-upgrade actions seem to be needed
• PostgreSQL 15.3 Announcement
  • No post-upgrade actions seem to be needed

So it looks like for upgrades internal to the PG 15.x series, then only the reindex of BRIN indexes is needed. And only if they're indexing NULL values.

[justinclift]

PostgreSQL SQL that returns a list of the BRIN indexes in a given database:

SELECT class.relname
FROM pg_class AS class
  JOIN pg_index idx on idx.indexrelid = class.oid
  JOIN pg_am access_method ON access_method.oid = class.relam
WHERE access_method.amname = 'brin';

Not (yet) sure if there's a way to figure out whether a BRIN index is indexing NULLs or not. Probably need to investigate that, although BRIN indexes should be pretty fast & cheap to recreate anyway.

---

So we could probably run that once per database, then regenerate the brin indexes for all of the databases on the system.

We also need to keep track of what version of PostgreSQL binaries were last being run with, so we know to only do this once upon running PG 15.4 for the first time.

I'm not (yet) aware of any existing place that stores this info. The PG_VERSION file only contains the major PG version, not the minor ones.

We might need to start storing this data ourselves, either on the filesystem (DATA_DIR/pgautoupgrade/something maybe?), or perhaps in a new pgautoupgrade database or schema.

If we go with the pgautoupgrade database or schema, it would give a bit more flexibility as it would easily allow for also storing things like upgrade history, etc.

But, adding that in could also have some downsides. Some software is likely very strongly bound to the database structure, so seeing new schema/structure could muck it up.

Probably safer (for now at least) to just create a "pgautoupgrade" directory inside the data directory, and store any persistent info we need there. Can also put upgrade history, error logs, etc there too I guess.