SPAR Database Restore Process

Database Restore Procedure (Using Backup File + New PVC)

This document describes how to safely restore the database using a selected backup file. The restore process creates a new PVC, loads the backup into a fresh database container, and switches the backend API back on after the data is restored.

Note: In this documentation, the dev deployment nr-spar-2218 is used as an example.
In test and prod environments, replace 2218 with the corresponding zone name (test or prod).

1. Log in to the Target Namespace

Ensure you are authenticated and switched to the correct OpenShift project/namespace.

2. Scale Down the Backend API

Stop the API temporarily to prevent traffic during the DB restore process.

oc scale deployment nr-spar-2218-backend --replicas=0

3. Rename the Current Database Deployment Config

Rename the active DB deployment config so the new DB deployment can take over cleanly. This preserves the original deployment for rollback or debugging.

./rename_deployment.sh nr-spar-2218-database nr-spar-2218-database-prev

The rename_deployment.sh script is as follows (created by Derek for FOM):

#!/bin/bash
#
# Usage:
#   ./rename_deployment.sh <source-deployment-name> [target-deployment-name]
#
# If [target-deployment-name] is not provided, defaults to <source-deployment-name>-prev
#
# This script renames an OpenShift deployment by exporting its manifest, updating the name,
# deleting the old deployment, and applying the new one.

# Strict mode: exit on error, unset vars, or failed pipes
set -euo pipefail

# Show usage from header if not enough arguments
if [[ $# -lt 1 ]]; then
  grep -v '^#!' "${0}" | awk '/^#/ { sub(/^# ?/, ""); print; next } NF==0 { exit }'
  exit 1
fi

SOURCE_DEPLOYMENT="${1}"
TARGET_DEPLOYMENT="${2:-${SOURCE_DEPLOYMENT}-prev}"
MANIFEST=$(mktemp "/tmp/${SOURCE_DEPLOYMENT}_$(date +%Y%m%d)_XXXXXX.json")
trap 'rm -f "${MANIFEST}"' EXIT

# Fail fast if the new deployment already exists
if oc get deployment "${TARGET_DEPLOYMENT}" &>/dev/null; then
  echo "Deployment '${TARGET_DEPLOYMENT}' already exists. Aborting to avoid overwrite."
  exit 2
fi

# Check if the old deployment exists
if ! oc get deployment "${SOURCE_DEPLOYMENT}" &>/dev/null; then
  echo "Deployment '${SOURCE_DEPLOYMENT}' not found."
  exit 0
fi

# Export, clean, and update deployment manifest
oc get deployment "${SOURCE_DEPLOYMENT}" -o json \
  | jq 'del(
      .metadata.uid,
      .metadata.resourceVersion,
      .metadata.selfLink,
      .metadata.creationTimestamp,
      .metadata.generation,
      .metadata.managedFields,
      .status
    )
    | .metadata.name = "'"${TARGET_DEPLOYMENT}"'"
    | .spec.selector.matchLabels.deployment = "'"${TARGET_DEPLOYMENT}"'"
    | .spec.template.metadata.labels.deployment = "'"${TARGET_DEPLOYMENT}"'"' \
  > "${MANIFEST}"

# Delete the old deployment and apply the new one
oc delete deployment "${SOURCE_DEPLOYMENT}"
oc apply -f "${MANIFEST}"

# Wait for the new deployment to become available
echo "Waiting for deployment '${TARGET_DEPLOYMENT}' to become available..."
if ! oc rollout status deployment/"${TARGET_DEPLOYMENT}" --timeout=120s; then
  echo "Error: Deployment '${TARGET_DEPLOYMENT}' did not become available in time."
  exit 3
fi

# Show matching deployments for confirmation
echo -e "\nMatching deployments after renaming:"
oc get deployments -o name | grep -iE "^deployment\.apps/(${SOURCE_DEPLOYMENT}|${TARGET_DEPLOYMENT})$"

4. Deploy a Fresh Database Instance

Create a brand-new database deployment config and PVC from our template common/openshift.database.yml. Update the PVC name in the template to avoid conflicts with the existing database PVC:

Run the following script to create a new database deployment config with a new PVC:

oc process -f openshift.database.yml \
  -p ZONE=2218 \
  -p DB_PVC_SIZE=192Mi \
  -p MEMORY_REQUEST=100Mi | oc apply -f -

Note: For test and prod environments, replace ZONE=2218 with ZONE=test or ZONE=prod. This will create a new empty database on a fresh PVC, which ensures a safe restore.

5. Create the Backup Helper Pod

Start a temporary pod with access to backup files:

oc apply -f db-bk-temp.yml

db-bk-temp.yml:

apiVersion: v1
kind: Pod
metadata:
  name: db-bk-temp
spec:
  containers:
    - name: db-bk-temp
      image: busybox
      command: ["sleep", "3600"]   # keeps the pod alive for 1 hour
      volumeMounts:
        - mountPath: /backups
          name: backup-volume
  volumes:
    - name: backup-volume
      persistentVolumeClaim:
        claimName: nr-spar-2218-database-backup
  restartPolicy: Never

Confirm the pod running: oc get pods | grep db-bk-temp

6. Locate the Backup File

Enter the temporary pod and inspect the available backups:

oc exec -it db-bk-temp -- sh
ls -lh /backups
ls -lh /backups/daily
cd 2025-11-26
exit

Assume the backup file you want is: nr-spar-2218-database-nr-spar_2025-11-26_00-25-17.sql.gz

7. Copy the Backup File Into the New Database Pod

Replace the pod name nr-spar-2218-database-798c4f56bf-5s6km with your new DB pod name:

oc exec db-bk-temp -- cat /backups/daily/2025-11-26/nr-spar-2218-database-nr-spar_2025-11-26_00-25-17.sql.gz \
| oc exec -i nr-spar-2218-database-798c4f56bf-5s6km -- sh -c "cat > /tmp/nr-spar-backup-251126.sql.gz"

This streams the backup directly from the backup pod into the DB pod.

8. Restore the Database

Enter the new database pod:

oc exec -it nr-spar-2218-database-798c4f56bf-5s6km -- bash

Then:

cd /tmp
ls -lh

# decompress backup
gunzip nr-spar-backup-251126.sql.gz

# confirm SQL file exists
ls -lh

# restore
psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -f nr-spar-backup-251126.sql

# verify spar schema tables
psql -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c "\dt spar.*"

Since this is a freshly mounted PVC, restoring into an empty database is safe.

9. Scale the Backend API Back Up

Once the database restore is complete, bring the API back online:

oc scale deployment nr-spar-2218-backend --replicas=3

Note: Always double-check that the PVC name is unique for new deployments, especially in test/prod environments, to avoid overwriting existing databases.

10. Clean Up the Backup Helper Pod

After the database is restored and verified, delete the temporary backup pod:

oc delete pod db-bk-temp