# Zalando Postgres Operator

DANGER

This is not complete !

The Postgres Operator enables highly-available PostgreSQL (opens new window) clusters on Kubernetes (K8s) powered by Patroni (opens new window). It is configured only through manifests to ease integration into automated CI/CD pipelines with no access to Kubernetes directly.

# Operator features

Rolling updates on Postgres cluster changes
Volume resize without Pod restarts
Cloning Postgres clusters
Logical Backups to S3 Bucket
Standby cluster from S3 WAL archive
Configurable for non-cloud environments
UI to create and edit Postgres cluster manifests

# PostgreSQL Operator features

Supports PostgreSQL 9.6+
Streaming replication cluster via Patroni
Point-In-Time-Recovery with pg_basebackup (opens new window) / WAL-E (opens new window) via Spilo (opens new window)
Preload libraries: bg_mon (opens new window), pg_stat_statements (opens new window), pgextwlist (opens new window), pg_auth_mon (opens new window)
InclUDES popular Postgres extensions such as decoderbufs (opens new window), hypopg (opens new window), pg_cron (opens new window), pg_partman (opens new window), pg_stat_kcache (opens new window), pgq (opens new window), plpgsql_check (opens new window), postgis (opens new window), set_user (opens new window) and timescaledb (opens new window)

The Postgres Operator has been developed at Zalando and is being used in production for over two years.

# Installation

Clone the repository

mkdir tmp
git clone https://github.com/zalando/postgres-operator.git tmp/zalando-postgres-operator
git -C tmp/zalando-postgres-operator checkout v1.2.0

Install the PostgreSQL Operator:

helm install --name zalando --namespace zalando tmp/zalando-postgres-operator/charts/postgres-operator \
  --set configKubernetes.cluster_domain=${MY_DOMAIN} \
  --set configLoadBalancer.db_hosted_zone=db.${MY_DOMAIN}

Create a Postgres cluster:

cat << EOF | kubectl apply -f -
apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: myteam-mytest-cluster5
  namespace: default
spec:
  teamId: "myteam"
  volume:
    size: 1Gi
  numberOfInstances: 2
  users:
    myuser:           # database owner
    - superuser
    - createdb
    # mytestdb_user: []
  enableMasterLoadBalancer: false
  enableReplicaLoadBalancer: false
  databases:
    mytestdb: myuser  # dbname: owner
  postgresql:
    version: "10"
EOF

Check the deployed cluster:

kubectl describe postgresql

Check created database pods and services:

kubectl get pods,svc -l application=spilo -L spilo-role

Allow access to the database using Istio:

cat << EOF | kubectl apply -f -
apiVersion: networking.istio.io/v1alpha3
kind: Gateway
metadata:
  name: pgsql-myteam-mytest-cluster-gateway
  namespace: default
spec:
  selector:
    istio: ingressgateway
  servers:
  - port:
      number: 5432
      name: pgsql-myteam-mytest-cluster
      protocol: TCP
    hosts:
    - pgsql.${MY_DOMAIN}
  - port:
      number: 5433
      name: pgsql-repl-myteam-mytest-cluster
      protocol: TCP
    hosts:
    - pgsql.${MY_DOMAIN}
---
apiVersion: networking.istio.io/v1alpha3
kind: VirtualService
metadata:
  name: pgsql-myteam-mytest-virtual-service
  namespace: default
spec:
  hosts:
  - pgsql.${MY_DOMAIN}
  gateways:
  - pgsql-myteam-mytest-cluster-gateway
  tcp:
  - match:
    - port: 5432
    route:
    - destination:
        host: myteam-mytest-cluster.default.svc.cluster.local
        port:
          number: 5432
  - match:
    - port: 5433
    route:
    - destination:
        host: myteam-mytest-cluster-repl.default.svc.cluster.local
        port:
          number: 5432
EOF

export PGHOST=pgsql.${MY_DOMAIN}
export PGPORT=5432
export PGPASSWORD=$(kubectl get secret postgres.myteam-mytest-cluster.credentials -o 'jsonpath={.data.password}' | base64 -d)
psql -U postgres

← Patroni