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 folllowing 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

Note: 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 PostgresSQL 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 provide 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 PostgresSQL 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 provide 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 PostgresSQL 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 provide 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 NameUnitDescription
postgresql_autovac_freezeAutovac FreezepercentProvides the percentage of current transactions to the maximum freeze number
postgresql_pingPingmicrosecondsProvides the Ping response time of PostgreSQL database
postgresql_bloatBloatBloatChecks the amount of bloat in tables and indexes
postgresql_conn_idle_tnxConnections Idle TnxconnectionsChecks the number of connections 'idle in transaction' state
postgresql_conn_idleConnections IdleconnectionsChecks number of connections idle in given state
postgresql_conn_totalConnections TotalconnectionsChecks total number of connections in given state
postgresql_conn_runningConnections RunningconnectionsChecks number of connections running in given state
postgresql_conn_waitingConnections WaitingconnectionsChecks number of connections waiting in given state
postgresql_tnxage_idle_tnxTnxage IdleTnxIdle Tnx AgeChecks the number and duration of 'idle in transaction' queries on one or more databases
postgresql_tnxage_runningTnxage RunningIdle Tnx RunningChecks the number and duration of 'running transaction' queries on one or more databases
postgresql_walWalFilesChecks how many WAL files exist in the pg_wal directory
postgresql_locksLocksLocksNumber of locks active for this database
postgresql_locks_grantedLocks GrantedLocksNumber of granted locks active for this database
postgresql_locks_not_grantedLocks Not GrantedLocksNumber of not granted locks active for this database
postgresql_commitsCommitsTransactions / secondNumber of transactions in this database that have been committed
postgresql_rollbacksRollbacksTransactions / secondNumber of transactions in this database that have been rolled back
postgresql_disk_readDisk ReadsBlocks / secondNumber of disk blocks read in this database
postgresql_buffer_hitBuffer HitsHits / secondNumber of times disk blocks were found in the buffer cache, preventing the need to read from the database
postgresql_rows_returnedRows ReturnedRows / secondNumber of rows returned by queries in this database
postgresql_rows_fetchedRows FetchedRows / secondNumber of rows fetched by queries in this database
postgresql_rows_insertedRows InsertedRows / secondNumber of rows inserted by queries in this database
postgresql_rows_updatedRows UpdatedRows / secondNumber of rows updated by queries in this database
postgresql_rows_deletedRows DeletedRows / secondNumber of rows deleted by queries in this database
postgresql_database_sizeDatabase SizeMegaBytesDisk space used by this database
postgresql_buffer_clearBuffer ClearClear BuffersChecks number of buffer which have clear page in the shared cache
postgresql_buffer_dirtyBuffer DirtyDirty BuffersChecks number of buffer which have dirty page in the shared cache
postgresql_buffer_usedBuffer UsedUsed BuffersChecks number of buffer used in the shared cache
postgresql_buffer_totalBuffer TotalTotal BuffersChecks total number of buffer in the shared cache
postgresql_temp_filesTemp FilesFiles / secondNumber of temporary files created by queries in this database.
postgresql_connectionsconnectionsconnectionsNumber of active connections to this database
postgresql_before_xid_wraparoundBefore Xid WraparoundTransactionsNumber of transactions that can occur until a transaction wraparound
postgresql_deadlocksDeadlocksDeadlocks / secondNumber of deadlocks detected in this database
postgresql_temp_bytesTemp Bytesbytes / secondAmount of data written to temporary files by queries in this database
postgresql_bgwriter_checkpoints_timedBgwriter Checkpoints TimedCheckpointsNumber of scheduled checkpoints that have been performed
postgresql_bgwriter_checkpoints_requestedBgwriter Checkpoints RequestedCheckpointsNumber of requested checkpoints that have been performed
postgresql_bgwriter_buffers_checkpointBgwriter Buffers CheckpointBuffersNumber of buffers written during checkpoints
postgresql_bgwriter_buffers_cleanBgwriter Buffers CleanBuffersNumber of buffers written by the background writer
postgresql_bgwriter_maxwritten_cleanBgwriter Maxwritten CleanCountNumber of times the background writer stopped a cleaning scan because it had written too many buffers
postgresql_bgwriter_buffers_backendBgwriter Buffers BackendBuffersNumber of buffers written directly by a backend
postgresql_bgwriter_buffers_allocBgwriter Buffers AllocBuffersNumber of buffers allocated
postgresql_bgwriter_buffers_backend_fsyncBgwriter Buffers Backend FsyncCountNumber of times a backend had to execute its own fsync call instead of the background writer.
postgresql_bgwriter_write_timeBgwriter Write TimemillisecondsTotal amount of checkpoint processing time spent writing files to disk
postgresql_bgwriter_sync_timeBgwriter Sync TimemillisecondsTotal amount of checkpoint processing time spent synchronizing files to disk
postgresql_seq_scansSeq ScansScans / secondNumber of sequential scans initiated on this table
postgresql_seq_rows_readSeq Rows ReadRows / secondNumber of live rows fetched by sequential scans
postgresql_index_scansIndex ScansScans / secondNumber of index scans initiated on this table
postgresql_index_rel_rows_fetchedIndex Rel Rows FetchedRows / secondNumber of live rows fetched by index scans
postgresql_index_rows_fetchedIndex Rows FetchedRows / secondNumber of live rows fetched by index scans
postgresql_rows_hot_updatedRows Hot UpdatedRow / secondNumber of rows HOT updated (that is, with no separate index update required)
postgresql_live_rowsLive RowsRowsEstimated number of live rows
postgresql_dead_rowsDead RowsRowsEstimated number of dead rows
postgresql_rows_inserted_table_statRows Inserted Table StatRows / secondNumber of rows inserted
postgresql_rows_updated_table_statRows Updated Table StatRows / secondNumber of rows updated (includes HOT updated rows)
postgresql_rows_deleted_table_statRows Deleted Table StatRows / secondNumber of rows deleted
postgresql_heap_blocks_readHeap Blocks ReadBlocks / secondNumber of disk blocks read from this table
postgresql_heap_blocks_hitHeap Blocks HitHits / secondNumber of buffer hits in this table
postgresql_index_blocks_readIndex Blocks ReadBlocks / secondNumber of disk blocks read from all indexes on this table
postgresql_index_blocks_hitIndex Blocks HitHits / secondNumber of buffer hits in all indexes on this table
postgresql_toast_blocks_readToast Blocks ReadBlocks / secondNumber of disk blocks read from this table's TOAST table (if any)
postgresql_toast_blocks_hitToast Blocks HitHits / secondNumber of buffer hits in this table's TOAST table (if any)
postgresql_toast_index_blocks_readToast Index Blocks ReadBlocks / secondNumber of disk blocks read from this table's TOAST table indexes (if any)
postgresql_toast_index_blocks_hitToast Index Blocks HitBlocks / secondNumber of buffer hits in this table's TOAST table indexes (if any)
postgresql_table_sizeTable SizebytesTotal disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
postgresql_index_sizeIndex SizebytesTotal disk space used by indexes attached to the specified table
postgresql_total_sizeTotal SizebytesTotal disk space used by the table, including indexes and TOAST data
postgresql_index_stat_index_scansIndex Stat Index ScansScans / secondNumber of index scans initiated on this index
postgresql_index_rows_readIndex Rows ReadRows / secondNumber of index entries returned by scans on this index
postgresql_index_stat_index_rows_fetchedIndex Stat Index Rows FetchedRows / secondNumber of live table rows fetched by simple index scans using this index
postgresql_archiver_archived_countArchiver Archived CountFilesNumber of WAL files that have been successfully archived
postgresql_archiver_failed_countArchiver Failed CountFailed AttemptsNumber of failed attempts for archiving WAL files
postgresql_table_countTable CountTablesNumber of user tables in this database
postgresql_function_callsFunction CallsCalls / secondNumber of times this function has been called
postgresql_function_total_timeFunction Total TimeMilliSecond / secondTotal time spent in this function and all other functions called by it
postgresql_function_self_timeFunction Self TimeMilliSecond / secondTotal time spent in this function itself, not including other functions called by it
postgresql_replication_delayReplication DelaysecondsCurrent replication delay. Only available with postgresql 9.1 and newer
postgresql_replication_delay_bytesReplication Delay BytesbytesCurrent replication delay. Only available with postgresql 9.2 and newer
postgresql_percent_usage_connectionsPercent Usage ConnectionsFractionNumber of connections to this database as a fraction of the maximum number of allowed connections
postgresql_max_connectionsMax ConnectionsconnectionsMaximum number of client connections allowed to this database
postgresql_transactions_openTransactions OpenTransactionsNumber of open transactions in this database
postgresql_transactions_idle_in_transactionTransactions Idle In TransactionTransactionsNumber of 'idle in transaction' transactions in this database
postgresql_active_queriesActive QueriesQueriesNumber of active queries in this database
postgresql_waiting_queriesWaiting QueriesQueriesNumber of waiting queries in this database