Staying Ahead: Proactive Measures to Support PostgreSQL Cluster

Flo-Jo was a sprinter decades ahead of her time. Since 1988, generations of athletes have chased her legendary 10.49-second mark in the women’s 100 meters — a world record that remains untouchable. Image credit: Allsport UK/Allsport/Getty Images

In a Cloud Pak for Data (CPD) environment, you’ll notice that more and more services are migrating their metadata repositories from other database solutions to PostgreSQL. Depending on which services you’ve installed on the CPD, you might have several PostgreSQL clusters set up implicitly to store metadata with respect to the service. For example, if you’ve installed services like IBM Knowledge Catalog, Analytics Engine, IBM Data Lineage, etc., you’ll likely see multiple PostgreSQL instances running behind the scenes. From a platform administration perspective, it makes your life easier, as you have fewer databases to manage and worry about.

An example of list of PostgreSQL clusters on CPD. Newer version use `oc get cluster.postgresql`.

CPD is designed with self-healing capabilities that help you maintain the health and availability of PostgreSQL. It automatically replaces failed containers, reschedules workloads when nodes become unavailable, and works to ensure that the system remains in its desired state. However, in rare scenarios such as replica node failures, replication issues, data corruption, or inconsistencies where CPD may not be able to recover the PostgreSQL service on its own. In these cases, your intervention may be required. To help you troubleshoot more efficiently during critical situations, starting with CPD version 5.x, a powerful CLI plugin was introduced for managing Cloud Native PostgreSQL (CNP) clusters.

This CNP plugin, developed by EnterpriseDB, acts as an operator for Kubernetes and OpenShift environments, and it supports both PostgreSQL and EDB Postgres Advanced clusters. It significantly simplifies PostgreSQL troubleshooting, helping you minimize downtime and accelerate resolution time. With the CNP plugin, you benefit from centralized PostgreSQL diagnostics, improved visibility into cluster health, and automated steps for identifying and resolving issues.

It’s a good idea to have the CNP plugin installed and ready in advance. Getting and installing the plugin is very simple. You need to run the following command from the bastion node to install the plug-in:

curl -sSfL https://github.com/EnterpriseDB/kubectl-cnp/raw/main/install.sh | \
 sudo sh -s -- -b /usr/local/bin

Verify the installation by running:

oc cnp status <name of the postgresql cluster>

The CNP plugin usage:

Options avaialble with CPN plugin.

In a CPD environment, some of the commonly used plugin commands include status, logs, restart, fencing, promote, and destroy, among others.

The status shows a PostgreSQL cluster’s status in Kubernetes, showing the instance’s health and potential ongoing operations. This option helps to identify any issues with the PostgreSQL cluster.

An example of PostgreSQL cluster status.

The logs option allows to follow the logs of a collection of pods related to Postgres in a single go. It helps to gather insights on failure points.

Example of log extracted from a PostgreSQL cluster.

The restart option can be used to orchestrate a rollout restart for a certain cluster. This is useful to apply configuration changes to cluster dependent objects, such as ConfigMaps containing custom monitoring queries.

Example of PostgreSQL cluster restart command.

The promote option lets you promote a standby pod in the cluster to primary, so you can start with maintenance work or test a switch-over situation in your cluster.

Example of PostgreSQL cluster “promote” command. You need to provide the cluster name and a healthy replica pod name for promoting as primary.

The fencing option used for protecting the data in one, more, or even all instances of a PostgreSQL cluster when they appear to be malfunctioning. When an instance is fenced, the PostgreSQL server process (postmaster) is guaranteed to be shut down, while the pod is kept running. This makes sure that, until the fence is lifted, data on the pod is not modified by PostgreSQL and that the file system can be investigated for debugging and troubleshooting purposes. The “on” or “off” controls the fencing mode of a PostgreSQL cluster.

Example of fencing a PostgreSQL replica pod. You can fence the whole cluster or a particular replica pod.

The destroy option helps remove a PostgreSQL instance along with all associated PVCs from OpenShift. If one of the PostgreSQL instances is faulty or the pod is unhealthy, you can destroy the bad instance, and a new PostgreSQL instance will be recreated based on the healthy primary replica.

There are many other options available with the CNP plugin. You can consult the EDB documentation for other usage and information.

In summary, managing PostgreSQL within a Cloud Pak for Data (CPD) environment is becoming more streamlined as services continue migrating to a centralized PostgreSQL-based metadata repository. While CPD’s self-healing capabilities handle most failures automatically, there are rare cases that require manual intervention. By leveraging the Cloud Native PostgreSQL (CNP) CLI plugin introduced in CPD 5.x, you can gain deeper visibility into your cluster’s health, accelerate troubleshooting, and minimize downtime. Proactively installing and familiarizing yourself with the CNP plugin ensures you’re well-prepared to maintain the reliability and stability of your PostgreSQL infrastructure in critical situations.