Enterprise Data Platform

Appearance

Local PostgreSQL ​

Use the local PostgreSQL container for development, connector testing, and early schema work. It is a developer convenience, not the production deployment pattern.

Start PostgreSQL ​

sh
cp .env.example .env
make postgres-up

The local stack keeps PostgreSQL private on the Compose network and routes database clients through PgBouncer. The init script creates these databases:

  • edp_raw
  • edp_ods
  • edp_vault
  • edp_mart
  • edp_app
  • airflow
  • superset
  • openmetadata
  • openmetadata_airflow
  • ckan
  • ckan_datastore

Postgres bootstrap settings and connection strings are documented in .env.example. Docker Compose reads .env automatically.

Use the Compose service name pgbouncer in database URLs. The default workflow runs database tooling inside containers on the same Compose network, so direct PostgreSQL host-port exposure is not required.

The init script uses POSTGRES_USER as the owner for EDP-owned databases and creates separate owners for the airflow, superset, OpenMetadata, and CKAN metadata databases.

Install Migration Tooling ​

Migration tooling runs through the db-tools Compose service. The image is built from postgres/Dockerfile and installs postgres/requirements.txt.

Apply Migrations ​

sh
make db-upgrade

The EDP migration histories target these databases:

  • raw targets edp_raw
  • ods targets edp_ods
  • vault targets edp_vault
  • mart targets edp_mart
  • app targets edp_app

Each EDP-owned database has its own meta schema for Alembic's version table and operational metadata that belongs with that layer.

The airflow, superset, OpenMetadata, and CKAN databases are component metadata databases. Create them locally so the tools have somewhere to store state, but let those applications manage their own tables.

Airflow, Superset, OpenMetadata, and CKAN use their own local database roles:

  • AIRFLOW_DATABASE_USER owns the airflow database.
  • SUPERSET_DATABASE_USER owns the superset database.
  • OPENMETADATA_DATABASE_USER owns the openmetadata database.
  • OPENMETADATA_AIRFLOW_DATABASE_USER owns the openmetadata_airflow database.
  • CKAN_DATABASE_USER owns the ckan and ckan_datastore databases.
  • CKAN_DATASTORE_READONLY_USER has read-only CKAN DataStore access after CKAN permissions are initialized.

Backup and Restore ​

The local PostgreSQL image includes pgBackRest for physical backup and WAL archiving. Initialize the local stanza and take a full backup with:

sh
make pgbackrest-stanza
make pgbackrest-check
make pgbackrest-backup
make pgbackrest-info

See Local pgBackRest for restore guidance and backup troubleshooting.

Create New Migrations ​

Edit the matching model module in postgres/models/, then generate an Alembic revision from the model diff:

sh
alembic -c postgres/alembic.ini -n raw revision --autogenerate -m "describe raw change"
alembic -c postgres/alembic.ini -n ods revision --autogenerate -m "describe ods change"
alembic -c postgres/alembic.ini -n vault revision --autogenerate -m "describe data vault change"
alembic -c postgres/alembic.ini -n mart revision --autogenerate -m "describe data mart change"
alembic -c postgres/alembic.ini -n app revision --autogenerate -m "describe app change"

Run these commands inside the tooling container:

sh
docker compose -f compose.yaml --profile tools run --rm db-tools alembic -c postgres/alembic.ini -n raw revision --autogenerate -m "describe raw change"

Use the same pattern for ods, vault, mart, and app.

Optional Database Port Exposure ​

The stack does not expose PostgreSQL by default. If you need temporary desktop database-tool access, expose PgBouncer explicitly:

sh
make db-port-up

Then connect to host port 6432. Stop the temporary exposed endpoint with:

sh
make db-port-down

PgAdmin ​

Start the local database stack with:

sh
make pgadmin-up
make pgadmin-logs
make pgadmin-down

PgAdmin is available at http://127.0.0.1:5050.

The PgAdmin container preloads connections from .env values on startup:

  • EDP Raw
  • EDP ODS
  • EDP Vault
  • EDP Mart
  • EDP App
  • Airflow Metadata
  • Superset Metadata

The preloaded servers connect through PgBouncer at pgbouncer:6432. PgAdmin also generates a temporary pgpass file inside the container so the registered servers can use the same passwords supplied by .env.

Use raw for ingestion metadata and source-specific raw schemas such as edp_raw.ad. Use ods for current operational structures. Use vault for historical Data Vault structures. Use mart for reporting marts. Use app for EDP custom application state.

Review autogenerated migrations before applying them. Alembic handles the common table, column, index, constraint, and type changes, but PostgreSQL schema creation and specialized DDL may still need manual migration code.

Check Model Drift ​

sh
make db-check

This fails if Alembic detects database changes that are not represented by the SQLAlchemy models, or model changes that have not been captured in migrations.

Tooling Guidance ​

Alembic is the right default for PostgreSQL DDL that needs explicit promotion, review, and rollback across environments.

Use dbt beside Alembic once transformations become meaningful. dbt should own select-based models, tests, documentation, lineage, and refresh workflows. It should not be the only owner of foundational database DDL unless the platform is intentionally modeled as a dbt-only analytics project.

Consider alternatives only when the platform shape changes:

  • Sqitch is a good alternative for SQL-first teams that do not want Python in the migration path.
  • Flyway or Liquibase fit organizations that already standardize on JVM-style database change management.
  • SQLMesh can replace part of dbt for model planning and incremental analytical transformations, but it is not a simpler replacement for service DDL.