pgstmts.sec.json.v12.sql

WITH su AS (
        SELECT usesysid FROM pg_user
        WHERE usename IN ('postgres', 'zbx_monitor', 'pgbouncer'))
SELECT jsonb_build_object(
                'temp_blks_read', sum(temp_blks_read),
                'shared_blks_read', sum(shared_blks_read),
                'shared_blks_hit', sum(shared_blks_hit),
                'local_blks_read', sum(local_blks_read),
                'local_blks_hit', sum(local_blks_hit),
                'total_time', sum(total_time),
                'calls', sum(calls),
                'rows', sum(rows)
        ) AS "json"
FROM pg_stat_statements(false)
WHERE (SELECT pgd.oid FROM pg_database AS pgd WHERE datname=$1) = dbid
        AND userid NOT IN (TABLE su);

pgstmts.sec.json.v13.sql

WITH su AS (
        SELECT usesysid FROM pg_user
        WHERE usename IN ('postgres', 'zbx_monitor', 'pgbouncer'))
SELECT jsonb_build_object(
                'temp_blks_read', sum(temp_blks_read),
                'shared_blks_read', sum(shared_blks_read),
                'shared_blks_hit', sum(shared_blks_hit),
                'local_blks_read', sum(local_blks_read),
                'local_blks_hit', sum(local_blks_hit),
                'total_time', sum(total_exec_time + total_plan_time),
                'calls', sum(calls),
                'rows', sum(rows)
        ) AS "json"
FROM pg_stat_statements(false)
WHERE (SELECT pgd.oid FROM pg_database AS pgd WHERE datname=$1) = dbid
        AND userid NOT IN (TABLE su);

odsamoylov.conf

# discover PostgreSQL servers accessible by file sockets
UserParameter=pg.servers, echo '['; delimiter=''; for f in /var/run/postgresql/.s.PGSQL.*; do if [ -S "$f" ]; then p="${f#/var/run/postgresql/.s.PGSQL.}"; svn="$(psql --no-psqlrc --set=ON_ERROR_STOP=on --no-password --no-readline --tuples-only --no-align --user='zbx_monitor' --port="$p" --dbname='postgres' --command="show server_version_num;")"; echo "${delimiter}{ \"{#URI}\":\":$p/\", \"{#POSTGRESQL_URI}\":\"postgresql://$HOSTNAME:$p\", \"{#PG.URI}\":\"$f\", \"{#PG.SERVER_VERSION_NUM}\":$svn, \"{#HOSTNAME}\":\"$HOSTNAME\", \"{#PG.PORT}\":$p }"; delimiter=', '; fi; done; echo ']'

# discover PostgreSQL databases accessible by file sockets
UserParameter=pg.databases,echo '['; delimiter='';for f in /var/run/postgresql/.s.PGSQL.*; do if [ -S "$f" ]; then p="${f#/var/run/postgresql/.s.PGSQL.}"; svn="$(psql --no-psqlrc --set=ON_ERROR_STOP=on --no-password --no-readline --tuples-only --no-align --user='zbx_monitor' --port="$p" --dbname='postgres' --command="show server_version_num;")"; for d in $(psql --no-psqlrc --set=ON_ERROR_STOP=on --no-password --no-readline --tuples-only --no-align --user='zbx_monitor' --port="$p" --dbname='postgres' --command="SELECT datname FROM pg_database WHERE NOT datistemplate AND datallowconn;"); do echo "${delimiter}{ \"{#URI}\":\":$p/$d\", \"{#POSTGRESQL_URI}\":\"postgresql://$HOSTNAME:$p/$d\", \"{#PG.URI}\":\"$f\", \"{#PG.PORT}\":$p, \"{#PG.DATABASE}\":\"$d\", \"{#PG.SERVER_VERSION_NUM}\":$svn }"; delimiter=', '; done; fi; done; echo ']'

