cnpg

Files (2)

• SKILL.md
• self-hosted-and-networkpolicy.md · references/

SKILL.md (first lines)

# cnpg

Provision and run CloudNativePG (CNPG) Postgres on Kubernetes as GitOps. Every
concrete identifier below is a placeholder - substitute your own:
`<project>`, `<gcp-sa>`, `<backups-bucket>`, `<cluster>`, `<ns>`, `<svc>`,
`<db>`, `<owner>`, `<env>`, `<s3-endpoint>`, `<objstore-secret>`, `<app-ns>`.

**Two platforms, one operator.** The Cluster spec, bootstrap, pgvector, PITR,
clone, and most gotchas are identical everywhere; only **backup auth** and
**NetworkPolicy** differ. Managed cloud (GKE + GCS): Workload Identity, no keys -
steps below default to this. Self-hosted (K3s / bare-metal + S3-compatible store:
MinIO, Ceph, R2, B2): an access-key Secret, plus explicit NetworkPolicies on a
default-deny cluster. Read **`references/self-hosted-and-networkpolicy.md`**
whenever there's no cloud Workload Identity or the cluster enforces default-deny.

## Mental model - two halves that MUST share one string

A CNPG database is two halves agreeing on exactly one string, `<ns>/<cluster>`:
the **GitOps half** - a CNPG `Cluster` whose `serviceAccountTemplate` annotation
points the auto-created **pod KSA** (named after the **cluster**) at the GCP
backup SA - and the **GCP half** - an IAM `workloadIdentityUser` binding whose
member is `serviceAccount:<project>.svc.id.goog[<ns>/<cluster>]` plus the GCS
backups bucket. Any standalone `ServiceAccount` named `cnpg-backup-sa` is a
**decoy/legacy** resource; CNPG does not use it for backup auth.

Deploy is pure GitOps: commit YAML → a Flux `Kustomization` (`dependsOn:
database-operators`) reconciles → the CNPG operator builds the cluster.
**Terraform (WI binding + bucket) must already be applied**, or the cluster
bootstraps but backups fail silently.

## Step 0 - prerequisites

- CNPG operator reconciled (a `database-operators` Flux Kustomization).
- GKE Workload Identity enabled; a shared per-env backup SA
  `<gcp-sa>@<project>.iam.gserviceaccount.com` exists.
- SOPS age key available to Flux (`decryption.secretRef`).
- (pgvector) the operand image ships the `vector` lib - it is **available, not
  installed**; the non-superuser app role cannot install it (see gotchas).
- (affinity) a dedicated DB node pool with the expected taint + label, else drop
  the `affinity` block.
- (cloud) ship Terraform via PR off `origin/main`. Terragrunt auth = personal ADC
  or `GOOGLE_OAUTH_ACCESS_TOKEN` (no SA key needed; SA keys often can't read tfstate).
- (self-hosted) an S3-compatible object store reachable from the cluster + an
  encrypted credentials Secret; a `storageClass` that exists (e.g. `local-path`,
  `longhorn`); and if the cluster is default-deny, the NetworkPolicies from the
  reference. Know your CNI (Cilium → `CiliumNetworkPolicy`; Calico → vanilla).

## Step 1 - backup auth

**Self-hosted (S3-compatible):** skip the Terraform/Workload-Identity below -
create an encrypted access-key Secret and use `s3Credentials` + `endpointURL` in
the barman config. Full manifests in `references/self-hosted-and-networkpolicy.md`.

**Managed cloud (GKE + GCS), Terraform BEFORE Flux:**
Append the new cluster's pod KSA to the shared backup SA
(`terraform/gcp/<env>/service-accounts/service-accounts.yaml`):

```yaml
service_accounts:
  <gcp-sa>:
    display_name: "<Env> CloudNativePG Backup Service Account"
    project_roles:
      - "roles/storage.admin"        # objectAdmin LACKS storage.buckets.get → barman fails; use admin or a bucket-level binding
    workload_identity_bindings:
      - "cnpg-system/cnpg-backup"
      - "<ns>/<cluster>"             # ← ADD THIS. Member = <namespace>/<cluster>, NOT cnpg-backup-sa
```

Renders to member `serviceAccount:<project>.svc.id.goog[<ns>/<cluster>]`, role
`roles/iam.workloadIdentityUser`.

Backups bucket (`terraform/gcp/<env>/gcs/buckets.yaml`):

```yaml
backups:
  name: <backups-bucket>
  location: US
  lifecycle_rules:
    - action: { type: Delete }
      condition: { age: 30 }         # BACKSTOP only - MUST exceed barman retentionPolicy or PITR breaks
  versioning: false                  # prod: true
  uniform_bucket_level_access: true
  force_destroy: true                # dev only; prod: false
  iam_bindings:
    - { service_account: <gcp-sa>, role: roles/storage.admin }
```

One shared backups bucket per env; each cluster isolates under its own
`cnpg/<svc>` prefix. Apply the SA dir, then the GCS dir.

## Step 2 - the app DB folder `fluxcd/databases/<env>/<svc>/`

`kustomization.yaml` in dependency order (namespace + secret first, then
cluster, then extensions + backup):

```yaml
apiVersion: kustomize.config.k8s.io/v1beta1
kind: Kustomization
resources:
  - namespace.yaml          # <ns>; labels app.kubernetes.io/part-of: databases
  - secrets.enc.yaml        # SOPS-encrypted app credentials (username/password) - referenced by initdb.secret.name
  - database.yaml           # the Cluster CR
  - database-vector.yaml    # pgvector Database CR (only if needed)
  - scheduled-backup.yaml   # ScheduledBackup CR
```

## Step 3 - the Cluster + ScheduledBackup

`database.yaml`:

```yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: <cluster>
  namespace: <ns>
  labels:
    app.kubernetes.io/name: <cluster>
    app.kubernetes.io/component: database

From vanducng/skills, MIT.

More from vanducng/skills