08:00
The Elements of postgresql.conf Style
13 April 2012
… or, inexcusable things I am tired of seeing in postgresql.conf files.
Do not mix ‘n’ match override styles.
There are two valid styles for overriding the default values in postgresql.conf: Putting your changes as a cluster at the end, or uncommenting the defaults and overriding in place. Both have advantages and disadvantages. Having some settings one way and some another is pure disadvantage. Do not do this.
Use units.
Quick, what is log_min_duration_statement set to here?
log_min_statement_duration = 2000
Now, what is it set to here?
log_min_statement_duration = 2s
Always use units with numeric values if a unit is available.
Do not remove the default settings.
If you strip out all of the defaults, it becomes impossible to tell what a particular value is set to. Leave the defaults in place, and if you comment out a setting, reset the value to the default (or at least include comments that make it clear what is going on).
Do not leave junk postgresql.conf files scattered around.
If you need to move postgresql.conf (and the other configuration files) to a different location from where the package for your system puts it, don’t leave the old, dead postgresql.conf lying around. Delete any trace of the old installation hierarchy.
Thank you.
There are 7 comments.
Selena Deckelmann at 09:20, 13 April 2012:
I recommend that people put settings at the bottom of the file, and leave all the default config in-place.
The problem with putting changes in-line is that you end up grepping the file for things that aren’t commented out (I have a shell alias for that).
And, I recommend aligning the ‘=’ sign, so that you can easily scan the variables and values.
Peter Eisentraut at 10:01, 13 April 2012:
I partially disagree. My preferred style nowadays is to delete the entire default postgresql.conf file and write a new one from scratch containing only the settings I specifically picked.
Xof at 10:43, 13 April 2012:
What I dislike about having a minimal file with only overrides is that you have just thrown away a perfectly good reference to what the default values are. The information is available, of course, but I find it extremely valuable to have it right in front of your face while you are editing pg.conf.
Chris Adams at 19:30, 13 April 2012:
Xof: I’ve found that style not to scale well – given enough systems and variety or time, it becomes increasingly likely that you’ll be confused by a commented “default” value which is not actually the current value.
Xof at 20:41, 13 April 2012:
@Chris – I’m fine with either the in-line or at-the-end style; I personally prefer the in-line style, so you can see directly if something has been overridden or not. The big no-no is to do a combination of these; sadly, some tools do that, which I think is dumb.
Peter van Hardenberg at 12:12, 14 April 2012:
Writing a full custom config file makes it difficult to upgrade major versions. Specifically it is tricky to identify what new settings you might need to add, and what defaults have changed. Our approach is to `include` a customization file at the end of a stock package config.
When a new Postgres version is released, we simply review the changes between the upstream default files and consider whether any of our customizations need tweaking or retiring.
We also include a “site-specific” customization file which contains changes that are not shared between servers. We have found that this approach scales very well.
Greg Smith at 13:21, 17 April 2012:
The default value is easy to find out by looking at the boot_val column in the pg_settings view. Configurations should be validated by looking at pg_settings anyway.