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

You will have to do the following:

1. Make the content of the SQL file as a ConfigMap like this, for example:

   apiVersion: v1
   kind: ConfigMap
   metadata:
     name: mariadb-config
   data:
     mariadb-schema: "DROP DATABASE IF EXISTS test;\n\nCREATE DATABASE IF NOT EXISTS test;
   

2. Make a volume from this config map in your deployment yaml like this:

    volumes:
    - name: mariadb-schema-config-vol
      configMap:
        name: mariadb-config
        defaultMode: 420
        items:
        - key: mariadb-schema
          path: mariadb-schema.sql
   

   And volume mount like this:

    volumeMounts:
    - mountPath: /var/db/config
      name: mariadb-schema-config-vol
   

Then your init container command will be like:

    ['sh', '-c', 'psql -a -f /var/db/config/mariadb-config.sql']

For your second question, make a shell script that reads the env variables (The db credentials - I am presuming that you are having them in secrets and using them as env variables) and then invoke this command:

   psql -a -f /var/db/config/mariadb-config.sql

So to make this happen the content of this script should be in a config map and execute the script from a volume mount, just like the above example.

Hope this helps.

I finally decided to take the approach of creating a config file with the script we want to run and then call this configMap from inside the volume.

this is a short explanation:

In my pod.yaml file there is a VolumeMount called "/pgconf" which is the directory that the docker image reads any SQL script that you put there and run it when the pod is starting. And inside Volumes I will put the configMap name (postgres-init-script-configmap) which is the name of the config defined inside the configmap.yaml file.

There is no need to create the configMap using kubernetes, The pod will take the configuration from the configMap file as long as you place it in the same directory as the pod.yaml .

my POD yaml file:

apiVersion: v1
kind: Pod
metadata:
  name: "{{.Values.container.name.primary}}"
  labels:
    name: "{{.Values.container.name.primary}}"
spec:
  securityContext:
    fsGroup: 26
  restartPolicy: {{default "Always" .Values.restartPolicy}}

  containers:
  - name: {{.Values.container.name.primary}}
    image: "{{.Values.image.repository}}/{{.Values.image.container}}:{{.Values.image.tag}}"
    ports:
    - containerPort: {{.Values.container.port}}
    env:
    - name: PGHOST
      value: /tmp
    - name: PG_PRIMARY_USER
      value: primaryuser
    - name: PG_MODE
      value: primary
    resources:
      requests:
        cpu: {{ .Values.resources.cpu }}
        memory: {{ .Values.resources.memory }}
    volumeMounts:
    - mountPath: /pgconf
      name: init-script
      readOnly: true
  volumes:
  - name: init-script
    configMap:
      name: postgres-init-script-configmap

my configmap.yaml (Which contains the SQL script that will initial the DB):

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-init-script-configmap
data:
  setup.sql: |-
    CREATE USER david WITH PASSWORD 'david';

this may help (here I have added configmap, persistent volume, persistent volume-claim, and Postgres deployment yaml. This yaml will automatically create a table named users in the Postgres database inside the Postgres-container. Thanks

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  Postgres_DB: postgresdb

---
apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
data:
  Postgres_User: postgresadmin
  Postgres_Password: admin123

---
kind: PersistentVolume
apiVersion: v1
metadata:
  name: postgres-pv-volume
  labels:
    type: local
    app: postgres
spec:
  storageClassName: manual
  capacity:
    storage: 5Gi
  accessModes:
    - ReadWriteMany
  hostPath:
    path: "/mnt/data"

---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-pv-claim
  labels:
    app: postgres
spec:
  storageClassName: manual
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 5Gi

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres-container
          image: postgres:latest
          imagePullPolicy: "IfNotPresent"
          lifecycle:
            postStart:
              exec:
                command: ["/bin/sh","-c","sleep 20 && PGPASSWORD=$POSTGRES_PASSWORD psql -w -d $POSTGRES_DB -U $POSTGRES_USER -c 'CREATE TABLE IF NOT EXISTS users (userid SERIAL PRIMARY KEY,username TEXT,password TEXT,token TEXT,type TEXT);'"]
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_DB
              valueFrom:
                configMapKeyRef:
                  name: postgres-config
                  key: Postgres_DB
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: Postgres_User
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-secret
                  key: Postgres_Password               
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgredb
      volumes:
        - name: postgredb
          persistentVolumeClaim:
            claimName: postgres-pv-claim

If you find this helpful, please mark it as answer.