PGA: Cluster Migrations

PGA stands for Postgres Admin, however in practice it is also responsible for NATS admin as well. This 'app' in the pivot-internal is simply a Dockerfile that installs CLIs psql and nats, and copies Shell scripts. It is designed to deploy cluster-level database and NATS migrations that individual apps cannot, such as creating databases and database users.

The first (and only) shell script creates the Postgres databases for each app and the database user for each app, after which it creates the NATS JetStream stream if it does not already exist.

In the future we could then chain a second shell script to the first script to extend the configuration without modifying the existing initial behavor.

PGA expects a POSTGRES_CONN environment variable which should be a Postgres connection string with 'master access' to the Postgres cluster as well as a NATS_PASSWORD environment variable.

PGA expects NATS to listen on the normal port 4222 and for the NATS_PASSWORD to correspond to a user in NATS named pivot_admin_user, which must already exist. PGA will attempt to find a NATS instance to connect to using the Cloud Map namespace nats.pivot.local and the service name nats.

We build and push PGA as an image to ECR and then deploy it to each of our backend AWS environments as part of the Terraform config by triggering an ECS one-off task in Terraform with the run-task API whenever the Git SHA of the PGA image changes.

Note that PGA is not designed to scaffold a local Postgres instance. We have an initilization script in the pivot repo integrated with docker compose for that purpose. Likewise, the NATS configuration for local development is not part of PGA, that's in the pivot repo.

Idempotency

Consider that PGA will run in the context of both new and existing Pivot deployments. In each script, each action must check to see if that action has already been completed, or must be written in such a way that repeated runs have no effect. This is key as if we add additional scripts in the future, prior scripts will still re-run every time we run PGA.