Question 187737 in PostgreSQL Question posted in
The official documentation can be found here.

Setup PostgreSql configuration with Helm

I’ve setup a PostgreSQL database with Helm with this ConfigMap, that contains the initial script allows to create the databases and schemas I need:

apiVersion: v1
kind: ConfigMap
metadata:
  name: {{ .Values.postgres.configmap }}
  namespace: {{ .Release.Namespace }}
data:
  init_script.sh: |+
    #!/bin/bash
    psql -v <<-EOSQL
    CREATE DATABASE my-db;
    GRANT ALL PRIVILEGES ON DATABASE my-db to <user>
    ...

I want to add the specification of the user and DB to use when I connect, and the priviledge for that user on the DB created with script.

I don’t know how to set them.

Tags:

2

Answers


  1. 0

    Update your script with the Postgress User and database name to pass to the psql script as environmental variables.

    apiVersion: v1
kind: ConfigMap
metadata:
  name: {{ .Values.postgres.configmap.name }}
  namespace: {{ .Release.Namespace }}
data:
  init_script.sh: |+
    #!/bin/bash
    set -e
    psql -v ON_ERROR_STOP=1 -U $POSTGRES_USER --dbname $POSTGRES_DB <<-EOSQL
    CREATE DATABASE my-db;
    GRANT ALL PRIVILEGES ON DATABASE my-db to $POSTGRES_USER;
    ...
    EOSQL

    Then, add into your Deloyment file the two enviroment variables in the Env section.

    apiVersion: apps/v1
kind: Deployment
spec:
    template:
        spec:
            containers:
                - name: {{ .Values.postgres.image }}
                  env:
                  - name: POSTGRES_USER
                    value: {{ .Values.postgres.user }}
                  - name: POSTGRES_DB
                    value: {{ .Values.postgres.db }}
    Login or Signup to reply.
  2. 0

    If you are using the standard Docker Hub postgres image and you are just trying to create a database, the easiest thing to do is to use its environment-variable settings

    image: postgres:15
env:
  - name: POSTGRES_USER
    value: <user>
  - name: POSTGRES_DB
    value: my-db

    For these settings you do not need a separate ConfigMap. In the context of a Helm chart, if you want to make these values configurable, you can

    value: {{ .Values.postgres.db | default "my-db" }}

    which will use a value from the configuration (values.yaml, helm install --set option, additional helm install -f files)

    postgres:
  db: database-name

    or my-db if it’s not set.

    If you do specifically want to use an init script, but won’t know the database user name until deploy time, you can ask Helm to inject this into the init script. If you name the script *.sql then the image will run it under psql for you so you don’t need the credentials.

    apiVersion: v1
kind: ConfigMap
metadata:
  name: {{ .Values.postgres.configmap }}
data:
  init_script.sql: |+
    CREATE DATABASE my-db;
    GRANT ALL PRIVILEGES ON DATABASE my-db to {{ .Values.postgres.user }}
    ...

    Helm will substitute the templated value before creating the ConfigMap.

    In all of these cases, note that the initialization scripts and environment variables are only considered the very first time the database is used, if the corresponding storage is empty. If you change one of the Helm values, it will change the environment-variable or ConfigMap setting, but that won’t actually cause a change in the database.

    Practically, my experience has been that the best approach here is to use the environment variables to create a database and user, and then to use your application framework’s database-migration system to actually create the tables. You’ll need the migrations in other contexts so it’s good to have a path to run them, and they’re useful if the database schema ever changes; you can’t just re-run the /docker-entrypoint-initdb.d scripts.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search