PostgreSQL cluster replicas get out of sync

This issue can manifest in several ways:

• You see a mismatch in the data between the replicas.
• The database cluster is stuck in the Waiting for the instances to become active state.
  To check the status of the database cluster, run the following command. Replace <instance-namespace> with the namespace where the common-service-db cluster instance is installed.

  oc get cluster.pg.ibm.com \ -n <instance-namespace>

• One or more of the database pods are in the CrashLoopBackOff state or are stuck in the Running state.
  To check the status of the database pods, run the following command:

  oc get pods \ -n <instance-namespace> \ -l pg.ibm.com/podRole=instance

  Note: If the command returns common-service-db pods in the CrashLoopBackOff state, complete step 1b in Diagnosing the problem to determine whether you need to contact IBM Support for help with resolving the issue.
• A backup checkpoint job, such as zen-checkpoint-backup-job-*, fails.

Confirm that the replicas are out of sync:

1. Check the logs for errors:
   1. Search the PostgreSQL operator logs for the phrase, WAL. In the following command, replace the <operator-namespace> value with the namespace where IBM Cloud Pak® foundational services, also called the IBM Common Service Operator, is installed.

      oc logs -n <operator-namespace> \ -l app.kubernetes.io/name=cloud-native-postgresql \ | grep "WAL"

      Look for messages with the following phrases:

      • missing WAL files
      • tried restoring WALs
   2. Search the PostgreSQL pod logs for the phrase, Failed to execute pg_rewind:

      oc logs -n <instance-namespace> \ -l pg.ibm.com/podRole=instance \ | grep '"level":"error"' \ | grep 'Failed to execute pg_rewind'

      Note: If the common-service-db pods are in the CrashLoopBackOff state, and you see the preceding phrases in the common-service-db pod logs, contact IBM Support for assistance for resolving the following issue: common-service-db pod in CrashLoopBackOff state due to EDB WAL storage full.

   For other pods, if you see either of the preceding phrases in the log files, proceed to the next step.

2. Set the POSTGRES_CLUSTER environment variable to the name of the PostgreSQL cluster where you want to check the replica status:

   export POSTGRES_CLUSTER=<cluster-name>

   Note: If you don't know the name of the PostgreSQL cluster, run the following command to see a list of any PostgreSQL clusters in the project:

   oc get cluster.pg.ibm.com \ -n <instance-namespace>

3. If you have the Cloud Native PostgreSQL (CNP) plug-in for kubectl, you can use it to check the status of the PostgreSQL cluster:
   1. Run the following command to check the status of the cluster:

      kubectl cnp status ${POSTGRES_CLUSTER}

   2. Compare the Current LSN of the primary node with the Received LSN and Replay LSN for the replicas.
      • If the log sequence number (LSN) is the same, the replicas are in sync.
      • If the LSN is different, proceed to Resolving the problem.
4. If you do not have the CNP plug-in, run the following commands to compare the log sequence number on the primary node and on the replicas:
   1. Set the PRIMARY_POD, PRIMARY_POD_LSN, REPLICA_POD, and REPLICA_POD_LSN environment variables:
      • For environments with one replica pod, set the PRIMARY_POD and REPLICA_POD environment variables:

        export PRIMARY_POD=$(oc get pod -n <instance-namespace> -l pg.ibm.com/cluster=${POSTGRES_CLUSTER},role=primary -o jsonpath="{.items[0].metadata.name}") export REPLICA_POD=$(oc get pod -n <instance-namespace> -l pg.ibm.com/cluster=${POSTGRES_CLUSTER},role=replica -o name)

        Set the PRIMARY_POD_LSN and REPLICA_POD_LSN environment variables:

        export PRIMARY_POD_LSN=$(oc exec ${PRIMARY_POD} -n <instance-namespace> -c postgres -- psql -U postgres -c "SELECT pg_current_wal_lsn();" | sed -n '3p' | sed -e 's/^[ \t]*//' -e 's/[ \t]*$//') export REPLICA_POD_LSN=$(oc -n <instance-namespace> exec -t ${REPLICA_POD} $ -c postgres -- psql -U postgres -c "SELECT pg_last_wal_replay_lsn();" | sed -n '3p' | sed -e 's/^[ \t]*//' -e 's/[ \t]*$//')

      • For environments with multiple replica pods, set the PRIMARY_POD environment variable:

        export PRIMARY_POD=$(oc get pod -n <instance-namespace> -l pg.ibm.com/cluster=${POSTGRES_CLUSTER},role=primary -o jsonpath="{.items[0].metadata.name}")

        1. Get the name of the replica pods. The command returns the list of pods that are associated with the specified PostgreSQL cluster, (${POSTGRES_CLUSTER}).

           oc get pod -n <instance-namespace> -l pg.ibm.com/cluster=${POSTGRES_CLUSTER},role=replica -o name

        2. Set the REPLICA_POD environment variable to one of the replica pods:

           export REPLICA_POD=<pod-name>

        3. Set the PRIMARY_POD_LSN and REPLICA_POD_LSN environment variables:

           export PRIMARY_POD_LSN=$(oc exec ${PRIMARY_POD} -n <instance-namespace> -c postgres -- psql -U postgres -c "SELECT pg_current_wal_lsn();" | sed -n '3p' | sed -e 's/^[ \t]*//' -e 's/[ \t]*$//') export REPLICA_POD_LSN=$(oc -n <instance-namespace> exec -t ${REPLICA_POD} $ -c postgres -- psql -U postgres -c "SELECT pg_last_wal_replay_lsn();" | sed -n '3p' | sed -e 's/^[ \t]*//' -e 's/[ \t]*$//')

   2. For each replica, compare the LSN for the primary node and the replica pod:

      echo $PRIMARY_POD_LSN, $REPLICA_POD_LSN

      The LSN of the primary node and the replica pod should be the same. If the LSN of any of the replica pods is different than the LSN of the primary pod, the PostgreSQL cluster is in an out-of-sync state and you must complete Resolving the problem.

If the LSN of the primary pod and the replica pod are different, complete the following steps:

1. Check whether the EDB Postgres cluster is fenced:

   oc get cluster.pg.ibm.com ${POSTGRES_CLUSTER} -o yaml | grep fencedInstances

2. If the EDB Postgres cluster is fenced, lift the fencing:

   oc annotate cluster.pg.ibm.com ${POSTGRES_CLUSTER} pg.ibm.com/fencedInstances-

3. Wait several minutes. Then, check the status of the EDB Postgres cluster:

   oc get cluster.pg.ibm.com ${POSTGRES_CLUSTER} \ -n <instance-namespace>

   • If the status is Cluster in healthy state, no additional resolution is required.
   • If the status is not Cluster in healthy state, continue to the next step.
4. Get the name of the persistent volume claim associated with the replica pod:

   oc describe pod ${REPLICA_POD} -n <instance-namespace>

   In the Volumes section, get the ClaimName value. The name of the persistent volume claim is usually the same as the name of the replica pod.
5. Delete the persistent volume claim:
   • If the claim name is the same as the replica pod name, use the following command:

     oc delete pvc ${REPLICA_POD} -n <instance-namespace>

   • If the claim name is different from the replica pod name, use the following command:

     oc delete pvc <pvc-name> -n <instance-namespace>

6. Delete the replica pod:

   oc delete pod ${REPLICA_POD} -n <instance-namespace>