Postgres' parameters have an associated context, which determines when that parameter can be changed.
You can see the context of every parameter using the following query. A sample output is also shown.
select name, context from pg_settings order by category;
name | context
---------------------------------+------------
autovacuum_freeze_max_age | postmaster
autovacuum_max_workers | postmaster
autovacuum_vacuum_threshold | sighup
...
IntervalStyle | user
...
server_encoding | internal
...
lc_messages | superuser
...
local_preload_libraries | backend
...
The possible values of context are:
- internal (called
PGC_INTERNAL
in source code) - postmaster (
PGC_POSTMASTER
) - sighup (
PGC_SIGHUP
) - backend (
PGC_BACKEND
) - superuser (
PGC_SUSET
) - user (
PGC_USERSET
)
The above list is in order of when a parameter can be set; if a parameter can be changed in a certain context, then it can be changed at any of the earlier contexts as well.
internal
The internal
parameters cannot be changed; these are usually compile-time constants. If you want to change any of these, you’ll have to change it in Postgres source code and compile a new set of Postgres executables.
postmaster
The postmaster
parameters can be set at Postgres startup, or during source code compilation. (Postmaster is the parent process of all the Postgres processes, hence the context’s name).
These parameters can be set in the postgresql.conf
file or on the command-line when starting the Postgres server.
sighup
The sighup
parameters can be changed while the server is running, at Postgres startup, or during code compilation.
To change such a parameter, you can change it in the postgresql.conf
file and send a SIGHUP
signal to the Postmaster process. An easy way to send the SIGHUP
signal to the Postmaster process is to use pg_ctl
or your distribution’s Postgres-init script, like so:
pg_clt -D $PGDATA reload
OR
sudo service postgresql-9.3 reload
backend
The backend
parameters can be changed/set while making a new connection to Postgres, and never after that (and these can be changed by SIGHUP
, at Postgres startup, or during code compilation).
Usually the applications set these parameters while making the initial connection.
An example is the local_preload_libraries
parameter. Say, if you want to try a plugin for just one session, then you can initiate a psql
session, with that plugin loaded for the connection, like so:
PGOPTIONS="-c local_preload_libraries=my_plugin" psql
The above method of changing parameters is possible for any application that uses libpq
library to connect to Postgres (for eg. pgAdmin), since the PGOPTIONS
environment variable is recognized and honored by libpq
. Other applications/libraries may have their own methods to allow changing parameters during connection initiation.
superuser
To change a superuser
parameter, one needs to have superuser
privileges in Postgres. These parameters can be changed while a session is in progress, during a backend startup, using SIGHUP
, at Postgres startup, or during code compilation.
Note that normal users cannot change these parameters.
user
The parameters with user
context can be changed by any user, at any time, to affect the current session they are connected to. Needless to say that since this is the last context in the list, a parameter that is marked as user
context, can be changed using any of the methods shown for the other contexts.
The SET
command can be used to change a user
context parameter’s value, for eg.:
SET work_mem = '32 MB';
Using context in queries
Although, as explained above, there is a certain order in the values of context
, there is no built-in way for one to see this order, and exploit that knowledge using queries.
Say, if one wants to see a list of all parameters that cannot be changed by a normal user, there’s no straightforward way to do it. To that end, I create the following enum
type and use it in queries to extract that information easily:
create type guc_context as enum (
'internal',
'postmaster',
'sighup',
'backend',
'superuser',
'user');
select name as cannot_be_changed_by_user, context
from pg_settings
where context::guc_context < 'user';
Other useful information that can now be easily extracted using this enum
:
select name as parameter,
context_enum > 'internal' as can_be_changed,
context_enum = 'postmaster' as change_requires_restart,
context_enum >= 'sighup' as can_be_changed_by_reload
from (select name, context::guc_context as context_enum
from pg_settings) as v;
parameter | can_be_changed | change_requires_restart | can_be_changed_by_reload
---------------------------------+----------------+-------------------------+--------------------------
allow_system_table_mods | t | t | f
application_name | t | f | t
archive_command | t | f | t
archive_mode | t | t | f
archive_timeout | t | f | t
array_nulls | t | f | t
authentication_timeout | t | f | t
...