이 랩에서는 PostgreSQL 에서 이벤트 트리거를 설정하는 방법을 배웁니다. 이벤트 트리거는 데이터베이스 객체를 생성, 변경 또는 삭제하는 것과 같은 데이터 정의 언어 (DDL) 이벤트를 캡처합니다.
이러한 이벤트를 기록하기 위한 전용 로그 테이블과 특수 함수를 생성합니다. 그런 다음 CREATE TABLE 명령 후에 발생하는 이벤트 트리거를 생성하여 새 테이블의 세부 정보를 기록합니다. 이 실습을 통해 필요한 구성 요소를 생성하고, 트리거의 기능을 테스트하고, 나중에 데이터베이스 객체를 정리하는 과정을 안내합니다.
이 단계에서는 DDL 이벤트 로깅에 필요한 데이터베이스 객체, 즉 로그를 저장할 테이블과 트리거가 실행할 함수를 생성합니다.
먼저 터미널을 열고 psql 대화형 쉘을 사용하여 PostgreSQL 데이터베이스에 연결합니다. 이후 SQL 작업은 이 쉘 내에서 수행합니다.
sudo -u postgres psql이제 postgres=#와 같이 생긴 PostgreSQL 프롬프트가 표시됩니다.
다음으로 각 DDL 이벤트의 세부 정보를 저장할 event_log라는 테이블을 생성합니다. 이 테이블에는 이벤트 시간, 유형, 스키마, 객체 이름 및 실행된 명령에 대한 열이 있습니다.
psql 쉘에서 다음 SQL 명령을 실행합니다.
CREATE TABLE event_log (
event_time timestamp with time zone,
event_type text,
schema_name text,
object_name text,
command_tag text
);성공적으로 실행되면 PostgreSQL 은 다음과 같이 응답합니다.
CREATE TABLE이제 DDL 이벤트에 의해 트리거될 함수를 생성합니다. 이 함수인 log_ddl_event는 event_log 테이블에 새 레코드를 삽입합니다.
CREATE OR REPLACE FUNCTION log_ddl_event()
RETURNS event_trigger
AS $$
BEGIN
INSERT INTO event_log (event_time, event_type, schema_name, object_name, command_tag)
SELECT now(), tg_event, ddl_command.schema_name, ddl_command.objid::regclass, tg_tag
FROM pg_event_trigger_ddl_commands() AS ddl_command;
END;
$$ LANGUAGE plpgsql;이 함수를 자세히 살펴보겠습니다.
RETURNS event_trigger: 이 함수가 이벤트 트리거와 함께 사용하도록 설계된 특수 함수 유형임을 지정합니다.AS $$ ... $$ LANGUAGE plpgsql: plpgsql 절차 언어를 사용하여 함수 본문을 정의합니다.INSERT INTO event_log ...: 이것이 핵심 작업으로, 로그 테이블에 새 행을 삽입합니다.now(): 현재 타임스탬프를 반환하는 표준 함수입니다.tg_event, tg_tag: 트리거 함수 내에서 사용할 수 있는 특수 변수로, ddl_command_end 및 CREATE TABLE과 같은 이벤트에 대한 컨텍스트를 제공합니다.pg_event_trigger_ddl_commands(): 실행된 DDL 명령을 설명하는 행 집합을 반환하는 함수입니다. 이를 사용하여 생성 중인 객체의 schema_name 및 objid(객체 식별자) 를 가져옵니다.명령을 실행한 후 다음과 같은 확인 메시지가 표시됩니다.
CREATE FUNCTION이제 이벤트 트리거의 기본 구성 요소를 성공적으로 설정했습니다.
로그 테이블과 함수를 설정했으므로 이제 이벤트 트리거 자체를 생성할 수 있습니다. 이 트리거는 특정 DDL 명령을 모니터링하고 해당 명령이 발생하면 로깅 함수를 실행합니다.
동일한 psql 셸에서 다음 명령을 실행하여 log_table_creation이라는 이벤트 트리거를 생성합니다.
CREATE EVENT TRIGGER log_table_creation
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_ddl_event();이 문장의 구성 요소를 살펴보겠습니다.
CREATE EVENT TRIGGER log_table_creation: 새 이벤트 트리거의 이름입니다.ON ddl_command_end: 트리거의 타이밍을 지정합니다. DDL 명령 실행이 완료된 후에 발생합니다.WHEN TAG IN ('CREATE TABLE'): 필터 조건입니다. 명령 태그가 CREATE TABLE과 일치하는 경우에만 트리거가 활성화됩니다. 여기에 ALTER TABLE 또는 DROP TABLE과 같은 다른 태그를 추가할 수도 있습니다.EXECUTE FUNCTION log_ddl_event(): 트리거의 조건이 충족될 때 실행할 함수를 지정합니다.명령을 실행한 후 PostgreSQL 은 생성을 확인합니다.
CREATE EVENT TRIGGER이벤트 트리거가 이제 활성화되었으며 모든 새 테이블 생성을 기록합니다.
이제 이벤트 트리거가 예상대로 작동하는지 테스트할 차례입니다. 새 테이블을 생성한 다음 event_log를 확인하여 생성 이벤트가 기록되었는지 확인합니다.
먼저 employees라는 간단한 테이블을 생성합니다.
CREATE TABLE employees (
id serial PRIMARY KEY,
name text NOT NULL
);CREATE TABLE 명령은 정상적으로 실행되며 표준 확인 메시지가 표시됩니다.
CREATE TABLE이 작업은 백그라운드에서 log_table_creation 트리거를 발생시켰어야 합니다. 이를 확인하려면 event_log 테이블을 쿼리하여 내용을 확인합니다.
SELECT schema_name, object_name, command_tag FROM event_log;출력에는 여러 레코드가 표시됩니다. 이는 serial PRIMARY KEY를 사용하여 테이블을 생성하면 시퀀스와 기본 키 제약 조건도 암시적으로 생성되기 때문입니다. 이벤트 트리거는 관련된 모든 DDL 명령을 캡처하므로 로그에 여러 항목이 생성됩니다.
schema_name | object_name | command_tag
-------------+------------------+--------------
public | employees_id_seq | CREATE TABLE
public | employees | CREATE TABLE
public | employees_pkey | CREATE TABLE
public | employees_id_seq | CREATE TABLE
(4 rows)이 결과는 이벤트 트리거가 올바르게 작동하고 테이블 생성과 해당 종속 객체를 기록하고 있음을 확인합니다.
더 이상 필요하지 않은 데이터베이스 객체는 제거하는 것이 좋습니다. 마지막 단계에서는 이 실습 중에 생성한 이벤트 트리거, 테이블 및 함수를 삭제합니다.
먼저 이벤트 트리거를 제거합니다.
DROP EVENT TRIGGER log_table_creation;확인 메시지가 표시됩니다.
DROP EVENT TRIGGER다음으로 생성한 두 테이블인 employees와 event_log를 삭제합니다.
DROP TABLE employees;
DROP TABLE event_log;PostgreSQL 은 각 작업을 확인합니다.
DROP TABLE
DROP TABLE마지막으로 트리거 함수를 제거합니다.
DROP FUNCTION log_ddl_event();최종 확인 메시지가 표시됩니다.
DROP FUNCTION이제 이 실습에서 생성된 모든 객체를 성공적으로 정리했습니다. psql 셸을 종료하고 일반 터미널 프롬프트로 돌아가려면 \q를 입력하고 Enter 키를 누릅니다.
\q이 실습에서는 PostgreSQL 에서 이벤트 트리거를 구현하여 DDL 활동을 모니터링하는 방법을 배웠습니다. 이벤트 데이터를 저장할 로그 테이블과 이를 채울 PL/pgSQL 함수를 생성했습니다. 그런 다음 CREATE TABLE 이벤트를 구체적으로 캡처하는 이벤트 트리거를 정의하고 로깅 함수와 연결했습니다. 트리거를 테스트하고 로그 항목을 확인하여 이러한 구성 요소가 함께 작동하는 방식에 대한 실질적인 경험을 쌓았습니다. 마지막으로 생성한 트리거, 테이블 및 함수를 정리하여 좋은 데이터베이스 관리 관행을 실천했습니다.
