Postgres Operator (netcracker/pgskipper-operator)

Postgres Operator

https://github.com/netcracker/pgskipper-operator
Admin
Postgres Operator is a Kubernetes operator that provides PostgreSQL as a service on Kubernetes and...

Tokens:32,033
Snippets:279
Trust Score:9.5
Update:1 month ago
Show doc for...
Context Summary (auto-generated)
Raw
# Postgres Operator (pgskipper-operator)

Postgres Operator provides PostgreSQL as a service on Kubernetes and OpenShift platforms. It deploys highly available PostgreSQL clusters using Patroni for automatic failover and replication management. The operator manages the complete lifecycle of PostgreSQL deployments including provisioning, scaling, backup/restore, monitoring, and disaster recovery across multiple clusters.

The solution consists of two main Helm charts: **Patroni Core** for the core PostgreSQL/Patroni cluster and **Patroni Services** for supplementary services like backup daemon, DBaaS adapter, monitoring agent, connection pooler (PGBouncer), and query exporter. The operator uses Kubernetes Custom Resource Definitions (CRDs) to manage PostgreSQL clusters declaratively.

## Installation

### Installing CRDs

Install the Custom Resource Definitions before deploying the Helm charts.

```bash
# Install PatroniCore CRD
kubectl create -f ./charts/patroni-core/crds/netcracker.com_patronicores.yaml

# Install PatroniServices CRD
kubectl create -f ./charts/patroni-services/crds/netcracker.com_patroniservices.yaml
```

### Installing Patroni Core with Helm

Deploy the core PostgreSQL cluster using Patroni with high availability.

```bash
# Install Patroni Core with default configuration
helm install --namespace=postgres --create-namespace \
  -f ./charts/patroni-core/patroni-core-quickstart-sample.yaml \
  patroni-core ./charts/patroni-core

# Verify installation
kubectl -n postgres get pods --selector=app=patroni --field-selector=status.phase=Running

# Check leader promotion
kubectl -n postgres get pods --selector=pgtype=master --field-selector=status.phase=Running
```

### Installing Patroni Services with Helm

Deploy supplementary services after the Patroni Core cluster is ready.

```bash
# Install Patroni Services
helm install --namespace=postgres \
  -f ./charts/patroni-services/patroni-services-quickstart-sample.yaml \
  patroni-services ./charts/patroni-services

# Verify installation
kubectl -n postgres get pods --selector=name=postgres-operator --field-selector=status.phase=Running
```

## PatroniCore Custom Resource

### Defining a PatroniCore Resource

The PatroniCore CRD defines the PostgreSQL cluster configuration with Patroni.

```yaml
apiVersion: netcracker.com/v1
kind: PatroniCore
metadata:
  name: patroni-core
  namespace: postgres
spec:
  patroni:
    clusterName: patroni
    replicas: 2
    image: ghcr.io/netcracker/pgskipper-docker-patroni-16:main
    resources:
      requests:
        cpu: 125m
        memory: 250Mi
      limits:
        cpu: 250m
        memory: 500Mi
    storage:
      type: provisioned
      size: 5Gi
      storageClass: local-storage
    postgreSQLParams:
      - "password_encryption: md5"
      - "max_connections: 200"
      - "shared_preload_libraries: pg_stat_statements, pg_hint_plan, pg_cron"
      - "tcp_keepalives_idle: 300"
      - "tcp_keepalives_interval: 10"
      - "tcp_keepalives_count: 5"
      - "wal_level: logical"
    patroniParams:
      - "failsafe_mode: true"
      - "primary_start_timeout: 30"
      - "retry_timeout: 600"
    enableShmVolume: true
  tls:
    enabled: false
  authSecret: postgres-credentials
```

### Checking PatroniCore Status

Query the installation status using kubectl.

```bash
# Check installation status
kubectl -n postgres get patronicore patroni-core \
  -o jsonpath='{.metadata.name}{"\t"}{.status.conditions[?(@.type=="Successful")].lastTransitionTime}'

# Get all conditions
kubectl -n postgres get patronicore patroni-core \
  -o=jsonpath='{range .status.conditions[*]}{.type }{"\t"}{.lastTransitionTime}{"\t"}{.message}{"\n"}{end}'
```

