JOB SCHEDULING

`pg_cron` is an extension for PostgreSQL that allows you to schedule tasks directly within the database. It's similar in concept to `cron` in Unix-like operating systems but integrated into PostgreSQL.

With `pg_cron`, you can schedule tasks using SQL queries, rather than relying on external cron jobs or schedulers.

This makes it convenient for database administrators and developers who want to manage database-related tasks without relying on additional tools or services.

To use `pg_cron`, you need to install the extension in your PostgreSQL environment and then define your scheduled tasks using SQL statements.
These tasks can include database maintenance,data aggregation,backups,or any other routine operations you need to perform.

Here's a basic overview of how you might use `pg_cron`:

**Install `pg_cron`** : First, you need to install the `pg_cron` extension in your PostgreSQL environment. You can typically do this using your package manager or by compiling it from source.

**Configure `pg_cron`** : After installation, you may need to configure `pg_cron` settings, such as defining the frequency of task execution or specifying logging options.

**Define scheduled tasks** : Once `pg_cron` is installed and configured, you can define your scheduled tasks using SQL statements. These tasks can be any valid SQL commands, such as running stored procedures, executing queries, or performing administrative tasks.

**Monitor and manage tasks** : You can monitor and manage your scheduled tasks using built-in PostgreSQL functions provided by `pg_cron`. This allows you to view the status of tasks, modify their schedules, or remove them as needed.

Step 1 : install pg_cron

  [root@krishna ~]# yum install -y pg_cron_13

Step 2 : set the parameters

  [root@krishna ~]# cd /var/lib/pgsql/13/data
  [root@krishna data]# vi postgresql.conf
  shared_preload_libraries = 'pg_cron'    # (change requires restart)
  cron.database_name='postgres'
  cron.timezone='Asia/Kolkata'
  [root@krishna data]# vi pg_hba.conf
  # IPv4 local connections:
  host    all             all             0.0.0.0/0               trust

Step 3 : restart the server

  -bash-4.2$ /usr/pgsql-13/bin/pg_ctl -D /var/lib/pgsql/13/data restart
  waiting for server to shut down.... done
  server stopped
  waiting for server to start....2024-02-12 13:26:12.220 IST [9068] LOG:  redirecting log output to logging collector process
  2024-02-12 13:26:12.220 IST [9068] HINT:  Future log output will appear in directory "log".
  done
  server started

Step 4 : create extension

  postgres=# create extension pg_cron;
  CREATE EXTENSION

Step 5 : Grant usage on schema

  postgres=# \dn
    List of schemas
    Name  |  Owner   
  --------+----------
  cron   | postgres
  public | postgres
  repack | postgres
  (3 rows)
  postgres=# grant usage on schema cron to postgres;
  GRANT

Step 6 : schedule job to vacuum table k1

  postgres=# select * from pg_stat_all_tables where relname='k1';
  -[ RECORD 1 ]-------+-------
  relid               | 106529
  schemaname          | public
  relname             | k1
  seq_scan            | 0
  seq_tup_read        | 0
  idx_scan            | 0
  idx_tup_fetch       | 0
  n_tup_ins           | 0
  n_tup_upd           | 0
  n_tup_del           | 0
  n_tup_hot_upd       | 0
  n_live_tup          | 0
  n_dead_tup          | 0
  n_mod_since_analyze | 0
  n_ins_since_vacuum  | 0
  last_vacuum         | 
  last_autovacuum     | 
  last_analyze        | 
  last_autoanalyze    | 
  vacuum_count        | 0
  autovacuum_count    | 0
  analyze_count       | 0
  autoanalyze_count   | 0
  postgres=# select cron.schedule('45 13 * * * ','vacuum k1');
  -[ RECORD 1 ]
  schedule | 1

