作者

digoal

日期

2020-10-06

标签

PostgreSQL , postgresql.conf , track

背景

https://pgxn.org/dist/pg_track_settings/2.0.1/

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.

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.