Event Scheduler

Für solche Aufgaben wird in PostgreSQL die Extension pg_cron verwendet. Damit kannst du geplante Aufgaben mit der cron-Syntax anlegen (wie unter Unix).

Mit pg_cron kannst du Folgendes automatisieren:

Zuerst musst du pg_cron auf dem Server installieren, zu shared_preload_libraries hinzufügen und PostgreSQL neu starten. Anschließend kannst du die Extension in der Datenbank anlegen:

MySQL 8.1
CREATE EXTENSION IF NOT EXISTS pg_cron;

Beginnen wir mit dem Einfachsten — einer Aufgabe, die einmalig zu einem bestimmten Zeitpunkt läuft:

pg_cron hat keinen eigenen Zeitplantyp für eine einmalige Ausführung. Verwende dafür einen externen Scheduler der Anwendung oder lege eine temporäre Aufgabe an und entferne sie nach der Ausführung.

Diese Aufgabe löscht zum Beispiel beim nächsten Erreichen von 3:00 Uhr alte Log-Einträge und anschließend sich selbst:

MySQL 8.1
SELECT cron.schedule(
    'cleanup_old_logs_once',
    '0 3 * * *',
    $command$
    DO $$
    BEGIN
        DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
        PERFORM cron.unschedule('cleanup_old_logs_once');
    END;
    $$;
    $command$
);

Normalerweise führt der Zeitplan '0 3 * * *' eine Aufgabe jeden Tag um 3:00 Uhr aus. Nach dem ersten Lauf ruft diese Aufgabe jedoch cron.unschedule() mit ihrem eigenen Namen auf und wird daher nicht erneut ausgeführt.

Häufiger sollen Aufgaben regelmäßig laufen — jeden Tag, jede Stunde oder jede Minute:

MySQL 8.1
SELECT cron.schedule(
    'cleanup_old_logs',
    '0 3 * * *',
    'DELETE FROM logs WHERE created_at < NOW() - INTERVAL ''30 days'''
);

Diese Aufgabe läuft jeden Tag um 3:00 Uhr und löscht Log-Einträge, die älter als 30 Tage sind.

Die Syntax im Detail:

• cron.schedule() — Funktion zum Anlegen einer geplanten Aufgabe
• 'cleanup_old_logs' — Name der Aufgabe
• '0 3 * * *' — Zeitplan im cron-Format (Minute, Stunde, Tag des Monats, Monat, Wochentag)
• letzter Parameter — der auszuführende SQL-Befehl

cron-Zeitplan-Format:

MySQL 8.1
SELECT cron.schedule(
    'update_statistics_hourly',
    '0 * * * *',
    $$
    UPDATE product_stats SET
        total_sales = (SELECT SUM(amount) FROM orders WHERE product_id = product_stats.product_id),
        last_updated = NOW()
    $$
);

Diese Aufgabe aktualisiert die Verkaufsstatistik jede Stunde (zur vollen Stunde).

Beispiele für Zeitpläne:

• '*/5 * * * *' — alle 5 Minuten
• '0 * * * *' — jede Stunde (zur vollen Stunde)
• '0 0 * * *' — jeden Tag um Mitternacht
• '0 0 * * 0' — jeden Sonntag um Mitternacht
• '0 9 1 * *' — am 1. jedes Monats um 9:00 Uhr
• '30 seconds' — alle 30 Sekunden ab pg_cron 1.5

Sekundenintervalle werden als eigene Zeichenfolge und nicht als sechstes cron-Feld angegeben. pg_cron unterstützt Werte von 1 bis 59 Sekunden.

Manchmal soll eine Aufgabe nur in einem bestimmten Zeitraum aktiv sein.

In einem pg_cron-Zeitplan kannst du kein Datum angeben, an dem eine Aufgabe automatisch beendet wird. Eine Aufgabe, die in den nächsten 30 Tagen täglich Logs bereinigt, legst du daher mit einem normalen Zeitplan an:

MySQL 8.1
SELECT cron.schedule(
    'temporary_log_cleanup',
    '0 0 * * *',
    $$DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days'$$
);

Damit die Aufgabe automatisch endet, verwendest du denselben Ansatz wie im Beispiel zur einmaligen Ausführung: Lege die Aufgabe stop_temporary_log_cleanup für ein Datum in 30 Tagen an. Ihr Befehlsblock sieht so aus:

MySQL 8.1
DO $$
BEGIN
    PERFORM cron.unschedule('temporary_log_cleanup');
    PERFORM cron.unschedule('stop_temporary_log_cleanup');
END;
$$;

Der erste Aufruf entfernt die Bereinigungsaufgabe, der zweite die Hilfsaufgabe. Gib im Zeitplan der Hilfsaufgabe Minute, Stunde, Tag und Monat des Datums in 30 Tagen an.

Die für den aktuellen Benutzer verfügbaren geplanten Aufgaben anzeigen:

MySQL 8.1
SELECT * FROM cron.job;

Ein regulärer Benutzer sieht nur die eigenen Aufgaben. Ein Superuser oder eine Rolle mit dem Attribut BYPASSRLS kann auch die Aufgaben anderer Benutzer sehen.

Ausführungshistorie der Aufgaben ansehen:

MySQL 8.1
SELECT * FROM cron.job_run_details
ORDER BY start_time DESC
LIMIT 10;

Geplante Aufgabe löschen:

MySQL 8.1
SELECT cron.unschedule('cleanup_old_logs');

Oder über die Job-ID:

MySQL 8.1
SELECT cron.unschedule(42);  -- wobei 42 die jobid aus der Tabelle cron.job ist

Aufgabe ändern:

Zeitplan, Befehl und Aktivitätsstatus einer bestehenden Aufgabe lassen sich mit cron.alter_job() ändern:

MySQL 8.1
SELECT cron.alter_job(
    42,
    schedule := '0 */2 * * *'
);

Dabei ist 42 die jobid der Aufgabe aus der Tabelle cron.job.

Wichtige Punkte beim Arbeiten mit pg_cron-Aufgaben

1. Berechtigungen: Normalerweise installiert ein Superuser die Extension. Danach kann regulären Benutzern USAGE für das Schema cron erteilt werden. Eine Aufgabe läuft mit den Rechten des Benutzers, der sie angelegt hat.

2. Zeitzone: cron-Ausdrücke verwenden die Einstellung cron.timezone, standardmäßig GMT. Den aktuellen Wert prüfst du mit SHOW cron.timezone;.

3. Intervalle: Ab Version 1.5 unterstützt pg_cron Intervalle von 1 bis 59 Sekunden. Wähle die Häufigkeit passend zu den Kosten der Aufgabe und der erwarteten Last.

4. Überlappende Ausführungen: pg_cron führt nicht mehrere Instanzen derselben Aufgabe gleichzeitig aus. Wird die nächste Ausführung fällig, während die Aufgabe noch läuft, kommt sie in die Warteschlange.

5. Logging: Wenn cron.log_run aktiviert ist, werden Ausführungsdetails in cron.job_run_details gespeichert. Das Logging ist standardmäßig aktiviert.

Welches Mindestintervall kannst du für wiederkehrende Aufgaben verwenden?

Geplante Aufgaben sind ein mächtiges Werkzeug, um Routineaufgaben in der Datenbank zu automatisieren. Sie sorgen für saubere Daten, aktuelle Statistiken und regelmäßige Wartungsoperationen — ganz ohne Eingreifen von Entwickelnden! 🚀