sql-info.de
May 24, 2013

PostgreSQL | Custom Background Worker: a practical example

A while back I posted some SQL which helps track of changes to the PostgreSQL settings file. I've found it useful when benchmarking tests with different settings, but unfortunately the pg_settings_log() function needs to be run manually after each setting change. However that sounds like something which a custom background worker (new in 9.3) could handle - basically all the putative background worker would need to do is execute the pg_settings_log() function whenever the server starts (or restarts) or receives SIGHUP.

This turned out to be surprisingly easy to implement. Based off the example contrib module and Michael Paquier's excellent posts, this is the code. Basically all it does is check for the presence of the required database objects (a function and a table) on startup, executes pg_settings_log() on startup, and adds a signal handler for SIGHUP which also calls pg_settings_log().

To install this, download the package files (I'll try and get this on PGXN at some point) from the PGXN project page; unpack; with the 9.3 pg_config in the shell path do make and make install. In the default postgres database, execute CREATE EXTENSION config_log then add config_log to postgresql.conf's shared_preload_libraries parameter and restart PostgreSQL.

The module will write something like the following into the PostgreSQL log:

LOG:  config_log: initialized, database objects validated
LOG:  config_log: pg_settings_logger() executed
LOG:  config_log: No configuration changes detected

and there'll be an eponymous bgworker process:

barwick         5894   0.0  0.2  2592464   7532   ??  Ss    4:34am   0:00.03 postgres: bgworker: config_log

To verify it catches changes which don't require a restart, change something in postgresql.conf (e.g. temp_buffers to 12MB) and execute "SELECT pg_reload_conf()" or "pgctl reload"; the log output will look something like this:

LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "temp_buffers" changed to "12MB"
LOG:  config_log: received sighup
LOG:  config_log: pg_settings_logger() executed
LOG:  config_log: Configuration changes recorded

Verify the setting has been changed:

postgres=# SELECT * from pg_settings_log_current WHERE name='temp_buffers';
     name     | setting | unit |                       sourcefile                        | sourceline |   op   |          recorded_ts
--------------+---------+------+---------------------------------------------------------+------------+--------+-------------------------------
 temp_buffers | 1536    | 8kB  | /Users/barwick/devel/postgres/data/HEAD/postgresql.conf |        116 | INSERT | 2013-05-22 04:44:54.101057+09
(1 row)

And that's it. Note that I haven't tested this exhaustively and there's no guarantee that it won't blow up in interesting and amusing ways and/or convert your data to bizarre ASCII art.

Potential todos:

  • make database and schema configurable
  • display units in human-readable form (e.g. 12M vs 1536 8kb units)

Links:

Posted at 10:36 PM