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.
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:
- .. and more
Note that there more fine-grained roles you can assign, should you want to.
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_wal_lsn), as well as the helper method
Also note that the
write_location, etc fields in
pg_stat_replication have been renamed to
write_lsn and so forth.
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
In addition to this, the new release also brings an additional 115 wait events (visible 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.
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:
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.