Postgresql Log 설정 방법

Log 관련 설정 및 주요 옵션 설명

 

설정	설명	옵션	Restart 필요 여부
log_destination	Log의 출력형태.	stderr csvlog syslog eventlog
log_directory	Log 파일이 쌓일 경로를 지정한다	[경로]
log_file_mode	Log파일의 권한을 지정한다.	[권한]
log_filename	Log파일의 이름을 지정한다. ISO파입의 Timestamp 형식을 지원한다. (예: %Y-%m-%d_%H%M%S)	[파일명]
log_line_prefix	각 로그 한줄마다 말머리에 찍힐 Prefix
log_min_duration_statement	SlowLog를 찍을 log의 duration
log_min_error_statement	ErrorLog의 Level
log_min_messages	AuditLog의 Level
log_rotation_age	시간대별 log rotate 지원 (기본 unit은 sec 이며, iso type을 지원한다. 예: 1d, 1m, 1y)
log_rotation_size	Log용량별 rotate 지원 (Iso type을 지원한다. 예: KB,MB,GB,TB)
log_statement	Log에 로깅할 SQL문을 제어한다. DDL은 ALTER,DROP문과 같은 모든 DDL문을 Loging하며, mod는 모든 DDL문과 INSERT/UPDATE/DELETE와 같은 데이터 수정문을 로깅한다. ALL로 두는경우, SELECT까지 Loging 한다.
log_truncate_on_rotation	Log의 truncate 여부. 해당 옵션이 활성화 된 경우, log가 rotate 될 때 새로운 파일을 생성하는게 아니라 기존의 파일에 덮어 쓰게 된다. 해당 옵션은 size 기반 rotation에는 적용되지 않는다.	on off
logging_collector	Log의 수집여부. 이 옵션이 활성화 되어 있어서 Log 파일 형태로 출력된다	on off	ㅇ

 

설정 예

log_destination = 'csvlog'
logging_collector = on
log_directory = '/data/auditlog'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 500MB
log_duration = on
log_line_prefix = '%m [%p] %q%u@%d '
log_lock_waits = on                     # log lock waits >= deadlock_timeout
log_statement = 'all'
log_timezone = 'ROK'

 

출력 예시

2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100256,"SET",2023-12-31 14:32:02 KST,101/76433839,0,LOG,00000,"execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100257,"SET",2023-12-31 14:32:02 KST,101/76433839,0,LOG,00000,"duration: 0.017 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1185,"BEGIN",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"execute S_2: BEGIN",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1186,"BEGIN",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"duration: 0.029 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1187,"BEGIN",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"statement: SAVEPOINT PGJDBC_AUTOSAVE",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1188,"SAVEPOINT",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"duration: 0.035 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1189,"PARSE",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"duration: 0.174 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1190,"BIND",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"duration: 0.187 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",207796,"10.**.***.**:55648",5fd6faaf.32bb4,7,"idle",2023-12-31 14:39:59 KST,140/10042479,0,LOG,00000,"statement: SELECT ""t_sky_connect_status"".""sky_connect_status_no"", ""t_sky_connect_status"".""company_no"", ""t_sky_connect_status"".""user_no"", ""t_sky_connect_status"".""connect_code"", ""t_sky_connect_status"".""service_type"", ""t_sky_connect_status"".""is_sky_connect"", ""t_sky_connect_status"".""sky_connect_status"", ""t_sky_connect_status"".""keylock_code"", ""t_sky_connect_status"".""customer_code"", ""t_sky_connect_status"".""is_deleted"" FROM ""t_sky_connect_status"" WHERE (""t_sky_connect_status"".""company_no"" = 42417 AND ""t_sky_connect_status"".""service_type"" = 'smarta' AND ""t_sky_connect_status"".""keylock_code"" = '110628RNSM1000283007' AND ""t_sky_connect_status"".""is_deleted"" = 'F') ORDER BY ""t_sky_connect_status"".""sky_connect_status_no"" ASC",,,,,,,,,""
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100258,"BIND",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"duration: 0.006 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100259,"BEGIN",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"execute S_2: BEGIN",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100260,"BEGIN",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"duration: 0.009 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100261,"BEGIN",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"statement: SAVEPOINT PGJDBC_AUTOSAVE",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100262,"SAVEPOINT",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"duration: 0.015 ms",,,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",206629,"10.**.***.**:53734",5fd6faa7.32725,1191,"SELECT",2023-12-31 14:39:51 KST,136/16045583,0,LOG,00000,"execute S_6: select
 
 
 
    service_no, service_category_no, service_name_kr, service_name_en, service_state,
    service_type, purchase_type, purchase_count, service_desc_kr, service_desc_en, service_feature_kr,
    service_feature_en, mobile_support, service_tag, open_date, access_auth_level, service_settings_yn,
    is_deleted, insert_timestamp, updated_timestamp, noti_setting_yn, is_show_market,
    service_code, is_show_deploy, service_key, service_parent_no, service_pay_type, service_deploy_type,
    point_use_yn, service_category_order, is_thirdparty, bp_no, service_sub_title, service_url,
    is_window, is_admin_usable, service_security_key, is_widget, service_group_code,
    is_show_main, mobile_app_id, service_image_title, service_image_sub_title, basic_service_charge,
    membership_service, is_sso, service_name_ja
 
    from t_service
 
 
 
     WHERE (  service_code = $1
 
 
 
                  and is_deleted = $2 )","parameters: $1 = 'smarta', $2 = 'F'",,,,,,,,"PostgreSQL JDBC Driver"
2023-12-31 14:39:59.741 KST,"service_app","test_common",136975,"10.**.***.**:59870",5fd6f8d2.2170f,100263,"BIND",2023-12-31 14:32:02 KST,101/76433840,0,LOG,00000,"duration: 0.052 ms",,,,,,,,,"PostgreSQL JDBC Driver"