Generic PostgreSQL Container

A self-contained PostgreSQL container image driven entirely by standard environment variables. It owns the full database lifecycle — install, initialize, optionally restore from a dump, apply bundled schema files, create a limited application role, and shut down gracefully — without any deployment-specific logic (no remote-access tunneling, no environment selection, no bundled database dumps).

The lifecycle is split into three sequential phase scripts plus a thin orchestrator, all in /usr/local/bin: init-db.sh (install / init / create role / optional restore), tables_manifest.sh (apply the manifest's schema files in order, then grant), and run-postgres.sh (foreground server under a graceful-shutdown trap). startup.sh is the entrypoint: it sources the first two and execs the third.

The image ships the orchestration.sql schema and a manifest listing it, and applies it on first initialization. To run with a different schema, point SCHEMA_DIR at a directory of *.sql files plus a tables_manifest.txt that sets the load order (see Schema Initialization).

Environment Variables

Variable	Required	Description
POSTGRES_USER	Yes	Application role to create with limited privileges (SELECT/INSERT/UPDATE/DELETE, no DDL).
POSTGRES_PASSWORD	Yes	Password for the application role.
POSTGRES_DB	Yes	Target database name to create / restore into.
BACKUP_FILE	No	Path to a pg_dump -Fc archive to restore on first initialization. If unset, the database starts empty.
RESTORE_SCHEMA_ONLY	No	Set to true to restore only the schema (no table data) from BACKUP_FILE. Intended for tests that exercise the restore path without the bulk-data cost.
FORCE_REINIT	No	Set to true to clear the data directory and reinitialize from scratch.
SCHEMA_DIR	No	Directory holding the schema *.sql files and the tables_manifest.txt that orders them, applied on first initialization (default /etc/postgres-schemas).

The server listens on port 5432 and accepts password-authenticated connections from any host. Data persists in /var/lib/postgresql/data; mount a volume there to retain it across container restarts (subsequent starts skip initialization, restore, and schema application).

Schema Initialization

On first initialization, after the optional dump restore and before granting the application role, tables_manifest.sh reads tables_manifest.txt from SCHEMA_DIR (default /etc/postgres-schemas) and applies each listed *.sql file in the order listed (entries are filenames relative to SCHEMA_DIR; blank lines and #-comments are ignored). Files use CREATE ... IF NOT EXISTS, so they are idempotent and safe to apply on top of a restored dump. The grant block runs at the end of tables_manifest.sh, after every manifest entry is applied, so the one-shot GRANT ... ON ALL TABLES covers every object created here.

This image bundles orchestration.sql and a tables_manifest.txt listing just that file. To apply a different schema set or load order, supply your own tables_manifest.txt (and *.sql files) through SCHEMA_DIR.

Build and Run

In the commands below, dazel run @cosmos_eval//database:image_load loads the image as database-postgres:<version>, where <version> is the value of VERSION in version.bzl (currently 1.1.0).

# Build and load the image into Docker
dazel run @cosmos_eval//database:image_load

# Run with only the bundled schema applied (no data)
docker run -d --name postgres \
  -p 5432:5432 \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=changeme \
  -e POSTGRES_DB=appdb \
  -v postgres-data:/var/lib/postgresql/data \
  database-postgres:<version>

# Run and restore from a bundled/mounted dump on first start
docker run -d --name postgres \
  -p 5432:5432 \
  -e POSTGRES_USER=appuser \
  -e POSTGRES_PASSWORD=changeme \
  -e POSTGRES_DB=appdb \
  -e BACKUP_FILE=/backups/snapshot.dump \
  -v /path/to/backups:/backups \
  -v postgres-data:/var/lib/postgresql/data \
  database-postgres:<version>

Testing

# Boots the image with the POSTGRES_* contract (no BACKUP_FILE) and verifies the init / app-user / grant path.
dazel test @cosmos_eval//database/tests:image_test