PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Prerequisites

  1. Create a read-only user with proper access to your PostgreSQL server and start psql on your PostgreSQL database.
    • For PostgreSQL version 10 and above, run the following:
    create user '<Username>' with password '<PASSWORD>';
    grant pg_monitor to <Username>;
    
    • For older versions of PostgreSQL, run the following:
    create user '<Username>' with password '<PASSWORD>';
    grant SELECT ON pg_stat_database to '<Username>';
    
  2. To get the metrics of WAL files, grant the following permissions:
REVOKE ALL ON FUNCTION pg_ls_dir(dirname text ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_ls_dir(dirname text ) TO '<Username>';
  1. To get the metric of Function:
vi /etc/postgresql/12/main/postgresql.conf
track_functions = all

Do not restart the server for this setting.

  1. For Virtual Machines, install the Linux Agent.

Configuring the credentials

Configure the credentials in the directory /opt/opsramp/agent/conf/app.d/creds.yaml

postgresql:
- name: postgresql
  user: <username>
  pwd: <Password>
  encoding-type: plain
  labels:
    key1: val1
    key2: val2

Configuring the application

Virtual machine

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-detection.yaml

- name: postgresql
  instance-checks:
    service-check:
      - postgresql
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgreSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and enter a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Docker environment

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-container-detection.yaml

- name: postgresql
  container-checks:
    image-check:
      - postgres
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgreSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and enter a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Kubernetes environment

Configure the application in config.yaml

- name: postgresql
  container-checks:
    image-check:
      - postgres
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgreSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and enter a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Validate

Go to Resources under the Infrastructure tab to check if your resources are onboarded and the metrics are collected.

Metrics

OpsRamp MetricMetric Display NameUnit
postgresql_autovac_freeze

Provides the percentage of current transactions to the maximum freeze number
Autovac Freezepercent
postgresql_ping

Provides the Ping response time of PostgreSQL database
Pingmicroseconds
postgresql_bloat

Checks the amount of bloat in tables and indexes
BloatBloat
postgresql_conn_idle_tnx

Checks the number of connections 'idle in transaction' state
Connections Idle Tnxconnections
postgresql_conn_idle

Checks number of connections idle in given state
Connections Idleconnections
postgresql_conn_total

Checks total number of connections in given state
Connections Totalconnections
postgresql_conn_running

Checks number of connections running in given state
Connections Runningconnections
postgresql_conn_waiting

Checks number of connections waiting in given state
Connections Waitingconnections
postgresql_tnxage_idle_tnx

Checks the number and duration of 'idle in transaction' queries on one or more databases
Tnxage IdleTnxIdle Tnx Age
postgresql_tnxage_running

Checks the number and duration of 'running transaction' queries on one or more databases
Tnxage RunningIdle Tnx Running
postgresql_wal

Checks how many WAL files exist in the pg_wal directory
WalFiles
postgresql_locks

Number of locks active for this database
LocksLocks
postgresql_locks_granted

Number of granted locks active for this database
Locks GrantedLocks
postgresql_locks_not_granted

Number of not granted locks active for this database
Locks Not GrantedLocks
postgresql_commits

Number of transactions in this database that have been committed
CommitsTransactions / second
postgresql_rollbacks

Number of transactions in this database that have been rolled back
RollbacksTransactions / second
postgresql_disk_read

Number of disk blocks read in this database
Disk ReadsBlocks / second
postgresql_buffer_hit

Number of times disk blocks were found in the buffer cache, preventing the need to read from the database
Buffer HitsHits / second
postgresql_rows_returned

Number of rows returned by queries in this database
Rows ReturnedRows / second
postgresql_rows_fetched

Number of rows fetched by queries in this database
Rows FetchedRows / second
postgresql_rows_inserted

Number of rows inserted by queries in this database
Rows InsertedRows / second
postgresql_rows_updated

Number of rows updated by queries in this database
Rows UpdatedRows / second
postgresql_rows_deleted

Number of rows deleted by queries in this database
Rows DeletedRows / second
postgresql_database_size

Disk space used by this database
Database SizeMegaBytes
postgresql_buffer_clear

Checks number of buffer which have clear page in the shared cache
Buffer ClearClear Buffers
postgresql_buffer_dirty

Checks number of buffer which have dirty page in the shared cache
Buffer DirtyDirty Buffers
postgresql_buffer_used

Checks number of buffer used in the shared cache
Buffer UsedUsed Buffers
postgresql_buffer_total

Checks total number of buffer in the shared cache
Buffer TotalTotal Buffers
postgresql_temp_files

Number of temporary files created by queries in this database.
Temp FilesFiles / second
postgresql_connections

Number of active connections to this database
connectionsconnections
postgresql_before_xid_wraparound

Number of transactions that can occur until a transaction wraparound
Before Xid WraparoundTransactions
postgresql_deadlocks

Number of deadlocks detected in this database
DeadlocksDeadlocks / second
postgresql_temp_bytes

Amount of data written to temporary files by queries in this database
Temp Bytesbytes / second
postgresql_bgwriter_checkpoints_timed

Number of scheduled checkpoints that have been performed
Bgwriter Checkpoints TimedCheckpoints
postgresql_bgwriter_checkpoints_requested

Number of requested checkpoints that have been performed
Bgwriter Checkpoints RequestedCheckpoints
postgresql_bgwriter_buffers_checkpoint

Number of buffers written during checkpoints
Bgwriter Buffers CheckpointBuffers
postgresql_bgwriter_buffers_clean

Number of buffers written by the background writer
Bgwriter Buffers CleanBuffers
postgresql_bgwriter_maxwritten_clean

Number of times the background writer stopped a cleaning scan because it had written too many buffers
Bgwriter Maxwritten CleanCount
postgresql_bgwriter_buffers_backend

Number of buffers written directly by a backend
Bgwriter Buffers BackendBuffers
postgresql_bgwriter_buffers_alloc

Number of buffers allocated
Bgwriter Buffers AllocBuffers
postgresql_bgwriter_buffers_backend_fsync

Number of times a backend had to execute its own fsync call instead of the background writer.
Bgwriter Buffers Backend FsyncCount
postgresql_bgwriter_write_time

Total amount of checkpoint processing time spent writing files to disk
Bgwriter Write Timemilliseconds
postgresql_bgwriter_sync_time

Total amount of checkpoint processing time spent synchronizing files to disk
Bgwriter Sync Timemilliseconds
postgresql_seq_scans

Number of sequential scans initiated on this table
Seq ScansScans / second
postgresql_seq_rows_read

Number of live rows fetched by sequential scans
Seq Rows ReadRows / second
postgresql_index_scans

Number of index scans initiated on this table
Index ScansScans / second
postgresql_index_rel_rows_fetched

Number of live rows fetched by index scans
Index Rel Rows FetchedRows / second
postgresql_index_rows_fetched

Number of live rows fetched by index scans
Index Rows FetchedRows / second
postgresql_rows_hot_updated

Number of rows HOT updated (that is, with no separate index update required)
Rows Hot UpdatedRow / second
postgresql_live_rows

Estimated number of live rows
Live RowsRows
postgresql_dead_rows

Estimated number of dead rows
Dead RowsRows
postgresql_rows_inserted_table_stat

Number of rows inserted
Rows Inserted Table StatRows / second
postgresql_rows_updated_table_stat

Number of rows updated (includes HOT updated rows)
Rows Updated Table StatRows / second
postgresql_rows_deleted_table_stat

Number of rows deleted
Rows Deleted Table StatRows / second
postgresql_heap_blocks_read

Number of disk blocks read from this table
Heap Blocks ReadBlocks / second
postgresql_heap_blocks_hit

Number of buffer hits in this table
Heap Blocks HitHits / second
postgresql_index_blocks_read

Number of disk blocks read from all indexes on this table
Index Blocks ReadBlocks / second
postgresql_index_blocks_hit

Number of buffer hits in all indexes on this table
Index Blocks HitHits / second
postgresql_toast_blocks_read

Number of disk blocks read from this table's TOAST table (if any)
Toast Blocks ReadBlocks / second
postgresql_toast_blocks_hit

Number of buffer hits in this table's TOAST table (if any)
Toast Blocks HitHits / second
postgresql_toast_index_blocks_read

Number of disk blocks read from this table's TOAST table indexes (if any)
Toast Index Blocks ReadBlocks / second
postgresql_toast_index_blocks_hit

Number of buffer hits in this table's TOAST table indexes (if any)
Toast Index Blocks HitBlocks / second
postgresql_table_size

Total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
Table Sizebytes
postgresql_index_size

Total disk space used by indexes attached to the specified table
Index Sizebytes
postgresql_total_size

Total disk space used by the table, including indexes and TOAST data
Total Sizebytes
postgresql_index_stat_index_scans

Number of index scans initiated on this index
Index Stat Index ScansScans / second
postgresql_index_rows_read

Number of index entries returned by scans on this index
Index Rows ReadRows / second
postgresql_index_stat_index_rows_fetched

Number of live table rows fetched by simple index scans using this index
Index Stat Index Rows FetchedRows / second
postgresql_archiver_archived_count

Number of WAL files that have been successfully archived
Archiver Archived CountFiles
postgresql_archiver_failed_count

Number of failed attempts for archiving WAL files
Archiver Failed CountFailed Attempts
postgresql_table_count

Number of user tables in this database
Table CountTables
postgresql_function_calls

Number of times this function has been called
Function CallsCalls / second
postgresql_function_total_time

Total time spent in this function and all other functions called by it
Function Total TimeMilliSecond / second
postgresql_function_self_time

Total time spent in this function itself, not including other functions called by it
Function Self TimeMilliSecond / second
postgresql_replication_delay

Current replication delay. Only available with postgresql 9.1 and newer
Replication Delayseconds
postgresql_replication_delay_bytes

Current replication delay. Only available with postgresql 9.2 and newer
Replication Delay Bytesbytes
postgresql_percent_usage_connections

Number of connections to this database as a fraction of the maximum number of allowed connections
Percent Usage ConnectionsFraction
postgresql_max_connections

Maximum number of client connections allowed to this database
Max Connectionsconnections
postgresql_transactions_open

Number of open transactions in this database
Transactions OpenTransactions
postgresql_transactions_idle_in_transaction

Number of 'idle in transaction' transactions in this database
Transactions Idle In TransactionTransactions
postgresql_active_queries

Number of active queries in this database
Active QueriesQueries
postgresql_waiting_queries

Number of waiting queries in this database
Waiting QueriesQueries