[PostgreSql] pg_cron job schedule 등록방법

/* pg_cron이 설치가 안되어 있는 경우 
 * https://github.com/citusdata/pg_cron  사이트에 가서 소스를 다운받아서 컴파일 설치를 한다.
 * git clone https://github.com/citusdata/pg_cron
 * cd pg_cron
 * postgresql이 설치된 bin 경로를 설정한다.
 */
리눅스에 .bash_profile 열어서 경로 설정

LANG=ko_KR.UTF-8

PGHOME=/home/passdata/apps/postgresql-13.4
PGDATA=/home/passdata/passdata/pgdata
PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin
LD_LIBRARY_PATH=/$PGHOME/lib
PGDATABASE=postgres

export PGHOME
export PGDATA
export PATH
export LD_LIBRARY_PATH
export PGDATABASE

저장후
source .bash_profile 명령어 실행해서 반영한다.
make 명령어로 컴파일 한다.
make install 명령어로 컴파일 파일을 위에서 설정한 PGHOME경로에  extension 폴더로 배포
/home/passdata/apps/postgresql-13.4/share/extension 파일로 배포한다.

환경설정 파일 열어서 pg_cron사용할수 있게 설정 한다.
PGDATA 로 설정한 경로 /home/passdata/passdata/pgdata
postgresql.conf 파일 열어서 

shared_preload_libraries = 'pg_cron'    # (change requires restart)
cron.database_name = 'db명_mydb'
cron.use_background_workers = on
# Increase the number of available background workers from the default of 8
max_worker_processes = 20

sql

닫기 줄바꿈

/* 1. posrgresql 확장팩 설치 확인 */
select * from pg_catalog.pg_available_extensions;

/* 2. pg_cron 확장팩 생성 */
CREATE EXTENSION pg_cron;


/* 3. pg_cron job 등록 실행 테스트 */
SELECT cron.schedule ('job-test', '* * * * *', 'SELECT 1')

SELECT cron.schedule ('job-test', '* * * * *', $$call job_test('파라미터인자')$$)


/* 4. job 등록확인 */
select * from cron.job j 

/* 5. job 실행 확인 */
select * from cron.job_run_details jrd 

/* 6. pg_cron job 삭제(jobid)으로 삭제 */
SELECT cron.unschedule(5);  --job id


select *
​​from public.test
where register_id = 'jobid'

delete   from public.test
where register_id = 'jobid'


CREATE OR REPLACE PROCEDURE public.job_test(param inout varchar(10))
LANGUAGE plpgsql
AS $procedure$
BEGIN
​​​​​​​​​insert into public.test (sn,code,cn,register_id,reg_dt)
​​​​​​​​​​values (nextval('sq_n_test'),param,'job에서 등록','jobid',now());
END;
$procedure$

call  job_test('그냥 파라미터');