Monitoring 2 Postgres databases, using native PG commands.

Usage:

# Start docker, force full refresh
docker-compose up --force-recreate --remove-orphans --build

# Tear down
docker-compose down --volumes --remove-orphans

• Setup uses 2 Postgres databases and one consul server/agent (see docker-compose).
• Both databases are monitored by consul using a healthcheck.
• Consul docker image is built with postgres-client so that it can probe Postgres from the server
• Database is checked every 3s here
  • This script checks pg_isready here
• If the datbase goes down then it triggers a watch script here
  • This watcher inserts the state change into Consul KV here, but it could really call any endpoint.

• There is only a single Consul server running - we should run multiple to ensure HA.
• There is no Postgres failover - this is merely handling the monitoring of 2 databases as a POC.
• There could be a liveness probe sent directly from the server, but I like the idea that we could drop this on top of any system (RDS, DO, GCP, etc), and it will "Just work". Since we are using native Postgres, it works with an Postgres-compatible docker image - no extensions required.
• I've written everything on the consul side in shell scripts for now. Could be moved to Rust/Go/Python/etc for better DX.

Could we use a local DB for service discovery - store availabilty data which is replicated p2p to every other service?

• NATs
• CRDT? - Yjs, Automerge rust: https://github.com/automerge/automerge-rs
• rqlite: https://github.com/rqlite/rqlite/ (although we probably want master-master)
• litestream
• Barrel https://barrel-db.org/

Should we just store all the availability state in a shared Postgres?

• Gets rid of KV store
• Consolidates on a single tool
• Atomic - no threat of split brain

Can we wrap all of this into a single Elixir cluster?

• Genserver for "pings" - healthcheck
• Genserver for "pooling" - hold open some connections like pgbouncer
• Reverse proxy for loadbalancing/message queing
• Globally distributed - multitenant