PostgreSQL SET session

PostgreSQL 에 클라이언트로 접속 후,
SET 명령어를 통해 환경설정 값을 변경할 수 있다.

변경을 하면, 현재 자신이 연결된 session 에만 영향을 준다.
다른 session 에는 영향을 주지 않는다.
물론, session 이 close 되면, 환경설정 값들은 사라지게 된다.

session 으로 연결된 상태에서 환경설정 값을 변경할 수 있다.
모든 환경설정이 다 되는 것은 아니다.

context 가 superuser 혹은 user 인 환경설정만 가능하다.
superuser 인 것은 superuser 권한이 있어야 한다.

postgres=# select name,setting,context from pg_settings where context = 'superuser' or context = 'user';

set 으로 변경이 불가능한 환경설정인 경우에는 에러가 발생한다.

ERROR:  parameter "allow_system_table_mods" cannot be changed without restarting the server

set 으로 변경 가능한 환경설정 중에서 'wal_compression' 을 변경해 보자.

현재 off 값이 셋팅되어 있다.
3가지 방법으로 확인할 수 있다.
3가지 방법 중 원하는 방법으로 확인하면 된다.

postgres=# select name,setting,context from pg_settings where name = 'wal_compression';

postgres=# show wal_compression;

postgres=# select current_setting('wal_compression');

postgres=# set session wal_compression = on;

postgres=# select name,setting,context from pg_settings where name = 'wal_compression';

postgres=# show wal_compression;

postgres=# select current_setting('wal_compression');

set session 말고, set local 옵션도 있다.
set local 은 transaction 안에서만 사용할 수 있고, transaction 안에서만 유효하다.
transaction 안에서만 사용할 수 있다고 메세지가 나온다.

transaction 안에서만 사용 가능하고, transaction 이 끝나면 다시 원래대로 돌아온다.

transaction 안에서 set session 을 수행하면, commit 여부에 따라 결과가 달라진다.
rollback 을 수행하면 적용되지 않는다.

commit 을 수행하면 적용된다.

※ set 다음에 session, local 을 생략할 수 있다. 생략을 하면 session 으로 동작하게 된다.

set 대신에 set_config() 를 사용할 수 있다.
함수 원형은 아래와 같다.
set_config(setting_name, new_value, is_local)

세번째 인자 값에 따라 결과가 달라진다.

출처
https://www.postgresql.org/docs/10/sql-set.html
https://www.postgresql.org/docs/10/sql-show.html
https://www.postgresql.org/docs/10/config-setting.html
https://www.postgresql.org/docs/10/runtime-config.html
https://www.postgresql.org/docs/10/functions-admin.html

PostgreSQL pg_settings

pg_settings 는 환경 설정된 값들의 리스트를 출력해 준다.

View pg_catalog.pg_settings 형태로 저장되어 있다.

내용을 좀 더 자세히 보면, pg_show_all_settings() 을 통해서 정보를 가져오고 있다.

pg_settings 의 각 항목에 대한 상세 내용이다.

