Пример создания джоба в postgres

1) установка pgagent

yum search pgagent
yum install pgagent_96

2) В каталоге /etc/init.d/ создаем файл и прописываем его в автозагрузку

#!/bin/bash
 #
 # /etc/rc.d/init.d/pgagent
 #
 # Manages the pgagent daemon
 #
 # chkconfig: - 65 35
 # description: PgAgent PostgreSQL Job Service
 # processname: pgagent
 . /etc/init.d/functions
 RETVAL=0
 prog="PgAgent"
 start() {
   echo -n $"Starting $prog: "
   daemon "/usr/bin/pgagent_95 -s /var/log/pgagent_95.log hostaddr=your_ip dbname=cuser=postgres password=YYYYYYYY"
   RETVAL=$?
   echo
 }
 stop() {
   echo -n $"Stopping $prog: "
   killproc /usr/bin/pgagent_95
   RETVAL=$?
   echo
 }
 case "$1" in
  start)
   start
   ;;
  stop)
   stop
   ;;
  reload|restart)
   stop
   start
   RETVAL=$?
   ;;
  status)
   status /usr/bin/pgagent_95
   RETVAL=$?
   ;;
  *)
   echo $"Usage: $0 {start|stop|restart|reload|status}"
   exit 1
 esac
 exit $RETVAL

3) Запуск /etc/init.d/pgagent_95 start

4) Создание расписания/запуск функции каждую минуту (расписание аналогично crontab)

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    1::integer, 'check events emails'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
    jstjobid, jstname, jstenabled, jstkind,
    jstconnstr, jstdbname, jstonerror,
    jstcode, jstdesc
) VALUES (
    jid, 'check events emails'::text, true, 's'::character(1),
    ''::text, 'cabinet'::name, 'f'::character(1),
    'begin;
       select msg.fnc_job_check_events_letters();'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
    jscjobid, jscname, jscdesc, jscenabled,
    jscstart, jscend,    jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
    jid, 'del'::text, ''::text, true,
    '2017-04-07 16:44:15+05'::timestamp with time zone, '2057-04-07 16:30:30+05'::timestamp with time zone,
    -- Minutes
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Hours
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Week days
    ARRAY[true, true, true, true, true, true, true]::boolean[],
    -- Month days
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Months
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

------------------------
5) 2 функции и тестовая таблица.

CREATE OR REPLACE FUNCTION msg.fnc_job_check_events_letters()
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
  v_conn_str text;
  v_conn_name varchar(64) := md5(clock_timestamp()::varchar);
  v_query text;
  v_res numeric;
begin

   select conn_str
     into v_conn_str
     from public.connect_data;
 
     v_query := 'SELECT msg.fnc_exec_check_events_letters()';

  PERFORM  public.dblink_connect(v_conn_name,v_conn_str);
  PERFORM public.dblink_exec(v_conn_name, 'SET datestyle = ISO, DMY;');          
 
  SELECT *
  into v_res
  FROM public.dblink(v_conn_name, v_query) AS p(res numeric);

  end;
$function$


CREATE OR REPLACE FUNCTION msg.fnc_exec_check_events_letters()
 RETURNS numeric
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
begin
 insert into msg.test_jobs(id) values(1);
 return 1;
end;
$function$

create table msg.test_jobs(
date_inserted timestamp NOT NULL DEFAULT 'now'::text::timestamp without time zone,
id numeric);