Jobs and Queues

Every operation pgbr performs is a job on a queue held in Postgres — the same database that stores your job history. The dashboard produces; the worker consumes. There is no separate queue service.

The three queues

All three live in one job_queue table, distinguished by the queue column. Each gets its own poll loop with its own concurrency, so a long migration never starves the backup queue. WORKER_CONCURRENCY (default 5) applies to each queue independently — one worker at the default can be running 15 jobs at once.

Claiming a job

Workers claim with FOR UPDATE SKIP LOCKED, so concurrent workers never block each other and no job is ever handed to two of them:

UPDATE job_queue q
   SET status = 'active', locked_by = $worker, locked_at = now(),
       heartbeat_at = now(), attempts = q.attempts + 1
  FROM (SELECT id FROM job_queue
         WHERE queue = $1 AND status = 'pending' AND run_at <= now()
         ORDER BY run_at, created_at
         FOR UPDATE SKIP LOCKED
         LIMIT $2) AS c
 WHERE q.id = c.id
RETURNING q.*;

The statement commits on its own. No transaction is ever held open across a pg_dump.

A worker wakes on a NOTIFY from the insert trigger and also polls every five seconds. Notifications are fire-and-forget, so the poll is the correctness floor — a dropped notification costs latency, never a lost job.

Job status

A job has two rows: its queue row in job_queue, and its history row in backup_jobs / restore_jobs / migration_jobs. They share an ID.

The history row is written by the processor before the work starts and finalised in both the success and failure paths, so a job never silently vanishes. Any failure after the row exists — dump, non-zero exit, tar, upload — is caught and recorded.

Finished queue rows are purged after seven days. The history tables are the permanent record.

Job IDs

Every job gets a UUID generated where it originates — the dashboard for user-triggered runs, the scheduler for scheduled ones. That UUID is the job_queue row's ID and the history row's ID, which is what lets the UI correlate a request with its row, and what makes a re-delivered job land on the same row instead of orphaning the first one.

Interruption recovery

pgbr has no "mark every running row failed on boot" sweep. That would be unsafe the moment more than one worker exists — a booting replica would fail jobs another replica was actively running.

Instead every worker stamps heartbeat_at on the jobs it holds, every 15 seconds, in a single statement. A reaper requeues anything whose heartbeat is more than 90 seconds stale. Because it keys off the heartbeat rather than a deadline, a job that legitimately runs for six hours is never mistaken for a dead one.

On a clean shutdown the worker stops claiming, waits for in-flight jobs to drain within the container's stop grace period, and hands back anything still running so a restart picks it up immediately instead of waiting out the reaper.

Retries

Jobs are enqueued with max_attempts = 1, meaning no automatic retries on failure. A failed backup stays failed and visible. This is intentional: a dump that failed on a schema error will fail identically on retry, and retrying a restore is a decision with side effects. Re-run it yourself once you know why it failed.

Scheduled jobs

A backup schedule is one row in backup_schedules, and that row is the scheduler. next_run_at says when it fires next; there is no derived state anywhere else and nothing to reconcile.

Every 30 seconds a worker takes a transaction-scoped advisory lock — so exactly one replica ticks — and, for each enabled schedule that has come due, inserts a backup job and computes the next occurrence from the cron expression and timezone. Both happen in the same transaction, so a schedule can't fire without being rescheduled, or vice versa.

A next_run_at of NULL means "not scheduled yet". The tick computes the next occurrence for those without firing them, which is what keeps a fresh install — or an upgrade that just added the column — from stampeding every schedule at once.

Creating, editing, enabling, or disabling a schedule is a single write to that one row. Deleting a database takes its schedules with it through the foreign key cascade.

Live updates

/api/events is an SSE stream carrying progress, completed, and failed from all three queues as { queue, event }. It emits a heartbeat comment every 25 seconds to keep proxies from closing the connection, and cleans up its listener when the client disconnects.

Events travel over Postgres LISTEN/NOTIFY. Each one carries the ID of the user who owns the job, so the route filters by comparison rather than looking every event's job up. You're told when your own jobs move and nothing else — another account's activity isn't even visible as a timing signal.

progress — not active — marks a job's start, because the worker writes its row and then reports progress. By the time the browser refreshes, there's something new to render.

Related