pg_track_settings

pg_track_settings is a small extension that helps you keep track of postgresql settings configuration.

It provides a function (pg_track_settings_snapshot()), that must be called regularly. At each call, it will store the settings that have been changed since last call. It will also track the postgresql start time if it's different from the last one.

This extension tracks both overall settings (the pg_settings view) and overloaded settings (the pg_db_role_setting table).

Usage

• Create the extension in any database:

  CREATE EXTENSION pg_track_settings;

Then make sure the pg_track_settings_snapshot() function called. Cron or PoWA can be used for that.

Functions

• pg_track_settings_snapshot(): collect the current settings value.
• pg_track_settings(timestamptz): return all settings at the specified timestamp. Current time is used if no timestamped specified.
• pg_track_settings_diff(timestamptz, timestamptz): return all settings that have changed between the two specified timestamps.
• pg_track_settings_log(text): return the history of a specific setting.
• pg_track_db_role_settings(timestamptz): return all overloaded settings at the specified timestamp. Current time is used if no timestamped specified.
• pg_track_db_role_settings_diff(timestamptz, timestamptz): return all overloaded settings that have changed between the two specified timestamps.
• pg_track_db_role_settings_log(text): return the history of a specific overloaded setting.

Example

Call a first time the snapshot function to get the initial values:

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

A first snapshot is now taken:

 postgres=# select DISTINCT ts FROM pg_track_settings_history ;
              ts
-------------------------------
 2015-01-25 01:00:37.449846+01
 (1 row)

Let's assume the configuration changed, and reload the conf:

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

Call again the snapshot function:

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

Now, we can check what settings changed:

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

And the detailed history of this setting:

postgres=# SELECT * FROM pg_track_settings_log('checkpoint_segments');
              ts               |     name            | setting_exists | setting
-------------------------------+---------------------+----------------+---------
 2015-01-25 01:01:42.581682+01 | checkpoint_segments | t              | 35
 2015-01-25 01:00:37.449846+01 | checkpoint_segments | t              | 30
(2 rows)

And you can retrieve all the PostgreSQL configuration at a specific timestamp:

postgres=# SELECT * FROM pg_track_settings('2015-01-25 01:01:00');
            name              | setting
------------------------------+---------
[...]
 checkpoint_completion_target | 0.9
 checkpoint_segments          | 30
 checkpoint_timeout           | 300
[...]

The same functions are provided for per role and/or database settings ( ALTER ROLE ... SET, ALTER ROLE ... IN DATABASE ... SET and ALTER DATABASE ... SET commands):

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

We also have the history of postgres start time:

postgres=# SELECT * FROM pg_reboot;
              ts
-------------------------------
 2015-01-25 00:39:43.609195+01
(1 row)

Please also note that all the history will be saved in a pg_dump / pg_dumpall backup. If you need the clear this history, the function pg_track_settings_reset() will do that for you.