Where do my Postgres settings come from ?
April 19, 2020Hacking PostgreSQL Administration
[2020-04-19 Edit: I had forgotten two ways to change
your Postgres setting: one setting a parameter inside a transaction by using
set local parameter=value and another one is not valid for every parameter
but you may set some of them at Postgres startup by feeding options to
I also learned that Debian had a tool for that! It’s called
will assist you in displaying and setting Postgres parameter!
Thank you @tapoueh and hunleyd!]
Postgres has a lot of settings and it’s both a good thing (you can really change it the way you want) and a bad thing (newcomers are confused by all those choices). On top of that, there are several ways to change a setting and if you don’t change the right one, your change might have no effect at all!
Settings values overwrite rules
The following picture will show you the different ways to set a parameter and how they can be overwritten.
So, a setting applied to a session will overwrite any value set in the database level, role level, or in any configuration file.
I added the SQL orders you might need to change the settings at a different level.
How do I know which one is taken?
Of course, you may change the setting with a different value for all levels to
know which one is taken into account, but there is a better way to find out:
pg_settings is a view that will be very helpful to find information about
settings. With that view, you’ll be able to tell where the setting comes from
and if it comes from a file, it will also give you the file and line number!
laetitia=# select name, source, sourcefile, sourceline from pg_settings where name = 'shared_buffers'; name | source | sourcefile | sourceline ----------------+--------------------+-----------------------------------------+------------ shared_buffers | configuration file | /etc/postgresql/12/main/postgresql.conf | 11 (1 row)
What if I can’t connect?
Well, if you can’t connect due to configuration wrong values (a typo in
postgresql.conf, a bad port, etc) while you looked at it three times already, it might be
because it’s overwritten somewhere you don’t think of.
So here is a little diagram to help you:
If you’re using Debian,
pg_conftool might help you!