Aidbox User Docs
Run Aidbox locallyRun Aidbox in SandboxTalk to us Ask community
  • Aidbox FHIR platform documentation
    • Features
    • Architecture
  • Getting Started
    • Run Aidbox in Sandbox
    • Run Aidbox locally
    • Run Aidbox on AWS
    • Upload Sample Data
  • Tutorials
    • CRUD, Search Tutorials
      • Delete data
      • Set up uniqueness in Resource
      • Search Tutorials
        • Custom SearchParameter tutorial
        • Create custom Aidbox Search resource
        • Multilingual search tutorial
        • Migrate from Aidbox SearchParameter to FHIR SearchParameter
        • Change sort order by locale collation
    • Bulk API Tutorials
      • 🎓Synthea by Bulk API
      • 🎓$dump-sql tutorial
    • Security & Access Control Tutorials
      • Allow patients to see their own data
      • Restrict operations on resource type
      • Relationship-based access control
      • Creating user & set up full user access
      • Restricting Access to Patient Data
      • Create and test access control
      • RBAC
        • Flexible RBAC built-in to Aidbox
        • RBAC with JWT containing role
        • RBAC with ACL
      • Set-up token introspection
      • Prohibit user to login
      • Debug access control
      • Managing Admin Access to the Aidbox UI Using Okta Groups
      • Run Multibox locally
      • How to enable labels-based access control
      • How to enable patient data access API
      • How to enable SMART on FHIR on Patient Access API
      • How to enable hierarchical access control
      • How to configure Audit Log
    • Terminology Tutorials
      • Load ICD-10 terminology into Aidbox
      • Uploading IG terminology content to external FHIR terminology server
    • Validation Tutorials
      • Upload FHIR Implementation Guide
        • Environment Variable
        • Aidbox UI
          • IG Package from Aidbox Registry
          • Public URL to IG Package
          • Local IG Package
        • Aidbox FHIR API
        • UploadFIG Tool
      • ISiK
      • Carin BB
      • US Core
      • Davinci Pdex
      • mCode
    • Integration Toolkit Tutorials
      • Postmark integration tutorial
      • Mailgun integration tutorial
    • Subscriptions Tutorials
      • AidboxTopicSubscription NATS tutorial
    • Other tutorials
      • Run Aidbox with FHIR R6
      • Migrate from Multibox to Aidbox
      • SDC with Custom Resources
      • How to create FHIR NPM package
      • Migrate from legacy licence portal to Aidbox portal
      • How to run Aidbox in GCP Cloud Run
  • Overview
    • Licensing and Support
    • Aidbox user portal
      • Projects
      • Licenses
      • Members
    • Aidbox UI
      • Aidbox Notebooks
      • REST Console
      • Database Console
      • Attrs stats
      • DB Tables
      • DB Queries
    • Versioning
    • Release Notes
    • Contact us
  • Configuration
    • Settings
    • Configure Aidbox and Multibox
    • Init Bundle
  • API
    • REST API
      • CRUD
        • Create
        • Read
        • Update
        • Patch
        • Delete
      • FHIR Search
        • SearchParameter
        • Include and Revinclude
        • Chaining
      • Aidbox Search
      • Bundle
      • History
      • $everything on Patient
      • Other
        • Aidbox & FHIR formats
        • Capability Statement
        • $document
        • Observation/$lastn
        • $validate
        • SQL endpoints
        • $matcho
        • $to-format
        • Aidbox version
        • Health check
    • Bulk API
      • Configure Access Policies for Bulk API
      • $dump
      • $dump-sql
      • $dump-csv
      • $export
      • $load & /fhir/$load
      • $import & /fhir/$import
      • aidbox.bulk data import
      • Bulk import from an S3 bucket
    • Batch/Transaction
    • GraphQL API
    • Other APIs
      • Plan API
        • Provider Directory API
          • Practitioner
          • PractitionerRole
          • Organization
          • OrganizationAffiliation
        • Plan API Overview
      • Archive/Restore API
        • create-archive
        • restore-archive
        • prune-archived-data
        • delete-archive
      • ETAG support
      • Cache
      • Changes API
      • RPC API
      • Sequence API
      • Encryption API
      • Batch Upsert
  • Modules
    • Profiling and validation
      • FHIR Schema Validator
        • Aidbox FHIR IGs Registry
        • Setup Aidbox with FHIR Schema validation engine
      • Skip validation of references in resource using request header
      • Asynchronous resource validation
    • Security & Access Control
      • Authentication Flows
        • Basic Auth
        • Client Credentials Grant
        • Resource Owner Grant
        • Authorization Code Grant
        • Implicit Grant
        • Two Factor Authentication
        • External OAuth 2.0 Providers
        • Token Exchange
      • External identity providers
        • Aidbox
        • Okta
        • Azure AD
        • Azure AD with certificate authentication
        • Keycloak
        • GitHub
        • Microsoft AD FS
        • Apple
      • Access Control
        • AccessPolicy
        • Evaluation engines
        • Role-Based Access Control (/RBAC)
        • Attribute-based Access Control (/ABAC)
        • Multitenancy
        • Access control lists (/ACL)
        • Access policy dev tool
        • AccessPolicy best practices
      • Audit
        • Audit Log
    • Observability
      • Getting started
        • Run Aidbox with OpenTelemetry locally
        • How to export telemetry to the OTEL collector
      • Logs
        • How-to guides
          • OpenTelemetry logs
          • Elastic Logs and Monitoring Integration
          • Datadog Log management integration
          • Loki Log management integration
        • Tutorials
          • Log analysis and visualization tutorial
          • Export logs to Datadog tutorial
        • Extending Aidbox Logs
        • Technical reference
          • Log appenders
          • Log transformations
          • Log Schema
          • OTEL logs exporter parameters
      • Metrics
        • How-to guides
          • How to export metrics to the OTEL collector
          • Use Aidbox Metrics Server
          • Set-up Grafana integration
        • Technical reference
          • OpenTelemetry Metrics
          • OTEL metrics exporter parameters
      • Traces
        • How to use tracing
        • OTEL traces exporter parameters
    • Subscriptions
      • Aidbox topic-based subscriptions
        • Kafka AidboxTopicDestination
        • Webhook AidboxTopicDestination
        • GCP Pub/Sub AidboxTopicDestination
        • Tutorial: produce QuestionnaireResponse to Kafka topic
      • Aidbox SubSubscriptions
    • Aidbox Forms
      • Getting started
      • Aidbox Forms Interface
      • Aidbox UI Builder
        • UI Builder Interface
        • Form creation
          • Form Settings
          • Widgets
          • Components
          • Versioning
          • Form customisation in Theme Editor
          • Form signature
          • How-to guides
            • How to: populate forms with data
            • How to extract data from forms
            • How to calculate form filling percentage
          • Multilingual forms
          • FHIRPath Editor
        • Import Questionnaire
        • Form sharing
        • Printing forms
          • Template-based PDF generation
        • FHIR versions
        • Offline forms
        • Embedding
          • Request Interception
        • Configuration
        • Forms multitenancy
        • Building reports using SQL on FHIR
        • Integration with external terminology servers
        • External FHIR servers as a data backend
        • Store attachments in S3-like storages
      • Access Control in Forms
      • Audit Logging in Forms
      • Aidbox Form Gallery
    • Define extensions
      • Extensions using StructureDefinition
      • Extensions using FHIRSchema
    • Custom Resources
      • Custom resources using FHIR Schema
      • Custom resources using StructureDefinition
      • Migrate to FHIR Schema
        • Migrate custom resources defined with Entity & Attributes to FHIR Schema
        • Migrate custom resources defined with Zen to FHIR Schema
    • Aidbox terminology module
      • Concept
        • $translate-concepts
        • Handling hierarchies using ancestors
      • ValueSet
        • ValueSet Expansion
        • ValueSet Code Validation
        • Create a ValueSet
      • CodeSystem
        • CodeSystem Concept Lookup
        • CodeSystem Subsumption testing
        • CodeSystem Code Composition
      • Import external terminologies
        • Import flat file (/CSV)
        • $import operation
        • Ready-to-use terminologies
      • $translate on ConceptMap
    • SQL on FHIR
      • Defining flat views with View Definitions
      • Query data from flat views
      • Reference
    • Integration toolkit
      • C-CDA / FHIR Converter
        • List of supported templates
          • Admission Diagnosis Section (/V3)
          • Advance Directives Section (/entries optional) (/V3)
          • Advance Directives Section (/entries required) (/V3)
          • Allergies and Intolerances Section (/entries optional) (/V3)
          • Allergies and Intolerances Section (/entries required) (/V3)
          • Assessment Section
          • Chief Complaint Section
          • Chief Complaint and Reason for Visit Section
          • Complications Section (/V3)
          • Course of Care Section
          • DICOM Object Catalog Section - DCM 121181
          • Default Section Rules
          • Discharge Diagnosis Section (/V3)
          • Document Header
          • Encounters Section (/entries optional) (/V3)
          • Encounters Section (/entries required) (/V3)
          • Family History Section (/V3)
          • Functional Status Section (/V2)
          • General Status Section
          • Goals Section
          • Health Concerns Section (/V2)
          • History of Present Illness Section
          • Hospital Consultations Section
          • Hospital Course Section
          • Hospital Discharge Instructions Section
          • Hospital Discharge Physical Section
          • Hospital Discharge Studies Summary Section
          • Immunizations Section (/entries optional) (/V3)
          • Immunizations Section (/entries required) (/V3)
          • Medical (/General) History Section
          • Medical Equipment Section (/V2)
          • Medications Administered Section (/V2)
          • Medications Section (/entries optional) (/V2)
          • Medications Section (/entries required) (/V2)
          • Mental Status Section (/V2)
          • Notes
          • Nutrition Section
          • Objective Section
          • Operative Note Fluids Section
          • Operative Note Surgical Procedure Section
          • Past Medical History (/V3)
          • Payers Section (/V3)
          • Plan of Treatment Section (/V2)
          • Postprocedure Diagnosis Section (/V3)
          • Preoperative Diagnosis Section (/V3)
          • Problem Section (/entries optional) (/V3)
          • Problem Section (/entries required) (/V3)
          • Procedure Description Section
          • Procedure Disposition Section
          • Procedure Estimated Blood Loss Section
          • Procedure Implants Section
          • Procedure Specimens Taken Section
          • Procedures Section (/entries optional) (/V2)
          • Procedures Section (/entries required) (/V2)
          • Reason for Visit Section
          • Results Section (/entries optional) (/V3)
          • Results Section (/entries required) (/V3)
          • Review of Systems Section
          • Social History Section (/V3)
          • Vital Signs Section (/entries optional) (/V3)
          • Vital Signs Section (/entries required) (/V3)
        • How to deploy the service
        • Producing C-CDA documents
        • How to customize conversion rules
      • HL7 v2 Integration
        • HL7 v2 integration with Aidbox Project
        • Mappings with lisp/mapping
      • X12 message converter
      • Analytics
        • Power BI
      • Mappings
      • Email Providers integration
        • Setup SMTP provider
    • SMARTbox | FHIR API for EHRs
      • Get started
        • Set up Smartbox locally
        • Deploy Smartbox with Kubernetes
      • (/g)(/10) Standardized API for patient and population services
      • The B11 Decision Support Interventions
        • Source attributes
        • Feedback Sections
      • How-to guides
        • Pass Inferno tests with Smartbox
        • Perform EHR launch
        • Pass Inferno Visual Inspection and Attestation
        • Revoke granted access
        • Set up EHR-level customization
        • Check email templates
        • Setup email provider
        • Register users
        • Set up SSO with Auth0
        • Publish Terms of Use link onto the documentation page
        • Find out what resources were exported during the $export operation
        • Find documentation endpoint
      • Background information
        • Considerations for Testing with Inferno ONC
        • Adding Clients for Inferno tests
        • Multitenancy approach
        • What is Tenant
        • Email templating
    • ePrescription
      • Getting started
      • Authentication with mTLS
      • Pharmacies synchronization
      • Prescribing
        • NewRx Message
        • CancelRx Message
        • How to test Callback
      • Directory
        • DirectoryDownload Message
        • GetProviderLocation Message
        • AddProviderLocation Message
        • UpdateProviderLocation Message
        • DisableProviderLocation Message
      • Medications
        • FDB
      • References
        • Environment Variables
      • Frequently Asked Questions
    • Other modules
      • MDM
        • Train model
        • Configure MDM module
        • Find duplicates: $match
        • Mathematical details
      • MCP
  • Database
    • Overview
    • Database schema
    • PostgreSQL Extensions
    • AidboxDB
      • HA AidboxDB
    • Tutorials
      • Migrate to AidboxDB 16
      • Working with pgAgent
  • File storage
    • AWS S3
    • GCP Cloud Storage
    • Azure Blob Storage
    • Oracle Cloud Storage
  • Deployment and maintenance
    • Deploy Aidbox
      • Run Aidbox on Kubernetes
        • Deploy Production-ready Aidbox to Kubernetes
        • Deploy Aidbox with Helm Charts
        • Highly Available Aidbox
        • Self-signed SSL certificates
      • Run Aidbox on managed PostgreSQL
      • How to inject env variables into Init Bundle
    • Backup and Restore
      • Crunchy Operator (/pgBackRest)
      • pg_dump
      • pg_basebackup
      • WAL-G
    • Indexes
      • Get suggested indexes
      • Create indexes manually
  • App development
    • Use Aidbox with React
    • Aidbox SDK
      • Aidbox JavaScript SDK
      • Apps
      • NodeJs SDK
      • Python SDK
    • Examples
  • Reference
    • FHIR Schema reference
    • Settings reference
      • General
      • FHIR
      • Security & Access Control
      • Modules
      • Database
      • Web Server
      • Observability
      • Zen Project
    • Environment variables
      • Aidbox required environment variables
      • Optional environment variables
      • AidboxDB environment variables
    • System resources reference
      • IAM Module Resources
      • SDC Module Resources
      • Base Module Resources
      • Bulk Module Resources
      • AWF Module Resources
      • Cloud Module Resources
      • HL7v2 Module Resources
      • SQL on FHIR Module Resources
    • Email Providers reference
      • Notification resource reference
      • Mailgun environment variables
      • Postmark environment variables
    • Aidbox Forms reference
      • FHIR SDC API
      • Aidbox SDC API
      • Generating Questionnaire from PDF API
    • Aidbox SQL functions
  • Deprecated
    • Deprecated
      • Zen-related
        • RPC reference
          • aidbox
            • mdm
              • aidbox.mdm/update-mdm-tables
              • aidbox.mdm/match
        • FTR
        • Aidbox configuration project
          • Run Aidbox locally using Aidbox Configuraiton project
          • Aidbox configuration project structure
          • Set up and use configuration projects
          • Enable IGs
          • Repository
          • Seed Import
          • Manage Indexes in Zen Project
          • Seed v2
          • 🎓Migrate to git Aidbox Configuration Projects
          • Aidbox Configuration project reference
            • Zen Configuration
            • Aidbox project RPC reference
            • aidbox.config/config
          • Custom resources using Aidbox Project
          • First-Class Extensions using Zen
          • Zen Indexes
        • US Core IG
          • US Core IG support reference
        • Workflow Engine
          • Task
            • Aidbox Built-in Tasks
            • Task Executor API
            • Task User API
          • Workflow
            • Workflow User API
          • Services
          • Monitoring
        • FHIR conformance Deprecated guides
          • Touchstone FHIR 4.0.1 basic server
          • Touchstone FHIR USCore ClinData
          • How to enable US Core IG
            • Start Aidbox locally with US Core IG enabled
            • Add US Core IG to a running Aidbox instance
          • HL7 FHIR Da Vinci PDex Plan Net IG
        • Terminology Deprecated Tutorials
          • Inferno Test-Suite US Core 3.1.1
        • API constructor (/beta)
        • zen-lang validator
          • Write a custom zen profile
          • Load zen profiles into Aidbox
        • FHIR topic-based subscriptions
          • Set up SubscriptionTopic
          • Tutorial: Subscribe to Topic (/R4B)
          • API Reference
            • Subscription API
        • 🏗️FHIR Terminology Repository
          • FTR Specification
          • Create an FTR instance
            • FTR from CSV
            • FTR from FHIR IG
            • FTR from FTR — Direct Dependency
            • FTR from FTR — Supplement
          • FTR Manifest
          • Load SNOMED CT into Aidbox
          • Load LOINC into Aidbox
          • Load ICD-10-CM into Aidbox
          • Load RxNorm into Aidbox
          • Load US VSAC Package to Aidbox
          • Import via FTR
        • Zen Search Parameters
      • Entity / Attribute
        • Entities & Attributes
        • First-Class Extensions using Attribute
        • Custom Resources using Entity
        • Working with Extensions
        • Aidbox Search Parameters
      • Forms
      • Other
        • Custom Search
        • SearchQuery
        • Subscribe to new Patient resource
        • App Development Deprecated Tutorials
          • Receive logs from your app
            • X-Audit header
          • Working with Aidbox from .NET
          • Patient Encounter notification Application
        • Other Deprecated Tutorials
          • Resource generation with map-to-fhir-bundle-task and subscription triggers
          • APM Aidbox
          • Automatically archive AuditEvent resources in GCP storage guide
          • HL7 v2 pipeline with Patient mapping
          • How to migrate to Apline Linux
          • How to migrate transaction id to bigint
          • How to fix broken dates
          • Configure multi-tenancy
        • AidboxProfile
        • GCP Pub/Sub
