PostgreSQL Monitoring

postgresql is a powerful open source relational database known for reliability, extensibility, and strong compliance with sql standards it is widely used for transactional applications, analytics workloads, and enterprise systems that require data integrity and performance postgresql monitoring in kloudmate helps you observe the health, performance, and behavior of your postgresql databases by collecting metrics and logs using the kloudmate agent powered by opentelemetry this enables centralized monitoring of postgresql instances running on aws ec2, azure virtual machines, or on premise servers postgresql integration using kloudmate agents what this integration provides with postgresql monitoring enabled, kloudmate collects telemetry that provides visibility into database health and performance metrics locks, deadlocks, and query behavior slow queries through log analysis resource usage at host level postgresql server logs this visibility helps identify performance bottlenecks, long running queries, locking issues, and availability problems pre requisites before configuring postgresql monitoring, ensure the following refer to postgresql documentation for https //www postgresql org/support/versioning/ monitoring user must have select permission on pg stat database postgresql is installed and running kloudmate agent installed on the postgresql host refer to agent installation for https //docs kloudmate com/linux agent or https //docs kloudmate com/windows agent postgresql username and password postgresql logging https //docs kloudmate com/enable logging in postgresql from configurations step 1 access agents and opentelemetry collector configuration log in to the kloudmate platform go to settings → agents select the agent running on the postgresql host click actions → collector configuration yaml editor opens for configuration step 2 add required extensions and receivers extensions extensions health check pprof endpoint 0 0 0 0 1777 zpages endpoint 0 0 0 0 55679 file storage create directory true receivers two receivers are used postgresql receiver – collects metrics file log receiver – collects logs (general, error, slow queries) if you only need metrics, configure only the postgresql receiver receivers postgresql endpoint localhost 5432 #update the endpoint transport tcp username \<postgres> #update postgres username password \<password> #update postgres password databases \[] collection interval 30s tls insecure true metrics postgresql deadlocks enabled true postgresql database locks enabled true filelog/pgsql json #use this file log if postgres version >= 15 include \[/var/lib/postgresql/ /main/log/postgresql ] storage file storage retry on failure enabled true operators \ type json parser parse to body severity parse from body error severity mapping info log filelog/pgsql log #use this file log if postgres version < 15 include \[/var/lib/postgresql/ /main/log/postgresql ] storage file storage multiline line start pattern ^\d{4} \d{2} \d{2} \d{2} \d{2} \d{2} retry on failure enabled true operators \ type regex parser parse to body regex '^(?p\<timestamp>\[0 9\\ ]+\s\[0 9 \\ ]+\s\w+)\s+\\\[(?p\<pid>\d+)\\]\(? \s+(?p\<database>\w+)@(?p\<user>\w+))?\s+(?p\<severity>\w+) (? \s+duration \s+(?p\<duration>\d+\\ \d+)\s+\[^ ] \s+(statement \s+)?)?\s+(?p\<query> +)$' timestamp parse from body timestamp layout '%y %m %d %h %m %s %f %z' severity parse from body severity mapping info \ statement \ log \ type remove field body severity \ type remove field body timestamp step 3 configure processors set up the processor component to identify resource information from the host and either append or replace the resource values in the telemetry data with this information choose one configuration based on where postgressql is running server (on premise / non cloud / cloud) processors batch send batch size 5000 timeout 10s resourcedetection detectors \[system] system resource attributes host name enabled true host id enabled true os type enabled false resource attributes \ key service name action insert from attribute host name aws ec2 (optional) to collect ec2 tags, attach an iam role with ec2\ describetags permission processors batch send batch size 5000 timeout 10s resourcedetection/ec2 detectors \["ec2"] ec2 tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute ec2 tag name \ key service instance id action insert from attribute host id azure virtual machines processors batch send batch size 5000 timeout 10s resourcedetection/azure detectors \["azure"] azure tags \ ^tag1$ \ ^tag2$ \ ^label $ \ ^name$ resource attributes \ key service name action insert from attribute azure vm name \ key service instance id action insert from attribute host id step 4 configure exporter and pipelines configure the kloudmate backend exporter and define pipelines for metrics and logs exporters debug verbosity detailed otlphttp endpoint 'https //otel kloudmate com 4318' headers authorization xxxxxxxx # use the auth key service pipelines metrics receivers \[postgresql] processors \[batch, resourcedetection, resource] # apply the correct resourcedetection exporters \[otlphttp] logs receivers \[pgsql log] #use the correct filelog receiver as per your pg version processors \[batch, resourcedetection, resource] # apply the correct resourcedetection exporters \[otlphttp] extensions \[health check, pprof, zpages, file storage] step 5 save configuration and restart agent after updating the configuration, click save configuration in the kloudmate ui you can also restart the agent from your server’s console for linux execute the following commands systemctl restart kmagent systemctl status kmagent these commands restart the kloudmate agent and display its current status for windows open the services window press win + r , type services msc , and press ok or search for services from the start menu locate the kloudmate agent service right click the service and select restart after restarting the agent, verify that postgresql metrics and logs are visible in the kloudmate dashboard post‑integration data validation verify that metrics are flowing into kloudmate using the explore view after the agent restarts log in to kloudmate navigate to explore select opentelemetry → metrics choose a postgresql metric and run the query seeing time series data confirms that postgresql telemetry is flowing successfully standard postgresql dashboards kloudmate provides prebuilt postgresql dashboards through https //templates kloudmate com/postgress/index html these dashboards visualize postgresql database performance, query operations, connections, and resource usage to import and start using these templates, follow the steps described in https //docs kloudmate com/creating a dashboard#sfk3g default metrics for postgresql name description type unit postgresql backends the number of backends sum none postgresql connection max configured the maximum number of client connections allowed gauge none postgresql db size the database disk usage sum bytes postgresql commits the number of commits sum none postgresql deadlocks the number of deadlocks sum none postgresql database locks the number of database locks gauge count postgresql wal lag the time between flushing the recent wal locally and receiving notification that the standby server has completed an operation on it gauge seconds postgresql replication data delay the amount of data delayed in replication gauge bytes for the complete metrics list, refer to the https //github com/open telemetry/opentelemetry collector contrib/blob/main/receiver/postgresqlreceiver/documentation md