Where do my Postgres settings come from ?

ยท 374 words ยท 2 minute read

[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

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