PostgreSQL start time is not working

[S0b1t]

What did you do?

docker run \
  --name postgresql_exporter \
  --restart=always \
  --net=host \
  -e DATA_SOURCE_NAME="postgresql://myuser:[email protected]:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter

What did you expect to see?

pg_postmaster_start_time_seconds

What did you see instead? Under which circumstances?

Empty query result

Environment

• System information:

$ uname -srm
Linux 4.18.0-372.26.1.el8_6.x86_64 x86_64

• postgres_exporter version:

$ postgres_exporter --version
postgres_exporter, version 0.13.1 (branch: HEAD, revision: bf4e4fb6cca3191d3c41df2efdf5d15d17b5fa60)
  build user:       root@1037cd0c8192
  build date:       20230627-11:15:42
  go version:       go1.20.5
  platform:         linux/amd64
  tags:             netgo static_build

• postgres_exporter flags: no flags

• PostgreSQL version:

"PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit"

• Logs:

ts=2023-07-19T07:27:08.941Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.000372781 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:08.952Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.011657627 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:18.953Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.012329414 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:18.953Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.012566458 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:28.941Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.001198647 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:28.954Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.013762043 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:38.953Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.012251402 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:38.956Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.015053869 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:48.941Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.000328506 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:48.954Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.013191418 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:58.953Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.012541427 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:27:58.954Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.013753347 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:28:08.941Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.000584332 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:28:08.943Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.001895538 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:28:18.953Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.01156534 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:28:18.954Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.012857593 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"
ts=2023-07-19T07:28:28.956Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.015037195 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2022-11-12 11:24:49.424682 +0500 +05\") to a float64: invalid syntax"

[roock]

Same issue here. It also affects the replication collector, not only the postmaster collector.

postgresql_exporter 0.13.1
postgresql: 13.11

2023-07-19T10:37:39.234431+02:00 xxxxxx postgres_exporter[13230]: ts=2023-07-19T08:37:39.232Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.000839141 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-07-03 19:37:44.930591 +0200 CEST\") to a float64: invalid syntax"
2023-07-19T10:37:39.246679+02:00 xxxxxxx postgres_exporter[13230]: ts=2023-07-19T08:37:39.246Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.014423637 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-07-03 19:37:44.930591 +0200 CEST\") to a float64: invalid syntax"
2023-07-19T10:37:39.255628+02:00 xxxxxxxxx postgres_exporter[13230]: ts=2023-07-19T08:37:39.255Z caller=collector.go:190 level=error msg="collector failed" name=stat_statements duration_seconds=0.023268289 err="pq: relation \"pg_stat_statements\" does not exist"
2023-07-19T10:37:40.947634+02:00 xxxxxxx patroni@13-testdbrw[4261]: 2023-07-19 10:37:40,945 INFO: no action. I am (yesss-inta1-testdbrw02), a secondary, and following a leader (yesss-inta1-testdbrw01)
2023-07-19T10:37:41.579697+02:00 yesss-inta1-testdbrw02 postgres_exporter[13230]: ts=2023-07-19T08:37:41.577Z caller=collector.go:190 level=error msg="collector failed" name=stat_statements duration_seconds=0.001280459 err="pq: relation \"pg_stat_statements\" does not exist"
2023-07-19T10:37:41.584268+02:00 xxxxxxxx2 postgres_exporter[13230]: ts=2023-07-19T08:37:41.583Z caller=collector.go:190 level=error msg="collector failed" name=replication duration_seconds=0.007571499 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-07-03 19:37:44.930591 +0200 CEST\") to a float64: invalid syntax"
2023-07-19T10:37:41.596256+02:00 xxxxxxx2 postgres_exporter[13230]: ts=2023-07-19T08:37:41.595Z caller=collector.go:190 level=error msg="collector failed" name=postmaster duration_seconds=0.019486125 err="sql: Scan error on column index 0, name \"pg_postmaster_start_time\": converting driver.Value type time.Time (\"2023-07-03 19:37:44.930591 +0200 CEST\") to a float64: invalid syntax"

Update: This has already been fixed on master! cc @S0b1t

[sysadmind]

This was fixed on the master branch and will be port of the 0.13.2 release.

[roock]

@sysadmind any ETA for the 0.13.2 release? Thank you!

[sysadmind]

The release is ready to be cut, but we are having issues with our CI pushing to our registry. We are just waiting for those issues to be resolved.

[roock]

Great! Let me know if you need help with the CI issue 😀

[sysadmind]

0.13.2 has been released.

[S0b1t]

Hi @sysadmind

Thanks for new release.
As you see there is new image(v0.13.2)

But after changing to the new image there is no changes(PostgreSQL start time is not working)

Perhaps I'm missing something. Could you help with this please!

[sysadmind]

Because of the problems we have had with pg_postmaster_* metrics, we have disabled it by default. You can enable it with -collector.postmaster. We are tracking progress on #830