Repository: cloudnative-pg/cloudnative-pg

Issue:
[Bug]: `pg_stat_wal` metrics fail with PostgreSQL 18
### Is there an existing issue already for this bug?

- [x] I have searched for an existing issue, and could not find anything. I believe this is a new bug.

### I have read the troubleshooting guide

- [x] I have read the troubleshooting guide and I think this is a new bug.

### I am running a supported version of CloudNativePG

- [x] I have read the troubleshooting guide and I think this is a new bug.

### Contact Details

_No response_

### Version

trunk (main)

### What version of Kubernetes are you using?

1.32

### What is your Kubernetes environment?

Self-managed: kind (evaluation)

### How did you install the operator?

YAML manifest

### What happened?

https://github.com/cloudnative-pg/postgres-trunk-containers E2E tests highlighted an issue with collecting `pg_stat_wal` metrics when using the trunk of PostgreSQL (e.g PG18).

```
{"level":"error","ts":"2025-02-26T15:02:35.699987751Z","msg":"while collecting pg_wal_stat","logging_pod":"cluster-example-1","error":"ERROR: column \"wal_write\" does not exist (SQLSTATE 42703)","stacktrace":"github.com/cloudnative-pg/machinery/pkg/log.(*logger).Error\n\t/Users/nfei/go/pkg/mod/github.com/cloudnative-pg/machinery@v0.0.0-20250102082645-95c37fe624d0/pkg/log/log.go:125\ngithub.com/cloudnative-pg/machinery/pkg/log.Error\n\t/Users/nfei/go/pkg/mod/github.com/cloudnative-pg/machinery@v0.0.0-20250102082645-95c37fe624d0/pkg/log/log.go:163\ngithub.com/cloudnative-pg/cloudnative-pg/pkg/management/postgres/webserver/metricserver.(*Exporter).collectPgMetrics\n\tpkg/management/postgres/webserver/metricserver/pg_collector.go:427\ngithub.com/cloudnative-pg/cloudnative-pg/pkg/management/postgres/webserver/metricserver.(*Exporter).Collect\n\tpkg/management/postgres/webserver/metricserver/pg_collector.go:304\ngithub.com/prometheus/client_golang/prometheus.(*Registry).Gather.func1\n\t/Users/nfei/go/pkg/mod/github.com/prometheus/client_golang@v1.21.0/prometheus/registry.go:456"}
```

The following patch https://github.com/postgres/postgres/commit/2421e9a51d20bb83154e54a16ce628f9249fa907 has changed the definition of the `pg_stat_wal` view, removing some columns whose information is already part of `pg_stat_io` (for which we don't produce metrics by default).








### Cluster resource

```shell

```

### Relevant log output

```shell

```

### Code of Conduct

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

Discussion:

Solution patch:
diff --git a/pkg/management/postgres/probes.go b/pkg/management/postgres/probes.go
index bebc986d..832d57a1 100644
--- a/pkg/management/postgres/probes.go
+++ b/pkg/management/postgres/probes.go
@@ -583,7 +583,7 @@ func (instance *Instance) IsWALReceiverActive() (bool, error) {
 	return result, nil
 }
 
