Skip to main content
Version: Cloud

Monitor Postgres DB on Instance

Overview

PostgreSQL on instances is monitored using sfAgent configured with Postgres plugin.

Get Started

To get started with PostgreSQL integration, create a user and grant permissions to collect data from your PostgreSQL server.

Create New User

Create a read-only user with the following command:

create user <username> with password '<password>';
note

Use the username and password created in this section while setting access permission and configuration.

Set Access Permission

For version 10.0 or greater, use the below code to set access permission.

grant SELECT ON pg_stat_database to <username>; 
grant pg_monitor to <username>;

For older versions, use the below code to set access permission.

grant SELECT ON pg_stat_database to <username>;
note

By default, these permissions are granted to the root user

Prerequisite to Collect Total Query Duration

  1. Execute the following command to check pg_stat_statement is available.

    SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements' and  installed_version is not null;
  2. If the table is empty, using the following command create the extension.

    CREATE EXTENSION pg_stat_statements;
    alter system set shared_preload_libraries='pg_stat_statements';

note

Once the extension is added restart the Postgres service using the following command: systemctl restart postgresql.

Configuration

Refer to sfAgent section for steps to install and automatically generate plugin configurations. User can also manually add the configuration shown below to config.yaml under /opt/sfagent/ directory.

key: <profile_key> 
tags:
Name: <name>
appName: <app_name>
projectName: <project_name>
metrics:
plugins:
- name: postgres
enabled: true
interval: 60
config:
documentsTypes:
- databaseDetails
- indexDetails
- queryDetails
- serverDetails
- tableDetails
host: 127.0.0.1
password: <password>
port: 5432
user: <username>
logging:
plugins:
- name: postgres-general
enabled: true
config:
log_level:
- error
- warning
- info
- log
log_path: /var/log/postgresql/postgresql-10-main.log
- name: postgres-slowquery
enabled: true
config:
log_path: /var/log/postgresql/postgresql-10-main.log

Enable Logs

Enable PostgreSQL general logs

It is required to configure PostgreSQL.conf file to enable PostgreSQL logs. To locate the PostgreSQL.conf, execute the following command:

postgres=# show config_file; 
config_file
----------------------------------
/data/pgsql/data/postgresql.conf
(1 row)

Update the Postgresql.conf file with the variables shown below:

log_min_messages = warning  # set level as appropriate 
log_line_prefix = '< %m > '

Example:

once the logs are enabled, PostgreSQL general logs will be generated in the below format.

< 2023-05-04 11:52:10.470 IST > LOG:  duration: 1006.577 ms  statement: select pg_sleep(1);

Enable PostgreSQL Slow Query logs

To enable PostgreSQL slow query logs, update the PostgreSQL.conf file with the variables shown below:

log_min_duration_statement = 200

Enable Total Query Duration

To enable PostgreSQL total query duration, update the PostgreSQL.conf file with the variables shown below:

pg_stat_statements.track = all
track_activities = on
track_io_timing = on
track_functions = all

View Database Metrics and Logs

  1. Go to the Application tab in SnappyFlow and navigate to your Project > Application > Dashboard.
  2. In the dashboard window, click the tab menu ... icon on the Metric section.
  3. Select the Import from template option.
  4. In the Import to Metrics Panes window, select Filter By: Standard, Template Name: PostgreSQL.
  5. Click the Save button.
  6. Database metrics are displayed in the Metrics section of the dashboard.
  1. To view general logs, navigate to Log management > Primary storage.

  2. In the Primary Storage window, set the log filter to a general log as per the requirement.

  1. To view slow query logs, navigate to Metrics > Slow Queries.

  2. To access the unprocessed data gathered from the plugins, navigate to the Browse data section and select the following data:

  • Index: Metrics
  • Instance: Select an instance
  • Plugin: postgres
  • Document type: serverDetails, databaseDetails, tableDetails, slowqueryDetails, postgres-general, postgres-slowquery, Session Details, checkpointDetails, lockDetails, vaccumDetails

Metric List

