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.

Screen_Shot_2018-01-18_at_11.46.12.png

Monitored metrics

MetricValues
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
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Monday  —  Friday.

10am  —  6pm UK.

Dedicated Support.