Where do my Postgres settings come from ?

April 19, 2020

Hacking 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 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.

Setting Postgres

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:

Troubleshooting Postgres Settings