sysmig

Lightweight tool that manages host state in DB-migration (Flyway) style. Started for a 1GB VM (ocrt-postgres), but deployable to any machine via cloud-init.

Usage

sudo bash /opt/sysmig/sysmig up        # apply all pending migrations (idempotent)
sudo bash /opt/sysmig/sysmig down      # roll back the last migration
sudo bash /opt/sysmig/sysmig down all  # roll back everything (reverse order)
bash /opt/sysmig/sysmig status         # history + drift check (exit 1 on drift)

• Migrations: migrations/NNN-name.sh, receiving up/down/status as $1
• Applied history: /var/lib/sysmig/applied - applied entries never re-run
• On failure the run aborts at that step; earlier steps stay applied
• status runs each applied migration's read-only drift check (exit 1 on drift); DB checks need root, otherwise shown as ?

Adding a new migration

1. Create migrations/NNN-name.sh (number higher than existing; leave gaps)
2. Implement up, down and status cases - up/down must actually work; status is a read-only check (exit 0 OK / 1 DRIFT / 2 unknown)
3. Commit & push, then on the server: git pull && sudo bash sysmig/sysmig up

cloud-init integration

#cloud-config
runcmd:
  - [ git, clone, <REPO_URL>, /opt/sysmig ]
  - [ bash, /opt/sysmig/sysmig, up ]

For a private repo you need an auth strategy:

• deploy key (existing machines): register a read-only key on the repo
• fleet rollout: make the repo public, or embed a read-only token in the https URL

Current migrations

#	name	effect
001	create-swap	2GB swap + fstab + vm.swappiness=10
002	disable-networkd-dispatcher	reclaim ~27MB RAM
003	purge-exim4	reclaim ~21MB RAM (package removed)
004	purge-haveged	reclaim ~8MB RAM (package removed)
005	mount-pgdata	/dev/sdb (20G) -> /var/lib/postgresql, fstab
006	install-postgres	PG 18 via PGDG repo, cluster on the disk
007	postgres-admin-role	iwanhae OS user -> PG superuser (peer auth)
008	create-db-iwanhae	database iwanhae owned by admin role
010	tune-postgres	1GB stability: conns=30, cache hint, timeouts, temp_file_limit
011	postgres-observability	pg_stat_statements, slow-log 500ms, io timing
012	tune-cpu	parallel caps + jit=off for shared 2 vCPU
013	install-pgbouncer	:6432 transaction pooling, localhost-only
015	remote-access	ocrt_dev db+role, TLS+SCRAM, listen 0.0.0.0

PgBouncer notes (013/015)

Serverless clients (many connections, few TPS) connect to PgBouncer on :6432; PostgreSQL keeps max_connections=30 (010) with headroom for admin sessions. Since 015, PgBouncer listens on 0.0.0.0 with mandatory TLS (self-signed CA in /etc/pgbouncer/tls) and SCRAM auth via /etc/pgbouncer/userlist.txt (synced from pg_authid). The app database is ocrt_dev, owned by role ocrt_dev (not superuser - DB ownership is enough for schema migrations); its password lives at /var/lib/sysmig/secrets/ocrt_dev.pass (root-only).

Two pool aliases point at the same database: ocrt_dev (transaction mode - runtime) and ocrt_dev_migrate (session mode - for migration tools that use session advisory locks, e.g. Prisma).

The GCP firewall rule for tcp:6432 is managed outside sysmig (see 015 header for the gcloud command).