Change the PostgreSQL primary instance¶

The Operator uses PostgreSQL high-availability implementation based on the Patroni template . This means that each PostgreSQL cluster includes one member availiable for read/write transactions (PostgreSQL primary instance, or leader in terms of Patroni) and a number of replicas which can serve read requests only (standby members of the cluster).

You may wish to manually change the primary instance in your PostgreSQL cluster to achieve more control and meet specific requirements in various scenarios like planned maintenance, testing failover procedures, load balancing and performance optimization activities. Primary instance is re-elected during the automatic failover (Patroni’s “leader race” mechanism), but still there are use cases to controll this process manually.

In Percona Operator, the primary instance election can be controlled by the patroni.switchover section of the Custom Resource manifest. It allows you to enable switchover targeting a specific PostgreSQL instance as the new primary, or just running a failover if PostgreSQL cluster has entered a bad state.

This document provides instructions how to change the primary instance manually.

For the following steps, we assume that you have the PostgreSQL cluster up and running. The cluster name is cluster1.

Check the information about the cluster instances. Cluster instances are defined in the spec.instances Custom Resource section. By default you have one cluster instance named instance1 with 3 PostgreSQL instances in it. You can check which cluster instances you have. Do this using Kubernetes Labels as follows (replace the <namespace> placeholder with your value):

$ kubectl get pods -n <namespace> -l postgres-operator.crunchydata.com/cluster=cluster1 \ 
    -L postgres-operator.crunchydata.com/instance \
    -L postgres-operator.crunchydata.com/role | grep instance1

Sample output

cluster1-instance1-bmdp-0             4/4     Running   0          2m23s   cluster1-instance1-bmdp   replica
cluster1-instance1-fm7w-0             4/4     Running   0          2m22s   cluster1-instance1-fm7w   replica
cluster1-instance1-ttm9-0             4/4     Running   0          2m22s   cluster1-instance1-ttm9   master

PostgreSQL primary is labeled as master, while other PostgreSQL instances are labeled as replica.

Now update the following options in the partoni.switchover subsection of the Custom Resource:

patroni:
  switchover:
    enabled: true
    targetInstance: <instance-name>

You can do it with kubectl patch command, specifying the name of the instance that you want to be the new primary. For example, let’s set the cluster1-instance1-bmdp as a new PostgreSQL primary:

$ kubectl -n <namespace> patch pg cluster1 --type=merge --patch '{
"spec": {
   "patroni": { "switchover": { "enabled": "true" } },
   "patroni": { "switchover": { "targetInstance": "cluster1-instance1-bmdp" } }
}}'

Trigger the switchover by adding the annotation to your Custom Resource. The recommended way is to set the annotation with the timestamp, so you know when switchover took place. Replace the <namespace> placeholder with your value:
```
$ kubectl annotate -n <namespace> pg cluster1 postgres-operator.crunchydata.com/trigger-switchover="$(date)"
```

Verify that the cluster was annotated (replace the <namespace> placeholder with your value, as usual):

$ kubectl get pg cluster1 -o yaml -n <namespace>

Sample output

apiVersion: pgv2.percona.com/v2
kind: PerconaPGCluster
metadata:
  annotations:
    kubectl.kubernetes.io/last-applied-configuration: |
      {....
      "patroni":{"switchover":{"enabled":true,"targetInstance":"cluster1-instance1-bmdp"}},}

Now, check instances of your cluster once again to make sure the switchover took place:

$ kubectl get pods -n <namespace> -l postgres-operator.crunchydata.com/cluster=cluster1 \ 
    -L postgres-operator.crunchydata.com/instance \
    -L postgres-operator.crunchydata.com/role | grep instance1

Sample output

cluster1-instance1-bmdp-0             4/4     Running     0          24m   cluster1-instance1-bmdp   master
cluster1-instance1-fm7w-0             4/4     Running     0          24m   cluster1-instance1-fm7w   replica
cluster1-instance1-ttm9-0             4/4     Running     0          23m   cluster1-instance1-ttm9   replica

The primary now should be changed to cluster1-instance1-bmdp.

Get expert help¶

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services. Join K8S Squad to benefit from early access to features and “ask me anything” sessions with the Experts.

Community Forum Get a Percona Expert Join K8S Squad

Last update: 2024-09-02