Server Details
MetricDescription
numCreatedTempFilesTotal number of temporary files created.
numTransactionsTotal number of transactions that have been committed and rolled back.
archiveFailCountNumber of failed attempts for archiving WAL files.
activeProcessesNumber of active process running in the server.
archivedCountNumber of WAL files that have been successfully archived.
numUpdateTotal number of rows updated by queries.
numRollbackTotal number of transactions that have been rolled back.
hostThe host address of the instance.
cacheHitsNumber of times disk blocks were found already in the buffer cache.
idle_in_transactionNumber of idle_in_transaction process running in the server.
numSelectTotal number of live rows fetched by select queries.
maxConnectionsThe maximum number of concurrent connections to the database server.
tempFileSizeTotal amount of data written to temporary files by queries.
idleProcessesNumber of idle process waiting in the server.
numCommitTotal number of transactions that have been committed.
cacheSizeThe effective size of the disk cache that is available to a single query.
numConnectionsTotal number of backends currently connected to this server.
indexSizeTotal size of the indices.
bufferSizeAmount of memory the database server uses for shared memory buffers.
dbSizeTotal size of the databases.
upTimeTime difference between start time and current time.
abortedConnectionNumber of aborted process in the server.
numInsertTotal number of rows inserted by queries in this database.
numDeleteTotal number of rows deleted by queries in this database.
cacheHitRatioCache Hit Percentage on the server.
numDatabasesNumber of Databases.
versionPostgreSQL version of the server.
activeSessionTotal number of active sessions.
blockedSessionTotal number of blocked sessions.
Database Details
MetricDescription
blkReadTimeTime spent writing data file blocks by backends in this database, in milliseconds.
blocksReadNumber of disk blocks read in this database.
transPerSecNumber of transaction in this database per second.
numDeadTupleEstimated number of dead rows in database.
numTransactionsNumber of transactions in this database that have been committed and rolled back.
numLiveTupleEstimated number of live rows in database.
numFetchNumber of live rows fetched by index scans in this database.
numRollbackNumber of transactions in this database that have been rolled back.
numUpdateNumber of rows updated by queries in this database.
hostThe host address of the instance.
numReturnNumber of live rows fetched by sequential scans and index entries returned by index scans in this database.
tempFileSizeTotal amount of data written to temporary files by queries in this database.
_dbNameName of the database.
numCommitNumber of transactions in this database that have been committed.
blocksHitNumber of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache).
blkWriteTimeTime spent writing data file blocks by backends in this database, in milliseconds.
numTempFileNumber of temporary files created by queries in this database.
indexSizeTotal disk space used by all indexes in the database.
dbSizeSize of the database.
numInsertNumber of rows inserted by queries in this database.
numDeleteNumber of rows deleted by queries in this database.
cacheHitRatioPercentage of cacheHit of this database.
indexHitRatioPercentage of indexHit of this table.
numTablesNumber of tables in this database.
Table Details
MetricDescription
numDeadTupleEstimated number of dead rows.
numLiveTupleEstimated number of live rows.
numUpdateNumber of rows updated.
_tableNameName of the table.
_dbNameName of the database for this table.
tableSizeTotal disk space used by the specified table.
idxBlksReadNumber of disk blocks read from all indexes on this table.
hostThe host address of the instance.
seqScanNumber of sequential scans initiated on this table.
indexScanFetchNumber of live rows fetched by index scans.
heapBlksReadNumber of disk blocks read from this table.
idxBlksHitNumber of buffer hits in all indexes on this table.
heapBlksHitNumber of buffer hits in this table.
seqScanFetchNumber of live rows fetched by sequential scans.
indexSizeTotal disk space used by indexes attached to the specified table.
numInsertNumber of rows inserted.
indexScanNumber of index scans initiated on this table.
numDeleteNumber of rows deleted.
cacheHitRatioPercentage of cacheHit of this table.
_schemaNameName of the schema of this table.
indexHitRatioPercentage of indexHit of this table.
Index Details
MetricDescription
_indexNameName of this index.
blksHitNumber of buffer hits in this index.
_tableNameName of the table for this index.
blksReadNumber of disk blocks read from this index.
numFetchNumber of live table rows fetched by simple index scans using this index.
indexScanNumber of index scans initiated on this index.
_schemaNameName of the schema of this index.
_dbNameName of the database for this index.
hostThe host address of the instance.
numReturnNumber of index entries returned by scans on this index.
Slow Query Details
MetricDescription
runtimePeriod of time for the current query is running.
userNameName of the user logged into this backend.
wait_eventTrue if backend is currently waiting, otherwise False.
_dbNameName of the database this backend is connected to.
hostThe host address of the instance.
stateCurrent overall state of this backend. Possible values are: active, idle, idle in transaction, idle in transaction (aborted), fastpath function call, disabled.
_queryNameIf state is active this field shows the currently executing query. In all other states, it shows the last query that was executed.
Session Details
MetricDescription
blockedSessionTotal number of blocked sessions.
totalqueryDurationThe total duration of active sessions in PostgreSQL database.
averageDurationThe average duration of active sessions in PostgreSQL database.
sessionperuserCount or frequency of sessions associated with each user.
sessionperDatabaseThe host address of the instance.
sessionperHostCount of sessions associated with each host.
sessionperApplicationCount of sessions associated with each application.
connectionperDatabaseCount of sessions associated with each database.
PIDPID assigned to the session.
usernameUser associated with the session.
client_addrIP address or hostname of the client machine initiating the session.
applicationName or identifier of the client application establishing the session.
backend_startTimestamp indicating when the session was established.
stateCurrent state of the session, such as "idle", "active", "idle in transaction", "idle in transaction (aborted)", etc.
stateChangeTimestamp when the state of a session transitions from one state to another.
Checkpoint Details
MetricDescription
checkpointSegmentsNumber of checkpoints performed based on the checkpoint_timeout.
checkpointBuffersAllocNumber of buffers allocated for database operations.
checkpointBuffersCleanNumber of buffers written by backends during normal database operations.
checkpointBuffersBackendFsyncNumber of times a backend had to execute its own sync call (normally the background writer handles those even when the backend does its own write).
Vacuum Details
MetricDescription
autovacuumStatusAutomatic maintenance process is enabled or not.
numOfVacuumProgressif vaccum is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming.
Lock Details
MetricDescription
DatabaseDatabase to which the lock belongs.
lockModeType of lock mode (AccessShareLock, RowShareLock,etc).
lockStatusLock is granted or not.
waitEventType of wait event.
lockTypeThe type of lock being held, such as relation, transactionid, virtualxid, object, extend, page, tuple, etc.
processIDProcess ID of the session holding the lock.
relationNameRelation (table, index, etc.) on which the lock is held.