name	text	파라미터 이름
setting	text	현재 설정된 값
unit	text	설정된 값의 단위 min, ms, 8kB, s, MB, kB 같은 단위 형태로 설정되어 있다. 사용자가 설정할 수 없다. "src/backend/utils/misc/guc.c" 파일에 각 파라미터 별로 단위가 설정되어 있다. "src/include/utils/guc.h" 파일에 define 형태로 정의되어 있다. ex) shared_buffers 의 unit 이 8kB 이고, postgresql.conf 에 shared_buffers 를 128MB 로 설정했다면, 128MB=16384*8kB 이므로, setting 에는 16384 가 표시된다.
category	text	아래와 같이, 어떠한 분류에 속하는지 표기해 준다. Resource Usage / Cost-Based Vacuum Delay Autovacuum Query Tuning / Planner Method Configuration Query Tuning / Other Planner Options Client Connection Defaults / Statement Behavior File Locations Write-Ahead Log / Settings Process Title Client Connection Defaults / Shared Library Preloading Replication Replication / Subscribers Client Connection Defaults / Locale and Formatting Reporting and Logging / What to Log Write-Ahead Log / Checkpoints Replication / Master Server Reporting and Logging / When to Log ... ... ... "src/backend/utils/misc/guc.c" 파일에 각 파라미터 별로 단위가 설정되어 있다.
short_desc	text	간단한 설명
extra_desc	text	자세한 설명 (null 인 경우도 있음)
context	text	변경 가능한 경우를 분류한다. (1) internal 변경 불가능 하다. 변경하기 위해서는 configuration을 다시 하거나, initdb에 제공된 옵션을 변경해야 한다. (2) postmaster 서버가 시작될 때만 적용된다. 변경하려면 postgresql.conf 수정 후, 서버를 다시 시작해야한다. 혹은 서버 시작시 명령 줄에서 전달할 수 있다. (3) sighup postgresql.conf를 다시 읽고 변경 사항을 적용하게 한다. "pg_ctl reload" 혹은 "select pg_reload_conf()" 을 수행하면 된다. "server signaled" 라고 나오면 정상적으로 수행된 것이다. 동작 방식은 postmaster에게 SIGHUP 신호를 보내 postgresql.conf를 다시 읽고 변경 사항을 적용하게 한다. kill -1 `head -1 $PGDATA/postmaster.pid` 동일한 효과를 발휘한다. (4) superuser-backend postgresql.conf를 다시 읽고 변경 사항을 적용하게 한다. "pg_ctl reload" 혹은 "select pg_reload_conf()" 을 수행하면 된다. 연결 요청 패킷의 특정 세션 (예 : libpq의 PGOPTIONS 환경 변수를 통해)에 설정할 수 있지만 연결하는 사용자가 수퍼 유저 인 경우에만 가능하다. (5) backend postgresql.conf를 다시 읽고 변경 사항을 적용하게 한다. "pg_ctl reload" 혹은 "select pg_reload_conf()" 을 수행하면 된다. 연결 요청 패킷의 특정 세션 (예 : libpq의 PGOPTIONS 환경 변수를 통해)에 설정할 수 있다. (6) superuser SET 명령을 통해 세션에서 설정할 수 있다. superuser만 SET 명령을 통해 변경할 수 있다. postgresql.conf의 변경 사항은 SET과 세션 로컬 값이 설정되지 않은 경우에만 기존 세션에 영향을 준다. (7) user SET 명령을 통해 세션에서 설정할 수 있다. 모든 사용자는 세션 로컬 값을 변경할 수 있다. postgresql.conf의 변경 사항은 SET과 세션 로컬 값이 설정되지 않은 경우에만 기존 세션에 영향을 준다.
vartype	text	타입 형태를 나타낸다. (1) enum (2) string (3) bool (1bytes) (4) integer (4bytes) (5) real (floating point number 4 bytes)
source	text	어디에서 설정되었는지 나타낸다. (1) default 설정하지 않았을 경우 기본적으로 설정된 값 (2) configuration file postgresql.conf에 의해 설정한 값 (3) environment variable OS의 export 명령어를 통해 설정한 값 (4) client client가 접속하면서 설정한 값 (5) override (6) command line postgres 실행시 옵션으로 설정한 값 (7) "global" (8) "database" (9) "user" (10) "database user" (11) "interactive" (12) "test" (13) "session"
min_val	text	설정 가능한 최소 값
max_val	text	설정 가능한 최대 값
enumvals	text[]	enum type 일 경우, 설정 가능한 값 리스트
boot_val	text	server start 할 때 설정된 값
reset_val	text	현재 session에서 설정된 값
sourcefile	text	설정한 configure 파일 이름
sourceline	integer	설정한 configure 파일 라인
pending_restart	boolean	configure 파일에서 값이 변경되었지만, 다시 시작해야하는 경우 true, 그렇지 않으면 false.

https://www.postgresql.org/docs/10/runtime-config.html
이곳에 환경 설정에 대한 자세한 설명 및 분류가 잘되어 있다.



출처
https://www.postgresql.org/docs/10/view-pg-settings.html
https://www.postgresql.org/docs/10/runtime-config.html

PostgreSQL pg_file_settings

pg_file_settings 는 $PGDATA/postgresql.conf 에 설정된 값들의 리스트를 출력해 준다.

위 11개 항목이 실제로, $PGDATA/postgresql.conf 에 설정된 값들이다.
% cat postgresql.conf | grep -n -v ^[[:space:]]*$*#

postgres=# \d pg_file_settings;
view 형태로 되어 있다.

sourcefile	property를 설정한 환경설정 파일 이름
sourceline	property를 설정한 환경설정 파일 라인
seqno	sequence number 순서 번호
name	property 이름
setting	설정된 값
applied	설정 성공 여부
error	설정 실패시 원인

출처
https://www.postgresql.org/docs/10/view-pg-file-settings.html