Announcing an Open Source Monitoring Extension...

Crunchy Data is pleased to announce a new open source pgMonitor Extension. Crunchy Data has worked on a pgMonitor tool for several years as part of our Kubernetes and self-managed Postgres deployments and recently we’ve added an extension to the tool set.

Two primary scenarios motivated the creation of the pgMonitor extension :

Quicker Metrics: Monitoring metrics often need quick response times to allow for frequent updates. We've noticed that certain metrics become slower as the database grows. This impacts not only common metrics but also more complex business metrics that could require several minutes to generate.
Version Compatibility: New PostgreSQL versions can break existing metrics due to changes in the catalogs. Managing different metric sets for various PostgreSQL versions is tedious and can be challenging.

Benefits of the pgMonitor extension

The pgMonitor extension focuses on improving query performance and simplifying metric collection:

Efficient Metrics Storage: the pgMonitor extension queries PostgreSQL internal tables and stores metrics data in materialized views. This setup reduces the overhead on the database and allows for frequent polling without performance degradation.
Flexible Refresh Intervals: The extension uses a background worker to refresh materialized views on the database at intervals you choose, ensuring up-to-date metrics with minimal impact.
High Performance: By leveraging materialized views, the pgMonitor extension can handle slower metric queries efficiently, keeping the overall metrics scrape process fast.
Broad Compatibility: the pgMonitor extension integrates seamlessly with various monitoring systems like Prometheus, Icinga/Nagios, etc., allowing for simpler configuration and flexibility.
Future-Proofing: With query definitions contained within the database, the pgMonitor extension can adapt more easily to changes in Postgres versions, simplifying support for multiple versions.

Getting started with pgMonitor

Install the extension code

make install

CREATE SCHEMA pgmonitor_ext;
CREATE EXTENSION pgmonitor SCHEMA pgmonitor_ext;

Add to shared libraries

shared_preload_libraries = 'pgmonitor_bgw'     # (change requires restart)

Set the database(s) for the background worker to run on

pgmonitor_bgw.dbname = 'proddb,staging'

Set the role for the background worker to use

pgmonitor_bgw.role = 'postgres'

Background workers and pgMonitor configuration

pgMonitor supports a refresh of the materialized views with a background worker. The extension has configuration tables that help manage all the objects mentioned above. The names of all views and materialized views are stored in the metric_views configuration table.

                              Table "pgmonitor_ext.metric_views"
       Column       |           Type           | Collation | Nullable |        Default
--------------------+--------------------------+-----------+----------+-----------------------
 view_schema        | text                     |           | not null | 'pgmonitor_ext'::text
 view_name          | text                     |           | not null |
 materialized_view  | boolean                  |           | not null | true
 concurrent_refresh | boolean                  |           | not null | true
 run_interval       | interval                 |           | not null | '00:10:00'::interval
 last_run           | timestamp with time zone |           |          |
 last_run_time      | interval                 |           |          |
 active             | boolean                  |           | not null | true
 scope              | text                     |           | not null | 'global'::text

Key configs:

Concurrent Refresh: The materialized_view column indicates if a view is a materialized view, and concurrent_refresh specifies if a concurrent refresh can be done. This avoids locking issues during refreshes.
Refresh Interval: The run_interval column sets how often a materialized view should be refreshed, ensuring timely updates.
Monitoring Metrics: Columns like last_run and last_run_time track the last refresh time and duration, useful for alerting if metrics are not refreshed as expected.
Custom Table Refresh: The metric_tables configuration table allows defining custom SQL statements for refreshing data, enabling versatile data collection methods.

Out of the box metrics

The pgMonitor comes with many metrics already built into it. Some examples are below and users are able to add their own views and materialized views and to meet their own business needs.

pgMonitor Views:

Recovery and uptime
Postgres version
Monitor status of exhausted transaction ids
Status of successful WAL archiving
List of pending settings requiring a restart
Replication lag
Connection statistics
Locks
Query times

pgMonitor Materialized views

Table information
Table size
Database size
pgBackrest monitoring
Background worker statistics
Database statistics
Transaction ID exhaustion/wraparound
Checksum settings

You can also see a list of some of the metrics below from the pgmonitor_ext.metric_views table.

SELECT view_schema, view_name, materialized_view, scope FROM pgmonitor_ext.metric_views ;

  view_schema  |          view_name           | materialized_view |  scope
---------------+------------------------------+-------------------+----------
 pgmonitor_ext | ccp_transaction_wraparound   | f                 | global
 pgmonitor_ext | ccp_archive_command_status   | f                 | global
 pgmonitor_ext | ccp_postmaster_uptime        | f                 | global
 pgmonitor_ext | ccp_replication_lag          | f                 | global
 pgmonitor_ext | ccp_connection_stats         | f                 | global
 pgmonitor_ext | ccp_replication_lag_size     | f                 | global
 pgmonitor_ext | ccp_stat_user_tables         | t                 | database
 pgmonitor_ext | ccp_table_size               | t                 | database
 [...]

Another example is an included function pg_stat_statements_func() that can be used instead of querying pg_stat_statements directly. This provides consistent compatibility across all supported versions of PostgreSQL without having to worry about catalog differences between the major versions. Crunchy Data plans on providing version compatibility functions like this within the extension should the need ever arise in the future for any other metric.

In addition to the included metrics, users are free to add their own metrics for the pgMonitor extension to manage as well. Simply add the name of the materialized view to the table with the additional configuration options as needed.

Conclusion

The pgMonitor extension will advance the state of the art for PostgreSQL monitoring, by offering performance improvements and simplifying the management of metrics across different PostgreSQL versions. By addressing these challenges, the pgMonitor extension ensures reliable, efficient, and scalable data monitoring. It also will enable easier management of custom monitoring queries, allowing you to easily customize your monitoring stack to capture and track the data that is important to you.

We invite you to try the pgMonitor extension and experience the benefits firsthand. For more details and installation instructions, visit our pgMonitor GitHub repository. If you have questions or are interested in Crunchy Data's integrated solutions that leverage the pgMonitor extension, contact us at info@crunchydata.com

Latest Articles

Announcing an Open Source Monitoring Extension for Postgres with pgMonitor