Checkpoint Lab — Database Backup, Restore, Disaster Recovery, and Data Protection

1. Checkpoint scenario

You are the recovery operator for a disposable SonarQube Community Build instance. The instance contains one synthetic project and one completed baseline analysis. You must prove that a database backup can reconstruct that state on an isolated target, show exactly which later analysis is outside the recovery point, and then prove the restored server can process a fresh analysis.

The checkpoint is successful only when the evidence packet explains what was restored, from which point, under which version/configuration, how search state was reconstructed, what data was intentionally lost after the backup point, and why no production system was touched.

2. Exact lab assumptions

3. Create the recovery manifest before taking the backup

# evidence/recovery-manifest.yml
exercise: sq28-dr-lab
production_data_touched: false
source:
  sonarqube_version: 26.9.0.129388
  edition: Community Build
  image: sonarqube:26.9.0.129388-community
  url: http://localhost:9100
  project_key: sq28-dr-lab
database:
  engine: PostgreSQL
  version: 17.11
  image: postgres:17.11-alpine
  source_database: sonar
  restore_database: sonar_restore
  backup_method: pg_dump custom format
configuration:
  compose_file: compose.yml
  secrets_recorded_in_manifest: false
  secret_owner: local disposable environment only
plugins:
  third_party: []
search:
  authority: rebuildable index
  restore_strategy: fresh data volume / reindex from database
objectives:
  rpo: backup snapshot timestamp
  rto: measure through validation CE SUCCESS
rollback:
  preserve_source_until_restore_accepted: true

Add image digests, Compose hash, scanner version, Git baseline SHA, backup destination, retention owner, and current date/time to your real packet.

4. Predictions required before execution

Write these predictions in evidence/predictions.md before the backup:

1. Prediction A: the custom-format dump will include the completed baseline analysis but not any analysis committed after the dump starts/completes.
2. Prediction B: the isolated restore target will initially show project sq28-dr-lab at the backup recovery point and will not show the later version 2.0.0 marker.
3. Prediction C: a fresh restore search-data volume will cause SonarQube to reconstruct search indexes from the restored database rather than needing the source sonarqube_data volume.
4. Prediction D: the wrong JDBC database name will fail before project validation and will be repairable by changing only the restore server configuration.
5. Prediction E: after creating a restore-only analysis token, the current Git revision will scan successfully and produce a new ceTaskId whose CE state reaches SUCCESS.

5. Execute the recovery drill

Use the complete Compose and source workflow from Lesson 2. The checkpoint must preserve these boundaries:

Do not substitute a pre-existing local project or database. Do not reuse the same database container for source and restore. Do not start sonar-restore until the target database has been restored.

For an in-place restore, current SonarQube guidance is to stop the server, restore the database, clear the Sonar-owned data/es8 index contents, and restart so search indexes are rebuilt. This checkpoint uses a fresh restore data volume, which is the isolated-lab equivalent: no stale search index is copied from the source instance.

6. Integrity and database verification gates

sha256sum -c evidence/backup/sonarqube.dump.sha256

docker run --rm -i postgres:17.11-alpine \
  pg_restore --list \
  < evidence/backup/sonarqube.dump \
  > evidence/backup/archive-list-recheck.txt

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

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

If any integrity or restore command fails, stop. Preserve output. Do not continue to SonarQube startup and then infer that the archive was “probably fine.”

7. Prove the recovery point before creating new state

After the restore server reaches UP and reindex is underway/completed enough for the project view, inspect the project's Activity history. Record:

• baseline project version and analysis date;
• absence of the version 2.0.0 post-backup marker;
• restored Quality Gate/profile/project visibility;
• restored user/project metadata required for the lab;
• reindex status and any temporarily unavailable search features.

This is the strongest lab evidence that the backup represents a specific time. Only after recording it should you create a restore-only token and run the validation analysis.

8. Post-restore scanner and Compute Engine validation

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
CE_TASK_ID="$(sed -n 's/^ceTaskId=//p' .scannerwork/report-task.txt)"
printf '%s\n' "$CE_TASK_ID" | tee evidence/validation/ceTaskId.txt

# Poll in a loop in the lab until terminal; preserve every response if desired.
curl -fsS -H "Authorization: Bearer $SONAR_TOKEN" \
  "$SONAR_HOST_URL/api/ce/task?id=$CE_TASK_ID" \
  | tee evidence/validation/ce-task-final.json

Verification is not complete until CE is terminal, the analysis appears in Activity, expected measures/issues can be queried, and the Quality Gate state is recorded independently. Scanner process success, report upload, CE success, analysis completion, Quality Gate, CI status, and provider decoration remain separate states.

9. Required evidence packet

10. Verification checklist

• ☐ Source and restore PostgreSQL instances are distinct.
• ☐ Exact SonarQube/PostgreSQL/scanner versions are recorded.
• ☐ Backup hash verifies and archive listing is readable.
• ☐ Source remains intact until restore acceptance.
• ☐ Restore target uses fresh/cleared search state.
• ☐ Post-backup marker is absent before the validation scan.
• ☐ Wrong-JDBC first-failure evidence is preserved.
• ☐ Corrected restore reaches system UP and coherent project history.
• ☐ Fresh validation analysis produces report-task.txt and ceTaskId.
• ☐ Compute Engine reaches terminal success and Quality Gate is recorded separately.
• ☐ No real credential, proprietary source, production IdP/provider, or uncontrolled database was used.
• ☐ Recovery packet includes RPO/RTO observations and limitations.

11. Guarded cleanup and rollback

docker compose -p sq28dr ps -a
docker volume ls --format '{{.Name}}' | grep '^sq28dr_' || true

# First stop containers without deleting volumes.
docker compose -p sq28dr down

# After explicit verification that no unrelated resources match the project:
docker compose -p sq28dr down -v --remove-orphans

unset SONAR_TOKEN SONAR_TOKEN_SOURCE SONAR_TOKEN_RESTORE SONAR_HOST_URL

Revoke any disposable SonarQube tokens before deleting the lab. If a cleanup command lists anything that is not clearly owned by this exercise, stop and investigate rather than broadening the delete command.

12. What Chapter 28 adds to the production operating model

You now have a recovery contract based on authoritative database state, explicit configuration/plugin/secret ownership, integrity-protected backups, isolated restoration, search-index reconstruction, source preservation, ceTaskId-based post-restore validation, and measured RPO/RTO evidence. That is materially stronger than “our Docker volume exists” or “the backup job was green.”

Chapter 29 continues with Release Cycle, LTA Strategy, Upgrade Paths, and Migration Planning. The separation is intentional: Chapter 28 proves you can reconstruct a known state; Chapter 29 teaches how to move that known state safely to another supported release.

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.