PostgreSQL protocol metrics (throughput, latency, errors) are captured out-of-the-box by collectors running with EPOCH_ANALYSIS_DEPTH=layer7.

The user can either look at pre-canned dashboards for PostgreSQL or use the Analytics Sandbox to plot charts without any additional configuration.

Note: In order to see additional infrastructure metrics the user can follow the steps listed under the Configuration section below (Optional).

Attributes

Below attributes can be used in filters and group by operations in analytics within alerts, dashboards, etc.

API Usage Name	UI Display Name	Description	Value / Example
postgresql.user	User	User name requesting the queries
postgresql.db	Database Name	Name of database accessed in queries
postgresql.application_name	Application Name	Name of the application that is connected to the PostgreSQL server
postgresql.server_version	Server Version	PgSQL server version number	9.6.1
postgresql.client_message	Client Message	Client message type indicating the state of connection full details of message flow	Startup, PasswordMessage, Query, Terminate
postgresql.server_message	Server Message	Server message type indicating the state of connection full details of server message	AuthenticationMD5Password, AuthenticationOk, ReadyForQuery, BackendKeyData, ErrorResponse, NoticeResponse, ParameterStatus
postgresql.error.severity	Server Error Severity	The severity field in error response full details of error message	ERROR, FATAL, PANIC
postgresql.error.string	Error String	Error message including No Error	database pgbench does not exist
postgresql.query	Query String	Query statements captured as strings	create table pgbench_accounts(aid int)
There are some attributes that are common across the integrations. The description to the relevant attributes for this integration can be found here: shared attributes

Protocol Datasources

Datasource	Available Aggregations	Unit	Description
postgresql.request_response.latency	sum avg min max std_dev	millisecond	The aggregated latency across all PostgreSQL request/response.
postgresql.request_response.throughput	throughput	/s	The total number of PostgreSQL request/response per second.
postgresql.request_response.count	count		The total number of PostgreSQL request/response.
postgresql.request.bytes	sum avg min max	byte	The aggregated number of bytes across all PostgreSQL requests.
postgresql.request.bytes.rate	rate	byte/s	The rate of bytes transferred across all PostgreSQL requests.
postgresql.request.count	count		The total number of PostgreSQL requests.
postgresql.request.packets	sum avg min max	packet	The aggregated number of packets across all PostgreSQL requests.
postgresql.request.packets.rate	rate	packet/s	The rate of packets transferred across all PostgreSQL requests.
postgresql.request.throughput	throughput	/s	The rate of total number of PostgreSQL requests.
postgresql.response.bytes	sum avg min max	byte	The aggregated number of bytes across all PostgreSQL responses.
postgresql.response.bytes.rate	rate	byte/s	The rate of bytes transferred across all PostgreSQL responses.
postgresql.response.count	count		The total number of PostgreSQL responses.
postgresql.response.packets	sum avg min max	packet	The aggregated number of packets across all PostgreSQL responses.
postgresql.response.packets.rate	rate	packet/s	The rate of packets transferred across all PostgreSQL responses.
postgresql.response.throughput	throughput	/s	The rate of total number of PostgreSQL responses.

---

Infrastructure Integration

Configuration:

Note: Epoch can provide stats for PostgreSQL queries via protocol parsing without any configuration. The following steps are required to poll for stats reported by the PostgreSQL server itself.

1. Create a read-only Epoch user with proper access to your PostgreSQL Server. Start psql on your PostgreSQL database and run:

   create user epoch with password '<PASSWORD>';
   grant SELECT ON pg_stat_database to epoch;
   

   Verify the privilege grant above was successful:

   psql -h localhost -U epoch postgres -c \
   "select * from pg_stat_database LIMIT(1);"
   && echo -e "\e[0;32mPostgres connection - OK\e[0m" || \
   || echo -e "\e[0;31mCannot connect to Postgres\e[0m"</pre>
   

2. Configure the Agent to connect to PostgreSQL by editing /etc/nutanix/epoch-dd-agent/conf.d/postgres.yaml.

   init_config:
   instances:
     - host: <localhost | PostgreSQL Server Host>
       port: <5432 | PostgreSQL Server Port>
       username: epoch
       password: <PASSWORD>
   

3. Restart the Agent

   /etc/init.d/epoch-collectors restart
   

4. Execute the info command

   /etc/init.d/epoch-collectors info
   

   The output of the command should contain a section similar to the following:

   Checks
   ======
   
     [...]
     postgres
     -----
         - instance #0 [OK]
         - Collected 8 metrics & 0 events
   

Infrastructure Datasources

