K
Q

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

Similar Questions

Adding removed etcd member in Kubernetes master
kubernetes getting 502 bad gateway
Does Kubernetes allocates resources if the resource limit is above node capacity?
Getting issue when application deployed in one pod calls to another application deployed in another pod in minikube
Sticky sessions considering src IP and src port in K8s

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