## PatroniServices Custom Resource

### Defining a PatroniServices Resource

The PatroniServices CRD configures supplementary services for PostgreSQL.

```yaml
apiVersion: netcracker.com/v1
kind: PatroniServices
metadata:
  name: patroni-services
  namespace: postgres
spec:
  patroni:
    clusterName: patroni
  backupDaemon:
    image: ghcr.io/netcracker/pgskipper-docker-backup-daemon:main
    resources:
      requests:
        cpu: 100m
        memory: 256Mi
      limits:
        cpu: 450m
        memory: 768Mi
    storage:
      type: provisioned
      size: 10Gi
    backupSchedule: "0 0/7 * * *"
    evictionPolicy: "7d/delete"
  metricCollector:
    image: ghcr.io/netcracker/pgskipper-docker-monitoring-agent:main
    collectionInterval: 60
    metricsProfile: prod
  dbaas:
    install: true
    aggregator:
      registrationAddress: "http://dbaas:8080"
      registrationUsername: "user"
      registrationPassword: "password"
```

## Storage Configuration

### Provisioned Storage with StorageClass

Configure dynamic volume provisioning for PostgreSQL data.

```yaml
patroni:
  storage:
    type: provisioned
    size: 10Gi
    storageClass: fast-ssd
```

### Manual PV Configuration

Configure pre-created persistent volumes for PostgreSQL nodes.

```yaml
patroni:
  storage:
    type: pv
    size: 10Gi
    volumes:
      - postgresql-pv-1
      - postgresql-pv-2
    nodes:
      - db-node1
      - db-node2
```

## Backup and Restore

### Backup Daemon Configuration

Configure the backup daemon for scheduled backups.

```yaml
backupDaemon:
  install: true
  backupSchedule: "0 0/7 * * *"
  evictionPolicy: "7d/delete"
  evictionBinaryPolicy: "7d/delete"
  archiveEvictionPolicy: "7d"
  walArchiving: false
  storage:
    type: provisioned
    size: 50Gi
    storageClass: backup-storage
```

### Backup Daemon REST API

Trigger and manage backups using the backup daemon API.

```bash
# Trigger a full backup
curl -XPOST http://postgres-backup-daemon:8080/backup
# Response: {"accepted": true, "reason": "http-request", "backup_requests_in_queue": 1, "message": "PostgreSQL backup has been scheduled successfully.", "backup_id": "20240913T1104"}

# List available backups
curl -GET http://postgres-backup-daemon:8080/backup

# Trigger granular backup for specific database
curl -XPOST -H "Content-Type: application/json" \
  -d '{"database": "mydb"}' \
  http://postgres-backup-daemon:8080/backup/granular
```

### pgBackRest Configuration

Configure pgBackRest for enterprise backup with S3 storage.

```yaml
pgBackRest:
  repoType: "s3"
  repoPath: "/var/lib/pgbackrest"
  diffSchedule: "30 0/1 * * *"
  incrSchedule: "30 0/1 * * *"
  fullRetention: 5
  diffRetention: 3
  s3:
    bucket: "pgbackrest-backup"
    endpoint: "https://s3.amazonaws.com"
    key: "AWS_ACCESS_KEY"
    secret: "AWS_SECRET_KEY"
    region: "us-east-1"
    verifySsl: true
```

### pgBackRest with RWX Storage

Configure pgBackRest with ReadWriteMany storage.

```yaml
pgBackRest:
  repoType: "rwx"
  repoPath: "/var/lib/pgbackrest"
  diffSchedule: "30 0/1 * * *"
  incrSchedule: "30 0/1 * * *"
  rwx:
    type: provisioned
    size: 50Gi
    storageClass: nfs-storage
```

### Point-in-Time Recovery

Restore PostgreSQL to a specific point in time.

