PostgreSQL Monitoring Tools

Overview

Applications Manager's PostgreSQL Monitoring capabilities play a vital role in monitoring your PostgreSQL database servers by providing end-to-end visibility into the performance of your database server in real-time. It aids you to keep check over the health and availability of your PostgreSQL servers by monitoring various key metrics such as database utilization, response time, query performance, session details and much more.

With Applications Manager, you can ensure that the performance of your PostgreSQL environments is kept on point. In this help document, you will learn how to get started with Postgres monitoring, along with the list of parameters that are monitored by Applications Manager.

For detailed information on the performance configurations of your PostgreSQL database server and recommendations for tuning them, visit our page on PostgreSQL tuning.

Creating a new PostgreSQL performance monitor

Supported Versions : 8.1 and above.

Prerequisites for monitoring PostgreSQL metrics :Click here

Rest API to add a new PostgreSQL performance monitor :Click here

To create a PostgreSQL database server monitor, follow the steps given below:

Click the New Monitor link and select PostgreSQL.
Enter the Display Name of the database server.
Enter the IP Address or the Host Name of the PostgrSQL host.
Enter the port number (default is 5432) in which PostgreSQL is running.
Select SSL is enabled option if your Postgres server requires an encrypted connection.
Provide the user name and password of a user with sufficient permissions to access the database and its performance views.
Specify the DBName. Enter the name of a database present in the PostgreSQL instance to establish the initial connection.
Enter the polling interval time in minutes.
If you are adding a new monitor from an Central Server, select a Probe Server.
Choose the Monitor Group with which you want to associate the PostgreSQL database server to, from the combo box (optional). You can choose multiple groups to associate your monitor.
Click Add Monitor(s). This discovers PostgreSQL database server from the network and starts monitoring them.

Monitored Parameters

Availability tab displays the availability history of the PostgreSQL database servers in your network for the past 24 hours or 30 days.
Performance tab displays the health status and events for the past 24 hours or 30 days.
List view tab displays all the PostgreSQL servers that you monitor along with their current availability and health status. You can also perform bulk admin configurations from this view.

Applications Manager's PostgreSQL monitoring tool provides complete visibility into your database environments based on the metrics listed in the following tabs:

Monitor Information

Parameter	Description
Name	Denotes the name of PostgreSQL database server monitor.
Type	Denotes the type you are monitoring.
Health	Denotes the health (Clear, Warning, Critical) status of the PostgreSQL server.
Last Polled at	Specifies the time at which the last poll was performed.
Next Poll at	Specifies the time at which the next poll is scheduled.
Availability	Shows the current status of the server - available or not available.

Overview

Parameter	Description
Connection Statistics:
Active Connections	Number of currently active connections to the database.
Total Users	The total number of users active at the time of data collection.
Lock Statistics:
Locks Held	Number of locks held by the indicated session.
Locks Wait	Number of locks waiting in the database.
Buffer Statistics:
Buffer Hits/min	Total buffer hits (i.e., block read requests avoided by finding the block already in buffer cache) per minute.
Block Reads/Min	Total disk blocks read per minute.
Cache Hit Ratio	The current ratio of buffer cache hits to total requests.
Disk Usage Details:
Disk Usage	Size of the on-disk representation of all tables in the database in MB.
Index usage	Size of the on-disk representation of all indexes in the database in MB.
Index Scan Details:
Index scans/min	Total number of index scans initiated per minute.
Index Reads/min	Total number of index entries returned by index scans per minute.
Index Fetches/min	Total number of live table rows fetched by simple index scans per minute.
Query Statistics:
Row inserts/min	Total number of rows inserted per minute.
Row Updates/min	Total number of rows updated per minute.
Row Deletes/min	Total number of rows deleted per minute.
Transaction Details:
Total Commits	Total number of transactions committed.
Total Rollbacks	Total number of transactions rolled back.
Commits/Min	Total number of transactions committed per minute.
Rollbacks/Min	Total number of transactions rolled back per minute.
Table Level Scan Details:
Sequential Scans/min	Total number of sequential scans per minute.
Table Index Scans/min	Total number of index scans per minute.
Sequential Scan Rows Read/min	Total number of rows returned by sequential scans per minute.
Table Index Scan Rows Read/min	Total numbers of rows returned by index scans per minute.
Primary Database Object Statistics:
Total Tables	Total number of tables in the database server.
Total Triggers	Total number of triggers in the database server.
Total Procedures	Total number of procedures in the database server.
Size of the Largest Table	Size of the largest table in the database server.
Largest Table(s)	Largest table in the database server.

