Since I’m a consultant, I frequently (well, kind of always, actually) run into customers with bad managed PostgreSQL configuration file.
It seems that I keep giving the same advice always and always, so I decided to write a blog post about them.
Too long configuration files
PostgreSQL conf files come with a lot of comments to explain to you what this setting is used for, what value you can put there and what is the default value. It’s pretty great to learn and that’s really good if you’re a beginner.
But, a lot of people use that commented conf files in production. This is wrong.
A conf file needs to be short if you want it to be processed by a human brain. You should be able to see all your settings in a shell screen. Why would you want a human brain to process your conf file? Because when there’s a problem, humans will need to process that file just to understand how the engine was set and so how it will work.
How can you manage to have a Postgresql conf file in less than a shell screen?
Just get rid of comments, included the commented settings. You don’t need
them. Some might object they need the commented value because it reminds them of
the default value for that setting. The default settings can be queried in
Postgres. You just need to query over the
pg_settings system view.
Here is an example of query:
\set param shared_buffer select name, setting, unit, reset_val from pg_settings where name = :'param';
(You might notice I use a variable for the setting name. That way, I can reuse the same query several times and just change the variable name.)
Sometimes, you might want to keep the comments that are used to separate different section of settings. If you know both the configuration file and the documentation well, maybe you have noted that the sections in the file match the chapters of the documentation.
Duplicates and useless settings
So, let’s assume you cleaned up your file and it’s still too long to be watched in a glance. What should we do ? There are two things you could still do :
- delete duplicates
- delete settings for which the value is the same as the default
First, let’s talk about duplicate. It happens that sometimes, you’re too lazy to look for your setting and you just add the new value at the end of the file. Please stop doing that! It’s pretty confusing and your co-workers might curse you over 5 generations because they lost half a day trying to figure out why their settings change wasn’t taken into acount.
Then, why would setting a parameter to its default value be a bad thing ? Two simple reasons : It’s useless and you need to keep your configuration file concise to increase readability. The second reason is more tricky. Let’s say you set a setting to it’s default value. 4 years later, this default value somehow changes in Postgres because the community found out this new default is more accurate, you won’t be able to use this Postgres improvement!
Thius query will help you find parameters set to the default value in your
select name, setting, unit, reset_val from pg_settings where setting = reset_val and source = 'configuration file';
If after all this cleanup you still don’t have a concise configuration file, it means you need to split it into sections and use the “include” features, either by including a whole directory or by including each file by name. (See Postgres documentation if you need to figure out how to do that)
As I explained earlier, the configuration file is already divided into sections and these sections match the documentation. It seems pretty accurate to create a file for each section (and to name those files after the section names).
Managing configuration files
As I said earlier, comments in configuration files in production aren’t good for readability and maintenability, so how can we keep a trace of the changes we made into our configuration files ?
The answer is so simple: use
git (or any other verioning tool). Don’t forget to
explain in your commit messages why you made this changes. You might find it
usefull some day… ;-)
For SQL compliance reasons, Postgres 9.4 was added the ability to change a
setting by using
alter system statement. The setting changed will be added to
postgresql.auto.conf file which you can’t manage and which is read after the
If you decide to use the
alter system statement anyway, make sure you:
- document your way of doing things so that your co-workers don’t waste their time
- document the settings changes and their reasons
Managing configuration files is not complicated, but it’s somehow often not managed because noone ever thought about it. Just take the time to understand why you’re doing things in certain ways. If you ever loose half a day because a process is not good, try to improve it.