```bash
# Connect to backup daemon pod
kubectl exec -it postgres-backup-daemon-xxx -- /bin/bash

# Restore to specific timestamp
export TYPE=time
export TARGET='2024-10-23 14:11:04+00'
cd /maintenance/recovery/ && python3 pg_back_rest_recovery.py

# Restore to specific LSN
export TYPE=lsn
export TARGET='0/14000138'
cd /maintenance/recovery/ && python3 pg_back_rest_recovery.py

# Restore to specific transaction ID
export TYPE=xid
export TARGET='12345'
cd /maintenance/recovery/ && python3 pg_back_rest_recovery.py
```

## TLS Configuration

### Auto-Generated Certificates with cert-manager

Configure automatic TLS certificate generation using cert-manager.

```yaml
tls:
  enabled: true
  certificateSecretName: pg-cert
  generateCerts:
    enabled: true
    clusterIssuerName: letsencrypt-prod
    duration: 365
    subjectAlternativeName:
      additionalDnsNames:
        - pg-patroni.postgres.svc.cluster.local
        - postgres.example.com
      additionalIpAddresses:
        - 10.0.0.100
```

### Manual TLS Certificate Configuration

Configure TLS with manually provided certificates.

```bash
# Generate certificates
openssl req -new -x509 -days 365 -nodes -text \
  -out server.crt -keyout server.key \
  -subj "/CN=pg-patroni.postgres.svc.cluster.local"

# Create Kubernetes secret
kubectl create secret generic pg-cert \
  --from-file=tls.key=server.key \
  --from-file=tls.crt=server.crt \
  -n postgres
```

```yaml
tls:
  enabled: true
  certificateSecretName: pg-cert
  generateCerts:
    enabled: false
  certificates:
    tls_key: "LS0tLS1CRUdJTi..."  # Base64 encoded private key
    tls_crt: "LS0tLS1CRUdJTi..."  # Base64 encoded certificate
    ca_crt: "LS0tLS1CRUdJTi..."   # Base64 encoded CA certificate
```

## Disaster Recovery

### Active Cluster Configuration

Configure the active PostgreSQL cluster in a DR setup.

```yaml
patroni:
  clusterName: patroni
  # No standbyCluster configuration for active site

siteManager:
  install: true
  activeClusterHost: "pg-patroni.postgres.svc.cluster-2.local"
  activeClusterPort: 5432
  httpAuth:
    enabled: true
    smNamespace: "site-manager"
    smServiceAccountName: "sm-auth-sa"
```

### Standby Cluster Configuration

Configure the standby PostgreSQL cluster in a DR setup.

```yaml
patroni:
  clusterName: patroni
  standbyCluster:
    host: "pg-patroni.postgres.svc.cluster-1.local"
    port: 5432

siteManager:
  install: true
  activeClusterHost: "pg-patroni.postgres.svc.cluster-1.local"
  activeClusterPort: 5432
  httpAuth:
    enabled: true
    smNamespace: "site-manager"
```

### Site Manager REST API

Manage DR switchover and failover using the Site Manager API.

```bash
# Get current cluster status
curl -GET -H "Authorization: Bearer $TOKEN" \
  http://postgres-operator.postgres:8080/sitemanager
# Response: {"mode": "active", "status": "done"}

# Check cluster health
curl -GET -H "Authorization: Bearer $TOKEN" \
  http://postgres-operator.postgres:8080/health
# Response: {"status": "up"}

# Switch to active mode (promote standby)
curl -XPOST -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"mode": "active"}' \
  http://postgres-operator.postgres:8080/sitemanager

# Switch to standby mode
curl -XPOST -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"mode": "standby"}' \
  http://postgres-operator.postgres:8080/sitemanager

# Disable Patroni cluster
curl -XPOST -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"mode": "disabled"}' \
  http://postgres-operator.postgres:8080/sitemanager
```

## Connection Pooler (PGBouncer)

### PGBouncer Configuration

Configure PGBouncer connection pooler for connection management.

```yaml
connectionPooler:
  install: true
  replicas: 2
  resources:
    requests:
      cpu: 200m
      memory: 256Mi
    limits:
      cpu: 500m
      memory: 512Mi
  config:
    databases:
      '*': "host=pg-patroni-direct port=5432"
    pgbouncer:
      listen_port: '6432'
      listen_addr: '0.0.0.0'
      auth_type: 'md5'
      auth_file: '/etc/pgbouncer/userlist.txt'
      auth_user: 'pgbouncer'
      auth_query: 'SELECT p_user, p_password FROM public.lookup($1)'
      ignore_startup_parameters: 'options,extra_float_digits'
      pool_mode: 'transaction'
      max_client_conn: '1000'
      default_pool_size: '20'
```