Performance

Parameter	Description
Top Queries by CPU:
Database Name	The database name under which the query has been executed.
Total CPU Time	The cumulative total amount of CPU time that has been spent in running the query (in millisecond).
Average CPU Time	The average CPU time taken to execute the query (in millisecond).
Max CPU Time	The maximum amount of time that was taken by the CPU to run the query (in millisecond). (Available from Postgres 9.4)
Query	The SQL query which consumed the maximum CPU time.
Long Running Query Details:
Process ID	The identifier for the process caused by the query.
Runtime	Amount of time for which the query has been running (in minutes).
User Name	Name of the user executing the query.
Query	The SQL query that was running.
Database Name	The database name under which the query has been running.
Top 50 Table Row Details:
Table Name	Name of the table.
No. of Rows	Number of rows available in the table.
Table Size	Size of the table (in MB).
Used Size(MB)	Size of the used space(in MB).
Free Size(MB)	Size of the free space (in MB).
Used Size(%)	Size of the used space in percentage.
Free Size(%)	Size of the free space in percentage.
Top 10 Table By Dead Tuples Percentage
Schema Name	The name of the schema where the table resides.
Table Name	The name of the table.
Data Length(MB)	The total size of the table's data (in MB).
Index Length(MB)	The size of the table's indexes (in MB).
Live Tuples	Number of live tuples (rows) in the table.
Dead Tuples	Number of dead tuples (rows that have been marked for deletion but not yet vacuumed).
Dead Tuple Percentage(%)	Percentage of Dead Tuples.
Days Since Last Vacuum	Days passed since the last VACUUM operation performed on the table.
Last Vacuum	Timestamp of the last VACUUM operation performed on the table.
Days Since Last Auto Vacuum	Days passed since the last automatic VACUUM operation performed on the table.
Last Auto Vacuum	Timestamp of the last automatic VACUUM operation performed on the table.

Note: If the threshold is breached for Top 10 queries by CPU and Long-Running Query Detail attributes, the details about the threshold breach will be attached in alert RCA message and as an HTML attachment in Email notification. To apply these feature, follow the given steps:

Go to Performance Polling -> Optimize Data Collection.
Choose PostgreSQL monitor type and Performance metric.
Set the time interval in Configure Long Running Query time interval in minutes tab.
Enable Attach query performance details in alert mail option if you wish to attach the query performance details as HTML file to the alert notification mails raised for metrics in 'Performance' tab.

Database

Parameter	Description
Database Details:
Database Name	Name of the database instance.
DB Size	Size of the database instance (in MB).
Connections	Number of connections for the database instance.
TableSpace	Name of the tablespace.
TableSpace Details:
Name	Name of the tablespace.
Owner	Owner who created the tablespace.
Location	Location where the tablespace is created.
Size	Current size of the tablespace (in MB).

Session

Parameter	Description
Session Details:
Process ID	Displays the process ID of the session running in the Postgres server.
Client Address	IP address of the client connected to the server.
Application Name	The name of the application that has established the session. (Available from Postgres 9.0)
Database Name	Displays the name of the database currently being used by the process.
User	The login name of the user executing the process.
State	The state of the process in the Postgres server. (Available from Postgres 9.2)
Blocked	Indicates whether the process is blocked or is currently waiting on lock.
Wait Event	Displays the Wait Event Name and Wait Event Type that is currently waiting. (Available from Postgres 9.6)
Query	The SQL query that was last executed in the process.
CPU Time	The cumulative CPU time taken for the process running in the Postgres server (in millisecond).
USER PROFILE DETAILS
User ID	Unique OID of the user.
User Name	Username of the user.
Role Name	Privilege assigned to the user.
Super User	User has Super user privilege or not. Possible values: True/ False.
CreateDB Allowed	User has the privilege to create the DB. Possible values: True/ False.
Replication User	User has is Replication User previlege or not. Possible values: True/ False.
Expired Days	Number of days until the password expiry.
Expired Time	Expire time of the Password

Lock

Parameter	Description
Locked Table Details
Process ID	The unique ID of the process.
Table Name	Name of the table.
Lock Type	The type of lock held. Possible Values: relation/ tuple/ page/ extend/ access share/ row share/ row exclusive etc..
Page	Page number within the table's data file.
Virtual Transaction	Represents the identifier for the virtual transaction holding the lock.
Mode	Lock mode indicates the specific type and level of lock held.
Granted Access	Boolean value (true or false) indicating whether the lock has been granted (true) or is waiting (false).
Blocked Session Details
Process ID	Process ID (PID) of the session that is currently blocked.
User Name	Username of the session that is blocked (blocked_pid).
Blocked Session by Process ID	Array of PIDs of sessions that are blocking the session identified by PID.
Blocked Query	Current query being executed by the session that is blocked.

