1  #!/bin/sh
 2  # cutover-to-pg.sh — Switch 333Method from SQLite to PostgreSQL
 3  #
 4  # Run from host terminal:
 5  #   cd ~/code/333Method && sh scripts/cutover-to-pg.sh
 6  #
 7  # What this does:
 8  # 1. Comments out DATABASE_PATH in .env
 9  # 2. Adds DATABASE_URL and PG_SEARCH_PATH
10  # 3. Restarts the pipeline and cron services
11  #
12  # Rollback:
13  #   git checkout pre-pg-migration  # restore SQLite code
14  #   Uncomment DATABASE_PATH, remove DATABASE_URL in .env
15  #   systemctl --user restart 333method-pipeline.service
16  
17  set -eu
18  
19  ENV_FILE="$HOME/code/333Method/.env"
20  ENV_2STEP="$HOME/code/2Step/.env"
21  
22  echo "=== PostgreSQL Cutover ==="
23  echo ""
24  
25  # 1. Update 333Method .env
26  echo "[1/4] Updating 333Method .env..."
27  if grep -q "^DATABASE_URL=" "$ENV_FILE" 2>/dev/null; then
28    echo "  DATABASE_URL already set, skipping"
29  else
30    # Comment out old SQLite path
31    sed -i 's|^DATABASE_PATH=|# DATABASE_PATH=|' "$ENV_FILE"
32    sed -i 's|^OPS_DB_PATH=|# OPS_DB_PATH=|' "$ENV_FILE" 2>/dev/null || true
33    sed -i 's|^TEL_DB_PATH=|# TEL_DB_PATH=|' "$ENV_FILE" 2>/dev/null || true
34    sed -i 's|^MESSAGES_DB_PATH=|# MESSAGES_DB_PATH=|' "$ENV_FILE" 2>/dev/null || true
35  
36    # Add PostgreSQL connection (peer auth via Unix socket)
37    cat >> "$ENV_FILE" << 'PGEOF'
38  
39  # PostgreSQL (DR-104: migrated from SQLite 2026-03-28)
40  DATABASE_URL=postgresql:///mmo
41  PG_SEARCH_PATH=m333, ops, tel, msgs
42  PG_POOL_MAX=10
43  PGEOF
44    echo "  Added DATABASE_URL, PG_SEARCH_PATH, PG_POOL_MAX"
45  fi
46  
47  # 2. Update 2Step .env (if exists)
48  if [ -f "$ENV_2STEP" ]; then
49    echo "[2/4] Updating 2Step .env..."
50    if grep -q "^DATABASE_URL=" "$ENV_2STEP" 2>/dev/null; then
51      echo "  DATABASE_URL already set, skipping"
52    else
53      sed -i 's|^DATABASE_PATH=|# DATABASE_PATH=|' "$ENV_2STEP" 2>/dev/null || true
54      sed -i 's|^MESSAGES_DB_PATH=|# MESSAGES_DB_PATH=|' "$ENV_2STEP" 2>/dev/null || true
55      cat >> "$ENV_2STEP" << 'PGEOF'
56  
57  # PostgreSQL (DR-104: migrated from SQLite 2026-03-28)
58  DATABASE_URL=postgresql:///mmo
59  PG_SEARCH_PATH=twostep, msgs
60  PG_POOL_MAX=5
61  PGEOF
62      echo "  Added DATABASE_URL for 2Step"
63    fi
64  else
65    echo "[2/4] No 2Step .env found, skipping"
66  fi
67  
68  # 3. Re-enable and restart pipeline
69  echo "[3/4] Restarting pipeline services..."
70  systemctl --user restart 333method-pipeline.service 2>/dev/null && echo "  Pipeline restarted" || echo "  Pipeline service not found (enable in services.nix first)"
71  systemctl --user start 333method-orchestrator.timer 2>/dev/null && echo "  Orchestrator timer started" || echo "  Orchestrator timer not found"
72  
73  # 4. Verify
74  echo "[4/4] Verifying PostgreSQL connection..."
75  if command -v psql >/dev/null 2>&1; then
76    SITES=$(psql -t -c "SELECT count(*) FROM m333.sites" mmo 2>/dev/null | tr -d ' ')
77    echo "  PostgreSQL connected: $SITES sites in m333.sites"
78  else
79    echo "  psql not found — verify manually: psql -d mmo -c 'SELECT count(*) FROM m333.sites'"
80  fi
81  
82  echo ""
83  echo "=== Cutover complete ==="
84  echo "Monitor: tail -f ~/code/333Method/logs/pipeline-$(date +%Y-%m-%d).log"
85  echo "Rollback: git checkout pre-pg-migration && restore .env && restart services"