Have you ever wished to know what configuration changed during the last weeks, when everything was so much faster, or wanted to check what happened on your beloved cluster while you were in vacation?

pg_track_settings is a simple, SQL only extension that helps you to know all of that and more very easily. As it’s designed as an extension, it requires PostgreSQL 9.1 or more.

Some insights

As amost any extension, you have to compile it from source, or use the pgxn client, since there’s no package yet. Assuming you just extract the tarball of the release 1.0.0 with a typical server configuration:

$ cd pg_track_settings-1.0.0
$ sudo make install

Then the extension is available. Create the extension on the database of your choice:

postgres=# CREATE EXTENSION pg_track_settings ;
CREATE EXTENSION

In order to historize the settings, you need to schedule a simple function call on a regular basis. This function is the pg_track_settings_snapshot function. It’s really cheap to call, and won’t have any measurable impact on your cluster. This function will do all the smart work of storing all the parameters that changed since the last call.

For instance, if you want to be able to know what changed on your server within a 5 minutes accuracy, a simple cron entry like this for the postgres user is enough:

*/5 *  * * *     psql -c "SELECT pg_track_settings_snapshot()" > /dev/null 2>&1

A background worker could be used on PostgreSQL 9.3 and more, but as we only have to call one function every few minutes, it’d be overkill to add one just for this. If you really want one, you’d better consider settting up PoWA for that, or another extension that allows to run task like pgAgent.

How to use it

Let’s call the snapshot function to get ti initial values:

postgres=# select pg_track_settings_snapshot()
 ----------------------------
  t
  (1 row)

A first snapshot with the initial settings values is saved. Now, I’ll just change a setting in the postgresql.conf file (ALTER SYSTEM could also be used on a PostgreSQL 9.4 or more release), reload the configuration and take another snapshot:

postgres=# select pg_reload_conf();
 pg_reload_conf
 ----------------
  t
  (1 row)

postgres=# select * from pg_track_settings_snapshot();
 pg_track_settings_snapshot
----------------------------
 t
(1 row)

Now, the fun part. What information is available?

First, what changed between two timestamp. For instance, let’s check what changed in the last 2 minutes:

postgres=# SELECT * FROM pg_track_settings_diff(now() - interval '2 minutes', now());
        name         | from_setting | from_exists | to_setting | to_exists
---------------------+--------------|-------------|------------|----------
 max_wal_size        | 93           | t           | 31         | t
(1 row)

What do we learn ?

• as the max_wal_size parameter exists, I’m using the 9.5 alpha release. Yes, what PostgreSQL really needs right now is people testing the upcoming release! It’s simple, and the more people test it, the faster it’ll be avalable. See the how to page to see how you can help :)
• the max_wal_size parameter existed 2 minutes ago (from_exists is true), and also exists right now (to_exists is true). Obviously, the regular settings will not disappear, but think of extension related settings like pg_stat_statements.* or auto_explain.*
• the max_wal_size changed from 93 (from_setting) to 31 (to_setting).

Also, we can get the history of a specific setting:

postgres=# SELECT * FROM pg_track_settings_log('max_wal_size');
              ts               |     name     | setting_exists | setting
-------------------------------+--------------+----------------+---------
 2015-07-17 22:42:01.156948+02 | max_wal_size | t              | 31
 2015-07-17 22:38:02.722206+02 | max_wal_size | t              | 93
(2 rows)

You can also retrieve the entire configuration at a specified timestamp. For instance:

postgres=# SELECT * FROM pg_track_settings('2015-07-17 22:40:00');
                name                 |     setting
-------------------------------------+-----------------
[...]
 max_wal_senders                     | 5
 max_wal_size                        | 93
 max_worker_processes                | 8
[...]

The sames functions are provided to know what settings have been overloaded for a specific user and/or database (the ALTER ROLE … SET, ALTER ROLE … IN DATABASE … SET and ALTER DATABASE … SET commands), with the functions:

• pg_track_db_role_settings_diff()
• pg_track_db_role_settings_log()
• pg_track_db_role_settings()

And finally, just in case you can also know when PostgreSQL has been restarted:

postgres=# SELECT * FROM pg_reboot;
              ts
-------------------------------
 2015-07-17 08:39:37.315131+02
(1 row)

That’s all for this extension. I hope you’ll never miss or forget a configuration change again!

If you want to install it, the source code is available on the github repository github.com/rjuju/pg_track_settings.

Limitations

As the only way to know what is the current value for a setting is to query pg_settings (or call current_setting()), you must be aware that the user calling pg_track_settings_snapshot() may see an overloaded value (like ALTER ROLE … SET param = value) rather than the original value. As the pg_db_role_setting table is also historized, it’s pretty easy to know that you don’t see the original value, but there’s no way to know what the original value really is.