Powered by GitBook
On this page
  • Crunchy Operator
  • Install Crunchy
  • Create cluster
  • Connect to the cluster
  • Backup a cluster
  • Create manual backup
  • Clone a Postgres Cluster
  • Restore PITR
  • Switchover
  • PGO CLI
  • Monitoring

Was this helpful?

Edit on GitHub
  1. Database
  2. AidboxDB

HA AidboxDB

HA AidboxDB installation with Crunchy Operator

PreviousAidboxDBNextTutorials

Last updated 1 day ago

Was this helpful?

High availability for PostgreSQL is complex because it requires multiple components to work seamlessly, can be time-consuming to set up and configure manually, and ongoing maintenance can be challenging.

Using ready solutions like the Crunchy operator for Kubernetes simplifies the process and improves reliability. Crunchy and similar operators provide a tested and production-ready infrastructure that integrates well with PostgreSQL, as well as features like automatic failover, backups, restores, and upgrades, which can be complex to implement manually. Overall, using a ready solution like Crunchy can reduce complexity and free up time and resources to focus on other aspects of your application.

Crunchy Operator

The is an open-source Kubernetes operator that automates the management of PostgreSQL clusters. It provides a simple way to deploy, manage, and operate PostgreSQL clusters in a Kubernetes environment, making it easier to run and scale PostgreSQL workloads.

