# PostgreSQL HA bundle

This bundle contains:

- per-VPS config files for `db1`, `db2`, `db3`, and `proxy1`
- an interactive shell script that generates all files for your actual values
- Docker Compose files for DB nodes and the proxy node
- Patroni, HAProxy, Caddy, WireGuard, and helper scripts

## Layout

- `scripts/generate-configs.sh` — interactive generator
- `db-node/` — templates for PostgreSQL + Patroni + etcd hosts
- `proxy/` — templates for HAProxy + pgAdmin + PostgREST + Caddy host
- `examples/` — example generated files for db1/db2/db3/proxy1

## Assumptions

- You will define `db1`, `db2`, and `db3` in `/etc/hosts` as needed.
- WireGuard runs on the host OS, not inside Docker.
- Docker and Docker Compose are installed on all VPS hosts.
- The 3 DB hosts each run:
  - WireGuard
  - etcd
  - Patroni + PostgreSQL 16 + `pg_cron`
- The proxy host runs:
  - HAProxy
  - pgAdmin
  - PostgREST
  - Caddy

## Important note about TLS

This bundle keeps PostgreSQL private behind WireGuard and does not enable PostgreSQL TLS.
That is acceptable only because PostgreSQL, Patroni, and etcd are intended to stay on the private WireGuard network and not on the public internet.

## Quick start

Run the interactive generator:

```bash
chmod +x scripts/generate-configs.sh
./scripts/generate-configs.sh
```

It will ask for:

- public IPs or hostnames of db1/db2/db3/proxy1
- WireGuard tunnel IPs
- WireGuard private/public keys
- database passwords
- PostgREST settings
- Caddy domains and credentials

It writes output under `./output/<timestamp>/`.

## Deployment order

1. Configure WireGuard on all hosts and confirm tunnel connectivity.
2. Deploy DB stack on db1, db2, db3.
3. Verify Patroni cluster health.
4. Deploy proxy stack on proxy1.
5. Point DNS for Caddy-managed domains.
6. Initialize `appdb`, `pg_cron`, and PostgREST roles on the primary.

## Verify cluster

On a DB node:

```bash
docker exec -it db1-patroni patronictl -c /etc/patroni/patroni.yml list
```

Through HAProxy:

```bash
psql "host=<proxy-public-ip> port=5432 user=postgres dbname=postgres password=<password>"
```

## Firewall guidance

Publicly expose only:

- WireGuard UDP port 51820 on all nodes
- TCP 5432 on proxy1 if apps connect from the internet
- TCP 80/443 on proxy1 for Caddy

Keep private behind WireGuard only:

- TCP 5432 on db1/db2/db3
- TCP 8008 on db1/db2/db3
- TCP 2379/2380 on db1/db2/db3
- TCP 8080 and 3000 on proxy1 are bound to localhost only

## What to customize after generation

- replace example passwords
- replace example domains
- set your `/etc/hosts` entries if you want name-based references on the hosts
- review `ufw` or equivalent firewall rules