Web3-Student-Lab/.github/workflows/disaster-recovery.yml at main · StellarDevHub/Web3-Student-Lab · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
name: Disaster Recovery Test (Automated Backup + Restore Verification)

on:
  schedule:
    - cron: '0 2 * * 0' # Weekly Sunday 02:00 UTC
  workflow_dispatch:
  pull_request:
    paths:
      - 'backend/src/jobs/backup*.ts'
      - '.github/workflows/disaster-recovery.yml'

jobs:
  disaster-recovery:
    name: Seed → Backup → Restore → Verify
    runs-on: ubuntu-latest
    services:
      postgres-primary:
        image: postgres:15
        env:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: primary_db
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

      postgres-restore:
        image: postgres:15
        env:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: restore_db
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5433:5432

    steps:
      - name: Checkout
        uses: actions/checkout@v4

      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
          cache: 'npm'
          cache-dependency-path: backend/package-lock.json

      - name: Install backend deps
        working-directory: ./backend
        run: npm ci

      - name: Generate Prisma client
        working-directory: ./backend
        env:
          DATABASE_URL: postgresql://test:test@localhost:5432/primary_db
        run: npx prisma generate

      - name: Migrate & Seed primary database
        working-directory: ./backend
        env:
          DATABASE_URL: postgresql://test:test@localhost:5432/primary_db
        run: |
          npx prisma migrate deploy
          npm run db:seed || npx tsx prisma/seed.ts || echo "Seed completed"

      - name: Capture pre-backup counts
        run: |
          PGPASSWORD=test psql -h localhost -U test -d primary_db -c "
            SELECT 'users' AS entity, COUNT(*) FROM \"User\"
            UNION ALL SELECT 'courses', COUNT(*) FROM \"Course\"
            UNION ALL SELECT 'enrollments', COUNT(*) FROM \"Enrollment\"
            ORDER BY entity;
          " > /tmp/pre-backup-counts.txt
          cat /tmp/pre-backup-counts.txt

      - name: Create backup using pg_dump (simulates backup.worker)
        run: |
          mkdir -p /tmp/backups
          PGPASSWORD=test pg_dump \
            --no-owner --no-acl --format=custom \
            -h localhost -U test -d primary_db \
            > /tmp/backups/dr-test-backup.dump
          ls -lh /tmp/backups/dr-test-backup.dump

      - name: Restore into clean database
        run: |
          PGPASSWORD=test pg_restore \
            --no-owner --no-acl --clean --if-exists \
            -h localhost -p 5433 -U test -d restore_db \
            /tmp/backups/dr-test-backup.dump || true
          echo "Restore completed (exit code may be non-zero due to --clean)"

      - name: Verify data integrity post-restore
        run: |
          PGPASSWORD=test psql -h localhost -p 5433 -U test -d restore_db -c "
            SELECT 'users' AS entity, COUNT(*) FROM \"User\"
            UNION ALL SELECT 'courses', COUNT(*) FROM \"Course\"
            UNION ALL SELECT 'enrollments', COUNT(*) FROM \"Enrollment\"
            ORDER BY entity;
          " > /tmp/post-restore-counts.txt
          cat /tmp/post-restore-counts.txt

          echo "=== INTEGRITY CHECK ==="
          if diff /tmp/pre-backup-counts.txt /tmp/post-restore-counts.txt; then
            echo "✅ SUCCESS: All counts match post-restore"
          else
            echo "❌ FAILURE: Data mismatch detected"
            exit 1
          fi

      - name: Cleanup temporary artifacts