Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Operator Need to be PostgreSQL extension aware

Description

Human operators (DBAs) take care of extensions as part of upgrade/migration.
There must be a similar feature from K8s Operator to take care of PostgreSQL extensions. 

For example, The Operator version 2.2 uses PostgreSQL images with the new version of pg_stat_monitor.
However. When someone upgrades from the old version of the operator, the pg_stat_monitor won't be useful until they run the statement.

ALTER EXTENSION pg_stat_monitor UPDATE;

This updates the pg_stat_monitor related view defenitions stored in the database. 

Without this the pg_stat_monitor won't be useful and the PMM will throw errors like

failed to get settings: pq: column pg_stat_monitor_settings.setting does not exist  [31magentID[0m=/agent_id/bd65a17a-228a-4797-9f6d-a27d84e91eab [31mcomponent[0m=agent-builtin [31mtype[0m=qan_postgresql_pgstatmonitor_agent Sending status: WAITING.                      [36magentID[0m=/agent_id/bd65a17a-228a-4797-9f6d-a27d84e91eab [36mcomponent[0m=agent-builtin [36mtype[0m=qan_postgresql_pgstatmonitor_agent

Because the views are not compatible

2023-08-28 00:00:08.488 UTC [115711]: [4409-1] db=postgres,user=postgres,app=[unknown],client=10.254.11.91ERROR:  42703: column pg_stat_monitor.total_exec_time does not exist at character 1365 2023-08-28 00:00:08.488 UTC [115711]: [4410-1] db=postgres,user=postgres,app=[unknown],client=10.254.11.91LOCATION:  errorMissingColumn, parse_relation.c:3514 2023-08-28 00:00:08.488 UTC [115711]: [4411-1] db=postgres,user=postgres,app=[unknown],client=10.254.11.91STATEMENT:  SELECT /* pmm-agent:pgstatmonitor */ "pg_stat_monitor"."bucket", "pg_stat_monitor"."client_ip", "pg_stat_monitor"."queryid", "pg_stat_monitor"."query", "pg_stat_monitor"."calls", "pg_stat_monitor"."shared_blks_hit", "pg_stat_monitor"."shared_blks_read", "pg_stat_monitor"."shared_blks_dirtied", "pg_stat_monitor"."shared_blks_written", "pg_stat_monitor"."local_blks_hit", "pg_stat_monitor"."local_blks_read", "pg_stat_monitor"."local_blks_dirtied", "pg_stat_monitor"."local_blks_written", "pg_stat_monitor"."temp_blks_read", "pg_stat_monitor"."temp_blks_written", "pg_stat_monitor"."blk_read_time", "pg_stat_monitor"."blk_write_time", "pg_stat_monitor"."resp_calls", "pg_stat_monitor"."cpu_user_time", "pg_stat_monitor"."cpu_sys_time", "pg_stat_monitor"."relations", "pg_stat_monitor"."datname", "pg_stat_monitor"."userid", "pg_stat_monitor"."bucket_start_time", "pg_stat_monitor"."rows_retrieved", "pg_stat_monitor"."top_queryid", "pg_stat_monitor"."planid", "pg_stat_monitor"."query_plan", "pg_stat_monitor"."top_query", "pg_stat_monitor"."application_name", "pg_stat_monitor"."cmd_type", "pg_stat_monitor"."cmd_type_text", "pg_stat_monitor"."elevel", "pg_stat_monitor"."sqlcode", "pg_stat_monitor"."message", "pg_stat_monitor"."wal_records", "pg_stat_monitor"."wal_fpi", "pg_stat_monitor"."wal_bytes", "pg_stat_monitor"."state_code", "pg_stat_monitor"."state", "pg_stat_monitor"."total_exec_time", "pg_stat_monitor"."min_exec_time", "pg_stat_monitor"."max_exec_time", "pg_stat_monitor"."mean_exec_time", "pg_stat_monitor"."stddev_exec_time", "pg_stat_monitor"."plans_calls", "pg_stat_monitor"."total_plan_time", "pg_stat_monitor"."min_plan_time", "pg_stat_monitor"."max_plan_time", "pg_stat_monitor"."mean_plan_time" FROM "pg_stat_monitor" WHERE queryid IS NOT NULL AND query IS NOT NULL AND (state_code = 3 OR state_code = 4)

So there should be a provision for checking the extension and upgrade/update if required.

The PostgreSQL Operator must be extension aware Or there should be a provision to make it extension aware.

 

Environment

None

AFFECTED CS IDs

CS0039380, CS0053741

Activity

Details

Assignee

Reporter

Needs QA

Yes

Components

Affects versions

Priority

Smart Checklist

Created September 8, 2023 at 3:44 AM
Updated March 17, 2025 at 9:40 PM

Flag notifications