On 18th of December, Tatsuo Ishii committed patch:

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.

On the next day, Fujii Masao committed patch:

Add tab completion for ALTER SYSTEM SET in psql.

For the long time, there were talks about changing system parameters using normal SQL connections. Some of the parameters you could actually change – per session, user, database, like:

SET client_min_messages = FATAL;

But there wasn't a way to set new value that would be cluster-wide. That would require editing postgresql.conf.

Now, we have a tool ALTER SYSTEM.

How does it work? Let's start with how it doesn't:

$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 0
(1 ROW)
 
$ ALTER system SET log_min_duration_statement = 100;
ALTER SYSTEM
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 0
(1 ROW)

Why? Well, it's simple. ALTER SYSTEM stores new config values in file, but doesn't (automatically) load it. So we have to reload config:

$ SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 ROW)
 
$ SHOW log_min_duration_statement ;
 log_min_duration_statement
----------------------------
 100ms
(1 ROW)

OK, and where is it stored?

In $PGDATA/postgresql.auto.conf. Which looks like this:

=$ cat $PGDATA/postgresql.auto.conf
# Do NOT edit this file manually! 
# It will be overwritten BY ALTER SYSTEM command. 
log_min_duration_statement = '100'

We can also check where the setting is set:

$ SELECT * FROM pg_settings WHERE name = 'log_min_duration_statement';
-[ RECORD 1 ]----------------------------------------------------------------------
name       | log_min_duration_statement
setting    | 100
unit       | ms
category   | Reporting AND Logging / WHEN TO Log
short_desc | Sets the minimum execution TIME above which statements will be logged.
extra_desc | Zero prints ALL queries. -1 turns this feature off.
context    | superuser
vartype    | INTEGER
SOURCE     | configuration file
min_val    | -1
max_val    | 2147483647
enumvals   | [NULL]
boot_val   | -1
reset_val  | 100
sourcefile | /home/pgdba/DATA/postgresql.auto.conf
sourceline | 3

Now. The cool thing is that it allows changing settings that require restart, but in a rather nice way. For example. Previously, if you wanted to change max_connections, you had to edit postgresql.conf. And if you made a typo …

=$ perl -pi -e 's/^max_connections.*/max_connections = oops typo/' $PGDATA/postgresql.conf
 
=$ pg_ctl restart
waiting for server to shut down.... done
server stopped
server starting
LOG:  syntax error in file "/home/pgdba/data/postgresql.conf" line 64, near token "typo"
FATAL:  configuration file "/home/pgdba/data/postgresql.conf" contains errors

And you end up with dead Pg. Which is of course trivial to fix. If you're awake. And you knew about the change. And you know Pg.

Now, with the alter system, we can:

$ ALTER system SET max_connections = 'oops typo';
ERROR:  invalid VALUE FOR parameter "max_connections": "oops typo"

And we immediately see the problem. Even before the change will be parsed by config reader!

And when we'll finally manage to change the config properly:

$ ALTER system SET max_connections = '200';
ALTER SYSTEM

We can just tell night shift to restart Pg, and it will come up, with proper value set. Now, isn't that great?

Anyway. After all these discussions about syntax, form, method and so on – I am very grateful to the guys who made it happen. Thanks a lot.