Take advantage of pg_settings when dealing with your configurationI often see messages on PostgreSQL related mailing list where the configuration is assumed by a Unix-style approach. For example, imagine you have been asked to provide your autovacuum configuration in order to see if there’s something wrong with it; one approach I often is the copy and paste of the following:
While this could be a correct approach and makes it simply to provide a full set of configuration values, it has few drawbacks:
% sudo -u postgres grep autovacuum /postgres/12/postgresql.conf #autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem #autovacuum = on # Enable autovacuum subprocess? 'on' #log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and autovacuum_max_workers = 7 # max number of autovacuum subprocesses autovacuum_naptime = 2min # time between autovacuum runs autovacuum_vacuum_threshold = 500 # min number of row updates before autovacuum_analyze_threshold = 700 # min number of row updates before #autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum #autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age #autovacuum_vacuum_cost_delay = 2ms # default vacuum cost delay for # autovacuum, in milliseconds; #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use
- it produces verbose output (e.g., there are comments on the right of each line);
- it could not be the whole story about the configuration, for example because something is in
- it does include commented out lines;
- it could be not the configuration your cluster is running on.
Verbose OutputThis is much annoyance than a real problem, but please consider that people on the other part of the world could have a screen resolution, line wrapping, or setting that makes it difficult to read verbose lines.
Could not be the whole truth about configurationI often place my own PostgreSQL configuration into
include_if_existsfiles, so that I leave the
postgresql.conffile unchanged. Let’s call it a kind of FreeBSD configuration style!
This means that, in order to use a Unix approach to find a particular setting, I have to include in the search every single configuration file in every single location. This can be as simple as doing
grep autovacuum *.conf*or much more complicated depending on your directory structure.
In any case, I could have omitted one single file, and that’s bad both for me and whoever is trying to help me.
ALTER SYSTEMis gaining more and more power, setting could also live into
postgresql.conf.autoand people should begin used to check also such file.
It does include commented-out linesCome on, who cares about that? After all commented-out lines means the value is at its default value.
And that could be the problem: do you remember all the default values for every single PostgreSQL version?
Therefore, don’t trust the default value, get the exact value!
It could not be the configuration you cluster is usingWhat if you modified the configuration file but edited the wrong context with regard to the action you did? May be you edited a
postmastercontext parameter and issued only a simple
SIGHUP? What if you forgot to notify the cluster at all?
What if another administrator changed the parameters without telling you and scheduling a cluster notification at night? Yes, that really happened to me…
Again, get the real configuration!
How to get the real configurationI’m glad you asked:
pg_settingsis there for you.
It is a matter of a single query, for example:
You can elaborate the query as you like, but the point is that you get exact values. In this particular example, as you can see, some values differs from what you get out of the configuration file. For example,
forumdb=> SELECT name, setting, pending_restart FROM pg_settings WHERE name like 'autovacuum%' ORDER BY 1; name | setting | pending_restart -------------------------------------|-----------|----------------- autovacuum | on | f autovacuum_analyze_scale_factor | 0.1 | f autovacuum_analyze_threshold | 50 | f autovacuum_freeze_max_age | 200000000 | f autovacuum_max_workers | 3 | f autovacuum_multixact_freeze_max_age | 400000000 | f autovacuum_naptime | 60 | f autovacuum_vacuum_cost_delay | 2 | f autovacuum_vacuum_cost_limit | -1 | f autovacuum_vacuum_scale_factor | 0.2 | f autovacuum_vacuum_threshold | 50 | f autovacuum_work_mem | -1 | f (12 rows)
autovacuum_max_workerhas been set to
7in the configuration file, but the database applies a value of
Now you can inspect this problem too, and see if it has been caused from a cluster that has not been notified about configuration changes or an included file that overwrites your settings.
ConclusionsThe configuration file is always only an hint about what your cluster is configured for, not the real thruth. When inspecting a configuration problem, the starting point to report even to others is the output of