One of the key benefits of using the Crunchy operator is that it allows for high availability and fault tolerance for your PostgreSQL database. When running a PostgreSQL cluster with the Crunchy operator, you can specify the number of replicas to create, which helps ensure that your database is always available in case of a failure.

Here's how high availability works in terms of the Crunchy operator:

  • The Crunchy operator deploys a primary PostgreSQL instance and one or more replicas.

  • The primary instance is responsible for accepting read and write requests and replicating changes to the replicas.

  • If the primary instance fails, one of the replicas is promoted to become the new primary instance.

  • The Crunchy operator automatically reconfigures the remaining replicas to replicate from the new primary instance.

  • This ensures that the cluster remains available even if one or more instances fail.

In addition to high availability, the Crunchy operator also provides other features such as backups and restores, scaling, rolling upgrades, and custom configurations using PostgreSQL custom resource definitions (CRDs).

Install Crunchy

We recommend following official Crunchy for how to install and get up and running with PGO. Here are some instructions to get Postgres up and running on Kubernetes:

  1. and clone it to your host machine.

YOUR_GITHUB_UN="<your GitHub username>"
git clone --depth 1 "git@github.com:${YOUR_GITHUB_UN}/postgres-operator-examples.git"
cd postgres-operator-examples
  1. Install PGO using kustomize

