2013-08-21

Taming Your postgresql.conf Changes With Includes


A few weeks ago, my comrade +Douglas Hunley and I were working on a small project together for a customer that involved a fair number of changes to parameters in the Postgres database configuration file, postgresql.conf.  And being the ridiculously anal admin type that I am when it comes to the organization of and commentary throughout my config files for any service, I was beginning to have fits with the havoc this was wreaking upon the relative beauty that is postgresql.conf.  (I blame my years in design engineering working with engineering change requests for this particular trait.)

postgresql.conf is a masterpiece of a configuration file, being ridiculously well documented throughout with a plethora of commentary to boot and parameters grouped by category and functionality, rather than just a straight alphabetical listing.  The numerous edits being made, plus my penchant for thorough commentary on each change was breaking up the flow of the file.  The result was not making for easy reading.  And the more I tried to address that issue, the less the changes that were being made stood out.

And then I recalled those wonderful includes in the Apache config files I used to know and love, wondering if there was any chance Postgres might have a similar capability.  Praise be to the Postgres Docs, it does.  Just throw them at the end of the file and they'll override any previous settings!

Okay, so why do this?  Consider the elegant simplicity of organization includes provide...




Easily Set Standard Configs For Related Parameters


Say, for example, your organization has a standard logging config you want running on every server.  You might consider having a standard postgresql.conf file with these parameters set.  But what if there are physical differences between the servers that affect other parameters, such as work_mem, effective_shared_cache, etc.?  Or your WAL settings differ?  Or autovacuum?  You can easily see where this is going.


Organization That Self Documents


Whether you work with many databases or just one, you're eventually going to return to one after enough time has passed for you to have forgotten everything you (or someone else, for that matter) you'd set and/or why it was set that way.  Let's say your organization comes up with a standard naming convention for these files.  As I work for +EnterpriseDB (EDB), I might use this to name my files:
    edb_logging.conf
    edb_tuning.conf
    edb_vacuuming.conf


and so on.  Now, when you look at the directory listing it becomes readily apparent fairly quickly where to look for any custom settings I or my colleagues may have made, doesn't it.


More Thoroughly Documented Changes


Being a huge advocate of not only clear and thorough documentation within configuration files, but also maintaining a record within of previous settings, dates of and reasons for changes and so on; I find this method allows much clearer and more readable information.  Some night consider this overkill.  But if I'm tasked with troubleshooting why sorts & merges on disk have recently dramatically increased, and I take a quick stroll through a file that might be named edb_memory, finding an entry akin to the following:

# change date:      2013-08-01
# previous value:   20MB
# new value:        5MB
# change by:        jgraber@edb
# reason:           let's see what happens!

    work_mem = 5MB

I'm going to be torn between buying this jgraber guy a beer for great documentation of changes, and smashing the bottle over his head for monkeying with this for no apparent reason.  But at least I've potentially saved a tremendous amount of time and frustration wondering what happened.

(Yes, you could do this in postgresql.conf, no question.  But imagine what that already heavily commented file is going to become over time as these changes are made.)




Okay then, go include some stuff... and things!