[PostgreSQL] Ubuntu Linux PostgreSQL 사용법 - #2-1 postgresql.conf 서버 구성 파일 설정

이전글

2023.04.23 - [본업/Database] - [PostgreSQL] Ubuntu Linux PostgreSQL 사용법 - #1 다운로드 및 설치

---

Server Configuration Files

PostgreSQL 서버를 사용하고 운영하는데 필요한 다양한 파라미터들이 있으며 이들을 설정 파일(.conf)들을 통해 구성 가능하다.

일부는 서버의 성능, 즉 스펙을 결정짓는 파라미터들도 존재하므로 해당 파라미터들을 적절히 조정하는 작업을 튜닝(tuning)이라고도 한다.

이 설정 파일들에 대해 간단하게 짚고 넘어가도록 한다.

• postgresql.conf
  • /etc/postgresql/$VERSION/main에 위치
  • 서버 구성 대부분의 파라미터를 제어하며 서버의 동작을 결정하는 설정들을 포함
  • ex: 연결 및 인증, 메모리 설정, 워크로드 튜닝, 플래너 구성 등
• pg_hba.conf
  • /etc/postgresql/$VERSION/main에 위치
  • 데이터베이스 서버에 대한 클라이언트 인증을 제어
  • ex: 어떤 호스트/사용자가 DB에 접속할 수 있는지, 어떤 인증 방법이 사용되어야 하는 지 등
• pg_ident.conf
  • /etc/postgresql/$VERSION/main에 위치
  • 클라이언트가 데이터베이스 서버에 연결을 생성할 때 사용하는 OS 사용자 이름을 PostgreSQL 사용자 이름으로 매핑하는데 사용
• postgresql.auto.conf
  • data 디렉토리에 위치
  • postgresql.conf 파일과 형식은 동일하나, 자동으로 편집되는 파일이기 때문에 직접 편집하지 않음
  • ALTER SYSTEM 명령(super user만 실항 가능)을 사용해 설정된 모든 파라미터를 포함시킴
  • 위 명령을 실행하면 postgresql.conf 파일을 직접 편집하지 않고도 서버 설정을 변경할 수 있음
• recovery.conf
  • 12버전 까지지만 사용되고 이후 버전부터 사용하지 않는 설정 파일
  • PostgreSQL이 장애 복구 모드에서 동작하는 방식을 제어

 

아래에서는 가장 많이 사용하고 가장 많은 파라미터가 포함된 postgresql.conf와 외부에서 DBMS 서버에 접속하기 위해 필요한 파라미터가 있는 pg_hba.conf 파일에 대해서만 다룬다.

또한 파라미터들이 너무 많기 때문에 자주 사용되는 파라미터만 다룰 것이다.

각 항목들은 당연히 공식 문서에 자세하게 설명되어 있으므로 링크만 걸어둔다.

 

postgresql.conf

Connection and Authentication

https://www.postgresql.org/docs/14/runtime-config-connection.html

20.3. Connections and Authentication

• listen_Address
  • 서버가 클라이언트 응용 프로그램(DBeaver 등)의 연결을 수신할 TCP/IP 주소를 지정.
  • '*'은 사용 가능한 모든 IP 인터페이스를 의미한다.
  • 나중에 다룰 외부 접속 허용을 위한 설정이다.
• port
  • 서버가 Listen중인 TCP 포트 번호를 정의한다.
  • 디폴트 포트 번호 '5432'는 오픈 소스 DBMS인 PostgreSQL의 잘 알려진 포트 번호(Well-Known Port Number)로 외부 공격의 취약점(특히 Brute-force)이 될 수 있으므로 상위 라우터에서 포트 포워딩을 설정하거나 해당 포트 번호를 다른 번호로 수정한다.
• max_connection
  • 서버에 대한 최대 동시 연결 개수를 의미한다.
  • 커널 설정에서 지원하지 않는 경우 해당 값에 미치지 못할 수 있음.
    • initdb중에 결정 됨
    • default = 100
  • Ex) 해당 파라미터는 현식적인 계산이 필요하다. 현재 내 상황에서는 클라이언트 하나 당 평균 6~8개의 연결이 활성화되는 것을 확인했다. 따라서 클라이언트 하나 당 rough하게 약 10개의 연결을 소모한다고 가정하고 100개의 클라이언트가 접속하면 1000개의 연결을 지원해야 하므로 1000으로 설정해 사용하고 있다.

Resource Usage

https://www.postgresql.org/docs/14/runtime-config-resource.html

20.4. Resource Consumption