-// PgStatWal is a representation of the pg_stat_wal table
+// PgStatWal is a representation of the pg_stat_wal table, introduced in PostgreSQL 14.
 type PgStatWal struct {
 	WalRecords     int64
 	WalFpi         int64
@@ -596,7 +596,7 @@ type PgStatWal struct {
 	StatsReset     string
 }
 
-// TryGetPgStatWAL retrieves pg_wal_stat on pg version 14 and further
+// TryGetPgStatWAL retrieves pg_stat_wal on pg version 14 and further
 func (instance *Instance) TryGetPgStatWAL() (*PgStatWal, error) {
 	version, err := instance.GetPgVersion()
 	if err != nil || version.Major < 14 {
@@ -608,31 +608,56 @@ func (instance *Instance) TryGetPgStatWAL() (*PgStatWal, error) {
 		return nil, err
 	}
 
+	// Since PostgreSQL 18, `wal_write`, `wal_sync`, `wal_write_time` and
+	// `wal_sync_time` have been removed.
+	// See https://github.com/postgres/postgres/commit/2421e9a51d20bb83154e54a16ce628f9249fa907
 	var pgWalStat PgStatWal
-	row := superUserDB.QueryRow(
-		`SELECT
+	if version.Major < 18 {
+		row := superUserDB.QueryRow(
+			`SELECT
+			wal_records,
+			wal_fpi,
+			wal_bytes,
+			wal_buffers_full,
+			wal_write,
+			wal_sync,
+			wal_write_time,
+			wal_sync_time,
+			stats_reset
+			FROM pg_catalog.pg_stat_wal`)
+		if err := row.Scan(
+			&pgWalStat.WalRecords,
+			&pgWalStat.WalFpi,
+			&pgWalStat.WalBytes,
+			&pgWalStat.WALBuffersFull,
+			&pgWalStat.WalWrite,
+			&pgWalStat.WalSync,
+			&pgWalStat.WalWriteTime,
+			&pgWalStat.WalSyncTime,
+			&pgWalStat.StatsReset,
+		); err != nil {
+			return nil, err
+		}
+	}
+
+	if version.Major >= 18 {
+		row := superUserDB.QueryRow(
+			`SELECT
         	wal_records,
 		wal_fpi,
 		wal_bytes,
 		wal_buffers_full,
-		wal_write,
-		wal_sync,
-		wal_write_time,
-		wal_sync_time,
 		stats_reset
 	    FROM pg_catalog.pg_stat_wal`)
-	if err := row.Scan(
-		&pgWalStat.WalRecords,
-		&pgWalStat.WalFpi,
-		&pgWalStat.WalBytes,
-		&pgWalStat.WALBuffersFull,
-		&pgWalStat.WalWrite,
-		&pgWalStat.WalSync,
-		&pgWalStat.WalWriteTime,
-		&pgWalStat.WalSyncTime,
-		&pgWalStat.StatsReset,
-	); err != nil {
-		return nil, err
+		if err := row.Scan(
+			&pgWalStat.WalRecords,
+			&pgWalStat.WalFpi,
+			&pgWalStat.WalBytes,
+			&pgWalStat.WALBuffersFull,
+			&pgWalStat.StatsReset,
+		); err != nil {
+			return nil, err
+		}
 	}
 
 	return &pgWalStat, nil
diff --git a/pkg/management/postgres/webserver/metricserver/pg_collector.go b/pkg/management/postgres/webserver/metricserver/pg_collector.go
index 2fd2cc7a..392ce31c 100644
--- a/pkg/management/postgres/webserver/metricserver/pg_collector.go
+++ b/pkg/management/postgres/webserver/metricserver/pg_collector.go
@@ -232,7 +232,8 @@ func newMetrics() *metrics {
 				Namespace: PrometheusNamespace,
 				Subsystem: subsystem,
 				Name:      "wal_write",
-				Help:      "Number of times WAL buffers were written out to disk via XLogWrite request. Only available on PG 14+",
+				Help: "Number of times WAL buffers were written out to disk via XLogWrite request." +
+					" Only available on PG 14 to 17.",
 			}, []string{"stats_reset"}),
 			WalSync: prometheus.NewGaugeVec(prometheus.GaugeOpts{
 				Namespace: PrometheusNamespace,
@@ -240,7 +241,7 @@ func newMetrics() *metrics {
 				Name:      "wal_sync",
 				Help: "Number of times WAL files were synced to disk via issue_xlog_fsync request " +
 					"(if fsync is on and wal_sync_method is either fdatasync, fsync or fsync_writethrough, otherwise zero)." +
-					" Only available on PG 14+",
+					" Only available on PG 14 to 17.",
 			}, []string{"stats_reset"}),
 			WalWriteTime: prometheus.NewGaugeVec(prometheus.GaugeOpts{
 				Namespace: PrometheusNamespace,
@@ -249,7 +250,7 @@ func newMetrics() *metrics {
 				Help: "Total amount of time spent writing WAL buffers to disk via XLogWrite request, in milliseconds " +
 					"(if track_wal_io_timing is enabled, otherwise zero). This includes the sync time when wal_sync_method " +
 					"is either open_datasync or open_sync." +
-					" Only available on PG 14+",
+					" Only available on PG 14 to 17.",
 			}, []string{"stats_reset"}),
 			WalSyncTime: prometheus.NewGaugeVec(prometheus.GaugeOpts{
 				Namespace: PrometheusNamespace,
@@ -257,7 +258,7 @@ func newMetrics() *metrics {
 				Name:      "wal_sync_time",
 				Help: "Total amount of time spent syncing WAL files to disk via issue_xlog_fsync request, in milliseconds " +
 					"(if track_wal_io_timing is enabled, fsync is on, and wal_sync_method is either fdatasync, fsync or " +
-					"fsync_writethrough, otherwise zero). Only available on PG 14+",
+					"fsync_writethrough, otherwise zero). Only available on PG 14 to 17.",
 			}, []string{"stats_reset"}),
 		},
 	}
@@ -287,14 +288,16 @@ func (e *Exporter) Describe(ch chan<- *prometheus.Desc) {
 	}
 
 	if version, _ := e.instance.GetPgVersion(); version.Major >= 14 {
-		e.Metrics.PgStatWalMetrics.WalSync.Describe(ch)
-		e.Metrics.PgStatWalMetrics.WalWriteTime.Describe(ch)
-		e.Metrics.PgStatWalMetrics.WalFpi.Describe(ch)
-		e.Metrics.PgStatWalMetrics.WalWrite.Describe(ch)
-		e.Metrics.PgStatWalMetrics.WalSyncTime.Describe(ch)
 		e.Metrics.PgStatWalMetrics.WalRecords.Describe(ch)
-		e.Metrics.PgStatWalMetrics.WALBuffersFull.Describe(ch)
+		e.Metrics.PgStatWalMetrics.WalFpi.Describe(ch)
 		e.Metrics.PgStatWalMetrics.WalBytes.Describe(ch)
+		e.Metrics.PgStatWalMetrics.WALBuffersFull.Describe(ch)
+		if version.Major < 18 {
+			e.Metrics.PgStatWalMetrics.WalWrite.Describe(ch)
+			e.Metrics.PgStatWalMetrics.WalSync.Describe(ch)
+			e.Metrics.PgStatWalMetrics.WalWriteTime.Describe(ch)
+			e.Metrics.PgStatWalMetrics.WalSyncTime.Describe(ch)
+		}
 	}
 }
 
@@ -321,14 +324,16 @@ func (e *Exporter) Collect(ch chan<- prometheus.Metric) {
 	e.Metrics.NodesUsed.Collect(ch)
 
 	if version, _ := e.instance.GetPgVersion(); version.Major >= 14 {
-		e.Metrics.PgStatWalMetrics.WalSync.Collect(ch)
-		e.Metrics.PgStatWalMetrics.WalWriteTime.Collect(ch)
-		e.Metrics.PgStatWalMetrics.WalFpi.Collect(ch)
-		e.Metrics.PgStatWalMetrics.WalWrite.Collect(ch)
-		e.Metrics.PgStatWalMetrics.WalSyncTime.Collect(ch)
 		e.Metrics.PgStatWalMetrics.WalRecords.Collect(ch)
-		e.Metrics.PgStatWalMetrics.WALBuffersFull.Collect(ch)
+		e.Metrics.PgStatWalMetrics.WalFpi.Collect(ch)
 		e.Metrics.PgStatWalMetrics.WalBytes.Collect(ch)
+		e.Metrics.PgStatWalMetrics.WALBuffersFull.Collect(ch)
+		if version.Major < 18 {
+			e.Metrics.PgStatWalMetrics.WalWrite.Collect(ch)
+			e.Metrics.PgStatWalMetrics.WalSync.Collect(ch)
+			e.Metrics.PgStatWalMetrics.WalWriteTime.Collect(ch)
+			e.Metrics.PgStatWalMetrics.WalSyncTime.Collect(ch)
+		}
 	}
 }
 
@@ -423,8 +428,8 @@ func (e *Exporter) collectPgMetrics(ch chan<- prometheus.Metric) {
 	}
 
 	if version, _ := e.instance.GetPgVersion(); version.Major >= 14 {
-		if err := collectPGWALStat(e); err != nil {
-			log.Error(err, "while collecting pg_wal_stat")
+		if err := collectPGStatWAL(e); err != nil {
+			log.Error(err, "while collecting pg_stat_wal")
 			e.Metrics.Error.Set(1)
 			e.Metrics.PgCollectionErrors.WithLabelValues("Collect.PGWALStat").Inc()
 		}
diff --git a/pkg/management/postgres/webserver/metricserver/wal.go b/pkg/management/postgres/webserver/metricserver/wal.go
index 6d0aa732..3f855f12 100644
--- a/pkg/management/postgres/webserver/metricserver/wal.go
+++ b/pkg/management/postgres/webserver/metricserver/wal.go
@@ -41,20 +41,22 @@ func collectPGWalArchiveMetric(exporter *Exporter) error {
 	return nil
 }
 
-func collectPGWALStat(e *Exporter) error {
+func collectPGStatWAL(e *Exporter) error {
 	walStat, err := e.instance.TryGetPgStatWAL()
 	if walStat == nil || err != nil {
 		return err
 	}
 	walMetrics := e.Metrics.PgStatWalMetrics
-	walMetrics.WalSync.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalSync))
-	walMetrics.WalSyncTime.WithLabelValues(walStat.StatsReset).Set(walStat.WalSyncTime)
-	walMetrics.WALBuffersFull.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WALBuffersFull))
+	walMetrics.WalRecords.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalRecords))
 	walMetrics.WalFpi.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalFpi))
-	walMetrics.WalWrite.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalWrite))
 	walMetrics.WalBytes.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalBytes))
-	walMetrics.WalWriteTime.WithLabelValues(walStat.StatsReset).Set(walStat.WalWriteTime)
-	walMetrics.WalRecords.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalRecords))
+	walMetrics.WALBuffersFull.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WALBuffersFull))
+	if version, _ := e.instance.GetPgVersion(); version.Major < 18 {
+		walMetrics.WalWrite.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalWrite))
+		walMetrics.WalSync.WithLabelValues(walStat.StatsReset).Set(float64(walStat.WalSync))
+		walMetrics.WalWriteTime.WithLabelValues(walStat.StatsReset).Set(walStat.WalWriteTime)
+		walMetrics.WalSyncTime.WithLabelValues(walStat.StatsReset).Set(walStat.WalSyncTime)
+	}
 
 	return nil
 }



Test patch:
diff --git a/tests/e2e/asserts_test.go b/tests/e2e/asserts_test.go
index 5e4ef696..7a302b5b 100644
--- a/tests/e2e/asserts_test.go
+++ b/tests/e2e/asserts_test.go
@@ -2542,17 +2542,21 @@ func collectAndAssertCollectorMetricsPresentOnEachPod(cluster *apiv1.Cluster) {
 		"cnpg_collector_replica_mode",
 	}
 
-	if env.PostgresVersion > 14 {
+	if env.PostgresVersion >= 14 {
 		cnpgCollectorMetrics = append(cnpgCollectorMetrics,
 			"cnpg_collector_wal_records",
 			"cnpg_collector_wal_fpi",
 			"cnpg_collector_wal_bytes",
 			"cnpg_collector_wal_buffers_full",
-			"cnpg_collector_wal_write",
-			"cnpg_collector_wal_sync",
-			"cnpg_collector_wal_write_time",
-			"cnpg_collector_wal_sync_time",
 		)
+		if env.PostgresVersion < 18 {
+			cnpgCollectorMetrics = append(cnpgCollectorMetrics,
+				"cnpg_collector_wal_write",
+				"cnpg_collector_wal_sync",
+				"cnpg_collector_wal_write_time",
+				"cnpg_collector_wal_sync_time",
+			)
+		}
 	}
 	By("collecting and verify set of collector metrics on each pod", func() {
 		podList, err := clusterutils.ListPods(env.Ctx, env.Client, cluster.Namespace, cluster.Name)