kubectl apply -k kustomize/install/namespace
kubectl apply --server-side -k kustomize/install/default
  1. Verify PGO installation

$ kubectl get pods -n postgres-operator
NAME                           READY   STATUS    RESTARTS   AGE
pgo-7b5d478777-7g6kc           1/1     Running   0          51m
pgo-upgrade-5b576ccfb5-m5qdc   1/1     Running   0          51m

Create cluster

  1. Create aidboxdb.yml file with the following content

aidboxdb.yml
apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: aidboxdb
  namespace: aidboxdb-db
spec:
  image: healthsamurai/aidboxdb:15.2.0-crunchy
  postgresVersion: 15
  port: 5432
  instances:
    - name: aidboxdb
      replicas: 2
      dataVolumeClaimSpec:
        accessModes:
          - ReadWriteOnce
        resources:
          requests:
            storage: 10Gi
  backups:
    pgbackrest:
      global:
        repo1-retention-full: "30"
        repo1-retention-full-type: time      
      manual:
        options:
          - '--type=full'
        repoName: repo1
      repos:
        - name: repo1
          schedules:
            full: "0 1 * * 0"
            incremental: "0 1 * * 1-6"        
          volume:
            volumeClaimSpec:
              accessModes:
                - ReadWriteOnce
              resources:
                requests:
                  storage: 10Gi
  users:
    - databases:
        - aidbox
      name: aidbox
      options: "SUPERUSER CREATEROLE LOGIN CREATEDB"
  patroni:
    switchover:
      enabled: true
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - host all all 0.0.0.0/0 md5
        parameters:
          listen_addresses : '*'
          shared_preload_libraries : 'pg_stat_statements'
          shared_buffers : '1GB'

