Catalog database schema

The NGBackup catalog is a documented, open PostgreSQL schema. Nothing about it is proprietary or hidden: administrators can connect with any PostgreSQL client, build custom reports, or wire third-party monitoring (Grafana, Zabbix, Power BI, …) directly against the tables described here. The schema follows the Bacula lineage, so existing Bacula reporting queries and community dashboards work unchanged.

Only read-only access should be used for reporting — the Director owns all writes. Create a dedicated PostgreSQL role with SELECT privileges for reporting tools.

Main tables

Job

One row per job run — the backbone of every report.

Column	Meaning
JobId	Unique numeric id of the run.
Name	Job name.
Type	Job type (B backup, R restore, V verify, C copy, M migrate, D admin).
Level	Level (F full, D differential, I incremental, …).
ClientId	FK → Client.
JobStatus	One-character termination/run status (see status codes).
SchedTime / StartTime / EndTime	Scheduled, actual start and end timestamps.
JobFiles	Files processed.
JobBytes	Bytes processed.
JobErrors	Non-fatal error count.

Client

One row per protected node: ClientId, Name, Uname (OS/architecture string reported by the File daemon), and the client’s file/job retention periods.

Media (volumes)

One row per volume, physical or logical.

Column	Meaning
VolumeName	Volume label.
PoolId	FK → Pool.
MediaType	Media type string (matches Storage/Device definitions — disk, cloud, tape).
VolBytes	Bytes currently stored on the volume.
VolStatus	Full, Used, Append, Recycle, Error, …
LastWritten	Timestamp of the last write.
LocationId	FK → Location (current physical location, for vault tracking).

Pool

One row per pool: PoolId, Name, PoolType, retention (VolRetention), recycling flags (Recycle, AutoPrune), limits (MaxVols, MaxVolBytes) and the volume LabelFormat.

File / Path / Filename (file index)

The per-file index behind restores and file-level reports. File holds one row per file per job (FileId, FileIndex, JobId, PathId, LStat encoded attributes, MD5/checksum digest); Path de-duplicates directory paths (PathId, Path). In current schemas the filename is carried on the File row (Filename column); older Bacula-lineage catalogs keep a separate Filename table — queries against either shape are documented.

JobMedia

The job↔volume mapping: which volumes each job wrote and where (JobId, MediaId, FirstIndex/LastIndex, block addresses). Join it to answer “which jobs are on this tape?” or “which volumes does restoring this job need?”.

Log

The full job log lines (LogId, JobId, Time, LogText) — the same text list joblog jobid=N prints, queryable for error-pattern reports.

Location & LocationLog

Vault tracking. Location defines named physical places (LocationId, Location, Cost, Enabled); LocationLog records every movement of a volume between locations with timestamp, status and the media id — an audit trail of off-site rotation.

Status codes

A reference table mapping each one-character JobStatus code to its human-readable meaning, so reports can join it instead of hard-coding the legend. The most common codes:

Code	Meaning
T	Terminated normally (OK).
W	Terminated with warnings.
E	Terminated in error.
f	Fatal error.
A	Canceled by user.
R	Running.
C	Created, not yet running.

Example: success rate per client, last 30 days

SELECT c.Name                                            AS client,
       COUNT(*)                                          AS jobs,
       COUNT(*) FILTER (WHERE j.JobStatus = 'T')         AS ok,
       ROUND(100.0 * COUNT(*) FILTER (WHERE j.JobStatus = 'T')
             / COUNT(*), 1)                              AS success_pct
FROM   Job j
JOIN   Client c ON c.ClientId = j.ClientId
WHERE  j.Type = 'B'
  AND  j.SchedTime >= NOW() - INTERVAL '30 days'
GROUP  BY c.Name
ORDER  BY success_pct ASC;

Query access

Three read paths, no direct database credentials required for the first two:

• Console .sql verb — * .sql query="SELECT ..." runs a read-only query through the Director and prints the rows; handy for ad-hoc checks from backup-console.
• REST API — job, media and statistics endpoints expose the same catalog data as JSON; see the REST API reference.
• Direct PostgreSQL — a read-only role pointed at the catalog database, for BI and monitoring tools.

Built-in and scheduled reports over this schema are covered in Reports & analytics.