Sunday, October 17, 2010

Modify the value of DB parameters in Postgresql

0 comments
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:

Leave a Reply

Labels