Try Live
Add Docs
Rankings
Pricing
Docs
Install
Install
Docs
Pricing
More...
More...
Try Live
Rankings
Enterprise
Create API Key
Add Docs
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
Context
Skills
Chat
Benchmark
62.8
Suggestions
Latest
Show doc for...
Code
Info
Show Results
Context Summary (auto-generated)
Raw
Copy
Link
# 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.