Database Backup, Restore, Disaster Recovery, and Data Protection: Guided Hands-On Workflow

1. Lab contract: two isolated stacks, one deliberate recovery point

2. Resource and credential preflight

mkdir -p sq28-dr-lab/{src,evidence/{baseline,backup,post-backup,restore,validation}}
cd sq28-dr-lab

docker --version
docker compose version
sonar-scanner --version
git --version

# Ports must be unused before the lab.
# Linux/macOS example; on Windows use Get-NetTCPConnection or netstat.
( ! command -v ss >/dev/null ) || ( ! ss -ltn | grep -E ':(9100|9200) ' )

df -h . || true
printf '%s\n' 'No production URL, DB host, token, or repository is allowed in this lab.'

Use a disposable local admin password when SonarQube first starts. Later, create project-analysis tokens through the UI and keep them only in shell environment variables. Do not add any token or database password to Git.

3. Define the source and restore stacks

The restore target is separate at both the database and SonarQube layers. The only shared object is the backup archive written to the host evidence directory.

# compose.yml
services:
  pg-source:
    image: postgres:17.11-alpine
    container_name: sq28-pg-source
    environment:
      POSTGRES_USER: sonar
      POSTGRES_PASSWORD: only-for-disposable-lab
      POSTGRES_DB: sonar
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U sonar -d sonar"]
      interval: 5s
      timeout: 3s
      retries: 30
    volumes:
      - sq28_pg_source:/var/lib/postgresql/data

  sonar-source:
    image: sonarqube:26.9.0.129388-community
    container_name: sq28-sonar-source
    depends_on:
      pg-source:
        condition: service_healthy
    environment:
      SONAR_JDBC_URL: jdbc:postgresql://pg-source:5432/sonar
      SONAR_JDBC_USERNAME: sonar
      SONAR_JDBC_PASSWORD: only-for-disposable-lab
    ports:
      - "9100:9000"
    volumes:
      - sq28_sonar_source_data:/opt/sonarqube/data
      - sq28_sonar_source_logs:/opt/sonarqube/logs
      - sq28_sonar_source_extensions:/opt/sonarqube/extensions

  pg-restore:
    image: postgres:17.11-alpine
    container_name: sq28-pg-restore
    environment:
      POSTGRES_USER: sonar
      POSTGRES_PASSWORD: only-for-disposable-lab
      POSTGRES_DB: sonar_restore
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U sonar -d sonar_restore"]
      interval: 5s
      timeout: 3s
      retries: 30
    volumes:
      - sq28_pg_restore:/var/lib/postgresql/data

  sonar-restore:
    image: sonarqube:26.9.0.129388-community
    container_name: sq28-sonar-restore
    depends_on:
      pg-restore:
        condition: service_healthy
    environment:
      SONAR_JDBC_URL: jdbc:postgresql://pg-restore:5432/sonar_restore
      SONAR_JDBC_USERNAME: sonar
      SONAR_JDBC_PASSWORD: only-for-disposable-lab
    ports:
      - "9200:9000"
    volumes:
      - sq28_sonar_restore_data:/opt/sonarqube/data
      - sq28_sonar_restore_logs:/opt/sonarqube/logs
      - sq28_sonar_restore_extensions:/opt/sonarqube/extensions

volumes:
  sq28_pg_source: {}
  sq28_sonar_source_data: {}
  sq28_sonar_source_logs: {}
  sq28_sonar_source_extensions: {}
  sq28_pg_restore: {}
  sq28_sonar_restore_data: {}
  sq28_sonar_restore_logs: {}
  sq28_sonar_restore_extensions: {}

docker compose -p sq28dr config > evidence/compose-rendered.yml
docker compose -p sq28dr up -d pg-source sonar-source

# Wait until the source server reports UP.
until curl -fsS http://localhost:9100/api/system/status | grep -q '"status":"UP"'; do sleep 5; done
curl -fsS http://localhost:9100/api/system/status | tee evidence/source-system-status.json

4. Create one synthetic project and baseline analysis

cat > src/app.py <<'PY'
def normalize(name: str) -> str:
    return " ".join(name.strip().split()).lower()
