I have too many connection open using the default docker postgresql configuration
I want to extend max_connection without using a volume for mounting the configuration (I need this to be available by default for my CI environment).
I have tried to use sed to edit the configuration but this has no effect.
What is the recommended way of overriding default configuration of postgresql docker official image?
run this docker-compose.yml
version: '2'
services:
postgres:
image: postgres:10.3-alpine
command: postgres -c 'max_connections=200'
environment:
POSTGRES_DB: pgdb
POSTGRES_PASSWORD: postgres
POSTGRES_USER: postgres
stdin_open: true
tty: true
ports:
- 5432:5432/tcp
It is as simple as (you just override the default CMD with postgres -N 500):
docker run -d --name db postgres:10 postgres -N 500
You can check it using:
docker run -it --rm --link db:db postgres psql -h db -U postgres
show max_connections;
max_connections
-----------------
500
(1 row)
The official image provides a way to run arbitrary SQL and shell scripts after the DB is initialized by putting them into the /docker-entrypoint-initdb.d/ directory. This script:
ALTER SYSTEM SET max_connections = 500;
will let us change the maximum connection limit. Note that the postgres server will be restarted after the initializing scripts are run, so even settings like max_connections that require a restart will go into effect when your container starts for the first time.
How you attach this script to the docker container depends on how you are starting it:
Save the SQL script to a file max_conns.sql, then use it as a volume:
docker run -it -v $PWD/max_conns.sql:/docker-entrypoint-initdb.d/max_conns.sql postgres
With docker compose, save the SQL script to a file max_conns.sql next to your docker-compose.yaml, and than reference it:
version: '3'
services:
db:
image: postgres:latest
volumes:
- ./max_conns.sql:/docker-entrypoint-initdb.d/max_conns.sql
With kubernetes, you will need to create a configmap for the script:
kind: ConfigMap
apiVersion: v1
metadata:
name: max-conns
data:
max_conns.sql: "ALTER SYSTEM SET max_connections = 500;"
And then use it with a container:
apiVersion: apps/v1
kind: Deployment
metadata:
name: postgres-example
spec:
template:
spec:
containers:
- name: postgres
image: postgres:latest
volumeMounts:
- name: max-conns
mountPath: /docker-entrypoint-initdb.d
volumes:
- name: max-conns
configMap:
name: max-conns
If you are using TestContainers do this before starting the container
postgreSQLContainer.setCommand("postgres", "-c", "max_connections=20000");
postgreSQLContainer.start();
From Google, Max value allowed 262143 min 1 and default 100
I spent a lot of time in this issue and the simplest way to resolve this is, you can add max_connections in your values.yaml file straight away. You can specify extended configuration parameters, this option will override the conf file.
For instance; extendedConfiguration: "max_connections = 500"
You need to develop a init script. Accept max connections value from environment variable and update it during start up from init script. Finally launch postgreSQL service