Look at image property. Generally, you can use the default image provided by Crunchy Operator. But we strongly recommend using healthsamurai/aidboxdb:15.2.0-crunchy image as the image that is optimized for Aidbox.

Important notes

  • image: healthsamurai/aidboxdb:15.2.0-crunchy - we recommend use our aidboxdb image build that is preconfigured for use in PGO

  • replicas: 2 - in this configuration, we install 1 master and 1 replica

  • pg_hba: ["host all all 0.0.0.0/0 md5"] - for this tutorial we allow non SSL connection

  1. Create a namespace and apply aidboxdb.yml resource

$ kubectl create ns aidboxdb-db
namespace/aidboxdb-db created                                                                                                      ⎈ kind-kind 11:21:57
$ kubectl apply -f aidboxdb.yaml
postgrescluster.postgres-operator.crunchydata.com/aidboxdb created
  1. Verify PostgreSQL cluster

$ kubectl get pods -n aidboxdb-db
NAME                         READY   STATUS      RESTARTS   AGE
aidboxdb-aidboxdb-p2tm-0     4/4     Running     0          12m
aidboxdb-aidboxdb-tc58-0     4/4     Running     0          12m
aidboxdb-backup-qvk7-q7qmv   0/1     Completed   0          11m
aidboxdb-repo-host-0         2/2     Running     0          12m