PY

cat > sonar-project.properties <<'EOF'
sonar.projectKey=sq28-dr-lab
sonar.projectName=SQ Chapter 28 DR Lab
sonar.sources=src
sonar.sourceEncoding=UTF-8
EOF

cat > .gitignore <<'EOF'
.scannerwork/
evidence/
EOF

git init
git config user.email learner@example.invalid
git config user.name "SonarQube Learner"
git add src sonar-project.properties .gitignore
git commit -m "chapter28 baseline"
git rev-parse HEAD | tee evidence/baseline/revision.txt

Open http://localhost:9100, change the default admin password, create project sq28-dr-lab, and create a disposable project analysis token. Export it as SONAR_TOKEN_SOURCE without printing it.

export SONAR_HOST_URL=http://localhost:9100
export SONAR_TOKEN="$SONAR_TOKEN_SOURCE"
sonar-scanner -Dsonar.projectVersion=1.0.0 2>&1 | tee evidence/baseline/scanner.log
cp .scannerwork/report-task.txt evidence/baseline/report-task.txt
BASE_TASK="$(sed -n 's/^ceTaskId=//p' .scannerwork/report-task.txt)"
echo "$BASE_TASK" > evidence/baseline/ceTaskId.txt
curl -fsS -H "Authorization: Bearer $SONAR_TOKEN" \
  "$SONAR_HOST_URL/api/ce/task?id=$BASE_TASK" > evidence/baseline/ce-task.json

Poll until the task is terminal before taking the backup. This makes the baseline analysis part of the recovery point rather than an in-flight ambiguous task.

5. Take and verify a hot PostgreSQL backup

Keep sonar-source running. Current SonarQube guidance supports hot database backups, and PostgreSQL pg_dump gives a consistent logical snapshot.

date -u +%FT%TZ | tee evidence/backup/started-at.txt

docker compose -p sq28dr exec -T pg-source \
  pg_dump -U sonar -d sonar --format=custom \
  > evidence/backup/sonarqube.dump

date -u +%FT%TZ | tee evidence/backup/completed-at.txt
sha256sum evidence/backup/sonarqube.dump | tee evidence/backup/sonarqube.dump.sha256

# Verify the archive is readable before calling it a backup candidate.
docker run --rm -i postgres:17.11-alpine \
  pg_restore --list \
  < evidence/backup/sonarqube.dump \
  > evidence/backup/archive-list.txt

test -s evidence/backup/archive-list.txt

Copy the dump and checksum to storage outside the source failure domain in a real design. The local lab keeps the file in evidence/ only so the entire exercise remains disposable.

6. Create an observable post-backup marker

Now change source after the backup. The restore target should not contain this second analysis until you deliberately run a validation scan against it.

cat >> src/app.py <<'PY'

def display_label(name: str) -> str:
    return normalize(name).title()
PY

git add src/app.py
git commit -m "post-backup marker"
git rev-parse HEAD | tee evidence/post-backup/revision.txt

export SONAR_HOST_URL=http://localhost:9100
export SONAR_TOKEN="$SONAR_TOKEN_SOURCE"
sonar-scanner -Dsonar.projectVersion=2.0.0 2>&1 | tee evidence/post-backup/scanner.log
cp .scannerwork/report-task.txt evidence/post-backup/report-task.txt
POST_TASK="$(sed -n 's/^ceTaskId=//p' .scannerwork/report-task.txt)"
echo "$POST_TASK" > evidence/post-backup/ceTaskId.txt

Confirm version 2.0.0 appears on the source server. This is intentional data that is newer than the recovery point.

7. Restore into the isolated PostgreSQL target

docker compose -p sq28dr up -d pg-restore
until docker compose -p sq28dr exec -T pg-restore pg_isready -U sonar -d sonar_restore >/dev/null; do sleep 2; done

# Re-check the dump immediately before restore.
sha256sum -c evidence/backup/sonarqube.dump.sha256

