Creating a Routine Maintenance job

To prevent tables from becoming extremely large over time, some tables of the database used by Z ChatOps are partitioned by date range. pgAgent is a job scheduling agent for PostgreSQL databases. pgAgent can run multi-step batch or shell scripts and SQL tasks on complex schedules, and can work very well in pgAdmin 4 user interface. You can use pgAgent to create a Routine Maintenance job.

Before you begin

Before you can create a pgAgent job, you must install, configure and start pgAdmin 4 and pgAgent first. For more information, see Installing pgAdmin 4 and Installing pgAgent.

Procedure

Use pgAgent to create a Routine Maintenance job to automatically create partition tables for next month. The job runs monthly.

Note: You must log in pgAdmin 4 User Interface by using the same database user when you start your pgAgent.

In the Object Explorer pane of your pgAgent, expand Servers and Your host name nodes, right click on the pgAgent Jobs node, and click Create → pgAgent Job....
In the Create - pgAgent Job dialog, specify the following parameters.

Name

Specify the name of the pgAgent job, for example, Routine Maintenance - Z ChatOps partition tables.

Job class

Expand the Job class drop-down list and select Routine Maintenance.

In the Steps tab, click the Add a row icon

to add a row and the Edit row icon

to edit the step.

Name: Specify a name for the step.
Kind: Specify the kind as SQL.
Connection type: Specify the type as Local.
Database: Select zchatops as the database where the job step runs.

Code

In the Code tab, paste the PL/SQL script from $ZCHATOPS_HOME/samples/sql/pgAgent/maintainPartition.sql that you create to automatically create table partitions for zchatops database.

DO $q$
DECLARE
    table_num int;
    start_year varchar;
    start_month varchar;
    end_year varchar;
    end_month varchar;
    start_date varchar;
    end_date varchar;
BEGIN
    RAISE NOTICE 'Start to maintain the partition tables for zchatops database ...';

   -- Get start and end date
    SELECT to_char(CURRENT_DATE + interval '1 month', 'YYYY') INTO start_year;
    SELECT to_char(CURRENT_DATE + interval '1 month', 'MM') INTO start_month;
    SELECT to_char(CURRENT_DATE + interval '2 months', 'YYYY') INTO end_year;
    SELECT to_char(CURRENT_DATE + interval '2 months', 'MM') INTO end_month;
    start_date = start_year || '-' || start_month || '-01';
    end_date = end_year || '-' || end_month || '-01';
	RAISE NOTICE 'Partition table start date: %', start_date;
	RAISE NOTICE 'Partition table end   date: %', end_date;

    -- Check whether the message table for next month exists or not
	SELECT count(1) INTO table_num FROM pg_tables WHERE schemaname='bnz_webchat' AND tablename=format('message_y%sm%s', start_year, start_month); 
	
    -- Create message partition table for next month
    IF table_num >= 1 THEN
        RAISE NOTICE 'The partition table bnz_webchat.message_y%m% already exists!', start_year, start_month;
    ELSE
        -- Create partition table
        RAISE NOTICE 'Creating the partition table bnz_webchat.message_y%m% ...', start_year, start_month;
        EXECUTE format('CREATE TABLE bnz_webchat.message_y%sm%s (LIKE bnz_webchat.message INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES)', start_year, start_month);
        EXECUTE format('ALTER TABLE bnz_webchat.message_y%sm%s ADD CONSTRAINT y%sm%s CHECK ( create_time >= DATE %L AND create_time < DATE %L )', start_year, start_month, start_year, start_month, start_date, end_date);
        EXECUTE format('ALTER TABLE bnz_webchat.message ATTACH PARTITION bnz_webchat.message_y%sm%s FOR VALUES FROM ( %L ) TO ( %L )', start_year, start_month, start_date, end_date);
        RAISE NOTICE 'The partition table bnz_webchat.message_y%m% has already been created!', start_year, start_month;
    END IF;

    -- Check whether the incident table for next month exists or not
	SELECT count(1) INTO table_num FROM pg_tables WHERE schemaname='bnz_webchat' AND tablename=format('incident_y%sm%s', start_year, start_month);
	

    -- Create incident partition table for next month
    IF table_num >= 1 THEN
        RAISE NOTICE 'The partition table bnz_webchat.incident_y%m% already exists!', start_year, start_month;
    ELSE
        -- Create partition table
        RAISE NOTICE 'Creating the partition table bnz_webchat.incident_y%m% ...', start_year, start_month;
        EXECUTE format('CREATE TABLE bnz_webchat.incident_y%sm%s (LIKE bnz_webchat.incident INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES)', start_year, start_month);
        EXECUTE format('ALTER TABLE bnz_webchat.incident_y%sm%s ADD CONSTRAINT y%sm%s CHECK ( create_time >= DATE %L AND create_time < DATE %L )', start_year, start_month, start_year, start_month, start_date, end_date);
        EXECUTE format('ALTER TABLE bnz_webchat.incident ATTACH PARTITION bnz_webchat.incident_y%sm%s FOR VALUES FROM ( %L ) TO ( %L )', start_year, start_month, start_date, end_date);
        RAISE NOTICE 'The partition table bnz_webchat.incident_y%m% has already been created!', start_year, start_month;
    END IF;

    RAISE NOTICE 'Finish to maintain the partition tables for zchatops database!';

    RETURN;
END;
$q$ LANGUAGE plpgsql;

In the Schedules tab, click the to add a row and the to edit the schedule.

Name

Specify a name for the schedule, for example, monthly.

Start

Choose a date to start the pgAgent job.

End

Choose a date to end the pgAgent job.

Month Days

Select Last day for the Month Days field.

Hours

Select 00 for the Hours field.

Minutes

Select 01 for the Minutes field.