wip

- Add the initital item definitions to the template for archive ready
  files and temp files.

- Fix the temp file query to be able to return both columns in all
  cases.

GENTOO/pgmon-1.1.0-rc1.ebuild

@@ -0,0 +1,74 @@
+# Copyright 2024 Gentoo Authors
+# Distributed under the terms of the GNU General Public License v2
+
+EAPI=8
+
+PYTHON_COMPAT=( python3_{6..13} )
+
+inherit python-r1 systemd
+
+DESCRIPTION="PostgreSQL monitoring bridge"
+HOMEPAGE="None"
+
+LICENSE="BSD"
+SLOT="0"
+KEYWORDS="amd64"
+
+SRC_URI="https://code2.shh-dot-com.org/james/${PN}/releases/download/v${PV}/${P}.tar.bz2"
+
+IUSE="-systemd"
+
+DEPEND="
+	${PYTHON_DEPS}
+	dev-python/psycopg:2
+	dev-python/pyyaml
+	dev-python/requests
+	app-admin/logrotate
+	"
+RDEPEND="${DEPEND}"
+BDEPEND=""
+
+#RESTRICT="fetch"
+
+#S="${WORKDIR}/${PN}"
+
+#pkg_nofetch() {
+#	einfo "Please download"
+#	einfo "  - ${P}.tar.bz2"
+#	einfo "from ${HOMEPAGE} and place it in your DISTDIR directory."
+#	einfo "The file should be owned by portage:portage."
+#}
+
+src_compile() {
+	true
+}
+
+src_install() {
+	# Install init script
+	if ! use systemd ; then
+		newinitd "openrc/pgmon.initd" pgmon
+		newconfd "openrc/pgmon.confd" pgmon
+	fi
+
+	# Install systemd unit
+	if use systemd ; then
+		systemd_dounit "systemd/pgmon.service"
+	fi
+
+	# Install script
+	exeinto /usr/bin
+	newexe "src/pgmon.py" pgmon
+
+	# Install default config
+	diropts -o root -g root -m 0755
+	insinto /etc/pgmon
+	doins "sample-config/pgmon.yml"
+	doins "sample-config/pgmon-metrics.yml"
+
+	# Install logrotate config
+	insinto /etc/logrotate.d
+	newins "logrotate/pgmon.logrotate" pgmon
+
+	# Install man page
+	doman manpages/pgmon.1
+}

Makefile

