Managing StackGres 1.6 Cluster Configurations: K8s Pods, PostgreSQL, and PgBouncer

Postgres Configuration

PostgreSQL settings are defined in the SGPostgresConfig CRD. If no custom config is supplied when creating a cluster, StackGres generates a default configuration that includes a professionally tuned postgresql.conf. Users can supply their own parameters via the same CRD.

apiVersion: stackgres.io/v1
kind: SGPostgresConfig
metadata:
  namespace: demo
  name: pgconfig1
spec:
  postgresVersion: "14"
  postgresql.conf:
    work_mem: '16MB'
    shared_buffers: '2GB'
    random_page_cost: '1.5'
    password_encryption: 'scram-sha-256'
    log_checkpoints: 'on'
    jit: 'off'

Apply the configuration with kubectl apply and inspect it with kubectl describe sgpgconfig pgconfig1. A cluster references the config via the sgPostgresConfig field in the SGCluster spec.

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
  configurations:
    sgPostgresConfig: 'pgconfig1'

Instance Profile

The SGInstanceProfile CRD abstracts CPU and memory resources into named "t‑shirt" sizes (e.g., S, M, L). This standardizes resource requests for Pods.

apiVersion: stackgres.io/v1
kind: SGInstanceProfile
metadata:
  namespace: demo
  name: size-small
spec:
  cpu: "4"
  memory: "8Gi"

A cluster references the profile with the sgInstanceProfile field.

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
  sgInstanceProfile: 'size-small'

Connection‑Pool Configuration

StackGres deploys a PgBouncer sidecar by default. The SGPoolingConfig CRD lets users supply custom pool settings. A production‑grade default is provided; custom values can be defined as shown.

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  namespace: demo
  name: poolconfig1
spec:
  pgBouncer:
    pgbouncer.ini:
      pgbouncer:
        max_client_conn: '200'
        default_pool_size: '200'
        pool_mode: transaction

A cluster references the pool config via the sgPoolingConfig field.

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
  configurations:
    sgPoolingConfig: 'poolconfig1'

Reloading Configuration

After applying a new SGPoolingConfig, reload the PgBouncer process in the primary pod by sending a HUP signal:

PRIMARY=$(kubectl get pod -l role=master -n cluster -o name)
kubectl exec -n cluster -it ${PRIMARY} -c postgres-util -- pkill --signal HUP pgbouncer

Disabling the Connection Pool

For workloads that do not benefit from pooling (e.g., reporting or OLAP), set disableConnectionPooling to true in the SGCluster spec.

apiVersion: stackgres.io/v1
kind: SGCluster
metadata:
  namespace: demo
  name: cluster
spec:
  pods:
    disableConnectionPooling: true

Pool Management and Statistics

Transaction Mode

Example of a transaction‑mode pool configuration:

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  namespace: my-cluster
  name: poolconfig1
spec:
  pgBouncer:
    pgbouncer.ini:
      pgbouncer:
        pool_mode: transaction
        max_client_conn: '1000'
        default_pool_size: '80'

Session Mode with Time‑outs

Session‑mode configuration that releases idle connections:

apiVersion: stackgres.io/v1
kind: SGPoolingConfig
metadata:
  namespace: my-cluster
  name: poolconfig-session-prod
spec:
  pgBouncer:
    pgbouncer.ini:
      pgbouncer:
        pool_mode: session
        max_client_conn: '1000'
        default_pool_size: '80'
        client_idle_timeout: '30s'
        idle_transaction_timeout: '60s'
        server_idle_timeout: '120s'
        server_lifetime: '240s'
        server_fast_close: '300s'

When the server pool is saturated, incoming client connections are placed in a wait state by PgBouncer, highlighting the importance of proper timeout settings.

Accessing the Pool Management Console

PgBouncer provides a console for inspecting pool statistics. Access it via the container socket:

kubectl exec -it -c postgres-util test-0 -- psql -p 6432 -d pgbouncer pgbouncer
pgbouncer=# \x

Useful Pool Statistics Commands

pgbouncer=# show stats;
pgbouncer=# show pools;
pgbouncer=# show clients;

Additional PgBouncer Commands

show servers

show fds

show mem

show stats_totals

show stat_averages

Reference Commands

SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|USERS|VERSION
SHOW FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SHOW STATS|STATS_TOTALS|STATS_AVERAGES|TOTALS
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
DISABLE <db>
ENABLE <db>
RECONNECT [<db>]
KILL <db>
SUSPEND
SHUTDOWN