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	hba_file
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.

Note
hba_file is a parameter in the postgresql.conf file that specifies the location of the Postgres HBA configuration file. You can append to the pg_hba.conf file by using a custom ConfigMap; you cannot assign the file a different path in postgresql.conf.

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.yaml is 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.
Per the PostgreSQL - STIG Readiness Guide, the log_destination parameter may require special steps in order to set it to syslog. See Error Reporting and Logging in the PostgreSQL documentation for more information.

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

2023-04-13T15:54:28.283Z	INFO	start updating pg_hba.conf
2023-04-13T15:54:28.284Z	INFO	finished updating pg_hba.conf
server signaled
2023-04-13T15:54:29.074Z	INFO	reloading postgres configs
2023-04-13T15:54:30.179Z	INFO	postgres config changes have been successfully applied
2023-04-13T15:54:30.179Z	INFO	reloaded based on file changes

Verifying PostgreSQL parameters

Run the following command to ensure that 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

Run the following command to check the conditions of the instance. If the custom configuration has been applied successfully, there should be a condition of type CustomConfigStatus with the Status as true.
```
kubectl describe postgres <instance-name> -n <namespace-name>
```
Alternatively, you can log into the pods using kubectl exec, and run the following command to start 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.