Connect to the cluster

$ kubectl describe secret aidboxdb-pguser-aidbox -n aidboxdb-db
Name:         aidboxdb-pguser-aidbox
Namespace:    aidboxdb-db
Annotations:  <none>

Type:  Opaque

Data
====
port:      4 bytes     # Database port 5432
host:      32 bytes    # Local K8S host name
user:      6 bytes     # User name
password:  24 bytes    # Password
dbname:    6 bytes     # database name
verifier:  133 bytes
jdbc-uri:  120 bytes
uri:       101 bytes
Aidbox ConfigMap
apiVersion: v1
kind: ConfigMap
metadata:
  name: aidbox
  namespace: prod
data:
  ...
  PGDATABASE: aidbox
  PGHOST: < host value from aidboxdb-pguser-aidbox secret>
  PGPORT: '5432' 
Aidbox Secret
apiVersion: v1
kind: Secret
metadata:
  name: aidbox
  namespace: prod
data:
  ...
  PGPASSWORD: < password value from aidboxdb-pguser-aidbox secret>
  PGUSER:     < user value from aidboxdb-pguser-aidbox secret>

Backup a cluster

spec:
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-4
      global:
        repo1-retention-full: "30"
        repo1-retention-full-type: time      
      manual:
        options:
          - '--type=full'
        repoName: repo1
      repos:
        - name: repo1
          schedules:
            full: "0 1 * * 0"
            incremental: "0 1 * * 1-6"        
          volume:
            volumeClaimSpec:
              accessModes:
                - ReadWriteOnce
              resources:
                requests:
                  storage: 10Gi
  • Define backup schedule. In this spec we define incremental backup from Monday to Saturday and take one full backup every Sunday at 1 AM

    schedules:
      full: "0 1 * * 0"
      incremental: "0 1 * * 1-6"  
  • Define backup retention policy. In this spec we store all backups 30 days, after that period - delete them

    global:
      repo1-retention-full: "30"
      repo1-retention-full-type: time  