# get pg_stat_statements per databases
# в данном запросе, если zbx_moniotor дать grant на pg_stat_statements_reset() и роль pg_read_all_stats или pg_monitor,
# то можно будет запускать под zbx_monitor
# расшифровка ключа
# pgstmts данные из pg_stat_statements
# day суточные данные (бывают еще посекундные)
# все гребу в единый json, потом разгребаю
# v13 -> postgresql >=13
# pgstmts.day.json.v13[port,database]
UserParameter=pgstmts.day.json.v13[*], psql --no-psqlrc --set=ON_ERROR_STOP=on --no-password --no-readline --tuples-only --quiet --no-align --user='zbx_monitor' --port='$1' --dbname='postgres' --command="WITH su AS (SELECT usesysid FROM pg_user WHERE usename IN ('postgres', 'zbx_monitor', 'pgbouncer')) SELECT jsonb_build_object( 'top_total_time', (SELECT jsonb_agg(stat.*) FROM (SELECT * FROM pg_stat_statements(true) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su) ORDER BY total_exec_time + total_plan_time DESC LIMIT 5) AS stat), 'top_max_time', (SELECT jsonb_agg(stat.*) FROM (SELECT * FROM pg_stat_statements(true) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su) ORDER BY max_exec_time + max_plan_time DESC LIMIT 5) AS stat)) || (SELECT jsonb_build_object( 'temp_blks_read', sum(temp_blks_read), 'shared_blks_miss', CASE WHEN sum(shared_blks_read)=0 THEN 0 ELSE 100.*sum(shared_blks_read)/sum(shared_blks_hit+shared_blks_read) END, 'local_blks_miss', CASE WHEN sum(local_blks_read)=0 THEN 0 ELSE 100.*sum(local_blks_read)/sum(local_blks_hit+local_blks_read) END, 'max_time', max(max_exec_time + max_plan_time), 'min_time', min(min_exec_time + min_plan_time), 'mean_time', avg(mean_exec_time + mean_plan_time), 'total_time', sum(total_exec_time + total_plan_time), 'calls', sum(calls), 'rows', sum(rows) ) FROM pg_stat_statements(false) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su) ) FROM pg_database AS pgd WHERE datname='$2';" --command "DO \$DO\$ BEGIN PERFORM pg_stat_statements_reset(0,pgd.oid,0) FROM pg_database AS pgd WHERE datname='$2'; END \$DO\$;"

# get pg_stat_statements per instance
# расшифровка ключа
# pgstmts данные из pg_stat_statements
# day суточные данные (бывают еще посекундные)
# все гребу в единый json, потом разгребаю
# v12 -> 10<= postgresql <=12
# pgstmts.day.json.v12[port]
UserParameter=pgstmts.day.json.v12[*], psql --no-psqlrc --set=ON_ERROR_STOP=on --no-password --no-readline --tuples-only --quiet --no-align --user='zbx_monitor' --port='$1' --dbname='postgres' --command="WITH su AS (SELECT usesysid FROM pg_user WHERE usename IN ('postgres', 'zbx_monitor', 'pgbouncer')) SELECT jsonb_object_agg( pgd.datname, jsonb_build_object( 'top_total_time', (SELECT jsonb_agg(stat.*) FROM (SELECT * FROM pg_stat_statements(true) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su) ORDER BY total_time DESC LIMIT 5) AS stat), 'top_max_time', (SELECT jsonb_agg(stat.*) FROM (SELECT * FROM pg_stat_statements(true) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su) ORDER BY max_time DESC LIMIT 5) AS stat)) || (SELECT jsonb_build_object( 'temp_blks_read', sum(temp_blks_read), 'shared_blks_miss', CASE WHEN sum(shared_blks_read)=0 THEN 0 ELSE 100.*sum(shared_blks_read)/sum(shared_blks_hit+shared_blks_read) END, 'local_blks_miss', CASE WHEN sum(local_blks_read)=0 THEN 0 ELSE 100.*sum(local_blks_read)/sum(local_blks_hit+local_blks_read) END, 'max_time', max(max_time), 'min_time', min(min_time), 'mean_time', avg(mean_time), 'total_time', sum(total_time), 'calls', sum(calls), 'rows', sum(rows) ) FROM pg_stat_statements(false) WHERE dbid=pgd.oid AND userid NOT IN (TABLE su)) ) FROM pg_database AS pgd WHERE NOT datistemplate AND datallowconn;" --command="DO \$DO\$ BEGIN PERFORM pg_stat_statements_reset(); END \$DO\$;"