## DBaaS Adapter

### DBaaS Adapter Configuration

Configure the DBaaS adapter for integration with DBaaS Aggregator.

```yaml
dbaas:
  install: true
  multiUsers: true
  pgHost: pg-patroni.postgres
  pgPort: 5432
  dbName: postgres
  aggregator:
    registrationAddress: "http://dbaas-aggregator.dbaas:8080"
    registrationUsername: "admin"
    registrationPassword: "secretpassword"
    physicalDatabaseIdentifier: "postgres:patroni"
  adapter:
    username: "dbaas-aggregator"
    password: "dbaas-aggregator"
    address: "http://dbaas-adapter.postgres:8080"
  extensions:
    - "postgres_fdw"
    - "pgcrypto"
    - "uuid-ossp"
  labels:
    clusterName: "patroni"
    environment: "production"
  apiVersion: v2
```

## Monitoring and Metrics

### Metric Collector Configuration

Configure the PostgreSQL monitoring agent.

```yaml
metricCollector:
  install: true
  collectionInterval: 60
  scrapeTimeout: 20
  metricsProfile: prod  # or 'dev' for additional metrics
  prometheusMonitoring: true
  applyGrafanaDashboard: true
  prometheusRules:
    backupAlertThreshold: 5
    backupWarningThreshold: 20
    alertDelay: 3m
    maxLastBackupAge: 86400
    locksThreshold: 500
    queryMaxTimeThreshold: 3600
    replicationLagValue: 33554432
    maxConnectionExceedPercentageThreshold: 90
    maxConnectionReachedPercentageThreshold: 80
```

### Query Exporter Configuration

Configure Query Exporter for custom Prometheus metrics.

```yaml
queryExporter:
  install: true
  queryTimeout: 30
  maxMasterConnections: 10
  maxLogicalConnections: 1
  pgUser: query-exporter
  pgPassword: "ExporterPassword123"
  selfMonitorDisabled: false
  collectionInterval: 60
  customQueries:
    enabled: true
    namespacesList:
      - postgres
      - monitoring
    labels:
      query-exporter: "custom-queries"
  excludeQueries:
    - "pg_lock_tree_query"
```

### Custom Query Configuration

Define custom queries for Query Exporter.

```yaml
# ConfigMap with custom queries
apiVersion: v1
kind: ConfigMap
metadata:
  name: custom-queries
  namespace: monitoring
  labels:
    query-exporter: custom-queries
data:
  queries.yaml: |
    metrics:
      pg_database_size:
        type: gauge
        labels:
          - datname
        description: Database size in bytes
    queries:
      database_size:
        databases:
          - master
        metrics:
          - pg_database_size
        sql: SELECT datname, pg_database_size(datname) as pg_database_size FROM pg_database WHERE datistemplate = false
```

## LDAP Integration

### LDAP Configuration

Configure LDAP authentication for PostgreSQL.

```yaml
ldap:
  enabled: true
  server: ldap.example.com
  port: 389
  basedn: "dc=example,dc=com"
  binddn: "cn=admin,dc=example,dc=com"
  bindpasswd: "adminpassword"
  ldapsearchattribute: "sAMAccountName"
```

## Major Upgrade

### PostgreSQL Major Version Upgrade

Configure major version upgrade from PostgreSQL 14 to 15.

```yaml
patroni:
  majorUpgrade:
    enabled: true
    initDbParams: "--encoding=UTF8 --data-checksums --lc-collate=C --lc-ctype=C"
    dockerUpgradeImage: ghcr.io/netcracker/pgskipper-docker-upgrade:main
  dockerImage: ghcr.io/netcracker/pgskipper-docker-patroni-15:main
```

### Validation After Upgrade

Verify upgrade completion and update extensions.

