Run psql in an initContainer

Kubernetes initContainers are handy for setting up configurations your container app may need before it starts up. I use psql command line client to setup roles and tables in Postgres.

Run psql commands in yaml

Using psql container and running the psql command directly in the initContainer will have some limitations. Writing long commands will have to be done in the yaml file.

Example snippet: deployment.yaml

    spec:
      initContainers:
        - name: init-db
          image: my-psql-container
          command:
            - /bin/sh
            - -c
            - psql -h ${DB_HOST} -U ${DB_USER} -d ${DB_NAME} -c "CREATE TABLE ...."
          env:
            - name: DB_HOST
              value: "my-db-host"

Run shell script file

Another option is to run a shell script in the psql container. Instead of writing our your commands in the yaml file, include a file for the shell script. This will give you more options for logic control and also make it easier to include environment variables.

Example snippet: deployment.yaml

    spec:
      initContainers:
        - name: init-db
          image: my-psql-container
          command:
            - /bin/sh
            - -c
            - /scripts/init-db.sh
          volumeMounts:
            - name: init-script
              mountPath: /scripts
              subPath: init-db.sh
          env:
            - name: DB_HOST
              value: "my-db-host"

Since you are using a Bash script, you can use your environment variables to define variables for the psql command. Use your environment variables to define variables for the psql command with the -v option.

Example snippet: init-db.sh

psql -U ${USER} -h ${HOST} -v myvar1="$ENV_VAR1" -v myvar2="$ENV_VAR2" ...

Passing environment variables with the -v option when calling the psql command will make myvar1 and myvar2 available in your sql. This will also allow you to use variables in your sql file heredoc.

Another benefit for using variables your sql commands is to include sql variable placeholders. This will help reduce the opportunity for sql injection.

Example snippet: init-db.sh

psql -U ${USER} -h ${HOST} -v myvar1="$ENV_VAR1" -v myvar2="$ENV_VAR2" ...
    CREATE DATABASE :myvar1 WITH OWNER :myvar2 ...

Since psql uses a env var for password, in your script you can authenticate to the Postgres server with different roles. You just need to re-assign the environment variable PGPASSWORD before the psql command.

Example snippet: init-db.sh

PGPASSWORD=${ENV_VAR_PASSWORD} psql -U ${USER} -h ${HOST} -v myvar1="$ENV_VAR1" -v myvar2="$ENV_VAR2" ...

My Github examples

I use this example setting up a database user and table for a containerized app in my k8s homelab. Github initContainer example

How to use environment variables in heredoc for an sql file: Github heredoc example