Customizing the PostgreSQL Server

This topic describes how to customize the Postgresql Server running in new or existing VMware Postgres Operator instances. The available parameters depend on the PostgreSQL version of you have deployed. For a detailed parameter list refer to PostgreSQL Parameters.

The customized parameters will not be listed within the postgresql.conf file. Instead, they will be listed in the override file located at /etc/customconfig/postgresql.conf. The customized parameters can be further verified through Verifying PostgreSQL parameters.

Configuring custom PostgreSQL parameters

Prerequisites

Ensure that you have a running VMware Postgres Operator. For details, refer to Installing a Postgres Operator.
Ensure you have a running Postgres instance, or familiarity with creating a new Postgres Instance. For details review Deploying a New Postgres Instance. Ensure you are familiar with your instance's YAML manifest file.
Review the various fields that can be configured in your PostgreSQL server version. For more details, refer to PostgreSQL Parameters.

Exceptions

The Postgres Operator allows users to configure most of the PostgreSQL configuration parameters, except the following:

Postgres Operator		pg_auto_failover
ssl	archive_mode	port
ssl_ca_file	archive_command	hot_standby
ssl_cert_file	listen_addresses	password_encryption
ssl_key_file	shared_preload_libraries
unix_socket_directories	log_directory

If you attempt to change any of these parameters, when you try to verify your changes, you will see the default values.

Procedure

Important: Depending on the parameters you customize, the data pods associated with the Postgres instance might be restarted when you apply a ConfigMap. View the parameters list, and check for Restart: true for each parameter you configure.

In the same namespace as your Postgres instance, create a ConfigMap with the parameters of your PostgreSQL server. For details refer to Kubernetes ConfigMaps. An example ConfigMap is shown below:
```
apiVersion: v1
kind: ConfigMap
metadata:
  name: my-postgresql-configmap
  labels:
    app: postgres
data:
  max_connections: "250"
  log_timezone: "GMT"
```
Apply the ConfigMap to your instance:
```
kubectl apply -f my-postgresql-configmap-file.yaml -n <namespace>
```
where my-postgresql-configmap-file.yaml is an example ConfigMap.

The command output will be similar to:
```
configmap/my-postgresql-configmap created
```
Edit your instance's YAML file, and alter the customConfig field to reflect the name of the ConfigMap that you created:
```
......
spec:
  customConfig:
    postgresql:
      name: my-postgresql-configmap
......
```
Deploy or redeploy the instance with the new customConfig setting using:
```
kubectl apply -f postgres.yaml -n <namespace>
```
where postgres.yamlis the example name of the Kubernetes manifest created for this instance.

The command output would be similar to:
```
postgres.sql.tanzu.vmware.com/postgres-sample created
```
where postgres-sample is the Postgres instance name defined in the YAML file.
If the instance was already running before applying the configMap, and the configMap includes a value that requires a restart according to the parameters list, then the data pods associated with the Postgres instance may be restarted to reflect the valid changes that were made. Once the instance is back in Running state, refer to Verifying PostgreSQL parameters.
```
kubectl get postgres
```
```
NAME              STATUS    DB VERSION   BACKUP LOCATION         AGE
postgres-sample   Running   15.1         backuplocation-sample   10s
```

Updating PostgreSQL parameters

Note: Depending on the parameters you alter, the Postgres instance might be restarted when you apply a ConfigMap. View the Parameters list, and check for Restart: true for each parameter you update.

Prerequisites

Your instance has been configured with a ConfigMap. For details refer to Configuring custom PostgreSQL parameters.
Review the PostgreSQL parameter fields that can be configured within PostgreSQL Parameters, and review the exceptions.

Procedure

Get the name of the instance's ConfigMap:

kubectl get postgres postgres-sample -n <namespace> -o jsonpath={.spec.customConfig.postgresql.name}

The command output will reflect the name of the ConfigMap being utilized:

my-postgresql-configmap

Make your changes by editing the ConfigMap data file:

kubectl edit configmap my-postgresql-configmap -n <namespace>

apiVersion: v1
kind: ConfigMap
metadata:
  name: my-postgresql-configmap
  labels:
    app: postgres
data:
  log_hostname: "on"
  max_connections: "250"
  log_timezone: "GMT"

Apply the changes:

kubectl apply -f my-postgresql-configmap-file.yaml -n <namespace>

The command output should look similar to:

configmap/my-postgresql-configmap configured

To customize the log rotation values (for example to 1 day), use settings similar to:

apiVersion: v1
kind: ConfigMap
metadata:
  name: my-postgresql-configmap
  labels:
    app: postgres
data:
  log_truncate_on_rotation: "on"
  log_rotation_age: "1d"
  log_filename: "postgresql-%A.log"
  log_rotation_size: "0"
  logging_collector: "on"

Once Kubernetes updates the volume mount, it applies your changes, and the pods will be restarted based on the parameter Restart field value:

kubectl logs -l postgres-instance=postgres-sample,type=data -c reconfigure-instance

2022-11-21T17:20:21.294Z	INFO	reloading postgres configs
2022-11-21T17:20:22.047Z	INFO	postgres config changes have been successfully applied
2022-11-21T17:20:22.048Z	INFO	reloaded based on file changes	{"file": "/etc/customconfig/postgresql.conf"

Verifying PostgreSQL parameters

Ensure your instance has a "Running" status. Use kubectl get to review the field:

kubectl get postgres/postgres-sample

with an output similar to:

NAME              STATUS    BACKUP LOCATION         AGE
postgres-sample   Running   backuplocation-sample   17m

Log into the pods using kubectl exec, and utilize the psql tool:

kubectl exec -it pod/postgres-sample-1 -- psql

The output could be similar to:

psql (14.5 (VMware Postgres 14.5.0))
Type "help" for help.

Use the show command to verify any parameter:

postgres=# show max_connections;

 max_connections
-----------------
 250
(1 row)

postgres=# show log_timezone;

 log_timezone
--------------
 GMT
(1 row)

To troubleshoot any errors or undesired parameter output, refer to Troubleshooting ConfigMap changes.