How to mount a sql file in a Init Container in order to bootstrap Postgres Database

Question

I am working on adding an init-container in my app's deployment.yaml so I can decouple my postgres db bootstrap or schema evolution.

As V1 I am trying to mount a sql file that will include the bootstraping of the db ( Database, Schema , Users) from the local directory and then by using a Postgres client image to run the commands in the script.

As of now I have the below

initContainers:
    - name: init-myservice
      image: jbergknoff/postgresql-client
      command: ['sh', '-c', 'psql -a -f /bootstrap.sql']
      env:
        - name: PGUSER
          ....
          ..... 
    volumeMounts:
      - mountPath: /bootstrap.sql
        name: bootstrap.sql

From my search results I am guessing I have to use an empty dir volume type to mount the file but I can not get it to work as of now.

I need to also pass the db credentials but not sure how to use them from env variables inside the sql script is there any syntax for that ?

Could you help connect the dots together ? Mainly how to mount the file in the init container and how to grab the env variables from the shell script and use them in the sql script to create for example the user with a password ?

Thanks in advance.

Amit · Accepted Answer · 2022-11-28 13:09:23Z

1

You will have to do the following:

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;

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.

answered Nov 28, 2022 at 13:09

Amit

4967 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kostas.kapasakis Over a year ago

Thank you for your response , more or less the solution I implemented has the psql command you mentioned but I created a container in which I had copied the sql files in in the DockerFile and then just run the psql command without the need for the configmap

Adam Gerard · Accepted Answer · 2024-11-05 19:35:59Z

Was looking into something similar and found the following approach as of 11.5.24 (postgres:16.2 docker image and kubernetes v1.31):

I had an existing SQL init file init.sql.
I ran kubectl create configmap initsql --from-file=init.sql to generate a configMap file. I renamed the file something like postgres-init.yaml.
I kubectl apply -f postgres-init.yaml.
I have a standard-fare postgres-deployment.yaml - basically copied from the official examples with a few modification.

In volumes, I added:

volumes:
  - ...
  - name: sql-init-mount
    configMap:
      name: initsql
      items:
       - key: init.sql
         path: init.sql

In the same postgres-deployment.yaml I added to the postgres container itself:

volumeMounts:
  - mountPath: /docker-entrypoint-initdb.d
    name: sql-init-mount

The official postgres Docker image supports automatic initialization of any scripts dropped into docker-entrypoint-initdb.d.

I had a suspicion that using a configMap might accomplish the same thing without using containerInit or any commands. Turns out I was correct, you may need to wait moment but if you:

minikube dashboard the deployed postgres pod can be found and you can easily exec in (if you're doing this locally) to verify the existing of the file (created corrected) and run: psql -U postgres -d postgres to login and query.

Collectives™ on Stack Overflow

How to mount a sql file in a Init Container in order to bootstrap Postgres Database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related