Parameter Needs Restart in PostgreSQL

We come up with a scenario where we want to know if a parameter needs restart or reload. So this article will describe how we can know which parameter needs what. Below are few parameter which needs restart. 

  1. Archive_mode

  2. Archive_level

  3. Port

  4. Connections

  5. Shared_buffer

  6. Max_wal_sender

Some of the below which needs reload:

  1. Archive_command

  2. Pg_min_log_statement

  3. Work_mem

  4. maintenance_work_mem

But it is difficult to remember all the parameter, therefore there is a trick. When we issue the below command and if the result is "postmaster", it means it needs reboot. And if the result is "sighup", it means it needs reload. Also, we can also know if any parameter there is a pending reboot. Below are the example commands -

1. This command would give the context if the parameter needs reboot.
SQL> select context from pg_settings where name = 'work_mem';

2. The below command would say us if the pending reboot is pending for the parameter.

SQL> select pending_restart from pg_settings where name = 'work_mem';

Below are some of the example commanfs for some of the parameter -

postgres=# select context from pg_settings where name ='work_mem';

 context

---------

 user

postgres=# select context from pg_settings where name ='port';

  context

------------

 postmaster

(1 row)

postgres=# select context from pg_settings where name ='archive_command';

 context

---------

 sighup

(1 row)

postgres=# select context from pg_settings where name ='archive_mode';

  context

------------

 postmaster

(1 row)