# Restore into the empty isolated database. No source DB is modified.
docker compose -p sq28dr exec -T pg-restore \
  pg_restore -U sonar -d sonar_restore \
  --no-owner --no-privileges --exit-on-error \
  < evidence/backup/sonarqube.dump \
  2>&1 | tee evidence/restore/pg-restore.log

docker compose -p sq28dr exec -T pg-restore \
  psql -U sonar -d sonar_restore -Atc 'select current_database(), version();' \
  | tee evidence/restore/database-identity.txt

At this point the source remains running and untouched. The target database is isolated and contains only state up to the backup snapshot.

8. Start a fresh restore server and let search state rebuild

The restore SonarQube service uses a fresh sq28_sonar_restore_data volume, so it begins without old Elasticsearch indexes. This is the Docker equivalent of the documented restore rule: restored DB plus cleared/fresh data/es8.

docker compose -p sq28dr up -d sonar-restore

docker compose -p sq28dr logs --no-color sonar-restore \
  | tee evidence/restore/sonar-startup.log

until curl -fsS http://localhost:9200/api/system/status | grep -q '"status":"UP"'; do sleep 5; done
curl -fsS http://localhost:9200/api/system/status | tee evidence/restore/system-status.json

Log in with the disposable local administrator state that existed before the backup. Verify that sq28-dr-lab exists and that its Activity history contains the baseline recovery point but not the post-backup version 2.0.0 analysis. That absence is expected and demonstrates the lab's RPO boundary.

9. Run a fresh post-restore analysis and preserve ceTaskId

After the historical state is verified, create a new restore-only project-analysis token in the restored project. Do not reuse this token anywhere else.

export SONAR_HOST_URL=http://localhost:9200
export SONAR_TOKEN="$SONAR_TOKEN_RESTORE"

git rev-parse HEAD | tee evidence/validation/revision.txt
sonar-scanner -Dsonar.projectVersion=3.0.0-dr-validated \
  2>&1 | tee evidence/validation/scanner.log
cp .scannerwork/report-task.txt evidence/validation/report-task.txt
VALIDATION_TASK="$(sed -n 's/^ceTaskId=//p' .scannerwork/report-task.txt)"
echo "$VALIDATION_TASK" | tee evidence/validation/ceTaskId.txt

curl -fsS -H "Authorization: Bearer $SONAR_TOKEN" \
  "$SONAR_HOST_URL/api/ce/task?id=$VALIDATION_TASK" \
  | tee evidence/validation/ce-task.json

Wait for SUCCESS, then verify the current Git revision, project measures/issues, and Quality Gate. A successful scanner upload is not enough; the restored server must process the task and expose coherent project state.

10. Challenge: identify the owning recovery layer

The database archive checksum verifies and pg_restore succeeds, but the restore SonarQube container cannot start because its JDBC URL points to sonar_restore_typo. Should you retake the backup?

No. The backup and database restore evidence are already sound. Preserve the SonarQube startup error, prove that PostgreSQL contains sonar_restore, correct only the restore server's JDBC target, and restart that disposable server. This is a deployment-configuration failure, not evidence of database-backup corruption.

Knowledge check

Official references and version notes

• SonarQube Community Build — Backup and restore — use the database vendor's backup tooling; hot database backups are supported; restore the database and rebuild Elasticsearch indexes.
• SonarQube Community Build — Reindexing — startup after restore rebuilds Elasticsearch indexes; project availability and background reindex behavior are documented.
• SonarQube Community Build — Installing database — current supported database engines and versions; PostgreSQL 14–18 is supported starting with Community Build 26.2.
• Configuration methods — startup/system properties can live in environment variables, command line, Helm configuration, or sonar.properties, and are not all database state.
• Performing an update — use a fresh distribution/config review and install compatible third-party plugins rather than blindly copying an old installation.
• Sensitive settings — secret-key material and encrypted configuration require separate protected handling.
• PostgreSQL — pg_dump — produces a consistent logical backup while the database can remain in use.
• PostgreSQL — pg_restore — restores non-plain-text archives created by pg_dump.
• Official SonarQube Docker tags — exact Community Build image identity used in the lab.
• Official PostgreSQL Docker tags — exact PostgreSQL image identity used in the lab.