Skip to content

PostgreSQL

ZenPacks.zenoss.PostgreSQL

This ZenPack makes it possible to monitor the performance of a PostgreSQL database server, the individual databases, and the tables within those databases.

The features added by this ZenPack can be summarized as follows. They are each detailed further below.

  • Discovery of PostgreSQL components.
  • Monitoring of PostgreSQL metrics.

Applications Monitored: PostgreSQL (8.1, 8.4, 9.x, 10.x - 18.x) Postgres-BDR 9.4

Open Source

This ZenPack is developed and supported by Zenoss Inc. Contact Zenoss to request more information regarding this or any other ZenPacks. Click here to view all available Zenoss Open Source ZenPacks.

License

GNU General Public License, Version 2, or later

Git Sources (For Cloning)

Link

Support

This is an Open Source ZenPack developed by Zenoss, Inc. Enterprise support for this ZenPack is available to commercial customers with an active subscription.

Releases

Version 1.1.0 - Download

  • Released on 2026/6/22
  • Compatible with Virtana Service Observability (formerly Zenoss Cloud), Virtana Resource Manager 6.9.0, Service Impact 5.7.2

Version 1.1.0 - Download

  • Released on 2026/1/6
  • Compatible with Zenoss 6.x, Zenoss Cloud and Service Impact 5.7.1

Version 1.0.13 - Download

  • Released on 2023/9/12
  • Compatible with Zenoss 6.x, Zenoss Cloud and Service Impact 5.6.1

Version 1.0.12 - Download

  • Released on 2020/1/14
  • Compatible with Zenoss 6.4.1 - 6.5.0, Zenoss Cloud and Service Impact 5.5.3

Discovery

The following types of components will be automatically discovered. The attributes and collections will be updated on Zenoss' normal remodeling interval which defaults to every 12 hours.

Databases:

  • Attributes:

    • Name
    • OID
    • Size
    • Table Count

  • Collections:

    • Tables

Tables:

  • Attributes: Name
    • OID
    • Schema
    • Size
    • Size
    • Database

Note: Tables are uniquely identified by both schema and name. Tables with the same name in different schemas (e.g., public.users and audit.users) are modeled as separate components. The table title displays as schema.tablename for clarity.

Monitoring

The following metrics will be collected every 5 minutes by default.

  • Server

    • Metrics: Summaries of all databases and tables.

  • Databases

    • Metrics
      • Size
      • Backends
      • Summaries of all tables
    • Latency Metrics
      • Connection
      • SELECT 1
    • Connection Metrics
      • Total
      • Active
      • Idle
    • Duration Metrics
      • Active Transactions (min/avg/max)
      • Idle Transactions (min/avg/max)
      • Queries (min/avg/max)
    • Efficiency Metrics
      • Transaction Rollback Percentage
      • Tuple Fetch Percentage
    • Transaction Rate Metrics
      • Commits/sec
      • Rollbacks/sec
    • Tuple Rate Metrics
      • Returned/sec
      • Fetched/sec
      • Inserted/sec
      • Updated/sec, Deleted/sec
    • Lock Metrics
      • Total
      • Granted
      • Waiting
      • Exclusive
      • AccessExclusive

  • Tables

    • Scan Rate Metrics
      • Sequential/sec
      • Indexed/sec
    • Tuple Rate Metrics
      • Sequentially Read/sec
      • Index Fetched/sec
      • Inserted/sec
      • Updated/sec
      • Hot Updated/sec
      • Deleted/sec
    • Tuple Metrics
      • Live
      • Dead

Prerequisites

The PostgreSQL server must be configured to allow connections from the Zenoss collector IP address via pg_hba.conf. Without this configuration, modeling will fail with "no pg_hba.conf entry" errors.

Troubleshooting

Connection Denied by pg_hba.conf

If you see the following error in Zenoss logs:

ERROR zen.PostgreSQL.utils: Async getDatabases failed due to fatal config error: 
FATAL: no pg_hba.conf entry for host "192.168.1.100", user "zenoss", database "postgres", no encryption. 
Skipping sync fallback.

