postgresql数据库搭建

Postgresql是一个功能强大的开源对象关系数据库管理系统(ORDBMS)，它是以加州大学计算机系开发的POSTGRES，4.2版本为基础的对象关系数据库管理系统。POSTGRES的许多领先概念只是在商业数据库中实现，而PostgreSQL是一个真正的开源数据库，它的源代码是开放的，任何人都可以免费使用。

特性：

复杂的查询
支持多版本并发控制
支持外键、触发器、视图、事务
支持函数和存储过程
支持大型对象
支持表空间
支持异步复制
支持表继承
支持规则
支持自定义数据类型
支持自定义索引方法
支持Unicode

Kubernetes部署

kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgresql
  namespace: stable-db
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 50Gi
  storageClassName: standard-rwo
---
apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgresql
  namespace: stable-db
  labels:
    app: postgresql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgresql
  template:
    metadata:
      labels:
        app: postgresql
    spec:
      volumes:
        - name: postgresql
          persistentVolumeClaim:
            claimName: postgresql
      containers:
        - name: postgresql
          image: postgres:9.6.21
          ports:
            - name: tcp-0
              containerPort: 5432
              protocol: TCP
          env:
            - name: POSTGRES_USER
              value: <username>
            - name: POSTGRES_PASSWORD
              value: <password>
            - name: POSTGRES_DB
              value: <database>
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
          resources:
            limits:
              cpu: "1"
              memory: 2Gi
            requests:
              cpu: "1"
              memory: 2Gi
          volumeMounts:
            - name: postgresql
              mountPath: /var/lib/postgresql/data
              subPath: pgdata
          livenessProbe:
            tcpSocket:
              port: 5432
            initialDelaySeconds: 30
            timeoutSeconds: 1
            periodSeconds: 10
            successThreshold: 1
            failureThreshold: 3
          startupProbe:
            tcpSocket:
              port: 5432
            initialDelaySeconds: 30
            timeoutSeconds: 1
            periodSeconds: 10
            successThreshold: 1
            failureThreshold: 3
          terminationMessagePath: /dev/termination-log
          terminationMessagePolicy: File
          imagePullPolicy: Always
      restartPolicy: Always
      terminationGracePeriodSeconds: 30
      dnsPolicy: ClusterFirst

docker-compose 部署

version: '3'
services:
  postgresql:
    image: postgres:9.6.21
    deploy:
      replicas: 1
      resources:
        limits:
          cpus: '1'
          memory: 2G
        reservations:
          cpus: '1'
          memory: 2G
    container_name: postgresql
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_USER=<username>
      - POSTGRES_PASSWORD=<password>
      - POSTGRES_DB=<database>
      - PGDATA=/var/lib/postgresql/data/pgdata
    volumes:
      - /data/postgresql:/var/lib/postgresql/data/pgdata

可以选择把pg_hba.conf挂载出来，方便修改。

1
2
3

volumes:
  - /data/postgresql:/var/lib/postgresql/data/pgdata
  - ./conf/pg_hba.conf:/var/lib/postgresql/data/pgdata/pg_hba.conf

内容如：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               trust
host    all             all             0.0.0.0/0               md5