Sunday, October 17, 2010

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;
psql=# set statement_timeout=10;
psql=#  show statement_timeout;
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:

Leave a Reply