@@ -9,6 +9,7 @@ FULL_VERSION := $(shell grep -m 1 '^VERSION = ' "$(SCRIPT)" | sed -ne 's/.*"\(.*
 VERSION := $(shell echo $(FULL_VERSION) | sed -n 's/\(.*\)\(-rc.*\|$$\)/\1/p')
 RELEASE := $(shell echo $(FULL_VERSION) | sed -n 's/.*-rc\([0-9]\+\)$$/\1/p')
 
+# Package version formatting
 ifeq ($(RELEASE),)
 RPM_RELEASE := 1
 RPM_VERSION := $(VERSION)-$(RPM_RELEASE)
@@ -33,55 +34,29 @@ BUILD_DIR := build
 SUPPORTED := ubuntu-20.04 \
              ubuntu-22.04 \
              ubuntu-24.04 \
-	     debian-10 \
 	     debian-11 \
+	     debian-12 \
+	     debian-13 \
 	     rockylinux-8 \
 	     rockylinux-9 \
+	     rockylinux-10 \
 	     oraclelinux-7 \
+	     amazonlinux-2 \
+	     amazonlinux-2023 \
 	     gentoo
 
 ##
 # These targets are the main ones to use for most things.
 ##
 
-.PHONY: all clean tgz lint format test query-tests install-common install-openrc install-systemd
+.PHONY: all clean tgz lint format test query-tests template-coverage
 
 all: package-all
 
+# Dump information related to the current version
 version:
 	@echo "full version=$(FULL_VERSION) version=$(VERSION) rel=$(RELEASE) rpm=$(RPM_VERSION) deb=$(DEB_VERSION)"
 
-# Build all packages
-.PHONY: package-all
-package-all: $(foreach distro_release, $(SUPPORTED), package-$(distro_release))
-
-# Gentoo package (tar.gz) creation
-.PHONY: package-gentoo
-package-gentoo:
-	mkdir -p $(BUILD_DIR)/gentoo
-	tar --transform "s,^,$(PACKAGE_NAME)-$(FULL_VERSION)/," -acjf $(BUILD_DIR)/gentoo/$(PACKAGE_NAME)-$(FULL_VERSION).tar.bz2 --exclude .gitignore $(shell git ls-tree --full-tree --name-only -r HEAD)
-
-
-# Create a deb package
-.PHONY: package-%
-package-%: PARTS=$(subst -, ,$*)
-package-%: DISTRO=$(word 1, $(PARTS))
-package-%: RELEASE=$(word 2, $(PARTS))
-package-%:
-	mkdir -p $(BUILD_DIR)
-	docker run --rm \
-	    -v .:/src:ro \
-	    -v ./$(BUILD_DIR):/output \
-	    --user $(shell id -u):$(shell id -g) \
-	    "$(DISTRO)-packager:$(RELEASE)"
-
-# Create a tarball
-tgz:
-	rm -rf $(BUILD_DIR)/tgz/root
-	mkdir -p $(BUILD_DIR)/tgz/root
-	$(MAKE) install-openrc DESTDIR=$(BUILD_DIR)/tgz/root
-	tar -cz -f $(BUILD_DIR)/tgz/$(PACKAGE_NAME)-$(FULL_VERSION).tgz -C $(BUILD_DIR)/tgz/root .
-
 # Clean up the build directory
 clean:
 	rm -rf $(BUILD_DIR)
@@ -98,7 +73,7 @@ format:
 	$(BLACK) src/pgmon.py
 	$(BLACK) src/test_pgmon.py
 
-# Run unit tests for the script
+# Run unit tests for the script (with coverage if it's available)
 test:
 ifeq ($(HAVE_COVERAGE),yes)
 	cd src ; $(PYTHON) -m coverage run -m unittest && python3 -m coverage report -m
@@ -114,8 +89,22 @@ query-tests:
 template-coverage:
 	$(PYTHON) zabbix_templates/coverage.py sample-config/pgmon-metrics.yml zabbix_templates/pgmon_templates.yaml
 
+# Create a tarball (openrc version)
+tgz:
+	rm -rf $(BUILD_DIR)/tgz/root
+	mkdir -p $(BUILD_DIR)/tgz/root
+	$(MAKE) install-openrc DESTDIR=$(BUILD_DIR)/tgz/root
+	tar -cz -f $(BUILD_DIR)/tgz/$(PACKAGE_NAME)-$(FULL_VERSION).tgz -C $(BUILD_DIR)/tgz/root .
+
+
+##
+# Install targets
+#
+# These are mostly used by the various package building images to actually install the script
+##
 
 # Install the script at the specified base directory (common components)
+.PHONY: install-common
 install-common:
 	# Set up directories
 	mkdir -p $(DESTDIR)/etc/$(PACKAGE_NAME)
@@ -138,6 +127,7 @@ install-common:
 	cp logrotate/${PACKAGE_NAME}.logrotate ${DESTDIR}/etc/logrotate.d/${PACKAGE_NAME}
 
 # Install for systemd
+.PHONY: install-systemd
 install-systemd:
 	# Install the common stuff
 	$(MAKE) install-common
@@ -149,6 +139,7 @@ install-systemd:
 	cp systemd/* $(DESTDIR)/lib/systemd/system/
 
 # Install for open-rc
+.PHONY: install-openrc
 install-openrc:
 	# Install the common stuff
 	$(MAKE) install-common
@@ -165,10 +156,46 @@ install-openrc:
 	cp openrc/pgmon.confd $(DESTDIR)/etc/conf.d/pgmon
 
 
-# Run all of the install tests
-.PHONY: install-tests debian-%-install-test rockylinux-%-install-test ubuntu-%-install-test gentoo-install-test
-install-tests: $(foreach distro_release, $(SUPPORTED), $(distro_release)-install-test)
+##
+# Packaging targets
+#
+# These targets create packages for the supported distro versions
+##
 
+# Build all packages
+.PHONY: package-all
+package-all: all-package-images $(foreach distro_release, $(SUPPORTED), package-$(distro_release))
+
+# Gentoo package (tar.bz2) creation
+.PHONY: package-gentoo
+package-gentoo:
+	mkdir -p $(BUILD_DIR)/gentoo
+	tar --transform "s,^,$(PACKAGE_NAME)-$(FULL_VERSION)/," -acjf $(BUILD_DIR)/gentoo/$(PACKAGE_NAME)-$(FULL_VERSION).tar.bz2 --exclude .gitignore $(shell git ls-tree --full-tree --name-only -r HEAD)
+	cp $(BUILD_DIR)/gentoo/$(PACKAGE_NAME)-$(FULL_VERSION).tar.bz2 $(BUILD_DIR)/
+
+# Create a deb/rpm package
+.PHONY: package-%
+package-%: PARTS=$(subst -, ,$*)
+package-%: DISTRO=$(word 1, $(PARTS))
+package-%: RELEASE=$(word 2, $(PARTS))
+package-%:
+	mkdir -p $(BUILD_DIR)
+	docker run --rm \
+	    -v .:/src:ro \
+	    -v ./$(BUILD_DIR):/output \
+	    --user $(shell id -u):$(shell id -g) \
+	    "$(DISTRO)-packager:$(RELEASE)"
+
+
+##
+# Install test targets
+#
+# These targets test installing the package on the supported distro versions
+##
+
+# Run all of the install tests
+.PHONY: install-tests debian-%-install-test rockylinux-%-install-test ubuntu-%-install-test amazonlinux-%-install-test gentoo-install-test
+install-tests: $(foreach distro_release, $(SUPPORTED), $(distro_release)-install-test)
 
 # Run a Debian install test
 debian-%-install-test:
@@ -181,7 +208,7 @@ debian-%-install-test:
 rockylinux-%-install-test:
 	docker run --rm \
 	     -v ./$(BUILD_DIR):/output \
-	     rockylinux:$* \
+	     rockylinux/rockylinux:$* \
 	     bash -c 'dnf makecache && dnf install -y /output/$(PACKAGE_NAME)-$(RPM_VERSION).el$*.noarch.rpm'
 
 # Run an Ubuntu install test
@@ -192,19 +219,30 @@ ubuntu-%-install-test:
 	     bash -c 'apt-get update && apt-get install -y /output/$(PACKAGE_NAME)-$(DEB_VERSION)-ubuntu-$*.deb'
 
 # Run an OracleLinux install test (this is for EL7 since CentOS7 images no longer exist)
+oraclelinux-%-install-test: MGR=$(intcmp $*, 7, yum, yum, dnf)
 oraclelinux-%-install-test:
 	docker run --rm \
 	     -v ./$(BUILD_DIR):/output \
-	     oraclelinux:7 \
-	     bash -c 'yum makecache && yum install -y /output/$(PACKAGE_NAME)-$(RPM_VERSION).el7.noarch.rpm'
+	     oraclelinux:$* \
+	     bash -c '$(MGR) makecache && $(MGR) install -y /output/$(PACKAGE_NAME)-$(RPM_VERSION).el7.noarch.rpm'
+
+# Run a Amazon Linux install test
+# Note: AL2 is too old for dnf
+amazonlinux-%-install-test: MGR=$(intcmp $*, 2, yum, yum, dnf)
+amazonlinux-%-install-test:
+	docker run --rm \
+	     -v ./$(BUILD_DIR):/output \
+	     amazonlinux:$* \
+	     bash -c '$(MGR) makecache && $(MGR) install -y /output/$(PACKAGE_NAME)-$(RPM_VERSION).amzn$*.noarch.rpm'
 
 # Run a Gentoo install test
 gentoo-install-test:
 	# May impliment this in the future, but would require additional headaches to set up a repo
-	true
+	echo "This would take a while ... skipping for now"
+
 
 ##
-# Container targets
+# Packaging image targets
 #
 # These targets build the docker images used to create packages
 ##
@@ -213,6 +251,11 @@ gentoo-install-test:
 .PHONY: all-package-images
 all-package-images: $(foreach distro_release, $(SUPPORTED), package-image-$(distro_release))
 
+# Nothing needs to be created to build te tarballs for Gentoo
+.PHONY: package-image-gentoo
+package-image-gentoo:
+	echo "Noting to do"
+
 # Generic target for creating images that actually build the packages
 # The % is expected to be: distro-release
 # The build/.package-image-% target actually does the work and touches a state file to avoid excessive building
@@ -228,11 +271,10 @@ package-image-%:
 # Inside-container targets
 #
 # These targets are used inside containers.  They expect the repo to be mounted
-# at /src and the package manager specific build directory to be mounted at
-# /output.
+# at /src and the build directory to be mounted at /output.
 ##
 
-.PHONY: actually-package-debian-% actually-package-rockylinux-% actually-package-ubuntu-% actually-package-oraclelinux-%
+.PHONY: actually-package-debian-% actually-package-rockylinux-% actually-package-ubuntu-% actually-package-oraclelinux-% actually-package-amazonlinux-%
 
 # Debian package creation
 actually-package-debian-%:
@@ -256,10 +298,36 @@ actually-package-ubuntu-%:
 	dpkg-deb -Zgzip --build /output/ubuntu-$* "/output/$(PACKAGE_NAME)-$(DEB_VERSION)-ubuntu-$*.deb"
 
 # OracleLinux package creation
+# Note: This needs to work inside OEL7, so we can't use intcmp
+actually-package-oraclelinux-7:
+	mkdir -p /output/oraclelinux-7/{BUILD,RPMS,SOURCES,SPECS,SRPMS}
+	sed -e "s/@@VERSION@@/$(VERSION)/g" -e "s/@@RELEASE@@/$(RPM_RELEASE)/g" RPM/$(PACKAGE_NAME)-el7.spec > /output/oraclelinux-7/SPECS/$(PACKAGE_NAME).spec
+	rpmbuild --define '_topdir /output/oraclelinux-7' \
+	         --define 'version $(RPM_VERSION)' \
+	         -bb /output/oraclelinux-7/SPECS/$(PACKAGE_NAME).spec
+	cp /output/oraclelinux-7/RPMS/noarch/$(PACKAGE_NAME)-$(RPM_VERSION).el7.noarch.rpm /output/
 actually-package-oraclelinux-%:
 	mkdir -p /output/oraclelinux-$*/{BUILD,RPMS,SOURCES,SPECS,SRPMS}
-	sed -e "s/@@VERSION@@/$(VERSION)/g" -e "s/@@RELEASE@@/$(RPM_RELEASE)/g" RPM/$(PACKAGE_NAME)-el7.spec > /output/oraclelinux-$*/SPECS/$(PACKAGE_NAME).spec
+	sed -e "s/@@VERSION@@/$(VERSION)/g" -e "s/@@RELEASE@@/$(RPM_RELEASE)/g" RPM/$(PACKAGE_NAME).spec > /output/oraclelinux-$*/SPECS/$(PACKAGE_NAME).spec
 	rpmbuild --define '_topdir /output/oraclelinux-$*' \
 	         --define 'version $(RPM_VERSION)' \
 	         -bb /output/oraclelinux-$*/SPECS/$(PACKAGE_NAME).spec
 	cp /output/oraclelinux-$*/RPMS/noarch/$(PACKAGE_NAME)-$(RPM_VERSION).el$*.noarch.rpm /output/
+
+# Amazon Linux package creation
+# Note: This needs to work inside AL2, so we can't use intcmp
+actually-package-amazonlinux-2:
+	mkdir -p /output/amazonlinux-2/{BUILD,RPMS,SOURCES,SPECS,SRPMS}
+	sed -e "s/@@VERSION@@/$(VERSION)/g" -e "s/@@RELEASE@@/$(RPM_RELEASE)/g" RPM/$(PACKAGE_NAME)-el7.spec > /output/amazonlinux-2/SPECS/$(PACKAGE_NAME).spec
+	rpmbuild --define '_topdir /output/amazonlinux-2' \
+	         --define 'version $(RPM_VERSION)' \
+	         -bb /output/amazonlinux-2/SPECS/$(PACKAGE_NAME).spec
+	cp /output/amazonlinux-2/RPMS/noarch/$(PACKAGE_NAME)-$(RPM_VERSION).amzn2.noarch.rpm /output/
+actually-package-amazonlinux-%:
+	mkdir -p /output/amazonlinux-$*/{BUILD,RPMS,SOURCES,SPECS,SRPMS}
+	sed -e "s/@@VERSION@@/$(VERSION)/g" -e "s/@@RELEASE@@/$(RPM_RELEASE)/g" RPM/$(PACKAGE_NAME).spec > /output/amazonlinux-$*/SPECS/$(PACKAGE_NAME).spec
+	rpmbuild --define '_topdir /output/amazonlinux-$*' \
+	         --define 'version $(RPM_VERSION)' \
+	         -bb /output/amazonlinux-$*/SPECS/$(PACKAGE_NAME).spec
+	cp /output/amazonlinux-$*/RPMS/noarch/$(PACKAGE_NAME)-$(RPM_VERSION).amzn$*.noarch.rpm /output/
+

docker/Dockerfile-amazonlinux

@@ -0,0 +1,22 @@
+# Dockerfile.rpm
+
+ARG DISTRO=amazonlinux
+ARG RELEASE=2023
+
+FROM ${DISTRO}:${RELEASE}
+
+ARG DISTRO
+ARG RELEASE
+
+RUN if [ ${RELEASE} -le 2 ] ; then MGR=yum ; else MGR=dnf ; fi && \
+    ${MGR} install -y \
+    rpm-build \
+    make \
+    && ${MGR} clean all
+
+RUN echo -e "#!/bin/bash\nmake actually-package-${DISTRO}-${RELEASE}" > /init.sh \
+    && chmod 755 /init.sh
+
+WORKDIR /src
+
+CMD ["/bin/bash", "/init.sh"]

docker/Dockerfile-oraclelinux

@@ -8,11 +8,12 @@ FROM ${DISTRO}:${RELEASE}
 ARG DISTRO
 ARG RELEASE
 
-RUN yum install -y \
+RUN if [ ${RELEASE} -le 7 ] ; then MGR=yum ; else MGR=dnf ; fi && \
+    ${MGR} install -y \
     rpm-build \
     make \
-    oracle-epel-release-el7 \
-    && yum clean all
+    oracle-epel-release-el${RELEASE} \
+    && ${MGR} clean all
 
 RUN echo -e "#!/bin/bash\nmake actually-package-${DISTRO}-${RELEASE}" > /init.sh \
     && chmod 755 /init.sh

docker/Dockerfile-rockylinux

@@ -3,7 +3,7 @@
 ARG DISTRO=rockylinux
 ARG RELEASE=9
 
-FROM ${DISTRO}:${RELEASE}
+FROM rockylinux/${DISTRO}:${RELEASE}
 
 ARG DISTRO
 ARG RELEASE

requirements.yml

@@ -1,2 +1,4 @@
-psycopg
+psycopg2
 pyyaml
+llfuse
+requests

sample-config/pgmon-metrics.yml

@@ -116,6 +116,63 @@ metrics:
         FROM pg_stat_io
         GROUP BY backend_type
 
+  temp_files:
+    type: row
+    query:
+      120000: >
+        SELECT count(*) AS count,
+               coalesce(sum(size), 0) AS size
+        FROM pg_ls_tmpdir()
+        WHERE name LIKE 'pgsql_tmp%%'
+      90500: >
+        SELECT count(*) AS count,
+               coalesce(sum((pg_stat_file(name)).size), 0) AS size
+        FROM pg_ls_dir('base/pgsql_tmp/', true, false) AS name
+        WHERE name LIKE 'pgsql_tmp%%'
+      0: >
+          SELECT count(*) AS count,
+                 coalesce(sum((pg_stat_file(files.name)).size), 0) AS size
+          FROM (
+            SELECT name
+            FROM pg_ls_dir('base') AS name
+            WHERE name = 'pgsql_tmp') AS dir
+          LEFT JOIN (
+            SELECT name
+            FROM pg_ls_dir('base/pgsql_tmp/') AS name
+            WHERE name LIKE 'pgsql_tmp%%') AS files
+          ON dir.name IS NOT NULL
+
+
+  ready_archive_files:
+    type: value
+    query:
+      120000: >
+        SELECT count(*) AS count
+        FROM pg_ls_archive_statusdir()
+        WHERE name LIKE '%%.ready'
+      100000: >
+        SELECT count(*) AS count
+        FROM pg_ls_dir('pg_wal/archive_status/', true, false) AS name
+        WHERE name LIKE '%%.ready'
+      90500: >
+        SELECT count(*) AS count
+        FROM pg_ls_dir('pg_xlog/archive_status/', true, false) AS name
+        WHERE name LIKE '%%.ready'
+      0: >
+        SELECT
+          CASE WHEN (
+            SELECT count(*)
+            FROM pg_ls_dir('pg_xlog/') AS f
+            WHERE f = 'archive_status') = 0
+          THEN (
+            SELECT 0 AS count
+          )
+          ELSE (
+            SELECT count(*) AS count
+            FROM pg_ls_dir('pg_xlog/archive_status/') AS name
+            WHERE name LIKE '%%.ready')
+          END
+
 
   ##
   # Per-database metrics
@@ -196,12 +253,28 @@ metrics:
     type: set
     query:
       0: >
-        SELECT state,
-               count(*) AS backend_count,
-               COALESCE(EXTRACT(EPOCH FROM max(now() - state_change)), 0) AS max_state_time
-        FROM pg_stat_activity
-        WHERE datname = %(dbname)s
-        GROUP BY state
+        SELECT
+          states.state,
+          COALESCE(a.backend_count, 0) AS backend_count,
+          COALESCE(a.max_state_time, 0) AS max_state_time
+        FROM (
+          SELECT state,
+                 count(*) AS backend_count,
+                 COALESCE(EXTRACT(EPOCH FROM now() - min(state_change)), 0) AS max_state_time
+          FROM pg_stat_activity
+          WHERE datname = %(dbname)s
+          GROUP BY state
+        ) AS a
+        RIGHT JOIN
+          unnest(ARRAY[
+            'active',
+            'idle',
+            'idle in transaction',
+            'idle in transaction (aborted)',
+            'fastpath function call',
+            'disabled'
+          ]) AS states(state)
+        USING (state);
     test_args:
       dbname: postgres
 
@@ -235,7 +308,7 @@ metrics:
   locks:
     type: row
     query:
-      0:
+      0: >
         SELECT COUNT(*) AS total,
                SUM(CASE WHEN granted THEN 1 ELSE 0 END) AS granted
         FROM pg_locks
@@ -318,3 +391,5 @@ metrics:
     type: value
     query:
       0: SELECT count(*) AS ntables FROM pg_stat_user_tables
+
+  #sequence_usage:

sample-config/pgmon.yml

@@ -1,8 +1,22 @@
-# The address the agent binds to
-#address: 127.0.0.1
+# # The name for this agent instance (controles the FUSE directory name)
+#name: pgmon
 
-# The port the agent listens on for requests
-#port: 5400
+# Mode to set on FUSE files (in octal)
+#fuse_mode: 0o400
+
+# Owner to set on FUSE files
+#fuse_owner: zabbix
+fuse_owner: james
+
+# Group to set on FUSE files
+#fuse_group: zabbix
+fuse_group: james
+
+# User the agent runs as
+agent_user: james
+
+# Groupthe agent runs as
+agent_group: james
 
 # Min PostgreSQL connection pool size (per database)
 #min_pool_size: 0
@@ -16,21 +30,28 @@
 # Log level for stderr logging (see https://docs.python.org/3/library/logging.html#logging-levels)
 # Possible values are: debug, info, warning, error, critical
 #log_level: 'error'
+log_level: 'info'
+
+# Base directory for FUSE file system
+fuse_base: /tmp/pgmon
 
 # Database user to connect as
 #dbuser: 'postgres'
 
 # Database host
 #dbhost: '/var/run/postgresql'
+dbhost: 127.0.0.1
 
 # Database port
 #dbport: 5432
+dbport: 54315
 
 # Default database to connect to when none is specified for a metric
 #dbname: 'postgres'
 
 # SSL connection mode
 #ssl_mode: require
+ssl_mode: allow
 
 # Timeout for getting a connection slot from a pool
 #pool_slot_timeout: 5

src/pgmon.py

@@ -14,6 +14,9 @@ import signal
 import argparse
 import logging
 import re
+import stat
+import errno
+import requests
 
 from decimal import Decimal
 
@@ -23,9 +26,6 @@ from contextlib import contextmanager
 
 from datetime import datetime, timedelta
 
-from http.server import BaseHTTPRequestHandler
-from http.server import ThreadingHTTPServer
-
 from threading import Lock
 
 import yaml
@@ -34,10 +34,13 @@ import psycopg2
 from psycopg2.extras import RealDictCursor
 from psycopg2.pool import ThreadedConnectionPool
 
-import requests
+from pwd import getpwnam
+from grp import getgrnam
+
+import llfuse
 
 
-VERSION = "1.0.4"
+VERSION = "1.1.0-rc1"
 
 
 class Context:
@@ -71,9 +74,6 @@ class Context:
     # Running state (used to gracefully shut down)
     running = True
 
-    # The http server object
-    httpd = None
-
     # Where the config file lives
     config_file = None
 
@@ -142,10 +142,20 @@ class InvalidDataError(Exception):
 
 # Default config settings
 DEFAULT_CONFIG = {
-    # The address the agent binds to
-    "address": "127.0.0.1",
-    # The port the agent listens on for requests
-    "port": 5400,
+    # The name for this agent instance (controles the FUSE directory name)
+    "name": "pgmon",
+    # Base directory for FUSE file system
+    "fuse_base": "/run/pgmon",
+    # Mode to set on FUSE files (in octal)
+    "fuse_mode": 0o400,
+    # Owner to set on FUSE files
+    "fuse_owner": "zabbix",
+    # Group to set on FUSE files
+    "fuse_group": "zabbix",
+    # The user the agent runs as
+    "agent_user": "pgmon",
+    # The group the agent runs as
+    "agent_group": "pgmon",
     # Min PostgreSQL connection pool size (per database)
     "min_pool_size": 0,
     # Max PostgreSQL connection pool size (per database)
@@ -349,11 +359,9 @@ def signal_handler(sig, frame):  # pylint: disable=unused-argument
     if sig in [signal.SIGINT, signal.SIGTERM, signal.SIGQUIT]:
         Context.log.info("Shutting down ...")
         Context.running = False
-        if Context.httpd is not None:
-            Context.httpd.socket.close()
 
     # Signal a reload
-    if sig == signal.SIGHUP:
+    if sig in [signal.SIGHUP, signal.SIGUSR1]:
         Context.log.warning("Received config reload signal")
         read_config(Context.config_file)
 
@@ -792,89 +800,253 @@ def test_queries():
     return 0
 
 
-class SimpleHTTPRequestHandler(BaseHTTPRequestHandler):
+class PGMonFuse(llfuse.Operations):
     """
-    This is our request handling server.  It is responsible for listening for
+    This is our FUSE filesystem for requests from Zabbix.  It is responsible for listening for
     requests, processing them, and responding.
     """
 
-    def log_request(self, code="-", size="-"):
-        """
-        Override to suppress standard request logging
-        """
+    def __init__(self):
+        super().__init__()
+        self.builtin = ["agent_version", "latest_version_info", "sleep"]
+        self.update_files()
 
-    def do_GET(self):  # pylint: disable=invalid-name
+        # Dictionary holdig context information for open file handles
+        self.inodes = dict()
+        self.file_handles = dict()
+
+    @staticmethod
+    def gen_attrs(inode, is_dir):
+
+        attrs = llfuse.EntryAttributes()
+        if is_dir:
+            attrs.st_mode = Context.config["fuse_mode"] | stat.S_IXUSR | stat.S_IFDIR
+        else:
+            attrs.st_mode = Context.config["fuse_mode"] | stat.S_IFREG
+        attrs.st_nlink = 1
+        attrs.st_size = 1024
+        attrs.st_uid = getpwnam(Context.config["fuse_owner"]).pw_uid
+        attrs.st_gid = getgrnam(Context.config["fuse_group"]).gr_gid
+
+        attrs.st_ino = inode
+
+        return attrs
+
+    def update_files(self):
+        self.files = self.builtin + sorted(list(Context.config["metrics"].keys()))
+
+    @staticmethod
+    def path_to_inode(path):
         """
-        Handle a request.  This is just a wrapper around the actual handler
-        code to keep things more readable.
+        Provide a consistent inode for a given file
         """
+        return hash(path) & 0xFFFFFFFFFFFFFFFF
+
+    def lookup(self, inode_p, name, ctx=None):
+        """ """
+        print(f"Lookup called with: inode_p={inode_p} name={name}")
+
+        # We only have a single level, so if the parent inode is anything else
+        # then it doesn't exist
+        if inode_p != llfuse.ROOT_INODE:
+            raise llfuse.FUSEError(errno.ENOENT)
+
+        # Generate the inode for this file (ie: query + args)
+        inode = self.path_to_inode(name)
+
+        # Get the file info if we already have it, create it otherwise
         try:
-            self._handle_request()
-        except BrokenPipeError:
-            Context.log.error("Client disconnected, exiting handler")
+            file = self.inodes[inode]
+        except KeyError:
+            # Separate out the name components
+            parts = name.decode("utf-8").split(":", 1)
+            base_name = parts[0]
+            try:
+                arg_str = parts[1]
+            except IndexError:
+                arg_str = ""
 
-    def _handle_request(self):
-        """
-        Request handler
-        """
-        # Parse the URL
-        parsed_path = urlparse(self.path)
-        metric_name = parsed_path.path.strip("/")
-        parsed_query = parse_qs(parsed_path.query)
+            # Make sure it's a valid request
+            if base_name not in self.files:
+                raise llfuse.FUSEError(errno.ENOENT)
 
+            # Split any key/value args
+            args = {}
+            for arg in arg_str.split(","):
+                if arg == "":
+                    continue
+                print(f"Splitting: {arg}")
+                key, value = arg.split("=", 1)
+                args[key] = value
+
+            file = {
+                "name": base_name,
+                "args": args,
+                "error": None,
+                "attrs": None,
+                "count": 0,
+            }
+
+            self.inodes[inode] = file
+
+        if not file["attrs"]:
+            # Fuse shouldn't be calling this for the base directory, so it
+            # should be safe to assume we're dealing with a file.
+            file["attrs"] = self.gen_attrs(inode, False)
+
+        # Increase the lookup count
+        file["count"] += 1
+
+        return file["attrs"]
+
+    def getattr(self, inode, ctx=None):
+        print(f"getattr called with: inode={inode}")
+
+        if inode == llfuse.ROOT_INODE:
+            # If we're looking at the directory itself
+            return self.gen_attrs(inode, True)
+
+        try:
+            return self.inodes[inode]["attrs"]
+        except KeyError:
+            print(f"Getattr called without a lookup first for: inode={inode}")
+            raise llfuse.FUSEError(errno.ENOENT)
+
+    def opendir(self, inode, ctx):
+        """
+        Note: We only allow one directory level
+        """
+        print(f"opendir called with: inode={inode}")
+
+        if inode != llfuse.ROOT_INODE:
+            raise llfuse.FUSEError(errno.ENOENT)
+        return inode
+
+    def readdir(self, fh, off):
+        print(f"readdir called with: fh={fh} off={off}")
+
+        # Something has gone wrong if this was called for anything other than the top level directory
+        assert fh == llfuse.ROOT_INODE
+
+        for i, entry in enumerate(self.files[off:]):
+            print(f" yielding: {entry.encode('utf8')}")
+            yield (
+                entry.encode("utf-8"),
+                self.lookup(llfuse.ROOT_INODE, entry.encode("utf-8")),
+                i + off + 1,
+            )
+
+    def new_file_handle(self, inode=None):
+        """
+        Pick an unused file handle (int) and create an entry in file_handles for it
+
+        returns: the file handle number
+        """
+        # Find the first unused number
+        new_fh = 0
+        for fh in sorted(self.file_handles.keys()):
+            if new_fh == fh:
+                new_fh += 1
+            else:
+                break
+
+        # Create the empty entry in the
+        self.file_handles[new_fh] = {
+            "inode": inode,
+            "data": None,
+        }
+
+        return new_fh
+
+    def open(self, inode, flags, ctx):
+        print(f"open called with: inode={inode} flags={flags}")
+
+        fh = self.new_file_handle(inode)
+
+        # Get the data associated with the file handle
+        file = self.file_handles[fh]
+
+        # Get the data associated with the file name and parameters
+        data = self.inodes[inode]
+
+        # Pull out the things we need to run the query
+        metric_name = data["name"]
+        metric_args = data["args"]
+
+        # Populate the data for the file
         if metric_name == "agent_version":
-            self._reply(200, VERSION)
+            file["data"] = VERSION.encode("utf-8")
+        elif metric_name == "sleep":
+            seconds = metric_args.get("seconds", "10")
+            with llfuse.lock_released:
+                time.sleep(int(seconds))
+            file["data"] = seconds.encode("utf-8")
         elif metric_name == "latest_version_info":
             try:
                 get_latest_version()
-                self._reply(
-                    200,
-                    json.dumps(
-                        {
-                            "latest": Context.latest_version,
-                            "supported": 1 if Context.release_supported else 0,
-                        }
-                    ),
-                )
+                file["data"] = json.dumps(
+                    {
+                        "latest": Context.latest_version,
+                        "supported": 1 if Context.release_supported else 0,
+                    }
+                ).encode("utf-8")
             except LatestVersionCheckError as e:
                 Context.log.error(
                     "Failed to retrieve latest version information: %s", e
                 )
-                self._reply(503, "Failed to retrieve latest version info")
+                file["data"] = "ERROR: Failed to retrieve latest version info".encode(
+                    "utf-8"
+                )
         else:
-            # Note: parse_qs returns the values as a list.  Since we always expect
-            #       single values, just grab the first from each.
-            args = {key: values[0] for key, values in parsed_query.items()}
-
             # Get the dbname.  If none was provided, use the default from the
             # config.
-            dbname = args.get("dbname", Context.config["dbname"])
+            dbname = metric_args.get("dbname", Context.config["dbname"])
 
             # Sample the metric
             try:
-                self._reply(200, sample_metric(dbname, metric_name, args))
+                file["data"] = sample_metric(dbname, metric_name, metric_args).encode(
+                    "utf-8"
+                )
             except UnknownMetricError:
                 Context.log.error("Unknown metric: %s", metric_name)
-                self._reply(404, "Unknown metric")
+                file["data"] = "ERROR: Unknown metric".encode("utf-8")
             except MetricVersionError:
                 Context.log.error("Failed to find an query version for %s", metric_name)
-                self._reply(404, "Unsupported version")
+                file["data"] = "ERROR: Unsupported version".encode("utf-8")
             except UnhappyDBError:
                 Context.log.info("Database %s is unhappy, please be patient", dbname)
-                self._reply(503, "Database unavailable")
+                file["data"] = "ERROR: Database unavailable".encode("utf-8")
             except Exception as e:  # pylint: disable=broad-exception-caught
                 Context.log.error("Error running query: %s", e)
-                self._reply(500, "Unexpected error: {}".format(e))
+                file["data"] = "ERROR: Unexpected error: {}".format(e).encode("utf-8")
 
-    def _reply(self, code, content):
-        """
-        Send a reply to the client
-        """
-        self.send_response(code)
-        self.send_header("Content-type", "application/json")
-        self.end_headers()
+        return fh
 
-        self.wfile.write(bytes(content, "utf-8"))
+    def read(self, fh, off, size):
+        print(f"read called with: fh={fh} off={off} size={size}")
+
+        file = self.file_handles[fh]
+
+        return file["data"][off : off + size]
+
+    def forget(self, inode_list):
+        """
+        Note: Per the dcumenation, this function must not raise an exception!
+        """
+        print(f"forget called with: inode_list={inode_list}")
+
+        for inode, nlookup in inode_list:
+            try:
+                self.inodes[inode]["count"] -= nlookup
+                if self.inodes[inode]["count"] <= 0:
+                    del self.inodes[inode]
+            except Exception as e:
+                Context.log.error("Error forgetting inode %s: %s", inode, e)
+
+    def release(self, fh):
+        print(f"release called with: fh={fh}")
+
+        del self.file_handles[fh]
 
 
 def main():
@@ -916,23 +1088,44 @@ def main():
             sys.exit(1)
         sys.exit(0)
 
-    # Set up the http server to receive requests
-    server_address = (Context.config["address"], Context.config["port"])
-    Context.httpd = ThreadingHTTPServer(server_address, SimpleHTTPRequestHandler)
-
     # Set up the signal handler
     signal.signal(signal.SIGINT, signal_handler)
     signal.signal(signal.SIGHUP, signal_handler)
+    signal.signal(signal.SIGUSR1, signal_handler)
 
-    # Handle requests.
-    Context.log.info("Listening on port %s...", Context.config["port"])
-    while Context.running:
-        Context.httpd.handle_request()
+    # Ensure the mount point exists
+    if not os.path.exists(Context.config["fuse_base"]):
+        os.makedirs(Context.config["fuse_base"])
+
+    # Create the FUSE filesystem
+    pgmon_fuse = PGMonFuse()
+    fuse_options = set(llfuse.default_options)
+    fuse_options.add("fsname=pgmon")
+    # fuse_options.add('direct_io')
+    # if options.debug_fuse:
+    #    fuse_options.add('allow_others')
+    try:
+        llfuse.init(pgmon_fuse, Context.config["fuse_base"], fuse_options)
+    except:
+        llfuse.close()
+        return -1
+
+    try:
+        llfuse.main(workers=1)
+    except:
+        llfuse.close()
+        raise
+
+    llfuse.close()
 
     # Clean up PostgreSQL connections
     # TODO: Improve this ... not sure it actually closes all the connections cleanly
     for pool in Context.connections.values():
-        pool.close()
+        pool.closeall()
+
+    logging.shutdown()
+
+    print("Good bye.")
 
 
 if __name__ == "__main__":

tests/docker-compose.yml

@@ -20,7 +20,7 @@ services:
       POSTGRES_PASSWORD: secret
     healthcheck:
       #test: [ "CMD", "pg_isready", "-U", "postgres" ]
-      test: [ "CMD-SHELL", "pg_controldata /var/lib/postgresql/data/ | grep -q 'in production'" ]
+      test: [ "CMD-SHELL", "pg_controldata $(psql -At -U postgres -c 'show data_directory') | grep -q 'in production'" ]
       interval: 5s
       timeout: 2s
       retries: 40

tests/run-tests.sh

@@ -24,6 +24,7 @@ images["14"]='14-bookworm'
 images["15"]='15-bookworm'
 images["16"]='16-bookworm'
 images["17"]='17-bookworm'
+images["18"]='18-trixie'
 
 declare -A results=()
 

zabbix_templates/pgmon_templates.yaml

@@ -49,6 +49,206 @@ zabbix_export:
           tags:
             - tag: Application
               value: PostgreSQL
+        - uuid: 1dd74025ca0e463bb9eee5cb473f30db
+          name: 'Total buffers allocated'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[buffers_alloc,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of shared buffers that have been allocated'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.buffers_alloc
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 4ab405996e71444a8113b95c65d73be2
+          name: 'Total buffers written by backends'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[buffers_backend,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of shared buffers written by backends'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.buffers_backend
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: e8b440a1d6ff4ca6b3cb27e2e76f9d06
+          name: 'Total number of fsyncs from backends'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[buffers_backend_fsync,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of times backends have issued their own fsync calls'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.buffers_backend_fsync
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: f8a14885edf34b5cbf2e92432e8fa4c2
+          name: 'Total buffers written during checkpoints'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[buffers_checkpoint,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of shared buffers written during checkpoints'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.buffers_checkpoint
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: f09956f7aaad4b99b0d67339edf26dcf
+          name: 'Total buffers written by the background writer'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[buffers_clean,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of shared buffers written by the background writer'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.buffers_clean
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: f1c6bd9346b14964bd492d7ccf8baf50
+          name: 'Total checkpoints due to changes'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[checkpoints_changes,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of checkpoints that have occurred due to the number of row changes'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.checkpoints_req
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 04c4587d2f2f4f5fbad866d70938cbb3
+          name: 'Total checkpoints due to time limit'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[checkpoints_timed,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of checkpoints that have occurred due to the configured time limit'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.checkpoints_timed
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 580a7e632b644aafa1166c534185ba92
+          name: 'Total time spent syncing files for checkpoints'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[checkpoint_sync_time,total]'
+          delay: '0'
+          history: 90d
+          units: s
+          description: 'Total time spent syncing files for checkpoints'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.checkpoint_sync_time
+            - type: MULTIPLIER
+              parameters:
+                - '0.001'
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 4faa33b55fae4fc68561020636573a46
+          name: 'Total time spent writing checkpoints'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[checkpoint_write_time,total]'
+          delay: '0'
+          history: 90d
+          units: s
+          description: 'Total time spent writing checkpoints'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.checkpoint_write_time
+            - type: MULTIPLIER
+              parameters:
+                - '0.001'
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: c0c829c6cdc046a4b5d216e10362e4bb
+          name: 'Total number of times the background writer stopped'
+          type: DEPENDENT
+          key: 'pgmon.bgwriter[maxwritten_clean,total]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of times the background writer stopped due to reaching the maximum  number of buffers it''s allowed to write in a single scan'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.maxwritten_clean
+          master_item:
+            key: 'pgmon[bgwriter]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 645fef7b55cd48c69b11de3201e88d78
+          name: 'Number of locks'
+          type: DEPENDENT
+          key: pgmon.locks.count
+          delay: '0'
+          history: 90d
+          description: 'Total number of locks in any database'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.total
+          master_item:
+            key: 'pgmon[locks]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: a56ad753b1f341928867a47b14ed8b77
+          name: 'Number of granted locks'
+          type: DEPENDENT
+          key: pgmon.locks.granted
+          delay: '0'
+          history: 90d
+          description: 'Total number of granted locks in any database'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.granted
+          master_item:
+            key: 'pgmon[locks]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
         - uuid: de1fa757395440118026f4c7a7c4ebbe
           name: 'PostgreSQL latest supported version'
           type: DEPENDENT
@@ -82,6 +282,39 @@ zabbix_export:
               expression: 'last(/PostgreSQL by pgmon/pgmon.release.supported)<>1'
               name: 'PostgreSQL major release is lo longer supported'
               priority: INFO
+        - uuid: d0651ffc203949e3a24b1e1a2beb3dd7
+          name: 'Temp file size'
+          type: DEPENDENT
+          key: 'pgmon.temp_files[bytes]'
+          delay: '0'
+          history: 90d
+          units: b
+          description: 'Total size of all temp files'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.size
+          master_item:
+            key: 'pgmon[temp_files]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: deb355b26c37441189c85ac82465f21e
+          name: 'Temp file count'
+          type: DEPENDENT
+          key: 'pgmon.temp_files[count]'
+          delay: '0'
+          history: 90d
+          description: 'Total number of temp flies'
+          preprocessing:
+            - type: JSONPATH
+              parameters:
+                - $.count
+          master_item:
+            key: 'pgmon[temp_files]'
+          tags:
+            - tag: Application
+              value: PostgreSQL
         - uuid: 763920af8da84db8a9a2667d9653cb21
           name: 'PostgreSQL Agent Version'
           type: HTTP_AGENT
@@ -95,6 +328,32 @@ zabbix_export:
           tags:
             - tag: Application
               value: PostgreSQL
+        - uuid: a0bd4a987b7643c5a86e160dd9f9afcb
+          name: 'PostgreSQL Archive ready count'
+          type: HTTP_AGENT
+          key: 'pgmon[archive_ready]'
+          history: 90d
+          trends: '0'
+          description: 'Number of WAL files ready and waiting to be archived'
+          url: 'http://localhost:{$AGENT_PORT}/ready_archive_files'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+        - uuid: 91baea76ebb240b19c5a5d3913d0b989
+          name: 'PostgreSQL BGWriter Info'
+          type: HTTP_AGENT
+          key: 'pgmon[bgwriter]'
+          delay: 5m
+          history: '0'
+          value_type: TEXT
+          trends: '0'
+          description: 'Maximum age of any frozen XID and MXID in any database'
+          url: 'http://localhost:{$AGENT_PORT}/bgwriter'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+            - tag: Type
+              value: Raw
         - uuid: 06b1d082ed1e4796bc31cc25f7db6326
           name: 'PostgreSQL Backend IO Info'
           type: HTTP_AGENT
@@ -124,6 +383,21 @@ zabbix_export:
               value: PostgreSQL
             - tag: Type
               value: Raw
+        - uuid: 4627c156923f4d53bc04789b9b88c133
+          name: 'PostgreSQL Lock Info'
+          type: HTTP_AGENT
+          key: 'pgmon[locks]'
+          delay: 5m
+          history: '0'
+          value_type: TEXT
+          trends: '0'
+          description: 'Maximum age of any frozen XID and MXID in any database'
+          url: 'http://localhost:{$AGENT_PORT}/locks'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+            - tag: Type
+              value: Raw
         - uuid: 8706eccb7edc4fa394f552fc31f401a9
           name: 'PostgreSQL ID Age Info'
           type: HTTP_AGENT
@@ -139,6 +413,20 @@ zabbix_export:
               value: PostgreSQL
             - tag: Type
               value: Raw
+        - uuid: bd3026ef3b82419d8a004e2d4684554a
+          name: 'PostgreSQL Temp Ffle info'
+          type: HTTP_AGENT
+          key: 'pgmon[temp_files]'
+          history: '0'
+          value_type: TEXT
+          trends: '0'
+          description: 'PostgreSQL temp file stats'
+          url: 'http://localhost:{$AGENT_PORT}/temp_files'
+          tags:
+            - tag: Application
+              value: PostgreSQL
+            - tag: Type
+              value: Raw
         - uuid: ee88f5f4d2384f97946d049af5af4502
           name: 'PostgreSQL version'
           type: HTTP_AGENT
@@ -170,6 +458,283 @@ zabbix_export:
           enabled_lifetime_type: DISABLE_AFTER
           enabled_lifetime: 1d
           item_prototypes:
+            - uuid: 28d5fe3a4f6848149afed33aa645f677
+              name: 'Max connection age: active on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[active,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "active")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: ff1b817c3d1f43dc8b49bfd0dcb0d10a
+              name: 'Connection count: active on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[active,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "active")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 4c0b9ca43adb45b895e3ba2e200e501e
+              name: 'Max connection age: disabled on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[disabled,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "disabled")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 280dfc9a84e8425399164e6f3e91cf92
+              name: 'Connection count: disabled on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[disabled,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "disabled")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 63e62108657f47aaa29f7ec6499e45fc
+              name: 'Max connection age: fastpath function call on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[fastpath_function,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "fastpath function call")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 33193e62f1ad4da5b2e30677581e5305
+              name: 'Connection count: fastpath function call on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[fastpath_function,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "fastpath function call")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 92e2366a56424bc18a88606417eae6e4
+              name: 'Max connection age: idle on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 505bbbb4c7b745d8bab8f3b33705b76b
+              name: 'Connection count: idle on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 282494d5900c4c2e8abd298160c7cbb6
+              name: 'Max connection age: idle in transaction (aborted) on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle_aborted,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle in transaction (aborted)")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 0da5075d79234602836fc7967a31f1cc
+              name: 'Connection count: idle in transaction (aborted) on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle_aborted,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle in transaction (aborted)")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 47aa3f7f4ff1473aae425b4c89472ab4
+              name: 'Max connection age: idle in transaction on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle_transaction,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle in transaction")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 95519bf0aefd49799601e1bbb488ec90
+              name: 'Connection count: idle in transaction on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[idle_transaction,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "idle in transaction")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: b2ae38a5733d49ceb1a31e774c11785b
+              name: 'Max connection age: starting on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[starting,age,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              value_type: FLOAT
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "starting")].max_state_time.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: c1890f2d7ce84a7ebaaadf266d3ffc51
+              name: 'Connection count: starting on {#DBNAME}'
+              type: DEPENDENT
+              key: 'pgmon_connection_states[starting,count,{#DBNAME}]'
+              delay: '0'
+              history: 90d
+              description: 'Number of disk blocks read in this database'
+              preprocessing:
+                - type: JSONPATH
+                  parameters:
+                    - '$[?(@.state == "starting")].backend_count.first()'
+              master_item:
+                key: 'pgmon_connection_states[{#DBNAME}]'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+            - uuid: 131653f883f448a7b861b16bc4366dfd
+              name: 'Database Connection States for {#DBNAME}'
+              type: HTTP_AGENT
+              key: 'pgmon_connection_states[{#DBNAME}]'
+              history: '0'
+              value_type: TEXT
+              trends: '0'
+              url: 'http://localhost:{$AGENT_PORT}/activity'
+              query_fields:
+                - name: dbname
+                  value: '{#DBNAME}'
+              tags:
+                - tag: Application
+                  value: PostgreSQL
+                - tag: Database
+                  value: '{#DBNAME}'
+                - tag: Type
+                  value: Raw
             - uuid: a30babe4a6f4440bba2a3ee46eff7ce2
               name: 'Time spent executing statements on {#DBNAME}'
               type: DEPENDENT