PostgreSQL and zero uptime upgrades

Summary

Geo customer opened a ticket as their testing of a GitLab 12 upgrade between two minor versions warned:

The version of the running postgresql service is different than what is installed.
Please restart postgresql to start the new version.
sudo gitlab-ctl restart postgresql

They are following our zero downtime guidance, which rests on PostgreSQL not being restarted.

The situation that would arise is illustrated by this output:

# sudo gitlab-psql
psql (9.6.17, server 9.6.11)

gitlabhq_production=# select version();
version
-----------------------------------------------
PostgreSQL 9.6.11 on x86_64-pc-linux-gnu [snip]

• the server is on one patch level, the binaries, such as psql on a more recent level.

Some questions / concerns:

• What happens if PG has to restart at some point

  • Answer, I think - nothing - the later patch version should run fine. /var/opt/gitlab/postgresql/data/PG_VERSION embodies the constraints around binary compatibility with the data, and this is used to prevent 10.x binaries using 9.6 datafiles, for example.

• Is there any risk from running like this.

  • There don't seem to be many issues around this, suggesting it's been proven 'in the field'. But, there are examples such as this issue relating to the FDW extension where there is incompatibility.

• Is there another strategy available?

  • If customers are using Omnibus as a distribution of PostgreSQL, and running a VM with just the database server on it, could they run an older Omnibus version than the rest of their fleet? Treat Omnibus purely as a distribution mechanism for PostgreSQL. Similar to how customers with external PostgreSQL may not do anything about upgrading it until or unless GitLab requires a major update.

Proposal

References

Minor PostgreSQL upgrades can break loaded extensions: #4808 (relating to the FDW extension that used to be used by Geo)