Database

Database and migration failure

Symptom #

Manager will not become ready, will not start, or schema commands fail:

• /health/ready 503 with database: not_configured, database: timeout, or database: unavailable
• journal: connection, SSL, password, or migration errors
• -migrate / -preflight-migrate / -check-schema exits non-zero
• restore left the Manager unable to authenticate Agents or admins

PostgreSQL is the system of record. SQLite is not the production Manager database.

Likely verified causes #

1. PostgreSQL down or not reachable at database.host:database.port (example localhost:5432).
2. Wrong database.name / database.user / password contract.
3. database.ssl_mode mismatch. Example default require. Allowed: disable, require, verify-ca, verify-full.
4. Enterprise production: password not in YAML; missing systemd credential database_password ($CREDENTIALS_DIRECTORY, expected mount /run/credentials/zwall-manager.service in strict custody).
5. Combined exclusive flags: -migrate, -preflight-migrate, -check-schema, -migration-exec-preflight, -validate-runtime (and -init-db as migrate alias) — choose one.
6. -init-db used; it is a deprecated alias for -migrate and logs a warning.
7. Enterprise: running -migrate from a random checkout instead of the admitted release Manager binary.
8. PostgreSQL major version confusion: Community README states 15+; Enterprise release/test gate is observed as PostgreSQL 16. There is no single published customer major-version matrix.
9. Hand-edited schema_migrations after a failed apply.
10. Restore from a dump that does not include required tables.

Diagnostic commands/endpoints #

sudo systemctl status postgresql
sudo systemctl status zwall-manager
sudo journalctl -u zwall-manager -n 200 --no-pager
curl -sS --cacert /etc/zcs/certs/manager.crt \
  https://manager.example.com:8443/health/readyCopy

Readiness database field: ok | not_configured | timeout | unavailable. Failed checks include database: not_configured, database: timeout, or database: plus HealthCheck() error text.

Manager flags:

# Validate pending plan without applying
/opt/zwall-manager/bin/zwall-manager -config /etc/zwall-manager/manager.yml -preflight-migrate

# Validate recorded schema version and checksums without applying
/opt/zwall-manager/bin/zwall-manager -config /etc/zwall-manager/manager.yml -check-schema

# Forward-only migrate, then exit (do not use as the running service)
/opt/zwall-manager/bin/zwall-manager -config /etc/zwall-manager/manager.yml -migrateCopy

Community source equivalent uses ./bin/manager -config /etc/zcs/manager.yml with the same flags.

-init-db is deprecated; use -migrate.

Do not combine those flags. The process fatals: Choose only one of -migrate, -preflight-migrate, -check-schema, -migration-exec-preflight, or -validate-runtime.

Scripts (both trees): scripts/backup_db.sh, scripts/restore_db.sh, scripts/full_backup_and_verify.sh. Required env: DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, DB_NAME. Optional PGSSLMODE.

restore_db.sh verifies tables exist after restore:

• agents
• admins
• rules
• schema_migrations

What each result means #

Safe remediation #

1. Start PostgreSQL. Confirm host/port/name/user.
2. Align ssl_mode with the server. Example production value is require, not disable.
3. Enterprise: restore database_password via systemd credentials / admitted custody path — not YAML. Community: operator-managed DB_PASSWORD (Community installer may embed secrets in the unit environment; treat that file as secret-bearing).
4. Back up before migrate (BACKUP).
5. Run -preflight-migrate, then -migrate from the admitted Manager binary, then start the service.
6. If schema is already applied: -check-schema for checksums; do not hand-edit migration tables.
7. Restore: use scripts/restore_db.sh in an isolated environment first. Restore license identity files as a set when licensed (see backup doc). Script existence is not a certified RPO/RTO or zero-downtime contract.

Scripts exist; the certified recovery contract is unresolved. Do not treat dump/restore scripts as a customer RPO/RTO product.

Do-not-do warnings #

• Do not combine exclusive migration flags.
• Do not use -init-db in new procedures.
• Do not run -migrate in the same process you expect to serve HTTP (it exits).
• Do not treat a receipt file as permission to skip release verification.
• Do not publish Community “15+” and Enterprise “16” as one certified matrix.
• Do not publish internal 15-minute RPO / 30-minute RTO as a customer SLA.
• Do not put DB_PASSWORD or backup passphrases on argv or in tickets.
• Do not use replication as a backup.

Escalation condition #

Escalate when:

• PostgreSQL 16 vs 15 is the suspected incompatibility (owner decision)
• checksums fail on an admitted release binary against a database that -preflight-migrate cannot explain
• restore scripts succeed on table presence but Agents/admins cannot authenticate — possible incomplete dump or missing license/identity files; still not a certified runbook
• Enterprise --manager-database-only / migration identity flags fail on a verified bundle

Related documentation #

• Database preparation
• Database maintenance
• Backup. Restore is not a certified DR product.
• Manager unavailable
• Manager authentication

Edition #

SHARED flags and table names. Enterprise uses systemd database_password and verified-release migration identity. Community uses operator env/YAML per Community installer.

Status #

SUPPORTED for connect/migrate diagnosis. The restore-contract and PostgreSQL major version remain gaps.