Create manual backup

For creating a manual full backup you should annotate postgrescluster resource

$ kubectl annotate -n aidboxdb-db postgrescluster aidboxdb --overwrite \
        postgres-operator.crunchydata.com/pgbackrest-backup="$(date)"

Clone a Postgres Cluster

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: stage
  namespace: aidboxdb-stage
spec:
  dataSource:
    postgresCluster:
      clusterName: aidboxdb
      clusterNamespace: aidboxdb-db
      repoName: repo1
  image: healthsamurai/aidboxdb:15.2.0-crunchy
  postgresVersion: 15
  instances:
    - dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-4
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

Take care of dataSource parameter. In this section we specify the source that will be used for cloning.

  dataSource:
    postgresCluster:
      clusterName: aidboxdb
      clusterNamespace: aidboxdb-db
      repoName: repo1

Restore PITR

When you needed to restore a specific time version of the cluster, or you want to periodically restore you can specify restore option on backup config.

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: stage-recovery
  namespace: aidboxdb-stage
spec:
  image: healthsamurai/aidboxdb:15.2.0-crunchy
  postgresVersion: 15
  dataSource:
    postgresCluster:
      clusterName: aidboxdb
      clusterNamespace: aidboxdb-db
      repoName: repo1
  instances:
    - dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:ubi8-2.41-4
      restore:
        enabled: true
        repoName: repo1
        options:
        - --type=time
        - --target="2023-04-25 16:20:00-02"
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi

Now you need to trigger the recovery process

$ kubectl annotate -n aidboxdb-db  postgrescluster aidboxdb --overwrite \
        postgres-operator.crunchydata.com/pgbackrest-restore="$(date)"

Switchover

To change the primary in your HA cluster, you need to update the PostgresCluster spec to include the following fields:

spec: 
  patroni: 
    switchover: 
      enabled: true

This will prepare your cluster for a switchover. To trigger the switchover, you need to add the postgres-operator.crunchydata.com/trigger-switchover annotation to your custom resource. It's recommended to use a timestamp as the value for the annotation so you can track when you initiated the change.

$ kubectl annotate -n aidboxdb-db  postgrescluster aidboxdb --overwrite \
        postgres-operator.crunchydata.com/trigger-switchover="$(date)"