Datasource	Available Aggregations	Unit	Description
postgresql.connections	avg max min sum	connection	The number of active connections to this database.
postgresql.commits	avg max min sum	transaction/second	The number of transactions that have been committed in this database.
postgresql.rollbacks	avg max min sum	transaction/second	The number of transactions that have been rolled back in this database.
postgresql.disk_read	avg max min sum	block/second	The number of disk blocks read in this database.
postgresql.buffer_hit	avg max min sum	hit/second	The number of times disk blocks were found in the buffer cache, preventing the need to read from the database.
postgresql.rows_returned	avg max min sum	row/second	The number of rows returned by queries in this database
postgresql.rows_fetched	avg max min sum	row/second	The number of rows fetched by queries in this database
postgresql.rows_inserted	avg max min sum	row/second	The number of rows inserted by queries in this database
postgresql.rows_updated	avg max min sum	row/second	The number of rows updated by queries in this database
postgresql.rows_deleted	avg max min sum	row/second	The number of rows deleted by queries in this database
postgresql.database_size	avg max min sum	byte	The disk space used by this database.
postgresql.deadlocks	avg max min sum		The number of deadlocks detected in this database
postgresql.temp_bytes	avg max min sum	byte/second	The amount of data written to temporary files by queries in this database.
postgresql.temp_files	avg max min sum	file/second	The number of temporary files created by queries in this database.
postgresql.bgwriter.checkpoints_timed	avg max min sum		The number of scheduled checkpoints that were performed.
postgresql.bgwriter.checkpoints_requested	avg max min sum		The number of requested checkpoints that were performed.
postgresql.bgwriter.buffers_checkpoint	avg max min sum		The number of buffers written during checkpoints.
postgresql.bgwriter.buffers_clean	avg max min sum		The number of buffers written by the background writer.
postgresql.bgwriter.maxwritten_clean	avg max min sum		The number of times the background writer stopped a cleaning scan due to writing too many buffers.
postgresql.bgwriter.buffers_backend	avg max min sum	buffer	The number of buffers written directly by a backend.
postgresql.bgwriter.buffers_alloc	avg max min sum		The number of buffers allocated
postgresql.bgwriter.buffers_backend_fsync	avg max min sum		The of times a backend had to execute its own fsync call instead of the background writer.
postgresql.bgwriter.write_time	avg max min sum	millisecond	The total amount of checkpoint processing time spent writing files to disk.
postgresql.bgwriter.sync_time	avg max min sum	millisecond	The total amount of checkpoint processing time spent synchronizing files to disk.
postgresql.locks	avg max min sum	lock	The number of locks active for this database.
postgresql.seq_scans	avg max min sum		The number of sequential scans initiated on this table.
postgresql.seq_rows_read	avg max min sum	row/second	The number of live rows fetched by sequential scans.
postgresql.index_scans	avg max min sum		The number of index scans initiated on this table.
postgresql.index_rows_fetched	avg max min sum	row/second	The number of live rows fetched by index scans.
postgresql.rows_hot_updated	avg max min sum	row/second	The number of rows HOT updated, meaning no separate index update was needed.
postgresql.live_rows	avg max min sum	row	The estimated number of live rows.
postgresql.dead_rows	avg max min sum	row	The estimated number of dead rows.
postgresql.index_rows_read	avg max min sum	row/second	The number of index entries returned by scans on this index.
postgresql.table_size	avg max min sum	byte	The total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
postgresql.index_size	avg max min sum	byte	The total disk space used by indexes attached to the specified table.
postgresql.total_size	avg max min sum	byte	The total disk space used by the table, including indexes and TOAST data.
postgresql.table.count	avg max min sum	table	The number of user tables in this database.
postgresql.max_connections	avg max min sum	connection	The maximum number of client connections allowed to this database.
postgresql.percent_usage_connections	avg max min sum	fraction	The number of connections to this database as a fraction of the maximum number of allowed connections.
postgresql.replication_delay	avg max min sum	second	The current replication delay in seconds. Only available with postgresql 9.1 and newer
postgres.replication_delay_bytes	avg max min sum	byte	The current replication delay in bytes. Only available with postgresql 9.2 and newer
postgresql.heap_blocks_read	avg max min sum	block/second	The number of disk blocks read from this table.
postgresql.heap_blocks_hit	avg max min sum	hit/second	The number of buffer hits in this table.
postgresql.index_blocks_read	avg max min sum	block/second	The number of disk blocks read from all indexes on this table.
postgresql.index_blocks_hit	avg max min sum	hit/second	The number of buffer hits in all indexes on this table.
postgresql.toast_blocks_read	avg max min sum	block/second	The number of disk blocks read from this table's TOAST table.
postgresql.toast_blocks_hit	avg max min sum	hit/second	The number of buffer hits in this table's TOAST table.
postgresql.toast_index_blocks_read	avg max min sum	block/second	The number of disk blocks read from this table's TOAST table index.
postgresql.toast_index_blocks_hit	avg max min sum	block/second	The number of buffer hits in this table's TOAST table index.