Skip to main content

Central PostgreSQL

French version: ../postgresql.md.

Connection​

  • EAI_DATABASE_URL: postgresql://user@host:5432/eai?sslmode=verify-full&sslrootcert=/etc/eai/pg-ca.pem. In prod the startup refuses a URL without sslmode=require, verify-ca or verify-full.
  • psycopg_pool pool (EAI_DB_POOL_MIN, EAI_DB_POOL_MAX), every connection is checked before use (replaced after a restart or failover), application_name=eai-control-plane in pg_stat_activity.
  • Isolation: every tenant transaction switches to the eai_app role (forced RLS, FORCE ROW LEVEL SECURITY).
  • Separate runtime login (ADR-0156): EAI_RUNTIME_DATABASE_URL is the login of the API and the worker, member of the eai_runtime role (V165: member of eai_app, no CREATE right, owner of nothing), created by the operator: CREATE ROLE eai_api LOGIN PASSWORD '...' IN ROLE eai_runtime;. The migrations keep EAI_DATABASE_URL (administrator login, owner of the schema). Empty: the API uses EAI_DATABASE_URL. In prod the same TLS requirement applies to both URLs.

Migrations​

  • python -m eai.migrate applies sql/postgres/V###__name.sql under an advisory lock; --status lists applied or pending. An applied migration that was modified is refused (checksum).

High availability​

  • CloudNativePG (deploy/data/postgres/cluster.yaml): 3 instances spread across zones, automatic failover, encrypted WAL archive to S3 and a daily base backup; PITR through a new Cluster in bootstrap.recovery.

Logical backup and restore (verified commands)​

The connection only goes through the libpq environment (PGHOST, PGPORT, PGUSER, PGDATABASE, PGSSLMODE, PGPASSFILE with mode 0600): never a password or URL in arguments, the scripts refuse it.

cd services/control-plane
export PGHOST=<pg-host> PGUSER=eai_backup PGSSLMODE=verify-full PGPASSFILE=/run/secrets/pgpass
PGDATABASE=eai scripts/pg_backup.sh /backup/eai-$(date +%F).dump   # pg_dump --format=custom, file 0600, checked by pg_restore --list
createdb eai_drill
PGDATABASE=eai_drill EAI_PYTHON=.venv/bin/python \
  EAI_DATABASE_URL='postgresql://eai_backup@<pg-host>/eai_drill?sslmode=verify-full' \
  scripts/pg_restore.sh /backup/eai-2026-10-30.dump                  # single transaction, fails on a pending migration

Roles (eai_app, owner) are cluster wide and must exist on the target. tests/test_core_pg_backup.py runs both scripts against a real PostgreSQL 16: tenant and data found, no pending migration, same forced RLS tables and same policies, eai_app sees nothing without a tenant scope.