PostgreSQL for DBA - 작성중

PostgreSQL을 관리하는 DBA를 위한 쿼리다.

1. 역할 생성

create role dbmaster01 with password 'somepassword' login;   
grant pg_monitor to dbmaster01;   

2. 현재 파라미터 설정과 기본 값 표시

create role dbmaster01 with password 'somepassword' login;   
grant pg_monitor to dbmaster01;   

★ 정적 파라미터를 적용하려면 DB 인스턴스를 재부팅해야 하고, 동적 파라미터는 바로 적용 가능하다. 아래 표는 PostgreSQL 인스턴서에서 변경 가능한 파라미터들 이다.

파라미터 이름	적용 유형	설명
application_name	동적	애플리케이션 이름이 통계 및 로그에 표시되도록 설정합니다.
array_nulls	동적	어레이의 NULL 요소 입력을 활성화합니다.
authentication_timeout	동적	클라이언트 인증 완료를 위한 최대 허용 시간을 설정합니다.
autovacuum	동적	autovacuum 서브프로세스를 시작합니다.
autovacuum_analyze_scale_factor	동적	분석 전 삽입, 업데이트 또는 삭제되는 튜플 수를 reltuples 분수 값으로 지정합니다.
autovacuum_analyze_threshold	동적	분석 전 삽입, 업데이트 또는 삭제되는 튜플의 최소 수를 지정합니다.
autovacuum_naptime	동적	autovacuum 실행 간 절전 시간을 지정합니다.
autovacuum_vacuum_cost_delay	동적	autovacuum에서 vacuum 코스트 지연 시간(밀리초)을 지정합니다.
autovacuum_vacuum_cost_limit	동적	autovacuum에서 지연 시간 이전에 이용 가능한 vacuum 코스트 값을 지정합니다.
autovacuum_vacuum_scale_factor	동적	vacuum 전 업데이트 또는 삭제되는 튜플 수를 reltuples 분수 값으로 지정합니다.
autovacuum_vacuum_threshold	동적	vacuum 전 업데이트 또는 삭제되는 튜플의 최소 수를 지정합니다.
backslash_quote	동적	문자열 리터럴에서 백슬래시(\)의 허용 여부를 설정합니다.
bgwriter_delay	동적	라운드 사이에 백그라운드 라이터의 절전 시간을 지정합니다.
bgwriter_lru_maxpages	동적	백그라운드 라이터가 라운드마다 LRU 페이지를 작성할 최대 수를 지정합니다.
bgwriter_lru_multiplier	동적	라운드마다 해제할 평균 버퍼 사용량의 배수를 지정합니다.
bytea_output	동적	bytea의 출력 형식을 설정합니다.
check_function_bodies	동적	CREATE FUNCTION 도중 함수 본문을 검사합니다.
checkpoint_completion_target	동적	체크포인트 도중 변경된 버퍼 플러시에 사용된 시간으로 체크포인트 간격의 분수 값입니다.
checkpoint_segments	동적	로그 세그먼트에서 자동 WAL 체크포인트의 최대 간격을 설정합니다.
checkpoint_timeout	동적	자동 WAL 체크포인트 사이의 최대 시간을 설정합니다.
checkpoint_warning	동적	체크포인트 세그먼트가 이 파라미터 값보다 더 빨리 채워지는 경우 경고를 활성화합니다.
client_encoding	동적	클라이언트 문자 세트 인코딩을 설정합니다.
client_min_messages	동적	클라이언트에게 보여지는 메시지 수준을 설정합니다.
commit_delay	동적	트랜잭션 커밋부터 디스크에 대한 WAL 플러시까지 지연 시간(밀리초)을 설정합니다.
commit_siblings	동적	commit_delay 실행 전에 동시에 열려 있는 트랜잭션 최소 개수를 설정합니다.
constraint_exclusion	동적	planner가 제약 조건을 사용하여 쿼리를 최적화하도록 활성화합니다.
cpu_index_tuple_cost	동적	인덱스 스캔 중 각 인덱스 항목을 처리하는 데 따른 planner의 예상 코스트를 설정합니다.
cpu_operator_cost	동적	각 연산자 또는 함수 호출을 처리하는 데 따른 planner의 예상 코스트를 설정합니다.
cpu_tuple_cost	동적	각 튜플(행)을 처리하는 데 따른 planner의 예상 코스트를 설정합니다.
cursor_tuple_fraction	동적	planner가 예상하는 검색할 커서 행의 분수 값을 설정합니다.
datestyle	동적	날짜와 시간 값에 대한 표시 형식을 설정합니다.
deadlock_timeout	동적	교착 상태 여부를 확인하기 이전 잠금 대기 시간을 설정합니다.
debug_pretty_print	동적	구문과 실행 계획 트리를 들여쓰기 하여 표시합니다.
debug_print_parse	동적	각 쿼리의 구문 분석 트리를 기록합니다.
debug_print_plan	동적	각 쿼리의 실행 계획을 기록합니다.
debug_print_rewritten	동적	각 쿼리에서 재작성된 구문 분석 트리를 기록합니다.
default_statistics_target	동적	기본 통계 대상을 설정합니다.
default_tablespace	동적	테이블과 인덱스를 생성할 기본 테이블스페이스를 설정합니다.
default_transaction_deferrable	동적	새로운 트랜잭션의 기본 deferrable 상태를 설정합니다.
default_transaction_isolation	동적	새로운 트랜잭션마다 트랜잭션 격리 수준을 설정합니다.
default_transaction_read_only	동적	새로운 트랜잭션의 기본 읽기 전용 상태를 설정합니다.
default_with_oids	동적	새로운 테이블을 생성할 때 OID가 기본적으로 포함됩니다.
effective_cache_size	동적	디스크 캐시 크기에 대한 planner의 가정을 설정합니다.
effective_io_concurrency	동적	디스크 하위 시스템에서 효율적으로 동시에 처리할 수 있는 요청 수를 지정합니다.
enable_bitmapscan	동적	planner가 비트맵 스캔 계획을 사용할 수 있도록 활성화합니다.
enable_hashagg	동적	planner가 해시된 집계 계획을 사용할 수 있도록 활성화합니다.
enable_hashjoin	동적	planner가 해시 조인 계획을 사용할 수 있도록 활성화합니다.
enable_indexscan	동적	planner가 인덱스 스캔 계획을 사용할 수 있도록 활성화합니다.
enable_material	동적	planner가 구체화를 사용할 수 있도록 활성화합니다.
enable_mergejoin	동적	planner가 병합 조인 계획을 사용할 수 있도록 활성화합니다.
enable_nestloop	동적	planner가 중첩 루프 조인 계획을 사용할 수 있도록 활성화합니다.
enable_seqscan	동적	planner가 순차적 스캔 계획을 사용할 수 있도록 활성화합니다.
enable_sort	동적	planner가 명시적 정렬 단계를 사용할 수 있도록 활성화합니다.
enable_tidscan	동적	planner가 TID 스캔 계획을 사용할 수 있도록 활성화합니다.
escape_string_warning	동적	일반 문자열 리터럴의 백슬래시(\) 이스케이프에 대해 경고합니다.
extra_float_digits	동적	부동 소수점으로 표시할 자릿수를 설정합니다.
from_collapse_limit	동적	서브 쿼리가 축소되지 않는 FROM 목록 크기를 설정합니다.
fsync	동적	업데이트를 디스크와 강제로 동기화합니다.
full_page_writes	동적	체크포인트 후 최초 변경 시 전체 페이지를 WAL에 기입합니다.
geqo	동적	유전적 쿼리 최적화를 활성화합니다.
geqo_effort	동적	GEQO: 다른 GEQO 파라미터의 기본값을 설정하는 데 사용됩니다.
geqo_generations	동적	GEQO: 알고리즘의 반복 횟수입니다.
geqo_pool_size	동적	GEQO: 모집단의 개체 수입니다.
geqo_seed	동적	GEQO: 무작위 경로 선택을 위한 시드(seed)를 지정합니다.
geqo_selection_bias	동적	GEQO: 모집단 내 선택적 압력을 지정합니다.
geqo_threshold	동적	GEQO가 사용되는 FROM 항목의 임계값을 설정합니다.
gin_fuzzy_search_limit	동적	정확한 GIN 기준 검색에 허용되는 최대 결과 수를 설정합니다.
hot_standby_feedback	동적	핫 스탠바이가 피드백 메시지를 기본 또는 업스트림 스탠바이로 전송하는지 여부를 결정합니다.
intervalstyle	동적	간격 값에 대한 표시 형식을 설정합니다.
join_collapse_limit	동적	JOIN 구문이 결합되지 않는 FROM 목록 크기를 설정합니다.
lc_messages	동적	메시지 표시 언어를 설정합니다.
lc_monetary	동적	통화 금액의 형식으로 사용할 로캘을 설정합니다.
lc_numeric	동적	숫자의 형식으로 사용할 로캘을 설정합니다.
lc_time	동적	날짜와 시간 값의 형식으로 사용할 로캘을 설정합니다.
log_autovacuum_min_duration	동적	autovacuum 작업이 기록되는 최소 실행 시간을 설정합니다.
log_checkpoints	동적	각 체크포인트를 기록합니다.
log_connections	동적	성공한 연결을 모두 기록합니다.
log_disconnections	동적	지속 시간을 포함해 세션 종료를 기록합니다.
log_duration	동적	완료된 개별 SQL 문의 지속 시간을 기록합니다.
log_error_verbosity	동적	기록된 메시지의 세부 사항을 설정합니다.
log_executor_stats	동적	실행기 성능 통계를 서버 로그에 기록합니다.
log_filename	동적	로그 파일의 이름 패턴을 설정합니다.
log_hostname	동적	연결 로그에 호스트 이름을 기록합니다.
log_lock_waits	동적	오랜 잠금 대기 시간을 기록합니다.
log_min_duration_statement	동적	문이 기록되는 최소 실행 시간을 설정합니다.
log_min_error_statement	동적	이 수준 이상으로 오류 원인이 되는 모든 문을 기록합니다.
log_min_messages	동적	기록되는 메시지 수준을 설정합니다.
log_parser_stats	동적	구문 분석기 성능 통계를 서버 로그에 기록합니다.
log_planner_stats	동적	planner 성능 통계를 서버 로그에 기록합니다.
log_rotation_age	동적	N분 후에 자동 로그 파일 로테이션이 일어납니다.
log_rotation_size	동적	N킬로바이트 후에 자동 로그 파일 로테이션이 일어납니다.
log_statement	동적	기록할 문 유형을 설정합니다.
log_statement_stats	동적	누적 성능 통계를 서버 로그에 기록합니다.
log_temp_files	동적	이 킬로바이트 수치보다 큰 임시 파일의 사용을 기록합니다.
maintenance_work_mem	동적	유지 관리 작업에 사용할 최대 메모리를 설정합니다.
max_stack_depth	동적	최대 스택 깊이(KB)를 설정합니다.
max_standby_archive_delay	동적	핫 스탠바이 서버가 아카이브 WAL 데이터를 처리할 때 쿼리 취소까지 걸리는 최대 지연 시간을 설정합니다.
max_standby_streaming_delay	동적	핫 스탠바이 서버가 스트리밍 WAL 데이터를 처리할 때 쿼리 취소까지 걸리는 최대 지연 시간을 설정합니다.
quote_all_identifiers	동적	SQL 조각 생성 시 모든 식별자에 인용 부호(")를 추가합니다.
random_page_cost	동적	비순차적으로 가져온 디스크 페이지에 대한 planner의 예상 코스트를 설정합니다.
rds.log_retention_period	동적	Amazon RDS가 N분 이상 지난 PostgreSQL 로그를 삭제합니다.
search_path	동적	스키마로 한정되지 않은 이름의 스키마 검색 순서를 설정합니다.
seq_page_cost	동적	순차적으로 가져온 디스크 페이지에 대한 planner의 예상 코스트를 설정합니다.
session_replication_role	동적	트리거 및 다시 쓰기 규칙에 대한 세션 동작을 설정합니다.
sql_inheritance	동적	다양한 명령에서 서브테이블이 기본적으로 상속됩니다.
ssl_renegotiation_limit	동적	암호화 키를 재협상하기 전에 전송 및 수신할 트래픽 양을 설정합니다.
standard_conforming_strings	동적	... 문자열에서 백슬래시가 리터럴로 처리됩니다.
statement_timeout	동적	모든 문에 허용되는 최대 지속 시간을 설정합니다.
synchronize_seqscans	동적	동기 방식의 순차적 스캔을 활성화합니다.
synchronous_commit	동적	현재 트랜잭션 동기화 수준을 설정합니다.
tcp_keepalives_count	동적	TCP keepalive의 최대 재전송 횟수를 지정합니다.
tcp_keepalives_idle	동적	TCP keepalive의 실행 주기를 지정합니다.
tcp_keepalives_interval	동적	TCP keepalive의 재전송 주기를 지정합니다.
temp_buffers	동적	각 세션에서 사용하는 임시 버퍼의 최대 수를 설정합니다.
temp_tablespaces	동적	임시 테이블 및 정렬 파일에 사용할 테이블스페이스를 설정합니다.
timezone	동적	타임스탬프를 표시 및 해석할 시간대를 설정합니다.
track_activities	동적	명령 실행에 대한 정보를 수집합니다.
track_counts	동적	데이터베이스 작업에 관한 통계를 수집합니다.
track_functions	동적	데이터베이스 작업에 관한 함수 수준 통계를 수집합니다.
track_io_timing	동적	데이터베이스 I/O 작업에 관한 시간 통계를 수집합니다.
transaction_deferrable	동적	잠재적 직렬화 오류 없이 실행될 때까지 직렬화가 가능한 읽기 전용 트랜잭션의 지연 여부를 결정합니다.
transaction_isolation	동적	현재 트랜잭션 격리 수준을 설정합니다.
transaction_read_only	동적	현재 트랜잭션의 읽기 전용 상태를 설정합니다.
transform_null_equals	동적	expr=NULL을 expr IS NULL로 처리합니다.
update_process_title	동적	프로세스 제목을 업데이트하여 활성 SQL 명령을 표시합니다.
vacuum_cost_delay	동적	vacuum 코스트 지연 시간(밀리초)을 지정합니다.
vacuum_cost_limit	동적	지연 시간 이전에 이용 가능한 vacuum 코스트 값을 지정합니다.
vacuum_cost_page_dirty	동적	vacuum으로 페이지 변경 시 부과되는 vacuum 코스트를 지정합니다.
vacuum_cost_page_hit	동적	버퍼 캐시에서 발견되는 페이지에 대한 vacuum 코스트를 지정합니다.
vacuum_cost_page_miss	동적	버퍼 캐시에서 발견되지 않는 페이지에 대한 vacuum 코스트를 지정합니다.
vacuum_defer_cleanup_age	동적	vacuum 및 hot cleanup을 연기해야 하는 트랜잭션 수를 지정합니다(있는 경우).
vacuum_freeze_min_age	동적	vacuum에서 테이블 행을 동결해야 하는 최소 기간을 지정합니다.
vacuum_freeze_table_age	동적	vacuum에서 전체 테이블을 스캔하여 튜플을 동결해야 하는 기간을 지정합니다.
wal_writer_delay	동적	WAL 플러시 사이에 WAL 작성기의 절전 시간을 지정합니다.
work_mem	동적	쿼리 작업 공간에 사용할 최대 메모리를 설정합니다.
xmlbinary	동적	XML에서 바이너리 값의 인코딩 방식을 설정합니다.
xmloption	동적	암시적 구문 분석 및 직렬화 작업에서 XML 데이터를 문서 또는 내용 조각으로 간주할지 여부를 설정합니다.
autovacuum_freeze_max_age	정적	트랜잭션 ID 랩어라운드를 방지하기 위한 테이블의 autovacuum 기간을 지정합니다.
autovacuum_max_workers	정적	autovacuum 작업자 프로세스를 동시에 실행할 수 있는 최대 수를 설정합니다.
max_connections	정적	동시에 접속할 수 있는 최대 수를 설정합니다.
max_files_per_process	정적	서버 프로세스마다 파일을 동시에 열 수 있는 최대 수를 설정합니다.
max_locks_per_transaction	정적	하나의 트랜잭션에서 사용할 수 있는 최대 잠금 횟수를 설정합니다.
max_pred_locks_per_transaction	정적	하나의 트랜잭션에서 사용할 수 있는 최대 술어(predicate) 잠금 횟수를 설정합니다.
max_prepared_transactions	정적	트랜잭션을 동시에 준비할 수 있는 최대 수를 설정합니다.
shared_buffers	정적	서버에서 사용할 공유 메모리 버퍼의 수를 설정합니다.
ssl	정적	SSL 연결을 활성화합니다.
track_activity_query_size	정적	pg_stat_activity.current_query에 예약되는 크기(바이트)를 설정합니다.
wal_buffers	정적	WAL 기능을 위해 공유 메모리에서 사용할 디스크 페이지 버퍼 수를 설정합니다.

3. 테이블의 Vacuum 해야 하는지 여부를 결정

PostgreSQL에서는 "처리 중" 상태인 vacuum 되지 않은 트랜잭션 20억 개가 있을 수 있다. vacuum되지 않은 트랜잭션 수가 20억개에 도달하면 로그에서 vacuum 작업을 수행해야 한다는 경고를 표시하기 시작한다. vacuum 되지 않ㅇ츤 트랜잭션 수가 20억개에 도달하면 PostgreSQL은 데이터베이스를 읽기 전용으로 설정하며 오프라인 상태로 설정된다. 

아래 쿼리는 vacuum 되지 않은 트랜잭션 수를 표시한다.

select datname, age(datfrozenxid) from pg_database order 
by age(datfrozenxid) desc limit 20;

트랜잭션 수를 20억 미만으로 유지하도록 설정한다.