Provision Kubernetes SQL DB from backup

charts_db-init_Chart.yaml

apiVersion: v1
description: A Helm chart for Kubernetes
icon: https://raw.githubusercontent.com/jenkins-x/jenkins-x-platform/master/images/java.png
name: db-init
version: 0.0.1

charts_db-init_requirements.yaml

dependencies:
- alias: server-db
  name: postgresql
  repository: https://kubernetes-charts.storage.googleapis.com/
  version: 6.3.4

charts_db-init_values.yaml

server-db:
  master:
    extraVolumes:
      - name: postgresql-initdb
        persistentVolumeClaim:
          claimName: postgresql-initdb
    extraVolumeMounts:
      - name: postgresql-initdb
        mountPath: /docker-entrypoint-initdb.d/
  persistence:
      enabled: false
  image:
    tag: 9.6.15

jenkins-x.yml

buildPack: jenkins
pipelineConfig:

  pipelines:

    pullRequest:
      promote:
        steps:
          - name: setup-db-volume-with-dump
            sh: DB_NAMESPACE=\$PREVIEW_NAMESPACE ./support/scripts/provision-new-db-from-backup.sh

support_postgres_docker-entrypoint-initdb.d_1_init_users_and_db.sql

CREATE USER admin;
CREATE DATABASE test;

GRANT ALL PRIVILEGES ON DATABASE postgres TO admin;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;

GRANT ALL PRIVILEGES ON DATABASE test TO admin;

support_scripts_provision-new-db-from-backup.sh

#!/usr/bin/env bash

if [[ -z "$DB_NAMESPACE" ]]; then
    echo "Must provide DB_NAMESPACE in environment" 1>&2
    exit 1
fi

set -exuo pipefail
cat <<EOF | kubectl apply -n $DB_NAMESPACE -f -
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgresql-initdb
  labels:
    app: server-db
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi
  storageClassName: regional-pd-ssd
EOF
cat <<EOF | kubectl apply -n $DB_NAMESPACE -f -
kind: Pod
apiVersion: v1
metadata:
  name: server-db-postgresql-data-loader
  labels:
    app: postgresql-loader
spec:
  volumes:
    - name: postgresql-initdb
      persistentVolumeClaim:
        claimName: postgresql-initdb
  containers:
    - name: server-db-postgresql-data-loader
      image: ubuntu
      volumeMounts:
      - name: postgresql-initdb
        mountPath: /docker-entrypoint-initdb.d
      command: ["/bin/bash", "-ecx", "while :; do printf '.'; sleep 5 ; done"]
EOF

jx step unstash -u gs://path-to-db-dump/db-dump.sql -o ./support/postgres/docker-entrypoint-initdb.d/2_import_db-dump.sql

kubectl wait -n $DB_NAMESPACE --for=condition=Ready --timeout=60s pod/server-db-postgresql-data-loader

kubectl cp -n $DB_NAMESPACE ./support/postgres/docker-entrypoint-initdb.d/ server-db-postgresql-data-loader:/

kubectl delete pods -n $DB_NAMESPACE -l app=postgresql-loader