Step 7 : find job details

  postgres=# select * from cron.job_run_details;
  -[ RECORD 1 ]--+---------------------------------
  jobid          | 1
  runid          | 1
  job_pid        | 10229
  database       | postgres
  username       | postgres
  command        | vacuum k1
  status         | succeeded
  return_message | VACUUM
  start_time     | 2024-02-12 13:45:00.144062+05:30
  end_time       | 2024-02-12 13:45:00.202443+05:30

  postgres=# select * from cron.job
  postgres-# ;
  -[ RECORD 1 ]----------
  jobid    | 1
  schedule | 45 13 * * * 
  command  | vacuum k1
  nodename | localhost
  nodeport | 5432
  database | postgres
  username | postgres
  active   | t
  jobname  |

Step 8 : job to insert values

  postgres=# select  * from k1;
  id 
  ----
    1
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   20
  (12 rows)

  postgres=# select cron.schedule('53 14 * * * ',$$delete from k1 where id=20$$)
  postgres-# ;
  schedule 
  ----------
          2
  (1 row)

  postgres=# select * from cron.job;
  jobid |   schedule   |          command           | nodename  | nodeport | database | username | active | jobname 
  -------+--------------+----------------------------+-----------+----------+----------+----------+--------+---------
      1 | 45 13 * * *  | vacuum k1                  | localhost |     5432 | postgres | postgres | t      | 
      2 | 53 14 * * *  | delete from k1 where id=20 | localhost |     5432 | postgres | postgres | t      | 
  (2 rows)

Step 9 : find the status of job

  postgres=# select * from cron.job_run_details;
  jobid | runid | job_pid | database | username |    command      |  status   | return   |            start_time            |         
      end_time                                                                 _message
  -------+-------+---------+----------+----------+----------------------------+-----------+----------------+--------------------------+---
  -------------------------
      1 |     1 |   10229 | postgres | postgres | vacuum k1       | succeeded | VACUUM   | 2024-02-12 13:45:00.144062+05:30 | 2024-02-
  12 13:45:00.202443+05:30
      2 |     2 |   12053 | postgres | postgres | delete from k1  | succeeded | DELETE 1 | 2024-02-12 14:53:00.143995+05:30 | 2024-02-
                                                   where id=20     
      12 14:53:00.146467+05:30
  (2 rows)

  postgres=# select  * from k1;
  id 
  ----
    1
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
  (11 rows)

Step 10 : creating job with job name

  postgres=# select cron.schedule('job to insert values','*/1 * * * * ',$$insert into k1 values (20)$$);
  schedule 
  ----------
          3
  (1 row)

  postgres=# select * from cron.job;
  jobid |   schedule   |          command           | nodename  | nodeport | database | username | active |       jobname        
  -------+--------------+----------------------------+-----------+----------+----------+----------+--------+----------------------
      5 | */1 * * * *  | insert into k1 values(20)  | localhost |     5432 | postgres | postgres | t      | job to insert values

Step 11 : find the status of job

  postgres=# select * from cron.job_run_details;
  jobid | runid | job_pid | database | username |              command              |  status   |    return_message   |          
    start_time            |             end_time             
  -------+-------+---------+----------+----------+-----------------------------------+-----------+-------------------------------+---
                  
      5 |     5 |   12523 | postgres | postgres | insert into k1 values(20)         | succeeded | INSERT 0 1          | 2024-02-1
  2 15:01:00.025512+05:30 | 2024-02-12 15:01:00.028313+05:30
  (5 rows)
  postgres=# select * from k1;
  id 
  ----
    1
    3
    4
    5
    6
    7
    8
    9
   10
   11
   12
   20

Step 12 : to unschedule a job:(mention jobid)

  postgres=# select cron.unschedule(5);
  unschedule 
  ------------
  t
  (1 row)

  postgres=# select * from cron.job;
  jobid |   schedule   |          command           | nodename  | nodeport | database | username | active | jobname 
  -------+--------------+----------------------------+-----------+----------+----------+----------+--------+---------
      1 | 45 13 * * *  | vacuum k1                  | localhost |     5432 | postgres | postgres | t      | 
      2 | 53 14 * * *  | delete from k1 where id=20 | localhost |     5432 | postgres | postgres | t      | 
  (2 rows)