Whats New in Postgres 10: Monitoring Improvements

Postgres 10 has been stamped on Monday, and will most likely be released this week, so this seems like a good time to review what this new release brings in terms of Monitoring functionality built into the database.

In this post you'll see a few things that we find exciting about the new release, as well as some tips on what to adjust, whether you use a hosted Postgres monitoring tool like pganalyze, or if you've written your own scripts.

New "pg_monitor" Monitoring Role

Most users of Postgres obviously don't want to give monitoring tools access to superuser, but in the past this was often required, as many Postgres statistic views (e.g. pg_stat_statements) only show the values for the current user, unless you are superuser.

This meant that you had to workaround with SECURITY DEFINER functions that queries the statistics views as superuser, but could be called from a restricted user.

Now, you can use the monitoring role in Postgres 10 to instead give a user specific access to monitor statistics views, without giving out any other access.

Its as simple as:

GRANT pg_monitor TO monitoring_user;

And afterwards that user can simply access statistics views without running into <insufficient privilege> issues like before.

This also works with pganalyze out of the box, so once you upgrade to 10 you can simply grant the monitoring role to the pganalyze user, and drop the helper functions we've previously asked you to create.

A subset of often used views that the monitoring role now grants you access to:

• pg_stat_statements
• pg_stat_activity
• pg_stat_replication
• pg_stat_progress_vacuum
• .. and more

Note that there more fine-grained roles you can assign, should you want to.

Renaming of "xlog" to "wal", and "location" to "lsn"

If you've written your own monitoring scripts to check replication lag, and other statistics that have to do with WAL or LSNs, you'll need to update some function names.

In this new release, besides the WAL directory being renamed from "pg_xlog" to "pg_wal", all system administration functions have also been renamed to match this change. In addition, where previously functions had the name "location" in them, it now refers to "lsn".

You are most likely going to run into this with the often used pg_current_xlog_location (now pg_current_wal_lsn), as well as the helper method pg_xlog_location_diff (now pg_wal_lsn_diff).

Also note that the sent_location, write_location, etc fields in pg_stat_replication have been renamed to sent_lsn, write_lsn and so forth.

Wait Events & Non-Client Connections in pg_stat_activity

The pg_stat_activity view and underlying data structure has been thoroughly improved this release, and now shows not just client connections and autovacuum, but also other background workers that are running in the system:

SELECT pid, backend_type, backend_start FROM pg_stat_activity WHERE backend_type != 'client backend';

 pid |    backend_type     |         backend_start         
-----+---------------------+-------------------------------
  58 | autovacuum launcher | 2017-10-03 21:02:45.458053+00
  60 | background worker   | 2017-10-03 21:02:45.459172+00
  56 | background writer   | 2017-10-03 21:02:45.457657+00
  55 | checkpointer        | 2017-10-03 21:02:45.457491+00
  57 | walwriter           | 2017-10-03 21:02:45.457817+00

If you have previously written monitoring scripts that rely on counting the number of entries in pg_stat_activity, you should filter the view by backend_type = 'client backend', or switch to using numbackends from pg_stat_database.

In addition to this, the new release also brings an additional 115 wait events (visible in wait_event_type and wait_event in pg_stat_activity), in particular more than 60 new I/O related events which help you understand better what a query is busy with.

You can find the full list of wait events in the Postgres documentation.

amcheck

Last but not least, a useful feature for consistency checking got added in this release. Initially developed by Peter Geoghegan and battle-tested at Heroku Postgres, this new tool allows you to check a B-Tree index for corruption as well as verify that invariants in the structure of the index are as expected.

It first needs to be created as CREATE EXTENSION amcheck and can then be run by a superuser like this:

SELECT bt_index_check('my_test_index');

 bt_index_check
----------------

An empty result indicates that the index is consistent, as would be expected.

Note that amcheck accesses the index through the shared buffer cache, so it might not show problems at the disk level right away. See more details on its documentation page.

This concludes a short overview of new monitoring functionality in Postgres 10.

Note that there are many other amazing new features like parallel query, logical replication and declarative partitioning that are not covered in this post.

If this article proved useful to you, you might also be interested in our Postgres Log Monitoring 101 article where we take a closer look at Deadlocks, Checkpoint Tuning, and Blocked Queries.