relation "qrtz_job_details" does not exist

Question

whenever trying to trigger a background job in Quartz getting Quartz.JobPersistenceException: Couldn't determine job existence (sda.dsa): 42P01: relation "qrtz_job_details" does not exist. I am storing the jobs in local database and table also exist in database still it throwing exception for storing i give as

builder.Services.AddDbContext<DatabaseContext>(options =>
           options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddQuartz(q =>
{
    q.UseMicrosoftDependencyInjectionJobFactory();
    q.UsePersistentStore(s =>
    {
        s.UsePostgres(options =>
        {
            options.ConnectionString = builder.Configuration.GetConnectionString("DefaultConnection");
        });
        s.UseJsonSerializer();
        s.PerformSchemaValidation = false;
    });

});

approach, code or blogs

Answer 1

Quart's database tables are not created automatically, you should create these tables yourself. Here you can find the scripts => Database scripts

Probably the best practice to achieve this is to create 2 sql files and create a migration using this sql scripts like following (for postgres):

Save following script and name like 20240210113729_CreateQuartzBackgroundJobTables_Up

CREATE SCHEMA Quartz;
CREATE TABLE Quartz.qrtz_job_details
  (
    sched_name TEXT NOT NULL,
    job_name  TEXT NOT NULL,
    job_group TEXT NOT NULL,
    description TEXT NULL,
    job_class_name   TEXT NOT NULL,
    is_durable BOOL NOT NULL,
    is_nonconcurrent BOOL NOT NULL,
    is_update_data BOOL NOT NULL,
    requests_recovery BOOL NOT NULL,
    job_data BYTEA NULL,
    PRIMARY KEY (sched_name,job_name,job_group)
);

CREATE TABLE Quartz.qrtz_triggers
  (
    sched_name TEXT NOT NULL,
    trigger_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    job_name  TEXT NOT NULL,
    job_group TEXT NOT NULL,
    description TEXT NULL,
    next_fire_time BIGINT NULL,
    prev_fire_time BIGINT NULL,
    priority INTEGER NULL,
    trigger_state TEXT NOT NULL,
    trigger_type TEXT NOT NULL,
    start_time BIGINT NOT NULL,
    end_time BIGINT NULL,
    calendar_name TEXT NULL,
    misfire_instr SMALLINT NULL,
    job_data BYTEA NULL,
    PRIMARY KEY (sched_name,trigger_name,trigger_group),
    FOREIGN KEY (sched_name,job_name,job_group)
        REFERENCES Quartz.qrtz_job_details(sched_name,job_name,job_group)
);

CREATE TABLE Quartz.qrtz_simple_triggers
  (
    sched_name TEXT NOT NULL,
    trigger_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    repeat_count BIGINT NOT NULL,
    repeat_interval BIGINT NOT NULL,
    times_triggered BIGINT NOT NULL,
    PRIMARY KEY (sched_name,trigger_name,trigger_group),
    FOREIGN KEY (sched_name,trigger_name,trigger_group)
        REFERENCES Quartz.qrtz_triggers(sched_name,trigger_name,trigger_group) ON DELETE CASCADE
);

CREATE TABLE Quartz.QRTZ_SIMPROP_TRIGGERS
  (
    sched_name TEXT NOT NULL,
    trigger_name TEXT NOT NULL ,
    trigger_group TEXT NOT NULL ,
    str_prop_1 TEXT NULL,
    str_prop_2 TEXT NULL,
    str_prop_3 TEXT NULL,
    int_prop_1 INTEGER NULL,
    int_prop_2 INTEGER NULL,
    long_prop_1 BIGINT NULL,
    long_prop_2 BIGINT NULL,
    dec_prop_1 NUMERIC NULL,
    dec_prop_2 NUMERIC NULL,
    bool_prop_1 BOOL NULL,
    bool_prop_2 BOOL NULL,
    time_zone_id TEXT NULL,
    PRIMARY KEY (sched_name,trigger_name,trigger_group),
    FOREIGN KEY (sched_name,trigger_name,trigger_group)
        REFERENCES Quartz.qrtz_triggers(sched_name,trigger_name,trigger_group) ON DELETE CASCADE
);

CREATE TABLE Quartz.qrtz_cron_triggers
  (
    sched_name TEXT NOT NULL,
    trigger_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    cron_expression TEXT NOT NULL,
    time_zone_id TEXT,
    PRIMARY KEY (sched_name,trigger_name,trigger_group),
    FOREIGN KEY (sched_name,trigger_name,trigger_group)
        REFERENCES Quartz.qrtz_triggers(sched_name,trigger_name,trigger_group) ON DELETE CASCADE
);

CREATE TABLE Quartz.qrtz_blob_triggers
  (
    sched_name TEXT NOT NULL,
    trigger_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    blob_data BYTEA NULL,
    PRIMARY KEY (sched_name,trigger_name,trigger_group),
    FOREIGN KEY (sched_name,trigger_name,trigger_group)
        REFERENCES Quartz.qrtz_triggers(sched_name,trigger_name,trigger_group) ON DELETE CASCADE
);

CREATE TABLE Quartz.qrtz_calendars
  (
    sched_name TEXT NOT NULL,
    calendar_name  TEXT NOT NULL,
    calendar BYTEA NOT NULL,
    PRIMARY KEY (sched_name,calendar_name)
);

CREATE TABLE Quartz.qrtz_paused_trigger_grps
  (
    sched_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    PRIMARY KEY (sched_name,trigger_group)
);

CREATE TABLE Quartz.qrtz_fired_triggers
  (
    sched_name TEXT NOT NULL,
    entry_id TEXT NOT NULL,
    trigger_name TEXT NOT NULL,
    trigger_group TEXT NOT NULL,
    instance_name TEXT NOT NULL,
    fired_time BIGINT NOT NULL,
    sched_time BIGINT NOT NULL,
    priority INTEGER NOT NULL,
    state TEXT NOT NULL,
    job_name TEXT NULL,
    job_group TEXT NULL,
    is_nonconcurrent BOOL NOT NULL,
    requests_recovery BOOL NULL,
    PRIMARY KEY (sched_name,entry_id)
);

CREATE TABLE Quartz.qrtz_scheduler_state
  (
    sched_name TEXT NOT NULL,
    instance_name TEXT NOT NULL,
    last_checkin_time BIGINT NOT NULL,
    checkin_interval BIGINT NOT NULL,
    PRIMARY KEY (sched_name,instance_name)
);

CREATE TABLE Quartz.qrtz_locks
  (
    sched_name TEXT NOT NULL,
    lock_name  TEXT NOT NULL,
    PRIMARY KEY (sched_name,lock_name)
);

create index idx_qrtz_j_req_recovery on Quartz.qrtz_job_details(requests_recovery);
create index idx_qrtz_t_next_fire_time on Quartz.qrtz_triggers(next_fire_time);
create index idx_qrtz_t_state on Quartz.qrtz_triggers(trigger_state);
create index idx_qrtz_t_nft_st on Quartz.qrtz_triggers(next_fire_time,trigger_state);
create index idx_qrtz_ft_trig_name on Quartz.qrtz_fired_triggers(trigger_name);
create index idx_qrtz_ft_trig_group on Quartz.qrtz_fired_triggers(trigger_group);
create index idx_qrtz_ft_trig_nm_gp on Quartz.qrtz_fired_triggers(sched_name,trigger_name,trigger_group);
create index idx_qrtz_ft_trig_inst_name on Quartz.qrtz_fired_triggers(instance_name);
create index idx_qrtz_ft_job_name on Quartz.qrtz_fired_triggers(job_name);
create index idx_qrtz_ft_job_group on Quartz.qrtz_fired_triggers(job_group);
create index idx_qrtz_ft_job_req_recovery on Quartz.qrtz_fired_triggers(requests_recovery);

Save following script and name like 20240210113729_CreateQuartzBackgroundJobTables_Down

DROP SCHEMA IF EXISTS Quartz CASCADE;
DROP TABLE IF EXISTS Quartz.qrtz_fired_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_paused_trigger_grps;
DROP TABLE IF EXISTS Quartz.qrtz_scheduler_state;
DROP TABLE IF EXISTS Quartz.qrtz_locks;
DROP TABLE IF EXISTS Quartz.qrtz_simprop_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_simple_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_cron_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_blob_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_triggers;
DROP TABLE IF EXISTS Quartz.qrtz_job_details;
DROP TABLE IF EXISTS Quartz.qrtz_calendars;

And create a migration which will empty then populate it as :

    public partial class CreateQuartzBackgroundJobTables : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(MigrationExtensions.GetRawSql("20240210113729_CreateQuartzBackgroundJobTables_Up"));
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(MigrationExtensions.GetRawSql("20240210113729_CreateQuartzBackgroundJobTables_Down"));
        }
    }

MigrationExtensions Class:

    public class MigrationExtensions
    {
        public static string GetRawSql(string fileName)
        {
            var filePath = Path.Combine(Directory.GetCurrentDirectory(), "SqlMigrationFiles", $"{fileName}.sql");
            return File.ReadAllText(filePath);
        }
    }

If the problem still remains, create a new connection string on appsettings file and add "SearchPath={quartz_schema_name("quartz" in example I gave)}" like following:

"ConnectionStrings": {
    "Quartz": "User ID=your_user_name;Password=your_password;Server=server_addres;Port=5432;Database=your_database;Pooling=true;CommandTimeout=300;SearchPath=quartz"
}

Then while configuring quartz :

x.UsePersistentStore(storeOptions =>
{                  
 storeOptions.UsePostgres(configuration.GetConnectionString("Quartz"));
 storeOptions.UseJsonSerializer();
});