Design Doc: Upgrade of the major version of postgres

[duanhongyi]

Design Doc

This is a design document for the upgrade of the major version of postgres(#12).

Goal

As we all know, workflow database uses postgresql, but at present it is still postgresql-9.4. How to establish a long-term upgrade mechanism is the main problem to be solved in this design. The goal of this design is to ensure seamless auto upgrade of major postgresql, the main implementation mechanism is using pg_upgrade tool.

Off-Cluster Storage Required

A Workflow upgrade requires using off-cluster object storage, since the default
in-cluster storage is ephemeral. Upgrading Workflow with the in-cluster default
of Minio will result in data loss.

See: https://docs.teamhephy.com/installing-workflow/configuring-object-storage/

Code Change

• Modify Dockerfile to use postgres:11-alpine, and add PGDATA_RECOVERY to docker image.
• Adding rootfs/bin/do_upgrade is used to implement the main upgrade function, because the old database is needed on the pg_upgrade mechanism, so the previous database binary program needs to be downloaded.
• The upgrade mainly involves two main branches:
  • One is the cluster using gluster/ceph and other block storage services, the upgrade scheme is detailed in rootfs/docker-entrypoint.sh.
  • The other rootfs/docker-entrypoint-initdb.d/003_restore_from_backup.sh, is using WALE to upgrade.
• For data security reasons, the PGDATA_RECOVERY content will not be deleted after the upgrade is successful. After confirming that there is no problem, it needs to be deleted manually, and not deleting it will not affect the service.

Tests

I added some test cases to ensure its robustness, which will run with the make test command. See in detail postgres/contrib/ci/test-upgrade.sh

[Cryptophobia]

Looks excellent and great design doc! Thank you for the contribution @duanhongyi. 👍

[kingdonb]

I think we can close this design doc, as the Database upgrades have already been launched now in v2.20.1.

The design is great, and the patch was perfect! There was a minor snag though:

https://blog.teamhephy.info/blog/posts/announcements/release-v2-20-1-postmortem#description-of-the-bug

Long story short, there was no issue with the patch as submitted, but we failed to update some of the charts in the release process. Nostra culpa maxima. Since gosu was replaced by su-exec, but the un-updated chart lifecycle still referred to gosu, we could have been losing cluster backups any time a new in-cluster database was launched and then terminated within the first 4 hours of its lifetime.

That's a pretty narrow window, but it was worth a blog post, so I wrote it up above.

We may decide pull v2.20.1 and launch this again with a different version number. I don't think that teamhephy/controller#92 is really severe enough to delete the release from the archive. We should still try to push v2.20.2 as soon as possible, with the fix for teamhephy/controller#92, once we figure out what that is.

Discuss?

[kingdonb]

The change was merged in #5

[Cryptophobia]

Thank you @duanhongyi for the work and the awesome update script! 🥇