PostgreSQL cluster replicas get out of sync

Diagnosing the problem

To confirm that the replicas are out of sync:

1. Check the logs for errors:
   1. Search the PostgreSQL operator logs for the phrase WAL:

      oc logs -n ${PROJECT_CPD_INST_OPERATORS} \
      -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 ${PROJECT_CPD_INST_OPERANDS} \
      -l app.kubernetes.io/name=cloud-native-postgresql \
      | grep '"level":"error"' \
      | grep 'Failed to execute pg_rewind'

      Important: 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 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>

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

   oc get clusters.postgresql.k8s.enterprisedb.io \
   -n ${PROJECT_CPD_INST_OPERANDS}

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 log sequence number (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:

      Environments with one replica pod

      1. Set the PRIMARY_POD and REPLICA_POD environment variables:

         export PRIMARY_POD=$(oc get pod -n ${PROJECT_CPD_INST_OPERANDS} -l k8s.enterprisedb.io/cluster=${POSTGRES_CLUSTER},role=primary -o jsonpath="{.items[0].metadata.name}")
         export REPLICA_POD=$(oc get pod -n ${PROJECT_CPD_INST_OPERANDS} -l k8s.enterprisedb.io/cluster=${POSTGRES_CLUSTER},role=replica -o name)

      2. Set the PRIMARY_POD_LSN and REPLICA_POD_LSN environment variables:

         export PRIMARY_POD_LSN=$(oc exec ${PRIMARY_POD} -n ${PROJECT_CPD_INST_OPERANDS} -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 ${PROJECT_CPD_INST_OPERANDS} 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]*$//')

      Environments with multiple replica pods

      1. Set the PRIMARY_POD environment variable:

         export PRIMARY_POD=$(oc get pod -n ${PROJECT_CPD_INST_OPERANDS} -l k8s.enterprisedb.io/cluster=${POSTGRES_CLUSTER},role=primary -o jsonpath="{.items[0].metadata.name}")

      2. Get the name of the replica pods:

         oc get pod -n ${PROJECT_CPD_INST_OPERANDS} -l k8s.enterprisedb.io/cluster=${POSTGRES_CLUSTER},role=replica -o name

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

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

         export REPLICA_POD=<pod-name>

      4. Set the PRIMARY_POD_LSN and REPLICA_POD_LSN environment variables:

         export PRIMARY_POD_LSN=$(oc exec ${PRIMARY_POD} -n ${PROJECT_CPD_INST_OPERANDS} -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 ${PROJECT_CPD_INST_OPERANDS} 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.