Sometimes I need to change a parameter programmatically. The easiest way is to set it via psql, e.g.:

psql -c "set autovacuum = off"

However sometimes this fails with, e.g.:

ERROR: parameter "autovacuum" cannot be changed now

The only supported way to do this is to modify the postgresql.conf file and signal the postmaster. So, I use the following script which sets a specified parameter. It is a bit limited in that it won’t work on commented out parameters or if they do not already exist in the config file but it’s good enough for my purposes.

. ~/.bash_profile
PARM=$1
VALUE=$2
PGCONF=$PGDATA/postgresql.conf
cat $PGCONF | sed "s/^\($PARM = \)[0123456789offn]\+/\1$VALUE/" > $PGCONF.2
mv $PGCONF.2 $PGCONF
pg_ctl reload

This script relies on .bash_profile to setup $PGDATA and paths to binaries/libraries. It basically searches the config file for the parameter at the start of the line and replaces anything after the = sign.

Call it like:

db_setparm.sh autovacuum off

Download the db_setparm.sh script.