```bash
# Check for update_extensions.sql
kubectl exec -it pg-patroni-master-xxx -- cat /var/lib/pgsql/data/update_extensions.sql

# Execute extension updates
kubectl exec -it pg-patroni-master-xxx -- psql -f /var/lib/pgsql/data/update_extensions.sql
```

## Connecting to PostgreSQL

### Port Forwarding Connection

Connect to PostgreSQL using kubectl port-forward.

```bash
# Get master pod and create port forward
PG_MASTER_POD=$(kubectl get pod -n postgres -o name -l app=patroni,pgtype=master)
kubectl -n postgres port-forward "${PG_MASTER_POD}" 5432:5432

# Connect with psql using credentials from secret
PGUSER=$(kubectl get secrets -n postgres "postgres-credentials" -o go-template='{{.data.username | base64decode}}')
PGPASSWORD=$(kubectl get secrets -n postgres "postgres-credentials" -o go-template='{{.data.password | base64decode}}')
psql -h localhost -U $PGUSER
```

### Service-Based Connection

Connect using Kubernetes services.

```bash
# Connect to leader (read-write)
psql -h pg-patroni.postgres.svc.cluster.local -U postgres

# Connect to replicas (read-only)
psql -h pg-patroni-ro.postgres.svc.cluster.local -U postgres

# Connect through PGBouncer
psql -h pgbouncer.postgres.svc.cluster.local -p 6432 -U postgres

# Connect with TLS
psql "host=pg-patroni.postgres.svc.cluster.local sslmode=require sslcert=/path/to/cert sslkey=/path/to/key"
```

## External Managed Databases

### Google CloudSQL Configuration

Configure Postgres Operator for Google CloudSQL.

```yaml
externalDataBase:
  type: cloudsql
  project: my-gcp-project
  instance: postgres-instance
  port: 5432
  region: us-central1
  connectionName: my-gcp-project:us-central1:postgres-instance
  authSecretName: cloudsql-credentials
  applyGrafanaDashboard: true
```

### AWS RDS Configuration

Configure Postgres Operator for AWS RDS.

```yaml
externalDataBase:
  type: rds
  instance: postgres-instance
  port: 5432
  region: us-east-1
  authSecretName: rds-credentials
  accessKeyId: "AKIAIOSFODNN7EXAMPLE"
  secretAccessKey: "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY"
```

### Azure Flexible Server Configuration

Configure Postgres Operator for Azure Database for PostgreSQL.

```yaml
externalDataBase:
  type: azure
  instance: postgres-flex-server
  port: 5432
  region: eastus
  authSecretName: azure-pg-credentials
  restoreConfig:
    mirror.subnet: "vnet/subnet"
```

## Cluster Deletion

### Uninstalling PostgreSQL Cluster

Remove the PostgreSQL cluster and all resources.

```bash
# Uninstall Helm releases
helm uninstall patroni-services -n postgres
helm uninstall patroni-core -n postgres

# Delete CRDs (optional)
kubectl delete crd patronicores.netcracker.com
kubectl delete crd patroniservices.netcracker.com

# Delete namespace (removes all resources)
kubectl delete namespace postgres
```

## Summary

Postgres Operator provides a comprehensive solution for running production-grade PostgreSQL clusters on Kubernetes and OpenShift. Key use cases include deploying highly available PostgreSQL clusters with automatic failover using Patroni, implementing disaster recovery across multiple Kubernetes clusters with automated switchover capabilities, and managing enterprise backup strategies using pgBackRest with S3 or RWX storage. The operator supports integration with DBaaS platforms through its adapter component, enabling self-service database provisioning.

The operator follows Kubernetes-native patterns using Custom Resource Definitions for declarative cluster management. Integration patterns include using the Site Manager API for DR orchestration, Query Exporter for custom Prometheus metrics, and the backup daemon REST API for backup automation. The solution supports both on-premise deployments and managed cloud databases (AWS RDS, Google CloudSQL, Azure Flexible Server), making it suitable for hybrid cloud environments. Connection pooling through PGBouncer, TLS encryption with cert-manager integration, and LDAP authentication provide enterprise-ready security and performance features.