PGO CLI

# Create a new backup
$ kubectl pgo backup aidboxdb -n aidboxdb-db

# Show backups
$ kubectl pgo show backup aidboxdb -n aidboxdb-db

Monitoring

Configure

Enable monitoring capabilities by configuring monitoring.

spec:
  monitoring:
    pgmonitor:
      exporter:
        image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres-exporter:ubi8-5.3.1-0

Install Prometheus stack

Install all parts manually

Or install Prebuild prometheus stack for Kubernetes

Scrape config

Configure scrape config for prometheus and PGO Crunchy Operator

- job_name: crunchy-postgres-exporter
  kubernetes_sd_configs:
  - role: pod
  relabel_configs:
  - source_labels:
    - __meta_kubernetes_pod_label_postgres_operator_crunchydata_com_crunchy_postgres_exporter
    - __meta_kubernetes_pod_label_crunchy_postgres_exporter
    action: keep
    regex: true
    separator: ''
  - source_labels:
    - __meta_kubernetes_pod_container_port_number
    action: drop
    regex: 5432
  - source_labels:
    - __meta_kubernetes_pod_container_port_number
    action: drop
    regex: 10000
  - source_labels:
    - __meta_kubernetes_pod_container_port_number
    action: drop
    regex: 8009
  - source_labels:
    - __meta_kubernetes_pod_container_port_number
    action: drop
    regex: 2022
  - source_labels:
    - __meta_kubernetes_pod_container_port_number
    action: drop
    regex: "^$"
  - source_labels:
    - __meta_kubernetes_namespace
    action: replace
    target_label: kubernetes_namespace
  - source_labels:
    - __meta_kubernetes_pod_name
    target_label: pod
  - source_labels:
    - __meta_kubernetes_pod_label_postgres_operator_crunchydata_com_cluster
    - __meta_kubernetes_pod_label_pg_cluster
    target_label: cluster
    separator: ''
    replacement: "$1"
  - source_labels:
    - __meta_kubernetes_namespace
    - cluster
    target_label: pg_cluster
    separator: ":"
    replacement: "$1$2"
  - source_labels:
    - __meta_kubernetes_pod_ip
    target_label: ip
    replacement: "$1"
  - source_labels:
    - __meta_kubernetes_pod_label_postgres_operator_crunchydata_com_instance
    - __meta_kubernetes_pod_label_deployment_name
    target_label: deployment
    replacement: "$1"
    separator: ''
  - source_labels:
    - __meta_kubernetes_pod_label_postgres_operator_crunchydata_com_role
    - __meta_kubernetes_pod_label_role
    target_label: role
    replacement: "$1"
    separator: ''

For creating a new PostgreSQL cluster using PGO you should create CRD PostgresCluster. More detailed information about creating a PGO cluster you can found in .

backup options - in this sample, we use local PVC for storing backups. For configuring cloud storages like S3 or GCS you can

Get connection credentials. Crunchy operator store all connection information in related Secret resource. In our case it aidboxdb-pguser-aidbox. More detailed information you can be found in the.

Now you can set up this parameter for the Aidbox database connection. Look at tutorial

You can specify a for cluster

You can create a single copy of the existing cluster by creating a new one and specify dataSource parameter. In the next example, we create a stage cluster that is a copy of aidboxdb cluster. .

PGO Command Line Interface (CLI) for the Crunchy Operator built as a kubectl plugin, the pgo CLI facilitates the creation and management of PostgreSQL clusters created using the Crunchy Postgres Operator. For more information about using the CLI and the various commands available, please see the .

- metrics and alerting open-source monitoring solution

- open-source observability tool

- Prebuild prometheus stack for Kubernetes

Crunchy Operator
Quickstart
Fork the Postgres Operator examples repository
official documentation
follow this instructions
connection tutorial
Install Aidbox in Kubernetes
schedule backup and retention policy
Related documentation
pgo CLI documentation
prometheus
grafana
kube-prometheus