• shared_buffers
  • 데이터베이스 서버가 공유 메모리 버퍼용으로 사용하는 메모리 양.
  • RAM이 1GB 이상인 전용 데이터베이스 서버를 사용하는 경우, 해당 파라미터의 적절한 시작값은 시스템 메모리의 25%다.
  • 단, PostgreSQL 역시 OS 캐시에 의존적이므로 시스템 효율을 위해 40% 이상의 RMA을 해당 파라미터에 하당하는 것은 좋지 않다.
  • 커널 설정에서 지원하지 않는 경우 해당 값에 미치지 못할 수 있다.
    • initdb중에 결정 됨
    • default = 128MB
    • minimum = 128KB
• temp_buffers
  • 데이터베이스 각 세션이 사용하는 임시 버퍼의 최대 메모리 양.
  • 임시 테이블에 액세스하는 용도로만 사용되는 세션의 로컬 버퍼.
• work_mem
  • 임시 디스크 파일을 쓰기 전 내부 정렬 명령 및 해시 테이블에서 사용되는 기본 최대 메모리 양.
  • 정렬 명령은 ORDER BY, DISTINCT 및 머지 조인에 사용됨.
  • 해시 테이블은 해시 조인, 해시 기반 집계(aggregation), IN 서브쿼리의 해시 기반 처리에 사용됨.
  • 일반적인 계산법 = shared_buffers / max_connections * 2
• maintenance_work_mem
  • VACUUM, CREATE INDEX 및 ALTER TABLE ADD FOREIGN KEY와 같은 유지 보수 명령에서 사용되는 최대 메모리의 양.
  • work_mem보다 훨씬 큰 값으로 설정하는 것이 안전함.
  • VACUUM 및 데이터베이스 dump(백업), resoter(복구) 성능이 개선될 수 있음.

Error Reporting and Logging

https://www.postgresql.org/docs/14/runtime-config-logging.html

20.8. Error Reporting and Logging

• log_destination
  • PostgreSQL은 stderr 및 csvlog, syslog를 비롯한 서버 메시지를 로깅하는 몇 가지 메서드를 지원함.
    • Windows의 경우, eventlog로 지원한다.
    • default = stderr
  • 원하는 로그 대상 목록을 쉼표로 구분하여 이 매개 변수를 설정한다.
  • 해당 매개 변수는 postgresql.conf파일 또는 서버 커맨드 라인에서만 설정 가능하다.
  • CSV 타입 로그 출력을 사용으로 설정하려면 아래의 logging_collector 파라미터를 on으로 설정해야 한다.
• logging_collector
  • stderr로 전송된 로그 메시지를 캡쳐한 후 로그 파일로 redirect하는 백그라운드 프로세스인 logging collector를 활성화함.
  • 이전 log_destination 파라미터에서 설명한 것처럼 CSV 타입 로그 출력을 사용하려면 해당 파라미터를 on으로 설정해야 한다.
• log_directory
  • logging_collector 파라미터를 사용으로 설정하면 이 매개 변수는 로그 파일이 생성되는 디렉터리를 결정한다.
  • 절대 경로 또는 클러스터 데이터 디렉토리에 대한 상대 경로로 설정할 수 있다.
  • 이 매개 변수는 postgresql.conf 파일 또는 서버 커맨드 라인에서만 설정 가능하다.
  • 나는 pg_log로 설정
• log_filename
  • logging_collector 파라미터를 사용으로 설정하면 이 매개 변수는 생성된 로그 파일의 파일 이름을 설정한다.
  • 값은 strftime패턴으로 처리되므로, 이스케이프 문자 %를 사용하여 시간에 따라 바뀌는 파일 이름을 지정할 수 있다.
    • 시간대 의존적 % 이스케이프가 있을 경우 log_timezone에서 지정된 시간대로 계산된다.
• log_rotation_age
  • logging_collector 파라미터를 사용으로 설정하면 이 매개 변수는 개별 로그 파일의 최대 수명을 결정한다.
  • 해당 파라미터에서 지정된 시간이 경과하면 새로운 로그 파일이 생성됨.
  • 나는 하루 단위 로그 파일을 생성하기 위해 1d로 설정.
• log_rotation_size
  • logging_collector 파라미터를 사용으로 설정하면 이 매개 변수는 개별 로그 파일의 최대 크기를 결정한다.
  • 파라미터 설정 값에 단위를 지정하지 않으면 크기는 킬로바이트(KB)로 간주됨.
    • default = 10MB
  • 파일 하나의 크기가 너무 크면 좋지 않으니 알아서 본인의 적절한 값으로 설정.
