postgresql : 동일한 key를 갖는 여러개의 테이블에 누적된 데이터 삭제하기

 postgresql을 사용하는 application을 운영하는 환경에서, application에 삭제 기능이 구현되지 않아 데이터가 계속 누적되는 이슈가 있었다. 운영중인 서버에 disk 용량은 정해져 있었기 때문에 일정 기간이 지난 데이터를 주기적으로 삭제를 해줘야하는 상황이 발생하였다. application에 요구사항이 반영되어 사이트에 업뎅이트 되려면 몇 달의 시간이 필요했고, 그 전까지 임시 운영 방안을 마련해야 했다.

 그래서 생각한 방법이 아래와 같이 procedure를 작성하여 database에 등록해두고, 리눅스 서버의 cron에서 주기적으로 procedure를 호출하도록 설정하기로 했다. 그전에 mysql에서 간략한 procedure를 작성해 본 경험은 있었으나 postgresql과 문법적 차이가 약간 있어서 삽질을 좀 했었다. 작성했던 procedure 예제와 실행 방법을 정리해 두려고 한다.

 

---

 

테이블 구조

tbl_root 테이블의 id 값을 tbl_child1과 tbl_child2에서 root_id 라는 값으로 참조를 하고 있다.

application에서 특정 서비스가 호출될 때마다 tbl_root에 데이터가 추가되고, 그 id를 참조하여 tbl_child1, tbl_child2에 1~N개 까지의 데이터가 추가될 수 있는 구조이다.

 

 

프로시저 샘플

 

 

프로시저 실행

Postgres CLI 에서 실행 쿼리

CALL public.delete_tbl_root_data('2021-08-01 00:00:00.000000+00');

 

docker container에 postgresql을 설치하였을 때, linux host CLI 에서 실행 쿼리

: cron daemon에 등록하려면 이렇게 주기적으로 실행하도록 설정해야 함

# docker container name : postgresql_db_1
# postgresql User name : postgres
# postgresql Database name : test_db

docker exec -it postgresql_db_1 psql -U postgres -d test_db -c "CALL public.delete_tbl_root_data('2021-08-01 00:00:00.000000+00');"