Modify the value of DB parameters in Postgresql

Often for different purposes we need to modify the value of database parameters.
Following command can be used from psql console to change the value of a parameter.

$psql –d bench_replication –U newuser
psql=#  show statement_timeout;
0ms
psql=# set statement_timeout=10;
psql=#  show statement_timeout;
10ms

Sometimes we need to restart the DB server after parameter’s value modification to take effect and sometimes a sighup signal is enough.  This is easily be identified when we need to restart DB server based on the context of the parameter.

Postgresql DB parameters can be categorized into 6 types on the basis of context and these are:

• - user
• - superuser
• - sighup
• - backend
• - postmaster
• - internal

Following query gives us the context of a parameter:

Select context
from pg_settings
where name = <parameter_name>

Following table shows us when we need to restart the DB server in order to take effect the parameter modification: