Repository: cloudnative-pg/cloudnative-pg

Issue:
[Feature]: PostgreSQL system identifier in the cluster status
### Is there an existing issue already for this feature request/idea?

- [x] I have searched for an existing issue, and could not find anything. I believe this is a new feature request to be evaluated.

### What problem is this feature going to solve? Why should it be added?

We currently store the system id of the Postgres cluster in a couple of places (e.g. instance status) but not in the cluster - although it is unique for the entire cluster.

### Describe the solution you'd like

Add it to the status.

### Describe alternatives you've considered

N/A

### Additional context

_No response_

### Backport?

Yes

### Are you willing to actively contribute to this feature?

Yes

### Code of Conduct

- [x] I agree to follow this project's Code of Conduct

Hints from issue comments:

Solution patch:
diff --git a/.devcontainer/devcontainer.json b/.devcontainer/devcontainer.json
index e66ed8db..f8469ae5 100644
--- a/.devcontainer/devcontainer.json
+++ b/.devcontainer/devcontainer.json
@@ -12,7 +12,7 @@
       "cilium": "none"
     },
     "ghcr.io/guiyomh/features/golangci-lint:0": {},
-    "ghcr.io/devcontainers-contrib/features/kubectx-kubens:1": {},
+    "ghcr.io/devcontainers-extra/features/kubectx-kubens:1": {},
     "ghcr.io/dhoeric/features/stern:1": {}
   },
 
diff --git a/.wordlist-en-custom.txt b/.wordlist-en-custom.txt
index cc086513..db131a22 100644
--- a/.wordlist-en-custom.txt
+++ b/.wordlist-en-custom.txt
@@ -480,6 +480,7 @@ SynchronizeReplicasConfiguration
 SynchronousReplicaConfiguration
 SynchronousReplicaConfigurationMethod
 Synopsys
+SystemID
 TCP
 TLS
 TLSv
@@ -1331,6 +1332,7 @@ synchronizeReplicas
 synchronizeReplicasCache
 sys
 syslog
+systemID
 systemd
 sysv
 tAc
diff --git a/api/v1/cluster_types.go b/api/v1/cluster_types.go
index 3602933f..66c16160 100644
--- a/api/v1/cluster_types.go
+++ b/api/v1/cluster_types.go
@@ -956,6 +956,10 @@ type ClusterStatus struct {
 	// WAL file, and Time of latest checkpoint
 	// +optional
 	DemotionToken string `json:"demotionToken,omitempty"`
+
+	// SystemID is the latest detected PostgreSQL SystemID
+	// +optional
+	SystemID string `json:"systemID,omitempty"`
 }
 
 // ImageInfo contains the information about a PostgreSQL image
@@ -996,6 +1000,9 @@ const (
 	ConditionBackup ClusterConditionType = "LastBackupSucceeded"
 	// ConditionClusterReady represents whether a cluster is Ready
 	ConditionClusterReady ClusterConditionType = "Ready"
+	// ConditionConsistentSystemID is true when the all the instances of the
+	// cluster report the same System ID.
+	ConditionConsistentSystemID ClusterConditionType = "ConsistentSystemID"
 )
 
 // ConditionStatus defines conditions of resources
diff --git a/config/crd/bases/postgresql.cnpg.io_clusters.yaml b/config/crd/bases/postgresql.cnpg.io_clusters.yaml
index d9aa43c9..2eb4f627 100644
--- a/config/crd/bases/postgresql.cnpg.io_clusters.yaml
+++ b/config/crd/bases/postgresql.cnpg.io_clusters.yaml
@@ -6376,6 +6376,9 @@ spec:
                       of switching a cluster to a replica cluster.
                     type: boolean
                 type: object
+              systemID:
+                description: SystemID is the latest detected PostgreSQL SystemID
+                type: string
               tablespacesStatus:
                 description: TablespacesStatus reports the state of the declarative
                   tablespaces in the cluster
