CESNET/ansible-role-postgresql-kubernetes

Ansible role for creating a high-availability PostgreSQL cluster in Kubernetes using CloudNativePG

cesnet.postgresql_kubernetes

Ansible role for creating a high-availability PostgreSQL cluster in Kubernetes using CloudNativePG Operator. The cluster has a single database, two users (one as the database owner, second named pgwatch for monitoring), creates continuous backups in an S3 bucket, and is accessible from outside from selected IP addresses.

Requirements

The role uses Ansible modules

• kubernetes.core.helm
• amazon.aws.s3_bucket

running on localhost, check their dependencies.

Variables must be provided that match settings in ~/.kube/config file:

• k8s_namespace — Kubernetes namespace
• k8s_context — Kubernetes context

Role Variables

• cnpg_cluster_name — name of Helm App and prefix for all Kubernetes objects related to this cluster
• cnpg_cluster_image — container image name for cluster nodes, see CNPG PostgreSQL Container Images and tagged versions
• cnpg_cluster_db_name — name of database, default is "app"
• cnpg_cluster_db_owner — database owner user, default is "app"
• cnpg_cluster_db_parameters — options for createdb, like encoding, localeCType, localeCollate
• cnpg_cluster_postgresql_timezone — timezone for server, affects SQL type timestamp with timezone, default is "Europe/Prague", use "Etc/UTC" for UTC
• cnpg_cluster_postgresql_parameters - options for postgresql.conf
• cnpg_cluster_enable_superuser_access — whether to enable access to user postgres from outside, default is true
• cnpg_cluster_user_password — password of db owner
• cnpg_cluster_pgwatch_user_password — password for pgwatch user
• cnpg_cluster_storage_size — store allocated on local disk for each replica
• cnpg_cluster_wal_keep_size — size for Write-Ahead-Logs files
• cnpg_cluster_requests_mem — memory requested for each replica
• cnpg_cluster_requests_cpu — CPU requested for each replica
• cnpg_cluster_limits_mem — memory limit for each replica
• cnpg_cluster_limits_cpu — CPU limit for each replica
• cnpg_sidecar_requests_mem — sidecar container with Barman backup plugin memory requested
• cnpg_sidecar_requests_cpu — sidecar container with Barman backup plugin cpu requested
• cnpg_sidecar_limits_mem — sidecar container with Barman backup plugin memory limit
• cnpg_sidecar_limits_cpu — sidecar container with Barman backup plugin CPU limit
• cnpg_cluster_retention_policy — retention policy of backup files in S3
• cnpg_cluster_instances — number of servers, at least 1 for a single master, recommended is at least 3
• cnpg_cluster_firewall_ports — ports to open in firewall, default is CESNET and MUNI VPNs
• cnpg_cluster_firewall_additional_ports — additional ports to open, default is empty
• cnpg_cluster_s3_bucket — name of S3 bucket for backups (full backups and WAL), must be initially empty
• cnpg_cluster_aws_access_key_id — S3 access key id
• cnpg_cluster_aws_secret_access_key — S3 access key secret
• cnpg_cluster_aws_endpointURL — S3 endpointURL
• cnpg_cluster_full_backup_schedule — cron schedule for full backups, in UTC timezone
• cnpg_cluster_backup_compression — compression algorithm for both full backups and WAL
• cnpg_cluster_ready_wait_seconds — how many seconds to wait for cluster to become ready, default is 400 seconds
• cnpg_cluster_synchronous — if defined, its content is added as spec/synchronous, see Synchronous Replication
• cnpg_cluster_recovery — if true, do a recovery from an S3 bucket specified by the cnpg_cluster_s3_recovery_bucket variable, default is false
• cnpg_cluster_s3_recovery_bucket — the name of the S3 bucket with backup files, default is undefined

Example

- name: "installs CNPG cluster in Kubernetes"
  hosts:
    - my-ns
  connection: local
  gather_facts: no
  roles:
    - role: cesnet.postgresql_kubernetes
      vars:
        k8s_namespace: my-ns
        k8s_context: my-context
        cnpg_cluster_name: mydbcluster
        cnpg_cluster_db_name: mydb
        cnpg_cluster_db_owner: myuser
        cnpg_cluster_db_parameters:
          encoding: UTF8
          localeCType: en_US.UTF-8
          localeCollate: en_US.UTF-8
        cnpg_cluster_postgresql_parameters:
          max_connections: 200
          max_pred_locks_per_transaction: 8192
          shared_buffers: 256MB
        cnpg_cluster_storage_size: 20Gi
        cnpg_cluster_synchronous:
          method: any
          number: 1
          dataDurability: preferred
        cnpg_cluster_image: ghcr.io/cloudnative-pg/postgresql:17.6-4-bookworm
        cnpg_cluster_s3_bucket: mydbbackups
        cnpg_cluster_user_password: "{{ user_password_vault }}"
        cnpg_cluster_pgwatch_user_password: "{{ pgwatch_user_password_vault }}"
        cnpg_cluster_aws_access_key_id: "{{ aws_access_key_id_vault }}"
        cnpg_cluster_aws_secret_access_key: "{{ aws_secret_access_key_vault }}"

Database recovery

The database cluster stores backups (both full backups once a day and WAL files every 5 minutes) in an S3 bucket with the name defined in the cnpg_cluster_s3_bucket variable. If you need to recover a lost cluster from the backup, create a playbook like this:

- name: "recover CNPG cluster from an S3 backup"
  hosts:
    - my-ns
  connection: local
  gather_facts: no
  vars:
    cnpg_cluster_recovery: true
    cnpg_cluster_s3_recovery_bucket: mydbbackups
    cnpg_cluster_s3_bucket: mydbbackups2
  tasks:
    - import_role:
        name: cesnet.postgresql_kubernetes

The variable cnpg_cluster_s3_recovery_bucket must contain the name of the S3 bucket containing the backups to recover from, and the variable cnpg_cluster_s3_bucket must contain the name of a non-existing or empty bucket where new logs will be written after the recovery. You cannot reuse the old bucket.

After the cluster is recovered, you can run the original playbook again to replace the recovery settings with normal settings, just do not forget to change the cnpg_cluster_s3_bucket to the new bucket.