From the objective of OSS-DB Exam Silver
- Operation management - configuration file
Sample Question
1.55
Choose the correct statement for changing PostgreSQL's configuration.
-
When changing the client side character set to UTF 8, as a SQL statement
SET client_encoding = utf 8;
, Or by using the meta command of psql
\set client_encoding = utf 8
, The effect is almost the same. -
SET LOCAL changes only the setting of the client that executed it, but when not using the LOCAL keyword, the settings of all clients connected to the server are changed.
- Use UNSET to reset what you changed with SET.
-
To check the contents changed by SET, use SHOW.
-
In order to reflect the changed contents in SET, the following must be executed.
pg_ctl reload or pg_ctl restart
※This sample exam is different from those that appear in the actual OSS-DB Exam.
2019/05/30
Answer and Explanation
PostgreSQL configuration parameters are set together in a file named postgresql.conf in the directory of the database cluster.
Several parameters can be changed using the SET statement after connecting to the database using psql etc. There is a metacommand called \set in psql, but you can set with the \set meta command is the value of the internal variable of psql, so be careful not to get confused as it is completely different from the SET statement.
The SET statement is like as below.
SET [option] variable = value; or
SET [option] variable TO value;
For option, you can specify SESSION or LOCAL. In case of SESSION (default), changes in the SET statement are effective only in the session of the connected client.
If LOCAL, the SET change is effective only within the transaction being executed. In other words, if you execute COMMIT or ROLLBACK and end the transaction, the settings will be restored.
The contents changed by SET will be restored when RESET is executed.
The contents changed with SET can be checked with SHOW.
Even if you change the setting with SET, it is confined within that session (or transaction), and not all configuration parameters can be changed with SET. Permanent setting change and parameter change which can not be changed by SET are done by changing the setting file called postgresql.conf, but in this case, in order to reflect the setting change by using below and to reload the file on the server pg_ctl reload or It is necessary to restart the server by using pg_ctl restart
Whether it is necessary to restart for reflection depends on the parameters. Of course, this is written by referring to the manual, but it is also written as a comment in the postgresql.conf file.
Therefore, the correct answer is D.