Skip to main content

PostgreSQL

PostgreSQL is the most flexible shared driver in AlmaForge. The same postgres driver field works for cluster state and audit events. It needs PostgreSQL 13 or later, the wal2json logical decoding plugin for the state backend, and a database user with the REPLICATION role attribute.

Storage concernSupported
Cluster state (name: state)yes
Audit events (name: audit-*)yes
Session recordings (name: recordings)no

TL;DR​

  1. Provision PostgreSQL 13+ with wal2json, wal_level = logical, and enough replication slots for your Auth Service instances.
  2. Write a StorageConfig with spec.postgres for name: state, and a separate database plus StorageConfig for any audit-* instances.
  3. Restart the Auth Service and verify with alma get storageconfig/state and alma get storageconfig/audit-primary.

Prerequisites​

Before you start, make sure you have:

  • PostgreSQL 13 or later.
  • wal2json 2.1 or later for the state backend.
  • wal_level = logical in postgresql.conf.
  • max_replication_slots set to at least the number of Auth Service instances you plan to run, with extra headroom for reconnects.
  • An AlmaForge database user with the REPLICATION role attribute for the state backend.
  • Static credentials, PostgreSQL TLS client certificates, or Azure AD authentication for the database user.

Azure Database for PostgreSQL ships with wal2json pre-installed. Debian and Ubuntu hosts can install it from the official PostgreSQL APT repository with apt-get install postgresql-<version>-wal2json. RHEL, CentOS, and Rocky hosts can install it from the official PostgreSQL YUM repository with dnf install wal2json_<version>. For other distributions, build from source per the wal2json README.

For dev, stand up a throwaway PostgreSQL instance with wal2json installed and discard it when you are done.

Configuration​

Step 1. Pick the database layout​

PostgreSQL is a portable shared driver that does not tie the control plane to one cloud provider. It fits self-hosted deployments, bare metal, Azure Database for PostgreSQL, Aurora PostgreSQL, and Cloud SQL for PostgreSQL.

Use one PostgreSQL database for cluster state and a different PostgreSQL database for audit events. Logical decoding works per database, not per table. Running state and audit in the same database forces the state change feed to sift through audit rows it does not care about.

Step 2. Write the state backend resource​

The state backend uses one PostgreSQL database that every Auth Service instance points at:

state.yaml:

examples/storage/postgresql/state.yaml
# PostgreSQL cluster-state backend using certificate authentication.
apiVersion: almaforge.com/v1
kind: StorageConfig
metadata:
  name: state
spec:
  postgres:
    connString: postgresql://almaforge@db.example.com/almaforge_state?sslmode=verify-full&pool_max_conns=20

For Azure Database for PostgreSQL with Azure AD authentication:

state-azure.yaml:

examples/storage/postgresql/state-azure.yaml
# Azure Database for PostgreSQL cluster-state backend using managed identity.
apiVersion: almaforge.com/v1
kind: StorageConfig
metadata:
  name: state
spec:
  postgres:
    connString: postgresql://almaforge@db-name.postgres.database.azure.com/almaforge_state?sslmode=verify-full
    authMode: azure

The connString accepts parameters supported by pgxpool. Use pool_max_conns to cap each Auth instance's connection pool, separate from the dedicated change-feed connection. Set it explicitly when budgeting connections across replicas. If your TLS certificates live outside the default ~/.postgresql location, set sslcert, sslkey, and sslrootcert in the connection string as well.

FieldRequiredDefaultNotes
connStringyesnonelibpq-compatible connection string for the state database. URI form or keyword=value form both work.
authModenostatic authEmpty string for static credentials or TLS client certificates, azure for Azure AD token authentication.
changeFeedConnStringnoconnStringUse a separate user or connection path for logical decoding if you need one.
changeFeedPollIntervalno1sPoll period for the logical change feed.
changeFeedBatchSizeno1000Maximum number of change-feed entries read per poll.
disableExpirynofalseDisable the built-in TTL sweep if another job handles expired-row cleanup.
expiryIntervalno30sInterval between expiry sweeps.
expiryBatchSizeno1000Maximum number of expired rows cleaned per pass.