Cause: PostgreSQL's host-based authentication (pg_hba.conf) is not configured to allow connections from the Zenoss collector server's IP address.

Resolution:

  1. Locate pg_hba.conf on the PostgreSQL server. It's typically in the data directory, for example /var/lib/pgsql/data/pg_hba.conf or /etc/postgresql/<version>/main/pg_hba.conf.

  2. Add an entry to allow the Zenoss collector IP address. To allow a single host:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             zenoss          192.168.1.100/32        md5

Or to allow a subnet:

host    all             zenoss          192.168.1.0/24          md5
  1. Reload the PostgreSQL configuration:
sudo systemctl reload postgresql
# or
sudo -u postgres pg_ctl reload

Note

The "Skipping sync fallback" message in the error indicates that Zenoss won't retry the connection using synchronous methods. This is a configuration error that requires administrator intervention, not a transient network issue.

SSL/Encryption Errors

If the error mentions "no encryption", choose one of the following options:

  • Set zPostgreSQLUseSSL to True in Zenoss and configure SSL on PostgreSQL.
  • Add hostnossl entries in pg_hba.conf to allow non-SSL connections from Zenoss.

Usage

Once the PostgreSQL ZenPack is installed you will have the following new configuration properties which should be set either for device classes or individual devices.

Configuration Properties

zPostgreSQLPort
Port where PostgreSQL is listening. Default: 5432
zPostgreSQLUseSSL
Whether to use SSL or not. Default: False
zPostgreSQLUsername
Must be a superuser. Default: postgres
zPostgreSQLPassword
Password for user. No default.
zPostgreSQLDefaultDB
Default database. Default: postgres
zPostgreSQLTableRegex
List of regular expressions to exclude tables from modeling and monitoring. Tables matching any pattern will NOT be monitored. Default: empty
PostgreSQLDatabaseRegex
List of regular expressions to exclude databases from modeling and monitoring. Databases matching any pattern will NOT be monitored. Default: empty

In addition to setting these properties you must add the zenoss.PostgreSQL modeler plugin to a device class or individual device. This modeler plugin will discover all databases and tables using the connectivity information provided through the above settings. Each database and table will automatically be monitored.

Table Filtering

Use zPostgreSQLTableRegex to exclude specific tables from monitoring across all databases. This is useful for: - Excluding temporary or staging tables - Filtering out tables with specific naming patterns - Reducing monitoring overhead by excluding large tables that don't need monitoring

Example configurations for zPostgreSQLTableRegex:

# Exclude tables starting with 'tmp_'
^tmp_.*

# Exclude tables ending with '_backup'
.*_backup$

# Exclude specific table names
^audit_log$
^session_data$

# Exclude tables in specific schema (schema.table format)
^temp\..*

Database Filtering (RDS/Aurora Use Case)

Use zPostgreSQLDatabaseRegex to exclude specific databases from monitoring. When monitoring PostgreSQL on AWS RDS or Aurora, system databases like rdsadmin should be excluded as they are not accessible to users and will cause connection errors.

Example configurations for zPostgreSQLDatabaseRegex:

# Exclude RDS admin database
^rdsadmin$

# Exclude template databases
^template[0-9]+$

# Exclude multiple databases (one pattern per line)
^rdsadmin$
^template[0-9]+$
^test_.*

Filtering Behavior

How filtering works: - Patterns are applied during both modeling (discovery) and data collection - Matching items are completely excluded from Zenoss - Uses Python regex syntax (case-sensitive by default) - Each line is a separate regex pattern - Empty lines are ignored

In addition to setting these properties you must add the ''zenoss.PostgreSQL'' modeler plugin to a device class or individual device. This modeler plugin will discover all databases and tables using the connectivity information provided through the above settings. Each database and table will automatically be monitored.

PostgreSQL Server Impact

Zenoss will run the following queries every five (5) minutes. These queries are intended to be lightweight so as to not adversely affect the server's performance.

