Modify PostgreSQL Parameters

Database reconfiguration involves modifying parameters, settings, or configurations to optimize performance, security, or availability. Parameter changes fall into two categories:

For static parameters, KubeBlocks minimizes downtime by:

1. Modifying and restarting replica nodes first
2. Performing a switchover to promote the updated replica as primary (typically completes in milliseconds)
3. Restarting the original primary node

This guide demonstrates how to modify both dynamic and static parameters of a PostgreSQL cluster managed by KubeBlocks using a Reconfiguring OpsRequest.

Prerequisites

Before proceeding, ensure the following:

• Environment Setup:
  • A Kubernetes cluster is up and running.
  • The kubectl CLI tool is configured to communicate with your cluster.
  • KubeBlocks CLI and KubeBlocks Operator are installed. Follow the installation instructions here.
• Namespace Preparation: To keep resources isolated, create a dedicated namespace for this tutorial:

kubectl create ns demo
namespace/demo created

Deploy a PostgreSQL Cluster

KubeBlocks uses a declarative approach for managing PostgreSQL clusters. Below is an example configuration for deploying a PostgreSQL cluster with 2 replicas (1 primary, 1 replicas).

Apply the following YAML configuration to deploy the cluster:

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: pg-cluster
  namespace: demo
spec:
  terminationPolicy: Delete
  clusterDef: postgresql
  topology: replication
  componentSpecs:
    - name: postgresql
      serviceVersion: 16.4.0
      labels:
        apps.kubeblocks.postgres.patroni/scope: pg-cluster-postgresql
      disableExporter: true
      replicas: 2
      resources:
        limits:
          cpu: "0.5"
          memory: "0.5Gi"
        requests:
          cpu: "0.5"
          memory: "0.5Gi"
      volumeClaimTemplates:
        - name: data
          spec:
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi

Verifying the Deployment

Monitor the cluster status until it transitions to the Running state:

kubectl get cluster pg-cluster -n demo -w

Expected Output:

NAME         CLUSTER-DEFINITION   TERMINATION-POLICY   STATUS     AGE
pg-cluster   postgresql           Delete               Creating   50s
pg-cluster   postgresql           Delete               Running    4m2s

Once the cluster status becomes Running, your PostgreSQL cluster is ready for use.

Check Parameter Values

Retrieve Credentials

KubeBlocks automatically creates a secret containing the PostgreSQL postgres credentials. Retrieve the credentials with the following commands:

NAME=`kubectl get secrets -n demo pg-cluster-postgresql-account-postgres -o jsonpath='{.data.username}' | base64 -d`
PASSWD=`kubectl get secrets -n demo pg-cluster-postgresql-account-postgres -o jsonpath='{.data.password}' | base64 -d`

Access PostgreSQL Cluster

To connect to the cluster's primary node, use the PostgreSQL client:

kubectl exec -it -n demo pg-cluster-postgresql-0 -c postgresql -- env PGUSER=${NAME} PGPASSWORD=${PASSWD} psql

Query Parameter Values

Once connected, you can query the current value of 'max_connections' and 'shared_buffers':

postgres=# SHOW max_connections;
 max_connections
-----------------
 56
(1 row)

postgres=# show pgaudit.log;
 pgaudit.log
-------------
 ddl,read,write
(1 row)

postgres=# show shared_buffers;
 shared_buffers
----------------
 128MB
(1 row)

Dynamic Parameter Example: Modifying max_connections and pgaudit.log

Dynamic parameters like max_connections can be modified without restarting PostgreSQL. Changes take effect immediately, allowing you to:

• Adjust connection limits on-the-fly
• Modify audit logging levels
• Tune performance parameters

apiVersion: operations.kubeblocks.io/v1alpha1
kind: OpsRequest
metadata:
  name: pg-reconfigure-dynamic
  namespace: demo
spec:
  clusterName: pg-clusters
  reconfigures:
  - componentName: postgresql
    parameters:
      - key: max_connections
        value: '100'
      - key: pgaudit.log
        value: ddl
  type: Reconfiguring

This configuration:

• Changes pgaudit.log from default ddl,read,write to ddl only
• Increases max_connections from 56 to 100

The pgaudit.log parameter controls audit logging granularity. Available options:

Wait for the OpsRequest to complete:

kubectl get ops pg-reconfigure-dynamic -n demo -w

Example Output:

NAME                     TYPE            CLUSTER      STATUS    PROGRESS   AGE
pg-reconfigure-dynamic   Reconfiguring   pg-cluster   Running   -/-        11s
pg-reconfigure-dynamic   Reconfiguring   pg-cluster   Succeed   -/-        31s

Verifying the Configuration Change

Log into the PostgreSQL instance and confirm that the max_connections and pgaudit.log parameters have been updated:

postgres=# show max_connections;
 max_connections
-----------------
 100
(1 row)

postgres=# show pgaudit.log;
 pgaudit.log
-------------
 ddl
(1 row)

The output verifies both parameters were updated:

• max_connections increased to 100
• pgaudit.log reduced to DDL-only logging

Static Parameter Example: Modifying shared_buffers

Static parameters like shared_buffers require a restart. This example increases the buffer from 128MB to 256MB.

Create a Reconfigure OpsRequest. Apply the following OpsRequest YAML to update the 'shared_buffers':

apiVersion: operations.kubeblocks.io/v1alpha1
kind: OpsRequest
metadata:
  name: postgresql-reconfigure-static
  namespace: demo
spec:
  clusterName: pg-cluster
  force: false
  reconfigures:
  - componentName: postgresql
    parameters:
    - key: shared_buffers
      value: '256MB'
  preConditionDeadlineSeconds: 0
  type: Reconfiguring

Check the status of the OpsRequest until it completes:

kubectl get ops postgresql-reconfigure-static -n demo -w

Example Output:

postgresql-reconfigure-static   Reconfiguring   pg-cluster   Running   -/-        5s
postgresql-reconfigure-static   Reconfiguring   pg-cluster   Succeed   -/-        31s

Verify the Configuration Change

Log into the PostgreSQL instance and confirm that the shared_buffers parameter has been updated:

postgres=# show shared_buffers;
 shared_buffers
----------------
 256MB
(1 row)

Validity Checking on Reconfiguration

KubeBlocks validates parameters before applying changes. For example, max_connections follow rules:

max_connections?: int & >=6 & <=8388607

It means max_connections must be an integer ranging from 6 to 8388607.

And if you somehow set a string to this value like:

apiVersion: operations.kubeblocks.io/v1alpha1
kind: OpsRequest
metadata:
  name: postgresql-reconfigure-invalid
  namespace: demo
spec:
  type: Reconfiguring
  clusterName: pg-cluster
  reconfigures:
  - componentName: postgresql
    parameters:
    - key: max_connections
      value: 'abc'

By checking the status of the OpsRequest

kubectl get ops postgresql-reconfigure-invalid -n demo

This OpsRequest fails fast. To checkout the details, you may describe the Parameter CR:

kubectl describe parameter postgresql-reconfigure-invalid -n demo

And you will find message failed to validate updated config: [failed to parse field max_connections: [strconv.Atoi: parsing "STRING": invalid syntax]]

Cleanup

To remove all created resources, delete the PostgreSQL cluster along with its namespace:

kubectl delete cluster pg-cluster -n demo
kubectl delete ns demo

Summary

This guide covered modifying PostgreSQL parameters through KubeBlocks:

• Dynamic changes (e.g., max_connections) apply immediately
• Static changes (e.g., shared_buffers) require restart but with minimal downtime
• All changes are validated before application
• Configuration follows declarative management principles