Upgrading our PostgreSQL image notes

These are just very rough notes for the moment, while investigating our moving to a newer (supported) version of PostgreSQL.

---

The problem is that we're using an ancient version of PostgreSQL (9.5-alpine). By using that, we're stuck to using very old versions of some Python libraries, which in turn themselves are forcing us to stick with other further extremely old Python libraries:

https://github.com/getredash/redash/blob/ee601ec20676b0705e352bfb9b6726a0e53e3b12/requirements.txt#L28-L31

# We can't upgrade SQLAlchemy-Searchable version as newer versions require PostgreSQL > 9.6, but we target older versions at the moment.
SQLAlchemy-Searchable==0.10.6
# We need to pin the version of pyparsing, as newer versions break SQLAlchemy-Searchable-10.0.6 (newer versions no longer depend on it)
pyparsing==2.3.0

Many of our existing data sources have newer versions of their own libraries available which we can't use due to this, and it's very common for other data sources to be unusable for us due to the above ancient libraries.

So, we must upgrade PostgreSQL sooner rather than later. This week if possible.

---

While it's fairly trivial for us to just increase the version number of PostgreSQL in our docker-compose.yml file, that will automatically break the Redash installation for all of our existing users who upgrade.

The official Docker "postgres" images don't handle upgrades at all, instead completely erroring out (thus a permanent restart loop) when presented with older PostgreSQL database files.

---

The existing postgres:9.5-alpine Docker image we use can actually have PostgreSQL 13 easily installed by using apk add postgresql. However, the upgrade process from 9 -> 13 is non trivial if we want to use pg_upgrade, and it requires the old PG (v9) server process to be stopped.

Which due to the existing docker-entrypoint.sh approach they use, means the docker container automatically exits.

The exiting-when-PG-stops problem is trivial to solve if we're allowed to modify the existing docker entrypoint script. eg by adding an unbounded sleep loop:

while :; do
  sleep 5
done

With that in place, the entrypoint script can run the PG daemons in the background and have them start/stop/etc without terminating the docker container.

Getting PG v9 and PG v13 to both be callable at the same time looks to be problematic though, as they both need libpq.so, but their versions are incompatible with each other and placed in different directories. That's fine when we can call LD_LIBRARY_PATH with the different directory names separately, but calling both from the same pg_upgrade command seems impossible.

---

For Alpine images, there is an alpine-sdk package that can be added which easily adds the useful developer tools. eg compiler, make, etc. If we need to compile PG ourselves, that'd probably be the first package to install.