diff --git a/docs/src/cloudnative-pg.v1.md b/docs/src/cloudnative-pg.v1.md
index 4ffd8790..77744325 100644
--- a/docs/src/cloudnative-pg.v1.md
+++ b/docs/src/cloudnative-pg.v1.md
@@ -2289,6 +2289,13 @@ TimeLineID, Latest checkpoint's REDO location, Latest checkpoint's REDO
 WAL file, and Time of latest checkpoint</p>
 </td>
 </tr>
+<tr><td><code>systemID</code><br/>
+<i>string</i>
+</td>
+<td>
+   <p>SystemID is the latest detected PostgreSQL SystemID</p>
+</td>
+</tr>
 </tbody>
 </table>
 
diff --git a/internal/controller/cluster_status.go b/internal/controller/cluster_status.go
index 6cf6d3c3..8819e703 100644
--- a/internal/controller/cluster_status.go
+++ b/internal/controller/cluster_status.go
@@ -28,9 +28,11 @@ import (
 
 	"github.com/cloudnative-pg/machinery/pkg/log"
 	pgTime "github.com/cloudnative-pg/machinery/pkg/postgres/time"
+	"github.com/cloudnative-pg/machinery/pkg/stringset"
 	batchv1 "k8s.io/api/batch/v1"
 	corev1 "k8s.io/api/core/v1"
 	apierrs "k8s.io/apimachinery/pkg/api/errors"
+	"k8s.io/apimachinery/pkg/api/meta"
 	metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
 	"k8s.io/utils/strings/slices"
 	"sigs.k8s.io/controller-runtime/pkg/client"
@@ -763,12 +765,53 @@ func (r *ClusterReconciler) updateClusterStatusThatRequiresInstancesState(
 	}
 
 	// we update any relevant cluster status that depends on the primary instance
+	detectedSystemID := stringset.New()
 	for _, item := range statuses.Items {
 		// we refresh the last known timeline on the status root.
 		// This avoids to have a zero timeline id in case that no primary instance is up during reconciliation.
 		if item.IsPrimary && item.TimeLineID != 0 {
 			cluster.Status.TimelineID = item.TimeLineID
 		}
+		if item.SystemID != "" {
+			detectedSystemID.Put(item.SystemID)
+		}
+	}
+
+	// we update the system ID field in the cluster status
+	switch detectedSystemID.Len() {
+	case 0:
+		cluster.Status.SystemID = ""
+
+		message := "No instances are present in the cluster to report a system ID."
+		if len(statuses.Items) > 0 {
+			message = "Instances are present, but none have reported a system ID."
+		}
+
+		meta.SetStatusCondition(&cluster.Status.Conditions, metav1.Condition{
+			Type:    string(apiv1.ConditionConsistentSystemID),
+			Status:  metav1.ConditionFalse,
+			Reason:  "NotFound",
+			Message: message,
+		})
+
+	case 1:
+		cluster.Status.SystemID = detectedSystemID.ToList()[0]
+		meta.SetStatusCondition(&cluster.Status.Conditions, metav1.Condition{
+			Type:    string(apiv1.ConditionConsistentSystemID),
+			Status:  metav1.ConditionTrue,
+			Reason:  "Unique",
+			Message: "A single, unique system ID was found across reporting instances.",
+		})
+
+	default:
+		// the instances are reporting an inconsistent system ID
+		cluster.Status.SystemID = ""
+		meta.SetStatusCondition(&cluster.Status.Conditions, metav1.Condition{
+			Type:    string(apiv1.ConditionConsistentSystemID),
+			Status:  metav1.ConditionFalse,
+			Reason:  "Mismatch",
+			Message: fmt.Sprintf("Multiple differing system IDs reported by instances: %q", detectedSystemID.ToSortedList()),
+		})
 	}
 
 	if !reflect.DeepEqual(existingClusterStatus, cluster.Status) {



Test patch:
diff --git a/internal/controller/cluster_status_test.go b/internal/controller/cluster_status_test.go
index 605c0b32..4e9d4786 100644
--- a/internal/controller/cluster_status_test.go
+++ b/internal/controller/cluster_status_test.go
@@ -24,11 +24,14 @@ import (
 
 	batchv1 "k8s.io/api/batch/v1"
 	corev1 "k8s.io/api/core/v1"
+	"k8s.io/apimachinery/pkg/api/meta"
+	metav1 "k8s.io/apimachinery/pkg/apis/meta/v1"
 	"k8s.io/apimachinery/pkg/types"
 	"k8s.io/apimachinery/pkg/util/rand"
 
 	v1 "github.com/cloudnative-pg/cloudnative-pg/api/v1"
 	"github.com/cloudnative-pg/cloudnative-pg/pkg/certs"
+	"github.com/cloudnative-pg/cloudnative-pg/pkg/postgres"
 	"github.com/cloudnative-pg/cloudnative-pg/pkg/reconciler/persistentvolumeclaim"
 
 	. "github.com/onsi/ginkgo/v2"
@@ -174,3 +177,214 @@ var _ = Describe("cluster_status unit tests", func() {
 		})
 	})
 })
