sql server - How to execute a sql script file in a Kubernetes Pod?

Question

I wanted to create a SQL Server database in Kubernetes pod using a SQL script file. I have the SQL script which creates the database and inserts the master data. As I'm new to Kubernetes, I'm struggling to run the SQL script in a pod. I know the SQL script can be executed manually in a separate kubectl exec command, but I wanted it to be executed automatically in the pod deploy yml file itself.

Is there a way to mount the script file into pod's volume and run it after starting the container?

Answer 1

You could use kubernetes hooks for that case. There are two of them: PostStart and PreStop.

PostStart executes immediately after a container is created. PreStop on other hand is called immediately before a container is terminated.

You have two types of hook handlers that can be implemented: Exec or HTTP

Exec - Executes a specific command, such as pre-stop.sh, inside the cgroups and namespaces of the Container. Resources consumed by the command are counted against the Container. HTTP - Executes an HTTP request against a specific endpoint on the Container.

PostStart is the one to go with here, however please note that the hook is running in parallel with the main process. It does not wait for the main process to start up fully. Until the hook completes, the container will stay in waiting state.

You could use a little workaround for that and add a sleep command to your script in order to have it wait a bit for your main container creation. Your script file can be stored in the container image or mounted to volume shared with the pod using ConfigMap. Here`s some examples how to do that:

kind: ConfigMap
apiVersion: v1
metadata:
  namespace: <your-namespace> 
  name: poststarthook
data:
  poststart.sh: |
     #!/bin/bash
     echo "It`s done"

Make sure your script does not exceed 1mb limit for ConfigMap

After you define configMap you will have mount it using volumes:

spec:
      containers:
      - image: <your-image>
        name: example-container
        volumeMounts:
          - mountPath: /opt/poststart.sh
            subPath: poststart.sh
            name: hookvolume
      volumes:
      - name: hookvolume
        configMap:
          name: poststarthook
          defaultMode: 0755 #please remember to add proper (executable) permissions

And then you can define postStart in your spec:

spec:
  containers:
  - name: example-container
    image: <your-image> 
    lifecycle:
      postStart:
        exec:
          command: ["/bin/sh", "-c", /opt/poststart.sh ]

You can read more about hooks in kubernetes documentation and in this article. Let me know if that was helpful.