How to change Postgresql max_connections config via Kubernetes statefulset environment variable?

6/9/2019

In Kubernetes, environment variables from a ConfigMap do not change the max_connections property in a PostgreSql pod. How do you change Postgres max_connections configuration via environment variables in Kubernetes ?

I've tried following parameters to configure Postgres.

The problem is, I can use DB, USER and PASSWORD parameters and values are set as expected. But i need to change max_connections configuration. I made related research, it looks like PGOPTIONS is the right choice to send configuration changes. Even i tried PGOPTIONS and other variations, there is no impact on max_connections value. I am connecting postgresql and i am executing SHOW MAX_CONNECTIONS query, it shows 100 even i specify 1000 in the environment configuration values.

I am using Kubernetes 1.14 in digitalocean.

ConfigMap

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config-demo
  labels:
    app: postgres
data:
  POSTGRES_DB: demopostgresdb
  POSTGRES_USER: demopostgresadmin
  POSTGRES_PASSWORD: demopostgrespwd
  PGOPTIONS: "-c max_connections=1000  -c shared_buffers=1024MB"
  POSTGRES_OPTIONS: "-c max_connections=1000  -c shared_buffers=1024MB"
  PG_OPTIONS: "-c max_connections=1000  -c shared_buffers=1024MB"
  MAX_CONNECTIONS: "1000"

Statefulset

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: pgdemo
spec:
  serviceName: "postgres"
  replicas: 2
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:latest          

          envFrom:
            - configMapRef:
                name: postgres-config-demo     
          env:         
            - name: PGOPTIONS
              value: "-c max_connections=1000  -c shared_buffers=1024MB"
            - name: "MAX_CONNECTIONS"
              value: "1000"
          ports:
            - containerPort: 5432
              name: postgredb
          volumeMounts:
            - name: postgredb
              mountPath: /var/lib/postgresql/data
              subPath: postgres              
  volumeClaimTemplates:
    - metadata:
        name: postgredb
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: do-block-storage
        resources:
          requests:
            storage: 3Gi

I am expecting to get max_connections value as 1000. But it looks like as default value like 100.

There is no error in any log.

-- okproject
digital-ocean
kubernetes
postgresql

4 Answers

6/10/2019

Postgres docker image does not allow to set max_connections property via environment variable. You have to use postgresql.conf file to set such configurations.

Create a ConfigMap using custom postgresql.conf file. Then mount the ConfigMap in /etc/postgresql/ directory.

You will find a sample postgresql.conf file here.

-- Emruz Hossain
Source: StackOverflow

5/27/2020

When max_connections is specified by the environment variable of PGOPTIONS, the following error occurs. Therefore, it is correct to specify it in args instead of the environment variable of PGOPTIONS.

2020-05-27 06:51:55.327 UTC [62] FATAL:  parameter "max_connections" cannot be changed without restarting the server
psql: FATAL:  parameter "max_connections" cannot be changed without restarting the server
containers:
        - name: postgres
          image: postgres:latest
          args: ["-c", "max_connections=500"]
-- Daisuke Yoshimoto
Source: StackOverflow

4/14/2020

Postgresql docker image can only support several env parameters, POSTGRES_PASSWORD, POSTGRES_USER ,POSTGRES_DB, POSTGRES_INITDB_ARGS, POSTGRES_INITDB_WALDIR, POSTGRES_HOST_AUTH_METHOD, PGDATA.

If you want modify some database configuration parameters, you can use args in kubernetes.

Deployment yaml file fraction

  containers:
  - args:
    - -c
    - max_connections=1000
    - -c
    - shared_buffers=1024MB
    envFrom:
    - configMapRef:
        name: postgres-config
    image: postgres
    imagePullPolicy: IfNotPresent
    name: postgres
-- Lance76
Source: StackOverflow

6/10/2019

you need to extend the base image ( postgres:latest ). overwrite the default configurations with custom changes and then launch postgres.

-- P Ekambaram
Source: StackOverflow