How to execute a database script after deploying a Postgresql image to openshift with Jenkins?

Question

I have a git repo with the Jenkins pipeline and the official template of postgresql:

kind: "BuildConfig"
apiVersion: "v1"
metadata:
  name: "postgresql-pipeline"
spec:
  strategy:
    jenkinsPipelineStrategy:
      jenkinsfile: |-
        pipeline {
          agent any
          environment {
            DATABASE_NAME = 'sampledb'
            DATABASE_USER = 'root'
            DATABASE_PASSWORD = 'root'
          }
          stages {
            stage('Clone git') {
              steps {
                git 'https://bitbucket.org/businnessdata_db/postgresql-test.git'
              }
            }
            stage('Deploy db') {
              steps {
                sh 'oc status'
                sh 'oc delete secret/postgresql'
                sh 'oc delete pvc/postgresql'
                sh 'oc delete all -l "app=postgresql-persistent"'
                sh 'oc new-app -f openshift/templates/postgresql-persistent.json'
              }
            }
            stage('Execute users script') {
              steps {
                sh 'oc status'


          }
        }
        stage('Execute update script') {
          steps {
            sh 'oc status'

          }
        }
      }
    }
type: JenkinsPipeline<code>

What i have to put in the last 2 steps to run a script against the new generated database?

Answer 1

You can either install psql on your Jenkins container and then run the script through the shell command.

sh """
export PGPASSWORD=<password>
psql -h <host> -d <database> -U <user_name> -p <port> -a -w -f <file>.sql
   """

Or, since Jenkinsfiles are written in Groovy, use Groovy to execute your statements. Here's the Groovy documentation for working with databases.