Monitoring PostgreSQL

This article will help you get the Postgres plugin for sd-agent configured and returning metrics

Installing the postgres plugin package

Install the postgres plugin on Debian/Ubuntu:

sudo apt-get install sd-agent-postgresql

Install the postgres plugin on RHEL/CentOS:

sudo yum install sd-agent-postgresql

Read more about agent plugins.

Configuring the agent to monitor PostgreSQL

1. Connect to psql and create a monitoring user:

CREATE USER serverdensity WITH PASSWORD 'supersecretpassword';
GRANT SELECT ON pg_stat_database TO serverdensity;

2. Configure /etc/sd-agent/conf.d/postgres.yaml

init_config:

instances:
 - host: localhost
   port: 5432
   username: serverdensity
   password: supersecretpassword

3. Restart the agent

sudo /etc/init.d/sd-agent restart

or

sudo systemctl restart sd-agent

Verifying the configuration
Execute info to verify the configuration with the following:

sudo /etc/init.d/sd-agent info 

or

/usr/share/python/sd-agent/agent.py info

If the agent has been configured correctly you'll see an output such as:

postgres
-----
  - instance #0 [OK]
  - Collected * metrics

You can also view the metrics returned with the following command:

sudo -u sd-agent /usr/share/python/sd-agent/agent.py check postgres

Configuring graphs

Click the name of your server from the Devices list in your Server Density account then go to the Metrics tab. Click the + Graph button on the right then choose the postgres metrics to display the graphs. The metrics will also be available to select when building dashboard graphs.

Monitored metrics

Metric	Values
postgres.replication_delay_bytes The current replication delay in bytes. Only available with postgresql 9.2 and newer	byte / None Type: float
postgresql.bgwriter.buffers_alloc The number of buffers allocated	None / None Type: float
postgresql.bgwriter.buffers_backend The number of buffers written directly by a backend.	buffer / None Type: float
postgresql.bgwriter.buffers_backend_fsync The of times a backend had to execute its own fsync call instead of the background writer.	None / None Type: float
postgresql.bgwriter.buffers_checkpoint The number of buffers written during checkpoints.	None / None Type: float
postgresql.bgwriter.buffers_clean The number of buffers written by the background writer.	None / None Type: float
postgresql.bgwriter.checkpoints_requested The number of requested checkpoints that were performed.	None / None Type: float
postgresql.bgwriter.checkpoints_timed The number of scheduled checkpoints that were performed.	None / None Type: float
postgresql.bgwriter.maxwritten_clean The number of times the background writer stopped a cleaning scan due to writing too many buffers.	None / None Type: float
postgresql.bgwriter.sync_time The total amount of checkpoint processing time spent synchronizing files to disk.	millisecond / None Type: float
postgresql.bgwriter.write_time The total amount of checkpoint processing time spent writing files to disk.	millisecond / None Type: float
postgresql.dead_rows The estimated number of dead rows.	row / None Type: float
postgresql.deadlocks The number of deadlocks detected in this database	None / None Type: float
postgresql.heap_blocks_hit The number of buffer hits in this table.	hit / second Type: float
postgresql.heap_blocks_read The number of disk blocks read from this table.	block / second Type: float
postgresql.index_blocks_hit The number of buffer hits in all indexes on this table.	hit / second Type: float
postgresql.index_blocks_read The number of disk blocks read from all indexes on this table.	block / second Type: float
postgresql.index_rows_fetched The number of live rows fetched by index scans.	row / second Type: float
postgresql.index_rows_read The number of index entries returned by scans on this index.	row / second Type: float
postgresql.index_scans The number of index scans initiated on this table.	None / None Type: float
postgresql.index_size The total disk space used by indexes attached to the specified table.	byte / None Type: float
postgresql.live_rows The estimated number of live rows.	row / None Type: float
postgresql.locks The number of locks active for this database.	lock / None Type: float
postgresql.replication_delay The current replication delay in seconds. Only available with postgresql 9.1 and newer	second / None Type: float
postgresql.rows_hot_updated The number of rows HOT updated, meaning no separate index update was needed.	row / second Type: float
postgresql.seq_rows_read The number of live rows fetched by sequential scans.	row / second Type: float
postgresql.seq_scans The number of sequential scans initiated on this table.	None / None Type: float
postgresql.table.count The number of user tables in this database.	table / None Type: float
postgresql.table_size The total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.	byte / None Type: float
postgresql.temp_bytes The amount of data written to temporary files by queries in this database.	byte / second Type: float
postgresql.temp_files The number of temporary files created by queries in this database.	file / second Type: float
postgresql.toast_blocks_hit The number of buffer hits in this table's TOAST table.	hit / second Type: float
postgresql.toast_blocks_read The number of disk blocks read from this table's TOAST table.	block / second Type: float
postgresql.toast_index_blocks_hit The number of buffer hits in this table's TOAST table index.	block / second Type: float
postgresql.toast_index_blocks_read The number of disk blocks read from this table's TOAST table index.	block / second Type: float
postgresql.total_size The total disk space used by the table, including indexes and TOAST data.	byte / None Type: float