PostgreSQL FAQs

1. Use ETCD as Patroni DCS

KubeBlocks PostgreSQL uses the Kubernetes API itself as DCS (Distributed Config Store) by default. But when the control plane is under extreme high load, it may lead to unexpected demotion of the primary replica. And it's recommended to use ETCD as DCS in such extreme cases.

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: pg-cluster-etcd
  namespace: demo
spec:
  terminationPolicy: Delete
  clusterDef: postgresql
  topology: replication
  componentSpecs:
    - name: postgresql
      serviceVersion: "16.4.0"
      env:
      - name: DCS_ENABLE_KUBERNETES_API  # unset this env if you use zookeeper or etcd, default to empty
      - name: ETCD3_HOST
        value: 'etcd-cluster-etcd-headless.demo.svc.cluster.local:2379' # where is your etcd?
      # - name: ZOOKEEPER_HOSTS
      #   value: 'myzk-zookeeper-0.myzk-zookeeper-headless.demo.svc.cluster.local:2181' # where is your zookeeper?
      replicas: 2
      resources:
        limits:
          cpu: "0.5"
          memory: "0.5Gi"
        requests:
          cpu: "0.5"
          memory: "0.5Gi"
      volumeClaimTemplates:
        - name: data
          spec:
            storageClassName: ""
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi

The key fields are:

• DCS_ENABLE_KUBERNETES_API: Unset this env to use ETCD or ZooKeeper as DCS
• ETCD3_HOST: The host of ETCD cluster

You can also use ZooKeeper as DCS by unsetting DCS_ENABLE_KUBERNETES_API and setting ZOOKEEPER_HOSTS to the host of ZooKeeper cluster.

KubeBlocks has ETCD and ZooKeeper Addons in the kubeblocks-addons repository. You can refer to the following links for more details.

• https://github.com/apecloud/kubeblocks-addons/tree/main/examples/etcd
• https://github.com/apecloud/kubeblocks-addons/tree/main/examples/zookeeper

You can shell into one of the etcd container to view the etcd data, and view the etcd data with etcdctl.

etcdctl get /service --prefix

2. What to do if log files consume too much space

PostgreSQL log files can accumulate and consume significant disk space over time. Here are several approaches to manage log file storage:

How to check current log file usage

First, check the disk usage of your PostgreSQL pod:

kubectl exec -it <pod-name> -n <namespace> -- df -h /home/postgres/pgdata/pgroot/data/log

Option 1. Configure log filename pattern and reduce log verbosity

You can adjust PostgreSQL's built-in log filename pattern and log verbosity settings by modifying the cluster configuration.

For example, you can set the log filename pattern to postgresql-%u.log to limit the number of log files to 7 days:

apiVersion: operations.kubeblocks.io/v1alpha1
kind: OpsRequest
metadata:
  name: pg-reconfigure-logs
  namespace: <namespace>
spec:
  clusterName: <cluster-name>
  reconfigures:
  - componentName: postgresql
    parameters:
      - key: log_filename
        value: 'postgresql-%u.log' # supports strftime specification, default value is 'postgresql-%Y-%m-%d.log'
      - key: log_statement
        value: 'none' # none, ddl, mod, all
  type: Reconfiguring

• log_filename: Pattern for log filenames (supports strftime specification, default value is 'postgresql-%Y-%m-%d.log')
• log_statement: Controls which SQL statements are logged (none, ddl, mod, all)

Option 2. Clean up old logs manually

If you need immediate space relief, you can manually remove old log files:

# Find and remove log files older than 7 days
kubectl exec -it <pod-name> -n <namespace> -- find /home/postgres/pgdata/pgroot/data/log -name "*.log" -mtime +7 -delete

Option 3. Increase storage capacity

If log management isn't sufficient, consider expanding the persistent volume here

This will increase the storage capacity for the data volume, which typically includes log files.

3. PostgreSQL fails to start with special characters in password

Problem Description

PostgreSQL may fail to start when the password contains certain special characters. By checking POD logs, it shows like this:

File "/usr/lib/python3/dist-packages/yaml/scanner.py", line 116, in check_token
    self.fetch_more_tokens()
  File "/usr/lib/python3/dist-packages/yaml/scanner.py", line 231, in fetch_more_tokens
    return self.fetch_anchor()
  File "/usr/lib/python3/dist-packages/yaml/scanner.py", line 621, in fetch_anchor
    self.tokens.append(self.scan_anchor(AnchorToken))
  File "/usr/lib/python3/dist-packages/yaml/scanner.py", line 929, in scan_anchor
    raise ScannerError("while scanning an %s" % name, start_mark,
yaml.scanner.ScannerError: while scanning an anchor
  in "<unicode string>", line 45, column 17:
          password: &amp;JgE#F5x&amp;eNwis*2dW!7&amp ...
                    ^

Affected Version

• KubeBlocks v0.9.4 and before
• KubeBlocks v1.0.0

Solution

Upgrade KubeBlocks to v1.0.1-beta.6 or v0.9.5-beta.4 or later.

To avoid this, you can explicitly set the list of symbols allowed in password generation policy.

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
spec:
  componentSpecs:
    - name: postgresql
      systemAccounts:
        - name: postgres
          passwordConfig:
            length: 20           # Password length: 20 characters
            numDigits: 4         # At least 4 digits
            numSymbols: 2        # At least 2 symbols
            letterCase: MixedCases # Uppercase and lowercase letters
            symbolCharacters: '!' # set the allowed symbols when generating password
# other fields in the Cluster manifest are omitted for brevity