Replication

Parameter	Available Versions	Description
Replication Details:
WAL files count	>=9.2	Number of WAL files present in WAL directory.
Current WAL Location	>=9.2	Gives the last inserted location.
WAL level	>=9.2	wal_level determines how much information is written to the WAL. Default value = minimal (version < 10) minimal → writes only the information needed to recover from a crash or immediate shutdown. Default value = replica (version > 10) replica → writes enough data to support WAL archiving and replication, including running read-only queries on a standby server. Other states: hot_standby → minimal state/replica state info + adds information required to run read-only queries on a standby server.
WAL sync method	>=9.2	Method used for forcing WAL updates out to disk. If fsync is off, then this setting is irrelevant, since WAL file updates will not be forced out at all. Possible values are: open_datasync (write WAL files with open() option O_DSYNC), fdatasync (call fdatasync () at each commit), fsync (call fsync () at each commit), fsync_writethrough (call fsync () at each commit, forcing write-through of any disk write cache)
checkpoints_timed	>=9.2	Number of scheduled checkpoints that have been performed.
checkpoints_req	>=9.2	Number of requested checkpoints that have been performed.
buffers_checkpoint	>=9.2	Number of buffers written during checkpoints.
buffers_clean	>=9.2	Number of buffers written by the background writer.
buffers_backend	>=9.2	Number of buffers written directly from backend.
Client Address	>=9.2	Address of client/standby server address.
Client Port	>=9.2	TCP port number that the client is using for communication with this WAL sender.
PID	>=9.2	Process ID of a WAL sender process.
application name	>=9.2	Name of the application that is connected to this WAL sender.
Sender State	>=9.2	Current WAL sender state. Possible values are: startup: This WAL sender is starting up. catchup: This WAL sender's connected standby is catching up with the primary. streaming: This WAL sender is streaming changes after its connected standby server has caught up with the primary. backup: This WAL sender is sending a backup. stopping: This WAL sender is stopping.
Sync State	>=9.2	Synchronous state of this standby server. Possible values are: async: This standby server is asynchronous. potential: This standby server is now asynchronous, but can potentially become synchronous if one of the current synchronous ones fails. sync: This standby server is synchronous. quorum: This standby server is considered as a candidate for quorum standbys.
Sending lag	>=9.2	Lag in sending the transactions to standby
Last replay location	>=9.2	Gives the last replayed location
Write lag	>=9.2	Lag in writing transaction to disk in standby.
Flush lag	>=9.2	Lag in flushing transactions to disk in standby.
Replay lag	>=9.2	Lag in replaying the transactions on standby
Total lag	>=9.2	Total lag sending from master to replaying transaction on standby.
Master Host	>=9.6	Host details of master node.
Master Port	>=9.6	Port of master node.
Master User	>=9.6	User details of the master node.
Replication Delay	>=9.1	Time lag between the last received transaction and the last replayed transaction.
Replication Slots:
slot_name	>=9.6	A unique, cluster-wide identifier for the replication slot.
slot_type	>=9.6	The slot type - physical or logical.
active	>=9.6	True if this slot is currently actively being used.
restart_lsn	>=9.6	The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints.
confirmed_flush_lsn	>=9.6	The address (LSN) up to which the logical slot's consumer has confirmed receiving data. Data older than this is not available anymore. NULL for physical slots.

Configuration

Parameter	Description
Version	Version of the PosgreSQL Server.
Config File	Location of Postgres server configuration file.
Data Directory	Location of data directory used by the Postgres server.
External PID File	Location of external PID file.
hba File	Location of hba file.
ident File	Location of ident file.

PostgreSQL Monitoring Tools

Overview

Creating a new PostgreSQL performance monitor

Monitored Parameters

Monitor Information

Overview

Performance

Database

Session

Lock

Replication

Configuration

How can we improve this page?

Do you need assistance with this topic?

Loved by customers all over the world

"Standout Tool With Extensive Monitoring Capabilities"

"I like Applications Manager because it helps us to detect issues present in our servers and SQL databases."

Carlos Rivero

Trusted by over 6000+ businesses globally