# 最简启动
docker run -d --name pg \
  -e POSTGRES_PASSWORD=mypassword \
  -p 5432:5432 \
  postgres:17

# 生产级配置
docker run -d --name pg \
  --restart=always \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=securepass \
  -e POSTGRES_DB=appdb \
  -e PGDATA=/var/lib/postgresql/data/pgdata \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  --shm-size=256m \
  postgres:17 \
    -c shared_buffers=512MB \
    -c effective_cache_size=1536MB \
    -c max_connections=200 \
    -c work_mem=16MB \
    -c random_page_cost=1.1

version: "3.8"

services:
  postgres:
    image: postgres:17
    container_name: pg-main
    restart: always
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: ${PG_PASSWORD}
      POSTGRES_DB: appdb
      PGDATA: /var/lib/postgresql/data/pgdata
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d
      - ./backup:/backup
    command:
      - "postgres"
      - "-c"
      - "shared_buffers=1GB"
      - "-c"
      - "effective_cache_size=3GB"
      - "-c"
      - "max_connections=200"
      - "-c"
      - "wal_level=replica"
    shm_size: 256m
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U appuser -d appdb"]
      interval: 10s
      timeout: 5s
      retries: 5
    networks:
      - backend

  pgadmin:
    image: dpage/pgadmin4:latest
    container_name: pg-admin
    restart: always
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: admin
    ports:
      - "5050:80"
    depends_on:
      postgres:
        condition: service_healthy
    networks:
      - backend

  pgbouncer:
    image: edoburu/pgbouncer:latest
    container_name: pg-pool
    restart: always
    environment:
      DATABASE_URL: "postgres://appuser:${PG_PASSWORD}@postgres:5432/appdb"
      POOL_MODE: transaction
      MAX_CLIENT_CONN: 1000
      DEFAULT_POOL_SIZE: 50
    ports:
      - "6432:5432"
    depends_on:
      - postgres
    networks:
      - backend

volumes:
  pgdata:
    driver: local

networks:
  backend:
    driver: bridge

# pg-statefulset.yaml
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  replicas: 3
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:17
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: pg-secret
                  key: password
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          volumeMounts:
            - name: pgdata
              mountPath: /var/lib/postgresql/data
          resources:
            requests:
              memory: "2Gi"
              cpu: "1"
            limits:
              memory: "4Gi"
              cpu: "2"
          readinessProbe:
            exec:
              command: ["pg_isready", "-U", "postgres"]
            initialDelaySeconds: 5
            periodSeconds: 10
          livenessProbe:
            exec:
              command: ["pg_isready", "-U", "postgres"]
            initialDelaySeconds: 30
            periodSeconds: 10
  volumeClaimTemplates:
    - metadata:
        name: pgdata
      spec:
        accessModes: ["ReadWriteOnce"]
        storageClassName: fast-ssd
        resources:
          requests:
            storage: 100Gi
---
apiVersion: v1
kind: Service
metadata:
  name: postgres
spec:
  type: ClusterIP
  clusterIP: None
  selector:
    app: postgres
  ports:
    - port: 5432
      targetPort: 5432

# pg-cluster.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pg-cluster
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:17

  postgresql:
    parameters:
      shared_buffers: "1GB"
      effective_cache_size: "3GB"
      max_connections: "200"
    pg_hba:
      - host all all 10.0.0.0/8 scram-sha-256

  bootstrap:
    initdb:
      database: appdb
      owner: appuser

  storage:
    storageClass: fast-ssd
    size: 100Gi

  resources:
    requests:
      memory: "2Gi"
      cpu: "1"
    limits:
      memory: "4Gi"
      cpu: "2"

  monitoring:
    enablePodMonitor: true

  backup:
    barmanObjectStore:
      destinationPath: "s3://pg-backups/cluster1"
      s3Credentials:
        accessKeyId:
          name: s3-creds
          key: ACCESS_KEY_ID
        secretAccessKey:
          name: s3-creds
          key: ACCESS_SECRET_KEY
    retentionPolicy: "30d"