[postgres] errors for unsupported features in Aurora

[ngraef]

I upgraded the Agent from v7.65.2 to v7.66.0 and noticed these new errors:

2025-05-22 16:42:19 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | postgres:1d450d17bcadb97b | (postgres.py:265) | Unhandled exception while using database connection postgres
Traceback (most recent call last):
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 250, in db
yield self._db
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 233, in execute_query_raw
cursor.execute(query)
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/cursor.py", line 28, in execute
return super().execute(query, vars)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.ObjectNotInPrerequisiteState: wal_level must be set to 'logical'
HINT: WAL control functions cannot be executed when wal_level < logical.
2025-05-22 16:42:19 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | - | (core.py:94) | Error querying pg_control_checkpoint: wal_level must be set to 'logical'
HINT: WAL control functions cannot be executed when wal_level < logical.
2025-05-22 16:42:13 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | - | (core.py:94) | Error querying pg_control_checkpoint: Function pg_last_xlog_receive_location() is currently not supported for Aurora
2025-05-22 16:42:13 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | postgres:7e5ee93554e2c1df | (postgres.py:265) | Unhandled exception while using database connection postgres
Traceback (most recent call last):
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 250, in db
yield self._db
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 233, in execute_query_raw
cursor.execute(query)
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/cursor.py", line 28, in execute
return super().execute(query, vars)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.FeatureNotSupported: Function pg_last_xlog_receive_location() is currently not supported for Aurora
2025-05-22 16:42:11 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | postgres:cc5a105c7f6eb213 | (postgres.py:265) | Unhandled exception while using database connection postgres
Traceback (most recent call last):
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 250, in db
yield self._db
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/postgres.py", line 233, in execute_query_raw
cursor.execute(query)
File "/opt/datadog-agent/embedded/lib/python3.12/site-packages/datadog_checks/postgres/cursor.py", line 28, in execute
return super().execute(query, vars)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.FeatureNotSupported: Function pg_last_xlog_receive_location() is currently not supported for Aurora
2025-05-22 16:42:11 UTC | CORE | ERROR | (pkg/collector/python/datadog_agent.go:143 in LogMessage) | - | (core.py:94) | Error querying pg_control_checkpoint: Function pg_last_xlog_receive_location() is currently not supported for Aurora

This appears to be related to #20017 added in v22.9.0 of the postgres check. The target database is AWS Aurora with engine version 14.6.

[lobsterdore]

We're seeing the same issue with Aurora Postgres, it's not possible to modify wal_level so this check should be modified to accommodate this.

[opokhvalit]

I have the same issue on Aurora 13.12.0 after upgrade agent from 7.54.1 to 7.66.1

[ngraef]

Possibly fixed by #20490 and #20500. Waiting for release.