Agent 1.57.2 fails to run monitor queries on Postgres 17 on Debian 12

[heldchen]

^^ Provide a general summary of the issue in the title above. ^^

Description

since upgrading to postgresql 17, newrelic-infra fails to read data from the postgresql server. the agent log shows no postgres related errors, while the postgres log contains lots of failed queries.

we noticed this after investigating why our infra ingestion rate doubled mid of last month.

Expected Behavior

data is logged.

Troubleshooting or [NR Diag]

excerpt from /var/log/postgresql/postgresql-17-main.log:

2024-11-04 09:31:19.796 CET [463593] newrelic@postgres ERROR:  column bg.checkpoints_timed does not exist at character 10
2024-11-04 09:31:19.796 CET [463593] newrelic@postgres STATEMENT:  SELECT
			BG.checkpoints_timed AS scheduled_checkpoints_performed,
			BG.checkpoints_req AS requested_checkpoints_performed,
			BG.buffers_checkpoint AS buffers_written_during_checkpoint,
			BG.buffers_clean AS buffers_written_by_background_writer,
			BG.maxwritten_clean AS background_writer_stops,
			BG.buffers_backend AS buffers_written_by_backend,
			BG.buffers_alloc AS buffers_allocated
			FROM pg_stat_bgwriter BG;
2024-11-04 09:31:19.796 CET [463593] newrelic@postgres ERROR:  column bg.buffers_backend_fsync does not exist at character 11
2024-11-04 09:31:19.796 CET [463593] newrelic@postgres STATEMENT:  SELECT 
			BG.buffers_backend_fsync AS times_backend_executed_own_fsync
			FROM pg_stat_bgwriter BG;
2024-11-04 09:31:19.797 CET [463593] newrelic@postgres ERROR:  column bg.checkpoint_write_time does not exist at character 16
2024-11-04 09:31:19.797 CET [463593] newrelic@postgres STATEMENT:  SELECT 
			cast(BG.checkpoint_write_time AS bigint) AS time_writing_checkpoint_files_to_disk,
			cast(BG.checkpoint_sync_time AS bigint) AS time_synchronizing_checkpoint_files_to_disk
			FROM pg_stat_bgwriter BG;
2024-11-04 09:31:25.615 CET [3044797] LOG:  checkpoint starting: time
2024-11-04 09:31:35.004 CET [463657] newrelic@postgres ERROR:  column bg.checkpoints_timed does not exist at character 10
2024-11-04 09:31:35.004 CET [463657] newrelic@postgres STATEMENT:  SELECT
			BG.checkpoints_timed AS scheduled_checkpoints_performed,
			BG.checkpoints_req AS requested_checkpoints_performed,
			BG.buffers_checkpoint AS buffers_written_during_checkpoint,
			BG.buffers_clean AS buffers_written_by_background_writer,
			BG.maxwritten_clean AS background_writer_stops,
			BG.buffers_backend AS buffers_written_by_backend,
			BG.buffers_alloc AS buffers_allocated
			FROM pg_stat_bgwriter BG;
2024-11-04 09:31:35.004 CET [463657] newrelic@postgres ERROR:  column bg.buffers_backend_fsync does not exist at character 11
2024-11-04 09:31:35.004 CET [463657] newrelic@postgres STATEMENT:  SELECT 
			BG.buffers_backend_fsync AS times_backend_executed_own_fsync
			FROM pg_stat_bgwriter BG;
2024-11-04 09:31:35.005 CET [463657] newrelic@postgres ERROR:  column bg.checkpoint_write_time does not exist at character 16
2024-11-04 09:31:35.005 CET [463657] newrelic@postgres STATEMENT:  SELECT 
			cast(BG.checkpoint_write_time AS bigint) AS time_writing_checkpoint_files_to_disk,
			cast(BG.checkpoint_sync_time AS bigint) AS time_synchronizing_checkpoint_files_to_disk
			FROM pg_stat_bgwriter BG;

no error reported in /var/log/newrelic-infra.log:

time="2024-11-05T11:30:55+01:00" level=info msg="Integration health check starting" instance=sample_postgres integration=com.newrelic.postgresql prefix=config/postgresql working-dir=/var/db/newrelic-infra/newrelic-integrations
time="2024-11-05T11:30:55+01:00" level=info msg="Integration health check finished with success" instance=sample_postgres integration=com.newrelic.postgresql prefix=config/postgresql working-dir=/var/db/newrelic-infra/newrelic-integrations

Steps to Reproduce

• install & configure newrelic-infra agent from
• install & configure postgresql 17

Your Environment

• Debian 12
• lastest newrelic-infra via https://download.newrelic.com/infrastructure_agent/linux/apt
• latest postgresql via https://apt.postgresql.org/pub/repos/apt/

Additional context

• worked using the identical configuration using postgresql-16

For Maintainers Only or Hero Triaging this bug

Suggested Priority (P1,P2,P3,P4,P5):
Suggested T-Shirt size (S, M, L, XL, Unknown):

[workato-integration]

https://new-relic.atlassian.net/browse/NR-336640

[heldchen]

the pg_stat_bgwriter view is missing most of the fields queried by newrelic:

postgres=# SELECT * FROM pg_stat_bgwriter;
 buffers_clean | maxwritten_clean | buffers_alloc |          stats_reset          
---------------+------------------+---------------+-------------------------------
       5650473 |            17234 |     330639599 | 2024-09-10 22:08:14.303576+02
(1 row)

I think it was separated into the new pg_stat_checkpointer view (https://www.dbi-services.com/blog/postgresql-17-new-catalog-view-pg_stat_checkpointer/):

postgres=# SELECT * FROM pg_stat_checkpointer;
 num_timed | num_requested | restartpoints_timed | restartpoints_req | restartpoints_done | write_time | sync_time | buffers_written |          stats_reset          
-----------+---------------+---------------------+-------------------+--------------------+------------+-----------+-----------------+-------------------------------
     15992 |            52 |                   0 |                 0 |                  0 |  153417631 |     83254 |         1135860 | 2024-09-10 22:08:14.303576+02
(1 row)

[rahulreddy15]

Support for Postgres v17 will be added in the next release.
Columns that were in pg_stat_bgwriter were moved to pg_stat_checkpointer and pg_stat_io.

[heldchen]

thanks, appreciated.

is there a page that outlines the current supported app versions? I was not able to find any so assumed it is compatible.

[rahulreddy15]

The latest supported version can be found here:Postgres On Host Integration