How to change a Postgres parameter
Follow these steps if you need to change a Postgres parameter (a.k.a., GUC, Grand Unified Configuration) for permanent effect.
Docs: Setting Parameters
1) Understand if a restart is needed
Two ways to quickly check if a restart is needed:
Use postgresqlco.nf and look at the
Restart: xxx
field. For example, for max_wal_size,Restart: false
, while for shared_buffers, it'strue
.Check
context
inpg_settings
– if it'spostmaster
, then a restart is needed, otherwise it's not (small exception: valuesinternal
– such parameters cannot be changed at all). For example:nik=# select name, context
from pg_settings
where name in ('max_wal_size', 'shared_buffers');
name | context
----------------+------------
max_wal_size | sighup
shared_buffers | postmaster
(2 rows)
2) Perform the change
Apply the change in Postgres config files (postgresql.conf
or its dependencies, if include
directive is used). It's
advisable to ALTER SYSTEM
unless necessary, because it might lead to confusion in the future (it writes
to postgresql.auto.conf
and later it can be easily overlooked; see also
this discussion)
3) Apply the change
If a restart is required, restart Postgres. If you forget to do it, you can later detect the situation of un-applied
changes by looking at pending_restart
in pg_settings
.
If a restart is not needed, execute under a superuser:
select pg_reload_conf();
Alternatively, you can use one of these methods:
pg_ctl reload $PGDATA
send a
SIGHUP
to the postmaster process, e.g.:kill -HUP $(cat "${PGDATA}/postmaster.pid")
When a non-restart-required change is applied, you'll see something like this in the Postgres log:
LOG: received SIGHUP, reloading configuration files
LOG: parameter "max_wal_size" changed to "2GB"
4) Verify the change
Use SHOW
or current_setting(...)
to ensure that the change is applied, e.g.:
nik=# show max_wal_size;
max_wal_size
--------------
2GB
(1 row)
or
nik=# select current_setting('max_wal_size');
current_setting
-----------------
2GB
(1 row)
Bonus: database-, user-, and table-level settings
Settings with context
in pg_settings
being user
or superuser
can be adjusted at database or user level, e.g.:
alter database verbosedb set log_statement = 'all';
alter user hero set statement_timeout = 0;
The result of this can be reviewed by looking at pg_db_role_setting
:
nik=# select * from pg_db_role_setting;
setdatabase | setrole | setconfig
-------------+---------+-----------------------
24580 | 0 | {log_statement=all}
0 | 24581 | {statement_timeout=0}
(2 rows)
Some settings can also be adjusted at individual table level, when using CREATE TABLE
or ALTER TABLE
, see
CREATE TABLE / Storage Parameters.
Keep in mind naming deviation: autovacuum_enabled
enables or disables the autovacuum daemon for a particular table,
while the global setting name is simply autovacuum
.