Create or update existing postgres db container through kubernetes job

Question

I have a Postgres DB container which is running in a Kubernetes cluster. I need to write a Kubernetes job to connect to the Postgres DB container and run the scripts from SQL file. I need to understand two things here

1. commands to run SQL script
2. how to load SQL file in Job.yaml file

Here is my sample yaml file for Kubernetes job

apiVersion: batch/v1
kind: Job
metadata:
  name: init-db
spec:
  template:
    metadata:
      name:  init-db
      labels:
        app: init-postgresdb
    spec:
      containers:
      - image: "docker.io/bitnami/postgresql:11.5.0-debian-9-r60"
        name: init-db
        command:
        - psql -U postgres 
        env:
          - name: DB_HOST
            value: "knotted-iguana-postgresql"
          - name: DB_DATABASE
            value: "postgres"
      restartPolicy: OnFailure  

Answer 1

You have to mount the SQL file as a volumen from a configmap and use the psql cli to execute the commands from mounted file.

To execute commands from file you can change the command parameter on the yaml by this:

psql -a -f sqlCommand.sql

The configmap needs to be created using the file you pretend to mount more info here

kubectl create configmap sqlCommands.sql --from-file=sqlCommands.sql

Then you have to add the configmap and the mount statement on your job yaml and modify the command to use the mounted file.

apiVersion: batch/v1
kind: Job
metadata:
  name: init-db
spec:
  template:
    metadata:
      name:  init-db
      labels:
        app: init-postgresdb
    spec:
      containers:
      - image: "docker.io/bitnami/postgresql:11.5.0-debian-9-r60"
        name: init-db
        command: [ "bin/sh", "-c", "psql -a -f /sqlCommand.sql" ]
        volumeMounts:
        - name: sqlCommand
          mountPath: /sqlCommand.sql
        env:
          - name: DB_HOST
            value: "knotted-iguana-postgresql"
          - name: DB_DATABASE
            value: "postgres"
      volumes:
        - name: sqlCommand
          configMap:
          # Provide the name of the ConfigMap containing the files you want
          # to add to the container
          name: sqlCommand.sql
      restartPolicy: OnFailure

Answer 2

You should make a docker file for the same first, execute it and map the same working docker image to the kubernetes job yaml file.

You can add an entrypoint.sh in docker file, where you can place your scripts to be executed