Skip to content

Commit 9dfa433

Browse files
erthalionFxKu
erthalion
and
FxKu
authored
Connection pooler (zalando#799)
Connection pooler support Add support for a connection pooler. The idea is to make it generic enough to be able to switch between different implementations (e.g. pgbouncer or odyssey). Operator needs to create a deployment with pooler and a service for it to access. For connection pool to work properly, a database needs to be prepared by operator, namely a separate user have to be created with an access to an installed lookup function (to fetch credential for other users). This setups is supposed to be used only by robot/application users. Usually a connection pool implementation is more CPU bounded, so it makes sense to create several pods for connection pool with more emphasize on cpu resources. At the moment there are no special affinity or tolerations assigned to bring those pods closer to the database. For availability purposes minimal number of connection pool pods is 2, ideally they have to be distributed between different nodes/AZ, but it's not enforced in the operator itself. Available configuration supposed to be ergonomic and in the normal case require minimum changes to a manifest to enable connection pool. To have more control over the configuration and functionality on the pool side one can customize the corresponding docker image. Co-authored-by: Felix Kunde <felix-kunde@gmx.de>
1 parent 579f788 commit 9dfa433

39 files changed

+2885
-53
lines changed

‎charts/postgres-operator/crds/operatorconfigurations.yaml

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -318,6 +318,47 @@ spec:
318318
pattern: '^(\d+(e\d+)?|\d+(\.\d+)?(e\d+)?[EPTGMK]i?)$'
319319
scalyr_server_url:
320320
type: string
321+
connection_pool:
322+
type: object
323+
properties:
324+
connection_pool_schema:
325+
type: string
326+
#default: "pooler"
327+
connection_pool_user:
328+
type: string
329+
#default: "pooler"
330+
connection_pool_image:
331+
type: string
332+
#default: "registry.opensource.zalan.do/acid/pgbouncer"
333+
connection_pool_max_db_connections:
334+
type: integer
335+
#default: 60
336+
connection_pool_mode:
337+
type: string
338+
enum:
339+
- "session"
340+
- "transaction"
341+
#default: "transaction"
342+
connection_pool_number_of_instances:
343+
type: integer
344+
minimum: 2
345+
#default: 2
346+
connection_pool_default_cpu_limit:
347+
type: string
348+
pattern: '^(\d+m|\d+(\.\d{1,3})?)$'
349+
#default: "1"
350+
connection_pool_default_cpu_request:
351+
type: string
352+
pattern: '^(\d+m|\d+(\.\d{1,3})?)$'
353+
#default: "500m"
354+
connection_pool_default_memory_limit:
355+
type: string
356+
pattern: '^(\d+(e\d+)?|\d+(\.\d+)?(e\d+)?[EPTGMK]i?)$'
357+
#default: "100Mi"
358+
connection_pool_default_memory_request:
359+
type: string
360+
pattern: '^(\d+(e\d+)?|\d+(\.\d+)?(e\d+)?[EPTGMK]i?)$'
361+
#default: "100Mi"
321362
status:
322363
type: object
323364
additionalProperties:

‎charts/postgres-operator/crds/postgresqls.yaml

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,13 +106,64 @@ spec:
106106
uid:
107107
format: uuid
108108
type: string
109+
connectionPool:
110+
type: object
111+
properties:
112+
dockerImage:
113+
type: string
114+
maxDBConnections:
115+
type: integer
116+
mode:
117+
type: string
118+
enum:
119+
- "session"
120+
- "transaction"
121+
numberOfInstances:
122+
type: integer
123+
minimum: 2
124+
resources:
125+
type: object
126+
required:
127+
- requests
128+
- limits
129+
properties:
130+
limits:
131+
type: object
132+
required:
133+
- cpu
134+
- memory
135+
properties:
136+
cpu:
137+
type: string
138+
pattern: '^(\d+m|\d+(\.\d{1,3})?)$'
139+
memory:
140+
type: string
141+
pattern: '^(\d+(e\d+)?|\d+(\.\d+)?(e\d+)?[EPTGMK]i?)$'
142+
requests:
143+
type: object
144+
required:
145+
- cpu
146+
- memory
147+
properties:
148+
cpu:
149+
type: string
150+
pattern: '^(\d+m|\d+(\.\d{1,3})?)$'
151+
memory:
152+
type: string
153+
pattern: '^(\d+(e\d+)?|\d+(\.\d+)?(e\d+)?[EPTGMK]i?)$'
154+
schema:
155+
type: string
156+
user:
157+
type: string
109158
databases:
110159
type: object
111160
additionalProperties:
112161
type: string
113162
# Note: usernames specified here as database owners must be declared in the users key of the spec key.
114163
dockerImage:
115164
type: string
165+
enableConnectionPool:
166+
type: boolean
116167
enableLogicalBackup:
117168
type: boolean
118169
enableMasterLoadBalancer:

‎charts/postgres-operator/templates/clusterrole.yaml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -128,6 +128,7 @@ rules:
128128
- apps
129129
resources:
130130
- statefulsets
131+
- deployments
131132
verbs:
132133
- create
133134
- delete

‎charts/postgres-operator/templates/configmap.yaml

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,4 +20,5 @@ data:
2020
{{ toYaml .Values.configDebug | indent 2 }}
2121
{{ toYaml .Values.configLoggingRestApi | indent 2 }}
2222
{{ toYaml .Values.configTeamsApi | indent 2 }}
23+
{{ toYaml .Values.configConnectionPool | indent 2 }}
2324
{{- end }}

‎charts/postgres-operator/templates/operatorconfiguration.yaml

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,4 +34,6 @@ configuration:
3434
{{ toYaml .Values.configLoggingRestApi | indent 4 }}
3535
scalyr:
3636
{{ toYaml .Values.configScalyr | indent 4 }}
37+
connection_pool:
38+
{{ toYaml .Values.configConnectionPool | indent 4 }}
3739
{{- end }}

‎charts/postgres-operator/values-crd.yaml

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -267,6 +267,25 @@ configScalyr:
267267
# Memory request value for the Scalyr sidecar
268268
scalyr_memory_request: 50Mi
269269

270+
configConnectionPool:
271+
# db schema to install lookup function into
272+
connection_pool_schema: "pooler"
273+
# db user for pooler to use
274+
connection_pool_user: "pooler"
275+
# docker image
276+
connection_pool_image: "registry.opensource.zalan.do/acid/pgbouncer"
277+
# max db connections the pooler should hold
278+
connection_pool_max_db_connections: 60
279+
# default pooling mode
280+
connection_pool_mode: "transaction"
281+
# number of pooler instances
282+
connection_pool_number_of_instances: 2
283+
# default resources
284+
connection_pool_default_cpu_request: 500m
285+
connection_pool_default_memory_request: 100Mi
286+
connection_pool_default_cpu_limit: "1"
287+
connection_pool_default_memory_limit: 100Mi
288+
270289
rbac:
271290
# Specifies whether RBAC resources should be created
272291
create: true

‎charts/postgres-operator/values.yaml

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -243,6 +243,26 @@ configTeamsApi:
243243
# URL of the Teams API service
244244
# teams_api_url: http://fake-teams-api.default.svc.cluster.local
245245

246+
# configure connection pooler deployment created by the operator
247+
configConnectionPool:
248+
# db schema to install lookup function into
249+
connection_pool_schema: "pooler"
250+
# db user for pooler to use
251+
connection_pool_user: "pooler"
252+
# docker image
253+
connection_pool_image: "registry.opensource.zalan.do/acid/pgbouncer"
254+
# max db connections the pooler should hold
255+
connection_pool_max_db_connections: 60
256+
# default pooling mode
257+
connection_pool_mode: "transaction"
258+
# number of pooler instances
259+
connection_pool_number_of_instances: 2
260+
# default resources
261+
connection_pool_default_cpu_request: 500m
262+
connection_pool_default_memory_request: 100Mi
263+
connection_pool_default_cpu_limit: "1"
264+
connection_pool_default_memory_limit: 100Mi
265+
246266
rbac:
247267
# Specifies whether RBAC resources should be created
248268
create: true

‎docker/DebugDockerfile

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3,8 +3,17 @@ MAINTAINER Team ACID @ Zalando <team-acid@zalando.de>
33

44
# We need root certificates to deal with teams api over https
55
RUN apk --no-cache add ca-certificates go git musl-dev
6-
RUN go get github.com/derekparker/delve/cmd/dlv
76

87
COPY build/* /
98

10-
CMD ["/root/go/bin/dlv", "--listen=:7777", "--headless=true", "--api-version=2", "exec", "/postgres-operator"]
9+
RUN addgroup -g 1000 pgo
10+
RUN adduser -D -u 1000 -G pgo -g 'Postgres Operator' pgo
11+
12+
RUN go get github.com/derekparker/delve/cmd/dlv
13+
RUN cp /root/go/bin/dlv /dlv
14+
RUN chown -R pgo:pgo /dlv
15+
16+
USER pgo:pgo
17+
RUN ls -l /
18+
19+
CMD ["/dlv", "--listen=:7777", "--headless=true", "--api-version=2", "exec", "/postgres-operator"]

‎docs/reference/cluster_manifest.md

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -140,6 +140,11 @@ These parameters are grouped directly under the `spec` key in the manifest.
140140
is `false`, then no volume will be mounted no matter how operator was
141141
configured (so you can override the operator configuration). Optional.
142142

143+
* **enableConnectionPool**
144+
Tells the operator to create a connection pool with a database. If this
145+
field is true, a connection pool deployment will be created even if
146+
`connectionPool` section is empty. Optional, not set by default.
147+
143148
* **enableLogicalBackup**
144149
Determines if the logical backup of this cluster should be taken and uploaded
145150
to S3. Default: false. Optional.
@@ -360,6 +365,35 @@ CPU and memory limits for the sidecar container.
360365
memory limits for the sidecar container. Optional, overrides the
361366
`default_memory_limits` operator configuration parameter. Optional.
362367

368+
## Connection pool
369+
370+
Parameters are grouped under the `connectionPool` top-level key and specify
371+
configuration for connection pool. If this section is not empty, a connection
372+
pool will be created for a database even if `enableConnectionPool` is not
373+
present.
374+
375+
* **numberOfInstances**
376+
How many instances of connection pool to create.
377+
378+
* **schema**
379+
Schema to create for credentials lookup function.
380+
381+
* **user**
382+
User to create for connection pool to be able to connect to a database.
383+
384+
* **dockerImage**
385+
Which docker image to use for connection pool deployment.
386+
387+
* **maxDBConnections**
388+
How many connections the pooler can max hold. This value is divided among the
389+
pooler pods.
390+
391+
* **mode**
392+
In which mode to run connection pool, transaction or session.
393+
394+
* **resources**
395+
Resource configuration for connection pool deployment.
396+
363397
## Custom TLS certificates
364398

365399
Those parameters are grouped under the `tls` top-level key.

‎docs/reference/operator_parameters.md

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -595,3 +595,40 @@ scalyr sidecar. In the CRD-based configuration they are grouped under the
595595

596596
* **scalyr_memory_limit**
597597
Memory limit value for the Scalyr sidecar. The default is `500Mi`.
598+
599+
## Connection pool configuration
600+
601+
Parameters are grouped under the `connection_pool` top-level key and specify
602+
default configuration for connection pool, if a postgres manifest requests it
603+
but do not specify some of the parameters. All of them are optional with the
604+
operator being able to provide some reasonable defaults.
605+
606+
* **connection_pool_number_of_instances**
607+
How many instances of connection pool to create. Default is 2 which is also
608+
the required minimum.
609+
610+
* **connection_pool_schema**
611+
Schema to create for credentials lookup function. Default is `pooler`.
612+
613+
* **connection_pool_user**
614+
User to create for connection pool to be able to connect to a database.
615+
Default is `pooler`.
616+
617+
* **connection_pool_image**
618+
Docker image to use for connection pool deployment.
619+
Default: "registry.opensource.zalan.do/acid/pgbouncer"
620+
621+
* **connection_pool_max_db_connections**
622+
How many connections the pooler can max hold. This value is divided among the
623+
pooler pods. Default is 60 which will make up 30 connections per pod for the
624+
default setup with two instances.
625+
626+
* **connection_pool_mode**
627+
Default pool mode, `session` or `transaction`. Default is `transaction`.
628+
629+
* **connection_pool_default_cpu_request**
630+
**connection_pool_default_memory_reques**
631+
**connection_pool_default_cpu_limit**
632+
**connection_pool_default_memory_limit**
633+
Default resource configuration for connection pool deployment. The internal
634+
default for memory request and limit is `100Mi`, for CPU it is `500m` and `1`.

‎docs/user.md

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -512,6 +512,59 @@ monitoring is outside the scope of operator responsibilities. See
512512
[administrator documentation](administrator.md) for details on how backups are
513513
executed.
514514

515+
## Connection pool
516+
517+
The operator can create a database side connection pool for those applications,
518+
where an application side pool is not feasible, but a number of connections is
519+
high. To create a connection pool together with a database, modify the
520+
manifest:
521+
522+
```yaml
523+
spec:
524+
enableConnectionPool: true
525+
```
526+
527+
This will tell the operator to create a connection pool with default
528+
configuration, through which one can access the master via a separate service
529+
`{cluster-name}-pooler`. In most of the cases provided default configuration
530+
should be good enough.
531+
532+
To configure a new connection pool, specify:
533+
534+
```
535+
spec:
536+
connectionPool:
537+
# how many instances of connection pool to create
538+
number_of_instances: 2
539+
540+
# in which mode to run, session or transaction
541+
mode: "transaction"
542+
543+
# schema, which operator will create to install credentials lookup
544+
# function
545+
schema: "pooler"
546+
547+
# user, which operator will create for connection pool
548+
user: "pooler"
549+
550+
# resources for each instance
551+
resources:
552+
requests:
553+
cpu: 500m
554+
memory: 100Mi
555+
limits:
556+
cpu: "1"
557+
memory: 100Mi
558+
```
559+
560+
By default `pgbouncer` is used to create a connection pool. To find out about
561+
pool modes see [docs](https://www.pgbouncer.org/config.html#pool_mode) (but it
562+
should be general approach between different implementation).
563+
564+
Note, that using `pgbouncer` means meaningful resource CPU limit should be less
565+
than 1 core (there is a way to utilize more than one, but in K8S it's easier
566+
just to spin up more instances).
567+
515568
## Custom TLS certificates
516569

517570
By default, the spilo image generates its own TLS certificate during startup.

0 commit comments

Comments
 (0)