• log_truncate_on_rotation
  • logging_collector 파라미터를 사용으로 설정하면 이 매개 변수에 의해 PostgreSQL가 이름이 동일한 기존 로그 파일에 추가하는 것이 아니라 파일을 비운다(truncate = overwrite : 덮어쓰기).
  • 단, 비우기는 서버 시작 시 또는 크기 기준 로테이션이 아니라 시간 기준 로테이션에 의해 새 파일이 열린 경우에만 실행된다.
  • ex) 7일간 로그를 유지하고, 1일 1로그 파일의 이름을 server_log.Mon, server_log.Tue 등으로 명명하고, 마지막 주의 로그를 이 주의 로그로 자동 덮어쓰기
    • log_filename = server_log.%a
    • log_truncate_on_rotation = on
    • log_rotation_age = 1440
  • ex) 24시간 로그를 유지하고, 1시간당 1개 로그 파일을 생성하되, 로그 파일 크기가 1GB를 초과하면 곧장 로테이션 되도록 하기
    • log_filename = server_log.%H%M
    • log_truncate_on_rotation = on
    • log_rotation_age = 60
    • log_rotation_size = 1000000
    • log_filename 파일에서 %M을 포함하면 크기 구동 로테이션으로 시간의 초기 파일 이름과는 다른 파일 이름이 선택되도록 할 수 있다.
• log_min_messages
• 서버 로그에 기록할 메시지 레벨을 제어.
• 후속 레벨일수록 메시지가 로그에 적게 전송됨.
• 차후 서버 장애 추적을 위해 더 자세한 내용이 로깅되는 INFO 레벨로 2 단계 상승 설정함.
  • default = WARNING
• LOG 레벨은 여기서 client_min_messages와는 다른 랭크를 갖는다.
• log_min_error_statement
  • 에러 상태를 유발한 SQL 문이 서버 로그에 기록되는 것을 제어.
  • Default 값은, 에러, 로그 메시지, 심각한 에러 또는 패닉을 유발한 문(statement)을 로깅하는 ERROR이다. Default 값으로 설정.
• log_line_prefix
  • 각 로그 줄(row)의 처음에 출력되는 printf 스타일 string을 지정.
  • % 문자는 ’escape sequence’로 시작되며 아래 요약된 상태 정보로 대체되며 미인식 escape는 무시됨.
  • 나는 다음과 같이 설정함: '%m [%p] %q%u@%d'
• log_timezone
  • 서버 로그에 작성되는 타임스탬프에 사용할 시간대를 설정.
  • TimeZone과 달리, 이 값은 클러스터 차원(cluster-wide)의 값이므로 모든 세션이 일관되게 타임스탬프를 알려준다.
  • 내장 기본값은 GMT이지만, 일반적으로 postgresql.conf에 override되고 initdb는 시스템 환경에 해당되는 곳의 값을 설정한다.
  • 나는 아직 해외 Locale을 이용할 계획이 없기 때문에 실제 서버가 위치한 한국의 'Asia/Seoul'로 설정. 그런데 실제로 서버가 회사에 있으니 맞긴 하다.

Client Connection Defaults

https://www.postgresql.org/docs/14/runtime-config-client.html

20.11. Client Connection Defaults

• client_min_messages = log
  • 클라이언트로 전송할 메시지 레벨을 제어.
  • LOG는 여기서 log_min_messages와는 다른 랭크를 갖는다.
  • 차후 에러 추적을 위해 더 자세한 내용이 로깅되는 LOG 레벨로 1 단계 상승 설정
    • default = NOTICE

Apply the configurations to running server

postgresql.conf 파일의 내용을 수정하고 저장하였다면, 해당 내용을 PostgreSQL 서버에 반영해야 한다.

여기서 주의할 점은 각 항목에 따라 서버의 재시작이 필요한지 불필요한지가 결정된다는 것이다.

• reload : PostgreSQL의 reload 명령어를 사용해 반영 가능. 이는 서버를 중단시키지 않고 설정을 다시 읽어들이는 작업임.
• restart : 문서에 "이 설정은 서버 시작시에만 읽힙니다"라고 명시되어 있는 항목일 경우 반드시 PostgreSQL 서버를 재시작해야 함.

아래는 Ubuntu Linux 환경에서 각 명령어를 사용하는 방법이다.

# RELOAD
sudo systemctl reload postgresql

# RESTART
sudo systemctl restart postgresql

---

처음에 PostgreSQL을 설치하고 사용할 때에는 이런 자세한 파라미터들을 알고 배울 여유 조차도 없었다.

하지만 사용하고 운영하다 보니 하나 씩 파라미터를 바꿔주게 되었고, 1년간 바꾸게 된 파라미터들이다.

앞으로도 추가되겠지만 postgresql.conf 파일에 한해서는 이 정도만 알고 있어도 그냥 저냥 버티는 것 같다.

다음은 외부 접속을 허용하는 내용이며 상대적으로 분량은 적다.