Skip to content

Database Backend

Jump to bottom
NeySlim edited this page May 2, 2026 · 3 revisions

Database Backend

Version: 2.142+

UCM supports two database backends:

Backend Default Use case
SQLite ✅ yes Single-node, simple, zero-config
PostgreSQL opt-in HA, multi-instance, larger PKIs, backups

The backend is selected at runtime via the DATABASE_URL environment variable. When unset, UCM uses the bundled SQLite database at ${UCM_DATA_DIR}/ucm.db.

Quick Reference

DATABASE_URL Backend
(unset) SQLite (default)
sqlite:///opt/ucm/data/ucm.db SQLite (explicit)
postgresql+psycopg2://user:pass@host:5432/ucm PostgreSQL
postgresql://user:pass@host:5432/ucm PostgreSQL (driver auto)

Requirements

  • PostgreSQL ≥ 13 (older versions are explicitly rejected by Test connection)
  • A pre-existing empty database and a user with CREATE privileges on it
  • Network reachability from UCM to the PostgreSQL server (port 5432 by default)

The psycopg2-binary driver is bundled in DEB, RPM and Docker images.

Admin lockout fix (v2.141) — switching the active backend from PostgreSQL back to SQLite no longer locks out the admin account. The bcrypt password hash from the SQLite source-of-truth is preserved across the swap and the in-process SQLAlchemy session pool is rebuilt before the next login attempt. Previously the swap could leave a stale connection pool pointing at the old backend, causing the next admin login to fail with Invalid credentials.

Settings → Database

The Settings → Database section shows:

  • Active backend — sqlite or postgresql
  • Database size — file size (SQLite) or pg_database_size (PG)
  • Table count — non-system tables
  • Migration version — current schema version

Three actions are available:

1. Test connection

Validates a DATABASE_URL before applying it. For PostgreSQL, this also reads server_version_num and rejects servers older than version 13 with a clear error message.

2. Switch backend

Persists DATABASE_URL to /etc/ucm/ucm.env (DEB/RPM only) and restarts the service. No data is migrated — switching to an empty PG points UCM at an empty schema, which UCM will create on next start.

Docker: the container cannot persist /etc/ucm/ucm.env. Set DATABASE_URL in your docker-compose.yml or docker run -e and restart the container.

3. Migrate data

Copies all data from the current backend to a target DATABASE_URL. The migration is bidirectional — both SQLite → PostgreSQL and PostgreSQL → SQLite are supported and verified end-to-end (47 tables, 3800+ rows).

Safety checks (fail fast, source untouched)

  1. Pre-flight emptiness check — the target is probed before any write. If users, cas or certificates already contain rows, migration is refused with HTTP 409 and a cleanup hint:
    • PostgreSQL target: psql -d ucm -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;'
    • SQLite target: delete the target file
  2. Source backup first — UCM creates a full backup of the current database before any migration.
  3. Source untouched on failure — if the migration fails mid-way, the source backend is unchanged. The error message points to the backup location.
  4. PostgreSQL ≥ 13 enforcement — applied to the target during Test connection.

How the migration works

  • Schema is created on the target via SQLAlchemy create_all() (no manual SQL).
  • Foreign key checks are temporarily disabled during bulk load:
    • PostgreSQL: SET session_replication_role = 'replica'
    • SQLite: PRAGMA foreign_keys = OFF
  • Source/target columns are intersected to handle legacy schema drift.
  • memoryview and JSON values are normalized across drivers.
  • PostgreSQL sequences are reset to the max existing ID after load.
  • The _migrations bookkeeping table is created on the target if missing.

Production patterns

Single-node, default

Leave DATABASE_URL unset. UCM uses ${UCM_DATA_DIR}/ucm.db. Use the standard backup tooling.

HA / shared backend (PostgreSQL)

  1. Provision an empty PG database (CREATE DATABASE ucm OWNER ucm;).
  2. On a freshly installed UCM: set DATABASE_URL and start — the schema is created automatically.
  3. On an existing SQLite UCM: use Settings → Database → Migrate data to copy SQLite → PG, then Switch backend.
  4. Point all UCM instances at the same DATABASE_URL.

Reversibility (PostgreSQL → SQLite)

Same workflow in the other direction — set DATABASE_URL to the SQLite URL, run Migrate data, then switch.

Backup considerations

The standard Settings → Backup workflow operates on the active backend — backups taken while running on PostgreSQL contain PG data, restore must be performed against a compatible PG schema. Use platform-native tools (pg_dump / pg_restore) if you want a portable snapshot independent of UCM.

Troubleshooting

Test connection says "PostgreSQL 12.x is older than the minimum supported version 13"

Upgrade your PostgreSQL server. UCM does not support PG < 13 due to JSON/sequence behaviour.

Migrate returns HTTP 409 "Target database is not empty"

Either the target was already used or a previous migration partially populated it. Reset the target as suggested in the error message and retry.

Migrate failed with FK / sequence errors

Check the application logs at /var/log/ucm/ucm.log. The source database is untouched — fix the target (drop schema, fix permissions, etc.) and retry. Restore the source from the backup created at the start of the migration if anything looks off.

Docker can't persist DATABASE_URL

Edit your docker-compose.yml:

services:
  ucm:
    environment:
      DATABASE_URL: postgresql+psycopg2://ucm:secret@db:5432/ucm

Then docker compose up -d.

See also

Clone this wiki locally