Data Platform

公司完整資料架構，包含 PostgreSQL OLTP/OLAP、原生 CDC（Logical Replication）和 dbt Kimball Star Schema。

架構概覽

┌─────────────────────────────────────────────────────────────┐
│                      Docker Compose                         │
│                                                             │
│  PostgreSQL OLTP (erp)  ──[Logical Replication]──▶  PostgreSQL OLAP (dwh) │
│      Publisher                                     Subscriber              │
│   erp.customers                                  erp.customers             │
│   erp.products            CDC: pgoutput           erp.products             │
│   erp.orders          (no Kafka, no Debezium)     erp.orders               │
│   erp.order_items                                 erp.order_items          │
│                                                        │                   │
│                                                    dbt (Kimball)            │
│                                              staging → marts (dim/fct)     │
└─────────────────────────────────────────────────────────────┘

外部服務 (不在 Compose 內，僅提供整合設定):
  Airflow  ── 排程 dbt runs
  GitLab   ── CI/CD pipeline

服務清單

服務	Image	Port	說明
postgres-oltp	postgres:16-alpine	5432	ERP 來源資料庫 (OLTP, Publisher)
postgres-olap	postgres:16-alpine	5433	資料倉儲 (OLAP, Subscriber + dbt target)

目錄結構

data-platform/
├── docker-compose.yml
├── .env.example
├── infra/
│   ├── postgres-oltp/
│   │   ├── postgresql.conf          # wal_level=logical
│   │   └── init/
│   │       ├── 01_extensions.sql
│   │       ├── 02_schemas.sql       # ERP tables + PUBLICATION
│   │       └── 03_seed_data.sql
│   ├── postgres-olap/
│   │   ├── postgresql.conf          # OLAP 調優
│   │   └── init/
│   │       ├── 01_schemas.sql       # schemas + dbt user
│   │       └── 02_erp_tables.sql    # erp.* tables (replication target)
│   └── postgres/
│       └── setup-replication.sh    # 建立 SUBSCRIPTION
├── dbt/
│   ├── dbt_project.yml / profiles.yml / packages.yml
│   ├── models/
│   │   ├── sources.yml              # source: erp schema on OLAP
│   │   ├── staging/                 # stg_erp__*  (view)
│   │   └── marts/
│   │       ├── dimensions/          # dim_customer, dim_product, dim_date
│   │       └── facts/               # fct_orders, fct_order_items
│   └── macros/
│       └── generate_schema_name.sql
└── integration/
    ├── airflow/example_dag.py
    └── gitlab/.gitlab-ci.yml

---

快速開始

前置條件

• Docker Desktop ≥ 4.x（核心服務需 ~2GB RAM）
• psql client（或使用 docker exec）
• Python ≥ 3.11 + pip（執行 dbt 時需要）

---

步驟 1：環境設定

cp .env.example .env
# 可視需要修改 .env 中的密碼

---

步驟 2：啟動核心服務

docker-compose up -d

確認服務健康：

docker-compose ps

等待兩個 PostgreSQL 都顯示 (healthy) 再繼續。

---

步驟 3：建立 PostgreSQL Logical Replication

先載入 .env（Windows PowerShell）：

Get-Content .env | ForEach-Object {
  if ($_ -match '^([^#][^=]*)=(.*)$') {
    [System.Environment]::SetEnvironmentVariable($matches[1], $matches[2], 'Process')
  }
}

執行 replication setup script（需要 psql 在 PATH 中）：

bash infra/postgres/setup-replication.sh

驗證 replication 狀態：

# 在 OLAP 確認 subscription 已建立
docker exec data-platform-postgres-olap \
  psql -U postgres -d dwh -c "SELECT subname, subenabled FROM pg_subscription;"

# 在 OLTP 確認 replication slot 已建立
docker exec data-platform-postgres-oltp \
  psql -U postgres -d erp -c "SELECT slot_name, active FROM pg_replication_slots;"

# 驗證資料已複製到 OLAP
docker exec data-platform-postgres-olap \
  psql -U postgres -d dwh -c "SELECT COUNT(*) FROM erp.customers;"

---

步驟 4：測試 CDC（即時同步驗證）

在 OLTP insert 一筆資料，觀察是否同步到 OLAP：

# 在 OLTP 新增一個客戶
docker exec data-platform-postgres-oltp psql -U postgres -d erp -c "
INSERT INTO erp.customers (customer_code, company_name, country)
VALUES ('CUST-TEST', 'Test CDC Company', 'Taiwan');
"

# 稍等 1~2 秒後，在 OLAP 查詢
docker exec data-platform-postgres-olap psql -U postgres -d dwh -c "
SELECT customer_code, company_name FROM erp.customers WHERE customer_code = 'CUST-TEST';
"

---

步驟 5：安裝 dbt

cd dbt

pip install dbt-postgres

# 安裝 dbt packages
dbt deps

---

步驟 6：執行 dbt

# 執行所有模型 (staging → dimensions → facts)
dbt run

# 執行所有測試
dbt test

# 生成並查看文件
dbt docs generate
dbt docs serve
# 開啟瀏覽器: http://localhost:8080

---

CDC 架構說明

使用 PostgreSQL 原生 Logical Replication（不需要 Kafka / Debezium）：

OLTP (erp DB)                          OLAP (dwh DB)
─────────────                          ─────────────
wal_level = logical
                                       CREATE SUBSCRIPTION erp_subscription
CREATE PUBLICATION erp_publication  ──▶  CONNECTION 'host=postgres-oltp ...'
  FOR ALL TABLES;                        PUBLICATION erp_publication;

---

dbt 資料模型 (Kimball Star Schema)

              dim_date
                │
dim_customer ──fct_orders──── dim_product
                │
         fct_order_items ──── dim_product

---

常用指令

# 查看所有服務狀態
docker-compose ps

# 連線到 OLTP
docker exec -it data-platform-postgres-oltp psql -U postgres -d erp

# 連線到 OLAP
docker exec -it data-platform-postgres-olap psql -U postgres -d dwh

# 停止所有服務（保留資料）
docker-compose down

# 完全清除（包含資料）
docker-compose down -v