Updating PostgreSQL parameters in Kubernetes and other systems

When it comes to changing PostgreSQL parameters, there is an important point to understand when these parameters take effect in your system. Some of them require  the database server to be restarted, others take effect without it. To know which parameters fall into which category, pg_settings is your friend. Here, the column context is of importance for this purpose, different values on the context mean different things,  for complete details refer to the official source. The gist is if the value there is internal or postmaster, then it can not be changed without server restart. If the value is user, sighup or backend, we are good to go without a restart.

Methods to update PostgreSQL configuration settings

What are the ways to update the PostgreSQL parameters for a cluster? There are basically 3 ways to do that:

• ALTER SYSTEM command
• patronictl for Patroni
• Modify cluster manistest

ALTER SYSTEM command

If this is your first experience in using PostgreSQL on Kubernetes or OpenShift, you might be used to the technique of modifying the postgresql.conf file or changing the parameters at the client, using ALTER SYSTEM and SELECT pg_reload_conf(). 

Here is a working example:

However, this will likely become more complex as you would have to do that for all the nodes of the cluster, and it will be lost at the next restart, since the values are not written to the conf file.

Patronictl to manage Patroni deployments

The next  way is to use Patroni commands to get the job done. For this method, you would also need to exec into the master pod and then employ patronictl utility to modify the Postgres parameters. So, we have patronictl show-config, which displays all the current settings used by the cluster. There is a section called postgresql and a  subsection called parameters. These parameters do not require a restart. If you wish to change any of these parameters, use patronictl edit-config.  to open this config file in vi, then change the parameters as per your requirement. Afterwards, save the file and exit. Finally, you may go ahead and check if the parameters have taken effect on the client and/or application side of things. More about this method can be found at the official Patroni documentation.

Modify cluster-manifest

Using a PostgreSQL operator has its own benefits—among them is ease  of use. So, the change in the parameters is now made possible using the cluster manifest itself. To do so, fire in the command to edit postgres resource with the name of the cluster. For example, if you are using Kubernetes, the following will work:

Now, this will give you the cluster manifest, now you may add/update the   parameters in the postgres section as per the requirements. As soon as you change these parameters and save the manifest, the updated parameters take effect.

Want to learn more about modifying the cluster? You can find detailed information here.