PostgreSQL no Kubernetes: tuning que funciona em produção

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# storage-class.yaml — OCI Block Volume
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
  name: oci-bv-high-perf
provisioner: blockvolume.csi.oraclecloud.com
parameters:
  vpusPerGB: "20"          # High Performance (20 VPUs = ~10k IOPS/TB)
  attachment-type: "paravirtualized"
reclaimPolicy: Retain      # NUNCA Delete em produção
volumeBindingMode: WaitForFirstConsumer
allowVolumeExpansion: true

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# pvc.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-data
  namespace: postgres
spec:
  storageClassName: oci-bv-high-perf
  accessModes: [ReadWriteOnce]
  resources:
    requests:
      storage: 100Gi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

# configmap-postgres.yaml
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  namespace: postgres
data:
  postgresql.conf: |
    # Memória — para instância com 8GB RAM
    shared_buffers       = 2GB
    effective_cache_size = 6GB
    work_mem             = 64MB
    maintenance_work_mem = 512MB
    huge_pages           = off        # off em containers

    # Checkpoint / WAL
    wal_buffers          = 64MB
    checkpoint_completion_target = 0.9
    max_wal_size         = 4GB
    min_wal_size         = 1GB
    wal_compression      = on

    # Conexões
    max_connections      = 100        # use PgBouncer — não aumente isso

    # Paralelismo
    max_worker_processes       = 4
    max_parallel_workers_per_gather = 2
    max_parallel_workers       = 4

    # Logging útil
    log_min_duration_statement = 1000   # loga queries > 1s
    log_checkpoints            = on
    log_lock_waits             = on
    log_temp_files             = 0

    # Timezone
    timezone = 'America/Sao_Paulo'
    log_timezone = 'America/Sao_Paulo'    

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# pgbouncer deployment
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: postgres
spec:
  replicas: 2
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
        - name: pgbouncer
          image: bitnami/pgbouncer:1.22
          env:
            - name: POSTGRESQL_HOST
              value: postgres-service
            - name: POSTGRESQL_PORT
              value: "5432"
            - name: PGBOUNCER_DATABASE
              value: "*"
            - name: PGBOUNCER_POOL_MODE
              value: transaction          # transaction pooling para alta concorrência
            - name: PGBOUNCER_MAX_CLIENT_CONN
              value: "1000"
            - name: PGBOUNCER_DEFAULT_POOL_SIZE
              value: "20"
            - name: PGBOUNCER_MIN_POOL_SIZE
              value: "5"
          ports:
            - containerPort: 5432
          resources:
            requests:
              cpu: 100m
              memory: 128Mi
            limits:
              cpu: 500m
              memory: 256Mi

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# postgres-exporter sidecar ou deployment separado
- name: postgres-exporter
  image: prometheuscommunity/postgres-exporter:v0.15.0
  env:
    - name: DATA_SOURCE_NAME
      valueFrom:
        secretKeyRef:
          name: postgres-exporter-secret
          key: dsn
  ports:
    - containerPort: 9187
      name: metrics

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# prometheus rules
groups:
  - name: postgresql
    rules:
      - alert: PostgreSQLHighConnections
        expr: pg_stat_activity_count > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "Conexões acima de 80% do max_connections"

      - alert: PostgreSQLSlowQueries
        expr: rate(pg_stat_statements_total_time[5m]) > 1000
        for: 10m
        labels:
          severity: warning

      - alert: PostgreSQLReplicationLag
        expr: pg_replication_lag > 30
        for: 2m
        labels:
          severity: critical

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

# Verificar IOPS reais do volume
fio --name=randwrite --ioengine=libaio --iodepth=1 \
    --rw=randwrite --bs=4k --direct=1 --size=1G \
    --numjobs=1 --runtime=60 --time_based --filename=/var/lib/postgresql/data/test.fio

# Verificar configuração carregada
kubectl exec -it postgres-0 -n postgres -- \
  psql -U postgres -c "SHOW shared_buffers; SHOW max_connections;"

# Verificar autovacuum
kubectl exec -it postgres-0 -n postgres -- \
  psql -U postgres -c "SELECT schemaname, tablename, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY last_autovacuum DESC LIMIT 10;"