+
+var _ = Describe("updateClusterStatusThatRequiresInstancesState tests", func() {
+	var (
+		env     *testingEnvironment
+		cluster *v1.Cluster
+	)
+
+	BeforeEach(func() {
+		env = buildTestEnvironment()
+		cluster = newFakeCNPGCluster(env.client, newFakeNamespace(env.client))
+	})
+
+	It("should handle empty status list", func(ctx SpecContext) {
+		statuses := postgres.PostgresqlStatusList{}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.InstancesReportedState).To(BeEmpty())
+		Expect(cluster.Status.SystemID).To(BeEmpty())
+
+		condition := meta.FindStatusCondition(cluster.Status.Conditions, string(v1.ConditionConsistentSystemID))
+		Expect(condition).ToNot(BeNil())
+		Expect(condition.Status).To(Equal(metav1.ConditionFalse))
+		Expect(condition.Reason).To(Equal("NotFound"))
+		Expect(condition.Message).To(Equal("No instances are present in the cluster to report a system ID."))
+	})
+
+	It("should handle instances without SystemID", func(ctx SpecContext) {
+		statuses := postgres.PostgresqlStatusList{
+			Items: []postgres.PostgresqlStatus{
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-1"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.1"},
+					},
+					IsPrimary:  true,
+					TimeLineID: 123,
+					SystemID:   "",
+				},
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-2"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.2"},
+					},
+					IsPrimary: false,
+					SystemID:  "",
+				},
+			},
+		}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.InstancesReportedState).To(HaveLen(2))
+		Expect(cluster.Status.TimelineID).To(Equal(123))
+		Expect(cluster.Status.SystemID).To(BeEmpty())
+
+		condition := meta.FindStatusCondition(cluster.Status.Conditions, string(v1.ConditionConsistentSystemID))
+		Expect(condition).ToNot(BeNil())
+		Expect(condition.Status).To(Equal(metav1.ConditionFalse))
+		Expect(condition.Reason).To(Equal("NotFound"))
+		Expect(condition.Message).To(Equal("Instances are present, but none have reported a system ID."))
+	})
+
+	It("should handle instances with a single SystemID", func(ctx SpecContext) {
+		const systemID = "system123"
+		statuses := postgres.PostgresqlStatusList{
+			Items: []postgres.PostgresqlStatus{
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-1"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.1"},
+					},
+					IsPrimary:  true,
+					TimeLineID: 123,
+					SystemID:   systemID,
+				},
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-2"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.2"},
+					},
+					IsPrimary: false,
+					SystemID:  systemID,
+				},
+			},
+		}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.InstancesReportedState).To(HaveLen(2))
+		Expect(cluster.Status.TimelineID).To(Equal(123))
+		Expect(cluster.Status.SystemID).To(Equal(systemID))
+
+		condition := meta.FindStatusCondition(cluster.Status.Conditions, string(v1.ConditionConsistentSystemID))
+		Expect(condition).ToNot(BeNil())
+		Expect(condition.Status).To(Equal(metav1.ConditionTrue))
+		Expect(condition.Reason).To(Equal("Unique"))
+		Expect(condition.Message).To(Equal("A single, unique system ID was found across reporting instances."))
+	})
+
+	It("should handle instances with multiple SystemIDs", func(ctx SpecContext) {
+		statuses := postgres.PostgresqlStatusList{
+			Items: []postgres.PostgresqlStatus{
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-1"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.1"},
+					},
+					IsPrimary:  true,
+					TimeLineID: 123,
+					SystemID:   "system1",
+				},
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-2"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.2"},
+					},
+					IsPrimary: false,
+					SystemID:  "system2",
+				},
+			},
+		}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.InstancesReportedState).To(HaveLen(2))
+		Expect(cluster.Status.TimelineID).To(Equal(123))
+		Expect(cluster.Status.SystemID).To(BeEmpty())
+
+		condition := meta.FindStatusCondition(cluster.Status.Conditions, string(v1.ConditionConsistentSystemID))
+		Expect(condition).ToNot(BeNil())
+		Expect(condition.Status).To(Equal(metav1.ConditionFalse))
+		Expect(condition.Reason).To(Equal("Mismatch"))
+		Expect(condition.Message).To(ContainSubstring("Multiple differing system IDs reported by instances:"))
+		Expect(condition.Message).To(ContainSubstring("system1"))
+		Expect(condition.Message).To(ContainSubstring("system2"))
+	})
+
+	It("should update timeline ID from the primary instance", func(ctx SpecContext) {
+		const timelineID = 999
+		statuses := postgres.PostgresqlStatusList{
+			Items: []postgres.PostgresqlStatus{
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-1"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.1"},
+					},
+					IsPrimary:  true,
+					TimeLineID: timelineID,
+					SystemID:   "system1",
+				},
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-2"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.2"},
+					},
+					IsPrimary:  false,
+					TimeLineID: 123,
+					SystemID:   "system1",
+				},
+			},
+		}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.TimelineID).To(Equal(timelineID))
+	})
+
+	It("should correctly populate InstancesReportedState", func(ctx SpecContext) {
+		statuses := postgres.PostgresqlStatusList{
+			Items: []postgres.PostgresqlStatus{
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-1"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.1"},
+					},
+					IsPrimary:  true,
+					TimeLineID: 123,
+				},
+				{
+					Pod: &corev1.Pod{
+						ObjectMeta: metav1.ObjectMeta{Name: "pod-2"},
+						Status:     corev1.PodStatus{PodIP: "192.168.1.2"},
+					},
+					IsPrimary:  false,
+					TimeLineID: 123,
+				},
+			},
+		}
+
+		err := env.clusterReconciler.updateClusterStatusThatRequiresInstancesState(ctx, cluster, statuses)
+		Expect(err).ToNot(HaveOccurred())
+
+		Expect(cluster.Status.InstancesReportedState).To(HaveLen(2))
+
+		state1 := cluster.Status.InstancesReportedState["pod-1"]
+		Expect(state1.IsPrimary).To(BeTrue())
+		Expect(state1.TimeLineID).To(Equal(123))
+		Expect(state1.IP).To(Equal("192.168.1.1"))
+
+		state2 := cluster.Status.InstancesReportedState["pod-2"]
+		Expect(state2.IsPrimary).To(BeFalse())
+		Expect(state2.TimeLineID).To(Equal(123))
+		Expect(state2.IP).To(Equal("192.168.1.2"))
+	})
+})