Highly available and scalable PostgreSQL on Kubernetes (k8s) with the Crunchy PostgreSQL Operator (pgo).

Prerequisites

Installing helm

Linux (Debian/Ubuntu)

curl https://baltocdn.com/helm/signing.asc | sudo apt-key add -
sudo apt-get install apt-transport-https --yes
echo "deb https://baltocdn.com/helm/stable/debian/ all main" | sudo tee /etc/apt/sources.list.d/helm-stable-debian.list
sudo apt-get update
sudo apt-get install helm

Mac OSX

curl -o helm.tar.gz https://get.helm.sh/helm-v3.4.1-darwin-amd64.tar.gz
tar -zxvf helm-v3.4.1-darwin-amd64.tar.gz
cd darwin-amd64/
sudo mv helm /usr/local/bin/helm
sudo chmod +x /usr/local/bin/helm

Installing pgo

git clone https://github.com/CrunchyData/postgres-operator.git
cd installers/kubectl/client-setup.sh
chmod +x client-setup.sh
./client-setup.sh
export PATH="$PATH:${HOME?}/.pgo/pgo/"
export PGOUSER="${HOME?}/.pgo/pgo/pgouser"
export PGO_CA_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_CERT="${HOME?}/.pgo/pgo/client.crt"
export PGO_CLIENT_KEY="${HOME?}/.pgo/pgo/client.key"
export PGO_APISERVER_URL='https://127.0.0.1:8443'
export PGO_NAMESPACE=pgo
source ~/.bashrc

Installing pgo operator

cd postgres-operator/installers/helm
helm install postgres-operator . -n pgo
kubectl -n pgo get pods
NAME                                READY   STATUS    RESTARTS   AGE
postgres-operator-56d6ccb97-tmz7m 4/4 Running 0 2m
kubectl -n pgo port-forward svc/postgres-operator 8443:8443
pgo client version 4.5.1
pgo-apiserver version 4.5.1

Installing pgo metrics

Showing the Grafana dashboard for pgo
cd postgres-operator/installers/metrics/helm
helm install metrics . -n pgo
kubectl -n pgo get poNAME                               READY   STATUS    RESTARTS   AGE
postgres-operator-689598d795 4/4 Running 1 7h
kubectl -n pgo port-forward --address 0.0.0.0 svc/crunchy-grafana 3000:3000

Creating your first cluster

pgo create cluster zercurity --replica-count=2 \
--pgbouncer --pgbadger --metrics --username zercurity \
--sync-replication --database=zercurity --pvc-size=150Gi \
--pgbackrest-pvc-size=500Gi
pgo test <zercurity>cluster : zercurity
Services
primary (100.66.14.31:5432): UP
pgbouncer (100.69.208.231:5432): UP
replica (100.67.75.10:5432): UP
Instances
replica (zercurity-79cf8bc9c-499jp): UP
replica (zercurity-vcvm-66cfb945f5-5f4hx): UP
replica (zercurity-xfrx-69b4f4bbc-69vtr): UP
pgo show pgbouncer <zercurity>CLUSTER   SERVICE             USERNAME  PASSWORD  CLUSTER IP
--------- ------------------- --------- ------------------------
zercurity zercurity-pgbouncer pgbouncer password 100.69.208.231
pgo show user -n pgo <zercurity> --show-system-accounts

Connecting to your cluster

kubectl -n pgo port-forward svc/<zercurity> 5432:5432
psql -h localhost -p 5432 -U <username> <zercurity>

Installing pgadmin

pgo create pgadmin <zercurity>
kubectl -n pgo get poNAME                               READY   STATUS    RESTARTS   AGE
...
zercurity-pgadmin-5464c6545-k99n4 1/1 Running 0 7h
kubectl port-forward svc/zercurity-pgadmin 5050:5050
pgadmin installed with pgo.

Updating & scaling your cluster

pgo update cluster <zercurity> --cpu=2.0 --cpu-limit=4.0 \
--memory=4Gi --memory-limit=6Gi

Scaling up

pgo scale <zercurity> --replica-count=1

Scaling down

pgo scaledown <zercurity> --query

pgo scaledown <zercurity> --target=zercurity-replica-xxxx

Backups

pgo backup <zercurity>
pgo backup <zercurity> --backup-opts="--type=full"
pgo show backup <zercurity>cluster: zercurity
storage type: local
stanza: db
status: ok
cipher: none
db (current)
wal archive min/max (12-1)
full backup: 20201120-222158F
timestamp start/stop: 2020-11-20 / 2020-11-20
wal start/stop: 00000001 / 00000001
database size: 31.3MiB, backup size: 31.3MiB
repository size: 3.8MiB, repository backup size: 3.8MiB
backup reference list:Upgrading pgo

Upgrading the Postgres Operator & pgo

cd postgres-operator/installers/helm
helm upgrade postgres-operator . -n pgo
wget -o /usr/local/bin/pgo https://github.com/CrunchyData/postgres-operator/releases/download/v4.4.2/pgo
chmod +x /usr/local/bin/pgo

Its all over!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store