citusdata/pg_cron
Pg Cron
pg_cron is a PostgreSQL job scheduler and background task manager that executes periodic SQL commands directly within the database engine. It functions as an automation tool using standard cron syntax to trigger recurring administrative and data tasks on a defined timetable.
The extension provides the capability to manage and trigger scheduled SQL operations across multiple target databases from a single instance. It includes a logging system that acts as an execution auditor, tracking the start time, end time, and success status of every job run.
The project covers database maintenance automation, job monitoring, and task lifecycle management, including the ability to modify or cancel existing jobs. Access control is managed through row-level security to restrict the visibility and modification of scheduled jobs to authorized users.
Features
- SQL Query Schedulers - Automates the periodic execution of SQL statements directly within the database using cron syntax.
- Database Background Workers - Implements a dedicated PostgreSQL background worker process to check for and execute scheduled tasks.
- Job State Persistence - Persists job definitions and schedules in relational tables to ensure state is maintained across database restarts.
- PostgreSQL Administration Tools - Provides a comprehensive system for automating recurring administrative and data tasks across PostgreSQL databases.
- PostgreSQL Extensions - Functions as a PostgreSQL extension that enables the execution of periodic SQL commands using cron syntax.
- PostgreSQL Task Scheduling - Automates recurring SQL commands and administrative tasks directly within a PostgreSQL database using cron syntax.
- Cron Scheduling - Acts as a scheduler that executes SQL statements on a defined timetable directly inside the database engine.
- Cross-Database Execution - Allows the execution of background tasks within specific target databases instead of just the local instance.
- Multi-Database Orchestration - Manages and triggers scheduled SQL tasks across different target databases from a single instance.
- Database Maintenance Automation - Automates recurring database health tasks such as indexing, cleanup, and data aggregation.
- Cron Timing Parsers - Uses specialized parsers to convert standard cron timing expressions into execution rules for SQL commands.
- Database Connection Management - Manages the lifecycle of connections to multiple target databases within a cluster to execute scheduled tasks.
- Job History Logs - Maintains persistent logs of all scheduled job executions, including start times, end times, and success status.
- Job Cancellation - Provides mechanisms to stop future runs of a periodic task by removing it from the execution queue.
- Job Configuration Modification - Enables updating the timing, command, target database, or active status of existing background tasks.
- Background Job Monitoring - Tracks the execution history and success status of background database tasks through a centralized logging system.
- Job Execution Logging - Tracks the start time, end time, and success status of scheduled tasks via a detailed persistent history table.
- Execution History Auditors - Implements a logging system that audits the historical state, start times, and success outcomes of scheduled jobs.
