Where do my Postgres settings come from ?

April 19, 2020
Hacking PostgreSQL Administration

[2020-04-19 Edit: I had forgot 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 parameters but you may set some of them at postgres startup by feeding options to pg_ctl. I also learnt 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 database level, role level or in any configuration file.

I added the SQL orders you might need to change the settings at different level.

How do I know which one is taken ?

Of course, you may change the setting with 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 which 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

If you’re using Debian, pg_conftool might help you!

Debian Default Logging Explained

April 4, 2020
Hacking PostgreSQL Debian

Can't connect to Postgres

March 30, 2020
Administration PostgreSQL Troubleshooting

Restoring Through timeline change

March 24, 2020
Installation PostgreSQL