PostgreSQL 9.4 has added a new SQL command called ALTER SYSTEM SET, introduced by this commit:
commit 65d6e4cb5c62371dae6c236a7e709d503ae6ddf8
Author: Tatsuo Ishii
Date: Wed Dec 18 23:42:44 2013 +0900
Add ALTER SYSTEM command to edit the server configuration file.
Patch contributed by Amit Kapila. Reviewed by Hari Babu, Masao Fujii,
Boszormenyi Zoltan, Andres Freund, Greg Smith and others.
The feature that this commit introduces is really powerful (as much as dangerous): it offers the possibility to set configuration parameters, the GUC parameters of server, directly with a SQL query.
The parameter values that this command changes are located in a new system file called postgresql.conf.auto located at the root of data folder. This file is created by initdb, has its data uploaded on server and persists with the server.
$ cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
So basically avoid to modify it manually as this is used for ALTER SYSTEM. When a parameter is modified, a temporary file called postgresql.auto.conf.temp is created to rollback to the original state in case of error.
Note that you can delete this file while server is running as well and still run the ALTER SYSTEM commands.
$ rm postgresql.auto.conf
$ psql -c "ALTER SYSTEM SET shared_buffers TO '400MB'"
ALTER SYSTEM
$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
shared_buffers = '400MB'
Using TO DEFAULT to reset a parameter removes the entry from postgresql.auto.conf to keep it readable.
Deleting this file and restarting server results in an harmless LOG message telling that the configuration file is skipped.
$ rm postgresql.auto.conf
$ pg_ctl restart
waiting for server to shut down....DEBUG: logger shutting down
done
server stopped
server starting
LOG: skipping missing configuration file "/to/pgdata/postgresql.auto.conf"
LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
Note also that the file is not recreated immediately and that the first ALTER SYSTEM command will do the job.
Parameters modified with ALTER SYSTEM SET are effective on server after either reloading parameters or restarting server, depending on the parameters types, like PGC_SIGHUP or PGC_POSTMASTER. And of course, some parameters like the recently-added data_checksums that cannot be changed after server initialization, cannot be set. For example, in the case of work_mem (not really recommended to avoid system-wide plan changes for the queries running but…):
=# ALTER SYSTEM SET work_mem TO '10MB';
ALTER SYSTEM
=# \! cat $PGDATA/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
work_mem = '10MB'
=# SHOW work_mem;
work_mem
----------
1MB
(1 row)
The parameter is set but its new value will be visible on server only after reloading it.
=# SELECT pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
=# SHOW work_mem;
work_mem
----------
10MB
(1 row)
An important thing to know is that server gives the priority to postgresql.auto.conf and not postgresql.conf.
=# \! cat $PGDATA/postgresql.auto.conf | grep -v "\#"
work_mem = '10MB'
=# \! cat $PGDATA/postgresql.conf | grep -v "\#" | grep work_mem
work_mem = 1MB
=# SHOW work_mem;
work_mem
----------
10MB
(1 row)
As you might have guessed already, postgresql.auto.conf has the priority on parameter values even for configuration files included in postgresql.conf with ‘include’ and friends.
Now let’s finish with a direct application of this feature: the possibility to switch synchronous standby nodes directly with SQL. This can simply be done by playing with synchronous_standby_names.
=# SELECT application_name, sync_state FROM pg_stat_replication;
application_name | sync_state
------------------+------------
node_5433 | async
(1 row)
=# ALTER SYSTEM SET synchronous_standby_names TO 'node_5433';
ALTER SYSTEM
=# select pg_reload_conf();
pg_reload_conf
----------------
t
(1 row)
=# SELECT application_name, sync_state FROM pg_stat_replication;
application_name | sync_state
------------------+------------
node_5433 | sync
(1 row)
This case has been mentioned on pgsql-hackers, called standby degradation. Its opposite, the switch sync-to-async or standby elevation (?) is possible as well.