Database Backup Restore

Database Backups

• The database backups are performed by a cronjob which uses the bcgov backup-container

# this is used to create a cronjob that will run a backup of the database
apiVersion: batch/v1
kind: CronJob
metadata:
  name: backup-cron
  annotations:
    openshift.io/display-name: Cron job templates for backups.
    description: CronJob to backup gdx-agreements-tracker database, https://developer.gov.bc.ca/Backup-Container.
    tags: backups,pmo,postgres,gdx-agreements-tracker
  labels:
    template: "postgres-cron-cronjob"
    cronjob: "postgres-cron-backup"
spec:
  schedule: "0 9 * * *" # 1 am PST / 0900 UTC
  concurrencyPolicy: Forbid
  successfulJobHistoryLimit: 3
  failedJobHistoryLimit: 2
  jobTemplate:
    metadata:
    labels:
      template: "postgres-cron-cronjob"
      cronjob: "postgres-cron-backup"
    spec:
      backoffLimit: 0
      template:
        metadata:
          labels:
            template: "postgres-cron-cronjob"
            cronjob: "postgres-cron-backup"
        spec:
          containers:
            - name: postgres-backup-runner
              image: "bcgovgdx/gdx-agreements-tracker-backup-container"
              command:
                - "/bin/bash"
                - "-c"
                - "/backup.sh -1"
              volumeMounts:
                - name: backup-db
                  mountPath: /backups/
                - name: backup-verification
                  mountPath: /var/lib/pgsql/data
                - name: backup-config
                  mountPath: /backup.conf
                  subPath: backup.conf
              env:
                - name: BACKUP_STRATEGY
                  value: rolling
                - name: BACKUP_DIR
                  valueFrom:
                    configMapKeyRef:
                      name: backup-config
                      key: BACKUP_DIR
                - name: MONTHLY_BACKUPS
                  value: "3"
                - name: DATABASE_USER
                  valueFrom:
                    configMapKeyRef:
                      name: config
                      key: POSTGRES_USER
                - name: DATABASE_PASSWORD
                  valueFrom:
                    secretKeyRef:
                      name: secrets
                      key: POSTGRES_PASSWORD
          volumes:
            - name: backup-db
              persistentVolumeClaim:
                claimName: backup-db
            - name: backup-verification
              persistentVolumeClaim:
                claimName: backup-verification
            - name: backup-config
              configMap:
                name: backup-config
                items:
                  - key: backup.conf
                    path: backup.conf
                    # make sure all these configKeyRefs are correct
          dnsPolicy: "ClusterFirst"
          successfulJobHistoryLimit: 5
          failedJobHistoryLimit: 2
          restartPolicy: "Never"
          terminationGracePeriodSeconds: 30
          activeDeadlineSeconds: 1600
          serviceAccountName: "default"
          serviceAccount: "default"

Database Restore

1. Login to Openshift:

   • Go to OpenShift.
   • Log in to your Openshift environment.
   • Navigate to the project where you are wanting to perform the restore

2. Select the Backup Deployment:

   • Navigate to either "Deployments" > "pmo-backup-restore"

3. Start the Backup Pod:

   • If the "pmo-backup-restore" pod is not running, start it, but increasing the pod deployments from 0 to 1

4. Access Backup Pod:

   • Go to "Pods" > "pmo-backup-restore-{temp-name}."

5. Open Pod Terminal:

   • Access the terminal for the selected pod.
   • To get the a list of all the backups run the following command:

     ./backup.sh -l
     

6. Run Command for Latest Backup:

   • For the latest backup, run the following command:

     ./backup.sh -r pmo-postgres-service:5432/pmodb
     

7. Run Command for Specific Backup:

   • For a specific backup, use the following command as an example:

     ./backup.sh -r pmo-postgres-service:5432/pmodb -f /backups/weekly/2023-12-10/pmo-postgres-service-pmodb_2023-12-10_01-00-22.sql.gz
     

8. Reset the Deployments down to 0 Pods:

   • Once the restoration is complete go to the pmo-backup-restore deployment and reset to 0 Pod.