Файл odsamoylov.conf должен лежать там, куда указывается опция Include=/etc/zabbix/zabbix_agent2.d/*.conf в файле /etc/zabbix/zabbix_agent2.conf. В данном случае в директории /etc/zabbix/zabbix_agent2.d/. А файлы pgstmts.sec.json.v*.sql должны лежать в директории, куда указывает опция Plugins.PostgreSQL.CustomQueriesPath в файле /etc/zabbix/zabbix_agent2.d/plugins.d/postgresql.conf. Обычно она закомментирована (значит, опция отключена), надо расскоментировать и прописать нужный путь, например /etc/zabbix/postgresql.

zbx_export_templates.yaml

А этот файл добавить в Zabbix через Импорт, раздел Шаблоны, кнопка в правом верхнем углу.

zabbix_export:
  version: '6.0'
  date: '2023-11-03T09:21:35Z'
  groups:
    - uuid: 748ad4d098d447d492bb935c907f652f
      name: Templates/Databases
  templates:
    - uuid: 11887f960b8945fd8405569f6d5cc4ab
      template: odsamoylov
      name: 'ODSamoylov PostgresQL discovery'
      description: 'Discover working PostgreSQL servers from existing socket files.'
      groups:
        - name: Templates/Databases
      items:
        - uuid: 9843942f673540f8b61b7983761cdbd7
          name: 'System Load (avg 1 min)'
          key: 'system.cpu.load[all, avg1]'
          value_type: FLOAT
          description: 'System load, 1min average.'
      discovery_rules:
        - uuid: 2d573742e4e64329a9d02d4165902df7
          name: Pg_db
          key: pg.databases
          delay: 1d
          lifetime: 1d
          item_prototypes:
            - uuid: deccbd857fba47b7be99646694c4032b
              name: '{#URI}->pgstmts.sec.json.v12'
              key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
              history: '0'
              trends: '0'
              discover: NO_DISCOVER
              value_type: TEXT
              description: |
                Текущие значения из pg_stat_statements, без reset.
                Для postgresql <13
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$[0]["json"]'
            - uuid: 60ab8bb11ea84732b2e28470e9e729ef
              name: '{#URI}->pgstmts.sec.json.v13'
              key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
              history: '0'
              trends: '0'
              value_type: TEXT
              description: |
                Текущие значения из pg_stat_statements, без reset.
                Для postgresql >=13
                Если для zbx_monitor выдать правильные пермишенсы, можно будет запускать от его имени.
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$[0]["json"]'
            - uuid: d51ce3c027194c03ac9b9fad60954f62
              name: '{#URI}->pgstmts.day.calls.v12'
              type: DEPENDENT
              key: 'pgstmts.day.calls.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["calls"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 9a5c104842d543f3bbcbe03108a1dbfa
              name: '{#URI}->pgstmts.day.calls.v13'
              type: DEPENDENT
              key: 'pgstmts.day.calls.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["calls"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 1817cd7081194d8cb91a584d3d497b13
              name: '{#URI}->pgstmts.day.json.v12'
              type: CALCULATED
              key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
              delay: 0;h2
              history: '0'
              trends: '0'
              discover: NO_DISCOVER
              value_type: TEXT
              params: 'first(//pgstmts.day.json.v12["{#PG.PORT}"], 4h)'
              description: 'Parse databases stats. 9.5 <= postgresql <=12.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["{#PG.DATABASE}"]'
            - uuid: 7c5e2872b3124158b315baf92547e292
              name: '{#URI}->pgstmts.day.json.v13'
              key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
              delay: 0;h0
              history: '0'
              trends: '0'
              value_type: TEXT
              description: 'Get pg_stat_statements and reset statistics, need postgresql >=13'
            - uuid: 6e9f27eb89704487b57e4fed2000d7db
              name: '{#URI}->pgstmts.day.local_blks_miss.v12'
              type: DEPENDENT
              key: 'pgstmts.day.local_blks_miss.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: '%'
              description: 'Need to increase temp_buffers PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_miss"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: ce749edb579b4d498fabb5879b812bc9
              name: '{#URI}->pgstmts.day.local_blks_miss.v13'
              type: DEPENDENT
              key: 'pgstmts.day.local_blks_miss.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: '%'
              description: 'Need to increase temp_buffers PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_miss"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 19569e8199f94a5f98531a996b062028
              name: '{#URI}->pgstmts.day.max_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.max_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["max_time"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: c989cf9537d14064b88302f92bc6cbc3
              name: '{#URI}->pgstmts.day.max_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.max_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["max_time"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 76b7a5b9f6604920babcf5cac5f54b97
              name: '{#URI}->pgstmts.day.mean_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.mean_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["mean_time"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 56c7a1339c8f45fb86a37fbf7ffdfe3c
              name: '{#URI}->pgstmts.day.mean_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.mean_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["mean_time"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 690c0ddf1d864d29a9482d59b46e7db3
              name: '{#URI}->pgstmts.day.min_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.min_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["min_time"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 55d279ff6943492d96cc6f23ace8aa86
              name: '{#URI}->pgstmts.day.min_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.min_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["min_time"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: df66165ed81142129de2cbdf5e8b59d7
              name: '{#URI}->pgstmts.day.rows.v12'
              type: DEPENDENT
              key: 'pgstmts.day.rows.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["rows"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 4d2dbd01aea2455ab58dccfdf13dd7bf
              name: '{#URI}->pgstmts.day.rows.v13'
              type: DEPENDENT
              key: 'pgstmts.day.rows.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["rows"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: a2ffb06cc87a4d19bda568497ec92743
              name: '{#URI}->pgstmts.day.shared_blks_miss.v12'
              type: DEPENDENT
              key: 'pgstmts.day.shared_blks_miss.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: '%'
              description: 'Need to increase shared_buffers PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_miss"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 6e41b880cdd243d89f5556c94d31c569
              name: '{#URI}->pgstmts.day.shared_blks_miss.v13'
              type: DEPENDENT
              key: 'pgstmts.day.shared_blks_miss.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: '%'
              description: 'Need to increase shared_buffers PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_miss"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: e29562671e1a4e88b29637b5a018f4a3
              name: '{#URI}->pgstmts.day.temp_blks_read.v12'
              type: DEPENDENT
              key: 'pgstmts.day.temp_blks_read.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              description: 'Need to increase work_mem PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["temp_blks_read"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: f26de5990a8d434a8d2ef3c401d66c44
              name: '{#URI}->pgstmts.day.temp_blks_read.v13'
              type: DEPENDENT
              key: 'pgstmts.day.temp_blks_read.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              description: 'Need to increase work_mem PostgresQL config option.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["temp_blks_read"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 243a966177c944adbbeddb07af730b06
              name: '{#URI}->pgstmts.day.top_max_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.top_max_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              trends: '0'
              discover: NO_DISCOVER
              value_type: TEXT
              description: 'Top queries for max_time.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["top_max_time"]'
                - type: JAVASCRIPT
                  parameters:
                    - 'return JSON.stringify(JSON.parse(value),null," ")'
                - type: STR_REPLACE
                  parameters:
                    - \\n
                    - \n
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 88daedcf4efb4645aa418e555b83e83b
              name: '{#URI}->pgstmts.day.top_max_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.top_max_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              trends: '0'
              value_type: TEXT
              description: 'Top queries for max_exec_time'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["top_max_time"]'
                - type: JAVASCRIPT
                  parameters:
                    - 'return JSON.stringify(JSON.parse(value),null," ")'
                - type: STR_REPLACE
                  parameters:
                    - \\n
                    - \n
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: ae1e10422cd0469faec4550bd9da3da0
              name: '{#URI}->pgstmts.day.top_total_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.top_total_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              trends: '0'
              discover: NO_DISCOVER
              value_type: TEXT
              description: 'Top queries for total_time.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["top_total_time"]'
                - type: JAVASCRIPT
                  parameters:
                    - 'return JSON.stringify(JSON.parse(value),null," ")'
                - type: STR_REPLACE
                  parameters:
                    - \\n
                    - \n
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 8a195b984ac24a6c8ff7a9da0ca673ce
              name: '{#URI}->pgstmts.day.top_total_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.top_total_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              trends: '0'
              value_type: TEXT
              description: 'Top queries for total_exec_time.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["top_total_time"]'
                - type: JAVASCRIPT
                  parameters:
                    - 'return JSON.stringify(JSON.parse(value),null," ")'
                - type: STR_REPLACE
                  parameters:
                    - \\n
                    - \n
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 2e9b82cb015a41e6ac34dc7abc50da13
              name: '{#URI}->pgstmts.day.total_time.v12'
              type: DEPENDENT
              key: 'pgstmts.day.total_time.v12["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              discover: NO_DISCOVER
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["total_time"]'
              master_item:
                key: 'pgstmts.day.json.v12["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: 501c9852f74147228960eabc2137b9c3
              name: '{#URI}->pgstmts.day.total_time.v13'
              type: DEPENDENT
              key: 'pgstmts.day.total_time.v13["{#PG.URI}","{#PG.DATABASE}"]'
              delay: '0'
              history: 1w
              value_type: FLOAT
              units: ms
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["total_time"]'
              master_item:
                key: 'pgstmts.day.json.v13["{#PG.PORT}","{#PG.DATABASE}"]'
            - uuid: a79a9d0c0e944d5792f1a8e061dc7ea4
              name: '{#URI}->pgstmts.sec.calls.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.calls.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              description: 'Queries calls per second.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["calls"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: 6a0e03a545644a82b0240f51ffd803d3
              name: '{#URI}->pgstmts.sec.calls.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.calls.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              description: 'Queries calls per second.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["calls"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: eb5775d274ae4b45a400b8eb5b676c93
              name: '{#URI}->pgstmts.sec.load.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.load.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              value_type: FLOAT
              description: 'Аналог system load, вычисляется на базу данных из pg_stat_statements. Поскольку данные поступают с завершением запросов, запросы больше минуты будут вносить неточность.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["total_time"]'
                - type: MULTIPLIER
                  parameters:
                    - '0.001'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: 3ab68c00d288440b8fe576905b1787a4
              name: '{#URI}->pgstmts.sec.load.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.load.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              value_type: FLOAT
              description: 'Аналог system load, вычисляется на базу данных из pg_stat_statements. Поскольку данные поступают с завершением запросов, запросы больше минуты будут вносить неточность.'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["total_time"]'
                - type: MULTIPLIER
                  parameters:
                    - '0.001'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: 9062ad5dad914883a02db6c42e0841f3
              name: '{#URI}->pgstmts.sec.local_blks_hit.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.local_blks_hit.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_hit"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: a2e6e98edcbb4ff5a87be6539b10ebd9
              name: '{#URI}->pgstmts.sec.local_blks_hit.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.local_blks_hit.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_hit"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: f1c46395b722427eb3abe1952bd64b70
              name: '{#URI}->pgstmts.sec.local_blks_miss.v12'
              type: CALCULATED
              key: 'pgstmts.sec.local_blks_miss.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              trends: '0'
              discover: NO_DISCOVER
              value_type: FLOAT
              units: '%'
              params: '100*last(//pgstmts.sec.local_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"])/(last(//pgstmts.sec.local_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"])+last(//pgstmts.sec.local_blks_hit.v12["{#PG.URI}",,,"{#PG.DATABASE}"])+0.00001)'
            - uuid: 5ac87f478a1349f4b4b1fd6200d94763
              name: '{#URI}->pgstmts.sec.local_blks_miss.v13'
              type: CALCULATED
              key: 'pgstmts.sec.local_blks_miss.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              trends: '0'
              value_type: FLOAT
              units: '%'
              params: '100*last(//pgstmts.sec.local_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"])/(last(//pgstmts.sec.local_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"])+last(//pgstmts.sec.local_blks_hit.v13["{#PG.URI}",,,"{#PG.DATABASE}"])+0.00001)'
            - uuid: 51baf10158164b0cb5470bb4743ddc53
              name: '{#URI}->pgstmts.sec.local_blks_read.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.local_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: c1eb2c53db3d48f698650822662d2c67
              name: '{#URI}->pgstmts.sec.local_blks_read.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.local_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["local_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: d8687f228a724f3a96036d520adc0d7c
              name: '{#URI}->pgstmts.sec.rows.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.rows.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["rows"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: 46c7fff000d944cc81792141877e30b3
              name: '{#URI}->pgstmts.sec.rows.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.rows.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["rows"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: 645e9467a9a04e86b4f7b29edca134b9
              name: '{#URI}->pgstmts.sec.shared_blks_hit.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.shared_blks_hit.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_hit"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: 3697b2fc1c3e40a18ede6998ec6915cf
              name: '{#URI}->pgstmts.sec.shared_blks_hit.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.shared_blks_hit.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_hit"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: b8a03880ab72460f84108b56215fafd9
              name: '{#URI}->pgstmts.sec.shared_blks_miss.v12'
              type: CALCULATED
              key: 'pgstmts.sec.shared_blks_miss.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              trends: '0'
              discover: NO_DISCOVER
              value_type: FLOAT
              units: '%'
              params: '100*last(//pgstmts.sec.shared_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"])/(last(//pgstmts.sec.shared_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"])+last(//pgstmts.sec.shared_blks_hit.v12["{#PG.URI}",,,"{#PG.DATABASE}"])+0.00001)'
            - uuid: 969c9423e22344d0beb86aed847e2c9f
              name: '{#URI}->pgstmts.sec.shared_blks_miss.v13'
              type: CALCULATED
              key: 'pgstmts.sec.shared_blks_miss.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              trends: '0'
              value_type: FLOAT
              units: '%'
              params: '100*last(//pgstmts.sec.shared_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"])/(last(//pgstmts.sec.shared_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"])+last(//pgstmts.sec.shared_blks_hit.v13["{#PG.URI}",,,"{#PG.DATABASE}"])+0.00001)'
            - uuid: 325fc8eacfeb4719b3634409a5151025
              name: '{#URI}->pgstmts.sec.shared_blks_read.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.shared_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: aa15364ef029496fa5b9ab4cd24dad0e
              name: '{#URI}->pgstmts.sec.shared_blks_read.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.shared_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["shared_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
            - uuid: 10020da9cc0e4363bb68d4f8b4d1eb41
              name: '{#URI}->pgstmts.sec.temp_blks_read.v12'
              type: DEPENDENT
              key: 'pgstmts.sec.temp_blks_read.v12["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              discover: NO_DISCOVER
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["temp_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v12", "{#PG.DATABASE}"]'
            - uuid: d7585acf54144ba9b9e2427c4aaaf3fc
              name: '{#URI}->pgstmts.sec.temp_blks_read.v13'
              type: DEPENDENT
              key: 'pgstmts.sec.temp_blks_read.v13["{#PG.URI}",,,"{#PG.DATABASE}"]'
              delay: '0'
              trends: '0'
              preprocessing:
                - type: JSONPATH
                  parameters:
                    - '$["temp_blks_read"]'
                - type: CHANGE_PER_SECOND
                  parameters:
                    - ''
              master_item:
                key: 'pgsql.custom.query["{#PG.URI}",''postgres'',,''postgres'',"pgstmts.sec.json.v13", "{#PG.DATABASE}"]'
          overrides:
            - name: 'pgstmts.*.v13'
              step: '1'
              filter:
                conditions:
                  - macro: '{#PG.SERVER_VERSION_NUM}'
                    value: '^(1[0-2]....|.....)$'
                    formulaid: A
              operations:
                - operationobject: ITEM_PROTOTYPE
                  operator: REGEXP
                  value: '->pgstmts\..*\.v13$'
                  discover: NO_DISCOVER
            - name: 'pgstmts.*.v12'
              step: '2'
              filter:
                conditions:
                  - macro: '{#PG.SERVER_VERSION_NUM}'
                    value: '^(1[0-2]....)$'
                    formulaid: A
              operations:
                - operationobject: ITEM_PROTOTYPE
                  operator: REGEXP
                  value: '->pgstmts\..*\.v12$'
                  discover: DISCOVER
        - uuid: f56274e9f87e42bb8122559c13c7ea5d
          name: Pg_srv
          key: pg.servers
          delay: 1d
          lifetime: 1d
          description: 'Discover PostgreSQL servers by looking for unix sockets.'
          item_prototypes:
            - uuid: cb751a1fe02f463da409a158375570b1
              name: '{#URI}->pgstmts.day.json.v12'
              key: 'pgstmts.day.json.v12["{#PG.PORT}"]'
              delay: 0;h0
              history: 4h
              trends: '0'
              discover: NO_DISCOVER
              value_type: TEXT
              description: |
                Get pg_stat_statements and reset statistics, need 9.5 <= postgresql <=12.
                Works per instances,
          overrides:
            - name: 'pgstmts.*.v12'
              step: '1'
              filter:
                conditions:
                  - macro: '{#PG.SERVER_VERSION_NUM}'
                    value: '^(1[0-2]....)$'
                    formulaid: A
              operations:
                - operationobject: ITEM_PROTOTYPE
                  operator: REGEXP
                  value: '/->pgstmts\..*\.v12$'
                  discover: DISCOVER