Starting wiht PostgreSQL 9.4 you can change parameters in postgresql.conf with the “alter system” command in psql. In principle every parameter can be changed that way but not every parameter is applied immediately. Some of the parameters require a reload others require a complete restart of the instance. But which of these parameter require what? How can you know which method you have to use for which parameter?

The first thing to know is that parameter changes you trigger by using “alter system” are not written to postgresql.conf which is the parameter file for the instance. Instead parameter changes are written to postgresql.auto.conf. Lets do a simple example by changing the work_mem parameter:

(postgres@[local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 32MB
(1 row)

Time: 0.204 ms
(postgres@[local]:5432) [postgres] > alter system set work_mem='16MB';
ALTER SYSTEM
Time: 320.400 ms

The initial setting was 32MB and I changed that to 16MB. Does it go into effect immediately?

(postgres@[local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 32MB
(1 row)

No. Before looking at how we can make this parameter change going live lets look at what was written to posstgresql.auto.conf:

postgres@centos7:/u02/pgdata/PG1/ [PG1] cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
work_mem = '16MB'

So we have the new setting in the postgresql.auto.conf file. What about postgresql.conf?

postgres@centos7:/u02/pgdata/PG1/ [PG1] grep work_mem postgresql.conf 
work_mem=32MB
maintenance_work_mem=64MB

We still see the old value there and this never gets changed. What does that mean? When the server starts it reads postgresql.conf and in addition the values from postgresql.auto.conf are applied and the values for parameters in postgresql.conf are overwritten/replaced by the values for the parameters in postgresql.auto.conf. This is important to undertand. If you change the value of a parameter in postgresql.conf but you have the same parameter specified in postgresql.auto.conf you will never succeed:

postgres@centos7:/u02/pgdata/PG1/ [PG1] sed -i 's/work_mem=32MB/work_mem=8MB/g' postgresql.conf 
postgres@centos7:/u02/pgdata/PG1/ [PG1] grep work_mem postgresql.conf 
work_mem=8MB
maintenance_work_mem=64MB

I changed the parameter in postgresql.conf to 8MB and then I’ll restart the instance and check the value of the parameter:

postgres@centos7:/u02/pgdata/PG1/ [PG1] pg_ctl -D /u02/pgdata/PG1/ restart -m fast
postgres@centos7:/u02/pgdata/PG1/ [PG1] psql
(postgres@[local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 16MB
(1 row)

There is a value of 16MB now and not 8MB what I did write to the postgresql.conf file. Conclusion: Parameters set in postgresql.conf are overwritten if the same parameter is set in postgresql.auto.conf.

Back to the original question: How can I know what action a parameter change requires to go into effect? The answer is pg_settings:

(postgres@[local]:5432) [postgres] > d pg_settings
     View "pg_catalog.pg_settings"
     Column      |  Type   | Modifiers 
-----------------+---------+-----------
 name            | text    | 
 setting         | text    | 
 unit            | text    | 
 category        | text    | 
 short_desc      | text    | 
 extra_desc      | text    | 
 context         | text    | 
 vartype         | text    | 
 source          | text    | 
 min_val         | text    | 
 max_val         | text    | 
 enumvals        | text[]  | 
 boot_val        | text    | 
 reset_val       | text    | 
 sourcefile      | text    | 
 sourceline      | integer | 
 pending_restart | boolean | 

The important column is the “context” column. By knowing the context you know what you need to do:

(postgres@[local]:5432) [postgres] > select context from pg_settings where name = 'work_mem';
 context 
---------
 user
(1 row)

In this case it means that every user can change the setting for the local session. Do we need to restart if we change it globally?

(postgres@[local]:5432) [postgres] > alter system set work_mem='12MB';
ALTER SYSTEM
Time: 316.481 ms
(postgres@[local]:5432) [postgres] > select pending_restart from pg_settings where name = 'work_mem';
 pending_restart 
-----------------
 f
(1 row)

No. So a reload should be fine:

(postgres@[local]:5432) [postgres] > select * from pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 4.158 ms
(postgres@[local]:5432) [postgres] > show work_mem;
 work_mem 
----------
 12MB
(1 row)

Now we have it applied and we didn’t need to restart. What about changing parameters that reuqire a restart?

(postgres@[local]:5432) [postgres] > show port;
 port 
------
 5432
(1 row)

Time: 0.177 ms
(postgres@[local]:5432) [postgres] > alter system set port=5555;
ALTER SYSTEM
Time: 311.963 ms
(postgres@[local]:5432) [postgres] > select pending_restart from pg_settings where name = 'port';
 pending_restart 
-----------------
 f
(1 row)

Hm. Changing the port for sure requires a restart. Why does pg_settings tell us that we do not need to restart? Lets try a reload and see what happens:

(postgres@[local]:5432) [postgres] > select * from pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

When you look at the log file for the last action you’ll notice this:

2016-06-10 08:11:23.987 CEST - 4 - 3111 -  - @ LOG:  received SIGHUP, reloading configuration files
2016-06-10 08:11:23.988 CEST - 5 - 3111 -  - @ LOG:  parameter "port" cannot be changed without restarting the serve

So at least here we can see that we need to restart for our parameter to be applied. What does pg_settings now show?

(postgres@[local]:5432) [postgres] > select pending_restart from pg_settings where name = 'port';
 pending_restart 
-----------------
 t
(1 row)

Now it shows true. This means you first need to reload PostgreSQL for the pending_restart column being updated (This is also true for the work_mem test above. We have just been lucky in that case). Conclusion: For whatever parameter you change do a reload afterwards and then check pg_settings if you need to additionally restart the instance.