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's true.

• Check context in pg_settings – if it's postmaster, then a restart is needed, otherwise it's not (small exception: values internal – 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.