-- Database statistics - Run once.
SELECT d.datname,
       pg_database_size(s.datid) AS size,
       numbackends,
       xact_commit, xact_rollback,
       blks_read, blks_hit,
       tup_returned, tup_fetched, tup_inserted,
       tup_updated, tup_deleted
  FROM pg_database AS d
  JOIN pg_stat_database AS s ON s.datname = d.datname
 WHERE NOT datistemplate AND datallowconn

-- Connection statistics - Run once.
SELECT datname, xact_start, query_start, backend_start,
       now() AS now
  FROM pg_stat_activity

-- Lock statistics - Run once.
SELECT d.datname, l.mode, l.granted
  FROM pg_database AS d
  LEFT JOIN pg_locks AS l ON l.database = d.oid
 WHERE NOT d.datistemplate AND d.datallowconn

-- Table statistics - Run once per database.
SELECT relname,
       pg_relation_size(relid),
       pg_total_relation_size(relid),
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del,
       n_tup_hot_upd, n_live_tup, n_dead_tup,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
  FROM pg_stat_user_tables

The following queries will be run whenever the PostgreSQL server device is remodeled. This occur once every 12 hours.

-- Database list - Run once.
SELECT d.datname, s.datid, pg_database_size(s.datid) AS size
  FROM pg_database AS d
  JOIN pg_stat_database AS s ON s.datname = d.datname
 WHERE NOT datistemplate AND datallowconn

-- Table list - Run once per database.
SELECT relname, relid, schemaname,
       pg_relation_size(relid) AS size,
       pg_total_relation_size(relid) AS total_size
  FROM pg_stat_user_tables

Limitations

Troubleshooting Modeling

If the device fails to model PostgreSQL components with the error: WARNING zen.ZenModeler: Python client creation failed you can either

  1. Clear the events and remodel the device or
  2. Set the zSnmpMonitorIgnore property to True and remodel.

Known Issues

Upgrade to 1.1.1

As a part of the table component ID migration to include schema names, historical data from legacy component IDs will not be visible on the standard graphs. Graphs for renamed tables will start fresh after upgrade.

Changes

1.1.1

  • Fixed tables with same name in different schemas being overwritten (ZPS-9263)
  • Added database filtering via zPostgreSQLDatabaseRegex property (ZPS-9253)
  • Tested with Virtana Service Observability (formerly Zenoss Cloud), Virtana Resource Manager 6.9.0, Service Impact 5.7.2

1.1.0

  • Implemented support for SCRAM-SHA-256 authentication (ZPS-9186)
  • Added message when modeling inaccessible via pg_hba.conf file (ZPS-9196)
  • Tested with Zenoss Cloud, Zenoss 6.9.0 and Service Impact 5.7.1

1.0.13

  • Add Support for PostgreSQL 11.x (ZPS-7185)
  • Make PostgreSQL table modeling optional (ZPS-8554)
  • Tested with Zenoss Cloud, Zenoss 6.7.0 and Service Impact 5.6.1

1.0.12

  • Resolved issue with error in pg8000 library on Ubuntu OS (ZPS-7424)
  • Resolved issue with falling data collecting after platform upgrade. (ZPS-6818)
  • Tested with Zenoss Cloud, Zenoss 6.4.1, Zenoss 6.5.0 and Service Impact 5.5.3

1.0.10

  • Add support for Bi-Directional Replication (ZPS-249)
  • Add support variable default database name (ZPS-2767)
  • Added zProperties:
    • zPostgreSQLDefaultDB: Sets default database for administration data
  • Added doc updates for failed modeling (ZEN-29375)
  • Tested with Zenoss Resource Manager 5.3.3 ,Zenoss Resource Manager 6.0.1, Zenoss Resource Manager 6.1.0 and Zenoss Resource Manager 4.2.5 RPS 743

1.0.9

  • Filter PIDs for lock query (ZEN-15165)
  • Guard against locks in PGSQL poller (ZPS-312)

1.0.8

  • Handle null data by skipping it (ZEN-14276)
  • Update pg8000 lib to 1.9.14 (ZEN-12752)