job-template.yml

apiVersion: batch/v1
kind: Job
metadata:
  name: ${JOB_NAME}
  labels:
    app.kubernetes.io/managed-by: github-actions
    app.kubernetes.io/component: database-schema-setup
spec:
  ttlSecondsAfterFinished: 60
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: prepare-preview-schema
          image: ${POSTGRES_IMAGE}
          command: [ "bash", "-c" ]
          args:
            - |
              set -e
              
              echo "### START $(date --iso-8601=seconds) ###"
              
              if psql -t -A -c "SELECT 1 FROM information_schema.schemata WHERE schema_name = '${PREVIEW_SCHEMA_NAME}'" | grep -q 1; then
                echo "Schema ${PREVIEW_SCHEMA_NAME} already exists. Skipping setup."
                echo "Script finished successfully!"
                echo "### END $(date --iso-8601=seconds) ###"
                exit 0
              fi

              echo "Dumping base schema ${BASE_SCHEMA}..."
              pg_dump --schema=${BASE_SCHEMA} \
                      --exclude-table-data="${BASE_SCHEMA}.databasechangeloglock" \
                      --no-comments \
                      --no-owner \
                      --no-acl \
                      --quote-all-identifiers \
                      --format=plain \
                      --file=preview-dump.sql
              
              echo "Modifying dump file..."
              sed -i preview-dump.sql \
                  -e "s/\"${BASE_SCHEMA}\"/\"${PREVIEW_SCHEMA_NAME}\"/g" \
                  -e 's/^SET /--SET /g' \
                  -e 's/SELECT pg_catalog/--SELECT pg_catalog/g' \
                  -e 's/--SELECT pg_catalog.setval/SELECT pg_catalog.setval/g'
              
              echo "Creating schema ${PREVIEW_SCHEMA_NAME} with modified dump file..."
              psql --echo-queries \
                   --echo-errors \
                   --single-transaction \
                   --set=ON_ERROR_STOP=1 \
                   --file=preview-dump.sql
              
              echo "Script finished successfully!"
              echo "### END $(date --iso-8601=seconds) ###"
          env:
            - name: PGHOST
              valueFrom:
                configMapKeyRef:
                  name: ${CONFIGMAP_NAME}
                  key: ${DB_HOST_KEY}
            - name: PGPORT
              valueFrom:
                configMapKeyRef:
                  name: ${CONFIGMAP_NAME}
                  key: ${DB_PORT_KEY}
            - name: PGDATABASE
              valueFrom:
                configMapKeyRef:
                  name: ${CONFIGMAP_NAME}
                  key: ${DB_NAME_KEY}
            - name: PGSCHEMA
              valueFrom:
                configMapKeyRef:
                  name: ${CONFIGMAP_NAME}
                  key: ${DB_SCHEMA_KEY}
            - name: PGUSER
              valueFrom:
                configMapKeyRef:
                  name: ${CONFIGMAP_NAME}
                  key: ${DB_USERNAME_KEY}
            - name: PGPASSWORD
              valueFrom:
                secretKeyRef:
                  name: ${SECRET_NAME}
                  key: ${DB_PASSWORD_KEY}