[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 pg_ctl.
I also learned that Debian had a tool for that! It’s called pg_conftool and
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.
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!
For example:
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!