Step 3. Write any audit-events resources​

The same postgres driver field works for an audit-events instance. Use a different PostgreSQL database from the state backend:

audit.yaml:

examples/storage/postgresql/audit.yaml
# PostgreSQL destination for audit events.
apiVersion: almaforge.com/v1
kind: StorageConfig
metadata:
  name: audit-primary
spec:
  postgres:
    connString: postgresql://almaforge@db.example.com/almaforge_audit?sslmode=verify-full

You can fan out to multiple Postgres clusters by adding more audit-* instances. Each is its own StorageConfig:

audit-fanout.yaml:

examples/storage/postgresql/audit-fanout.yaml
# Two PostgreSQL destinations that each receive every audit event.
apiVersion: almaforge.com/v1
kind: StorageConfig
metadata:
  name: audit-primary
spec:
  postgres:
    connString: postgresql://almaforge@primary.example.com/almaforge_audit?sslmode=verify-full
---
apiVersion: almaforge.com/v1
kind: StorageConfig
metadata:
  name: audit-failover
spec:
  postgres:
    connString: postgresql://almaforge@failover.example.com/almaforge_audit?sslmode=verify-full

Audit writes do not use logical decoding, so the REPLICATION role attribute is required only for state-backend users, not for audit-only users.

Retention lives on the driver block as auditRetentionPeriod. PostgreSQL honors it via a background sweeper.

Step 4. Restart and verify​

Restart the Auth Service after updating the bootstrap file, then verify the active resources:

Terminal
sudo systemctl restart almadalma get storageconfig/state -o yamlalma get storageconfig/audit-primary -o yaml

The configuration is loaded when these commands return the intended databases. Sign in again and confirm the login event appears in the audit log to check the audit write path.

URI form​

Equivalent URI strings, suitable for an environment variable or a CLI flag. Standard libpq query parameters (sslmode=..., application_name=..., etc.) stay in the URI query string. AlmaForge-specific knobs go in the URI fragment after #.

State backend:

text
postgres://alma:secret@db.example.com:5432/almaforge?sslmode=verify-full

Audit backend with retention and Azure AD authentication:

text
postgres://alma@db.example.com:5432/audit?sslmode=verify-full#retention_period=72h&auth_mode=azure

retention_period is the canonical fragment key. The typed JSON-tag form auditRetentionPeriod is accepted as an alias.

Troubleshooting​

Connection succeeds but the state backend does not replicate changes​

The usual causes are wal2json missing, wal_level not set to logical, or too few replication slots. Fix the PostgreSQL configuration first, then restart the Auth Service.

PostgreSQL is behind pgbouncer or another transaction pooler​

External poolers are not supported. pgbouncer and similar transaction-pooling proxies break the temporary replication slots and prepared statement cache the state backend depends on. Connect AlmaForge directly to PostgreSQL.

State and audit point at the same database​

Split them. The state change feed should not share a database with audit writes.

Operations​

Authentication​

Certificate authentication is the preferred path. Use standard PostgreSQL TLS client cert auth, add hostssl ... cert rules in pg_hba.conf, and put sslmode, sslcert, sslkey, and sslrootcert in the connection string when the files are not in the default PostgreSQL client locations.

If you must use a password, keep it in ~/.pgpass instead of embedding it in the bootstrap file.

Set authMode: azure on the driver for Azure AD. AlmaForge obtains short-lived tokens through the Azure credential chain and uses them as the database password. That works with environment-based credentials, Workload Identity, and managed identity, as long as PostgreSQL is configured to allow Azure AD sign-in for the target user. The same authMode: azure setting works for an audit-* instance pointing at the same Azure Database.

Capacity and failover​

The change feed opens one logical replication slot per Auth Service instance. Size max_replication_slots with headroom for reconnects and rolling maintenance.

The REPLICATION role attribute effectively gives AlmaForge broad read power across the PostgreSQL cluster. Run it on a dedicated cluster, or restrict that user's connect privileges to the AlmaForge databases only.

A synchronous replica plus tested failover is the safest production shape. Azure's built-in high-availability mode is usually enough if you want managed failover.

References​