---
title: "How do I query the local PostgreSQL JobQueue tables directly?"
url: "https://dm0bkdes5q7a2ls.productfruits.help/en/article/how-do-i-query-the-local-postgresql-jobqueue-tables-directly"
language: "en"
last_updated: "2026-09-17T22:37:42.420Z"
---

# How do I query the local PostgreSQL JobQueue tables directly?

In **Spirion Sensitive Data Platform (SDP) v13.6+**, the agent-side queuing architecture stores all scan tasks and results locally in PostgreSQL.

Because each **Queue is a SQL table**, each **Item is a row**, and **Progress is a status transition**, you can query the database directly to inspect live scan progress, diagnose stuck workers, or troubleshoot shipper backlogs.

## 1\. Connection Parameters & Credentials

The local PostgreSQL instance is isolated and runs directly on the Agent host:

-   **Host:** `127.0.0.1` / `localhost`
-   **Default Ports:**
-   -   **PostgreSQL (Direct):** `5433` *(used to avoid conflict with standard 5432)*
    -   **pgBouncer (Pooled):** `6433` *(used to avoid conflict with standard 6432)*
-   **Connection Strings & Port Verification:**
-   -   **Windows Registry:** `HKLM\SOFTWARE\WOW6432Node\Identity Finder\Postgres` (contains `Port`, `PgbouncerPort`, and username configs).
    -   **Linux / macOS:** `/etc/pgbouncer/pgbouncer.ini` or `/opt/identityfinder/config/agent.conf`.
-   **CLI Connection via** `psql`:
    
    ```
    psql -h 127.0.0.1 -p 5433 -U <PostgresUser> -d <DatabaseName>
    ```
    

## 2\. Core Queue Tables & Status Codes

| Table Name | Scope | Purpose |
| --- | --- | --- |
| `search_queue_<scanId>` | Per-Scan | Contains the "to-do" list of target paths/items generated by the Discovery Agent for Search workers to claim. |
| `shipper_queue` | Permanent | Durable staging area for sensitive data match payloads waiting to be POSTed to SaaS Ingress by `IDFMessagingSvc.exe`. |
| `heartbeats` | System | Stores worker/agent liveness timestamps used by the maintenance worker to detect dead or crashed processes. |

#### **Status Column Values (**`status smallint`):

-   `0` = **Pending** *(Waiting to be claimed)*
-   `1` = **Processing** *(Claimed and actively being scanned)*
-   `2` = **Processed** *(Completed successfully)*
-   `3` = **Failed** *(Encountered an error; eligible for retry)*
-   `4` = **PermFailed** *(Exhausted retry budget; permanently failed)*

## 3\. Essential Troubleshooting SQL Queries

### **A. Check Overall Job Distribution for an Active Scan**

To see how many items are waiting, actively scanning, completed, or failed run the following commands:

```
SELECT 
    status,
    CASE status
        WHEN 0 THEN '0 - Pending (Waiting)'
        WHEN 1 THEN '1 - Processing (Scanning)'
        WHEN 2 THEN '2 - Processed (Complete)'
        WHEN 3 THEN '3 - Failed (Retrying)'
        WHEN 4 THEN '4 - PermFailed (Exhausted)'
    END AS status_name,
    COUNT(*) AS total_items
FROM "search_queue_<scanId>"
GROUP BY status
ORDER BY status;
```

### **B. Identify Stuck/Orphaned Jobs in** `Processing`

If a scan appears frozen, check which workers own the in-flight tasks and when they were last updated using the following commands:

```
SELECT 
    id, 
    endpointid AS owning_agent_id, 
    retry_count, 
    created, 
    updated, 
    payload
FROM "search_queue_<scanId>"
WHERE status = 1
ORDER BY updated ASC
LIMIT 25;
```

*(If rows remain in* `Processing` with stale `updated` timestamps $> 1\\text{ minute}$, the EPS background maintenance worker will automatically return them to `Pending` for another worker to claim).

### **C. Check Agent Liveness & Heartbeats**

Verify that search workers are actively communicating with the queue host using the following commands:

```
SELECT 
    id AS endpoint_id, 
    updated AS last_heartbeat, 
    NOW() - updated AS time_since_heartbeat
FROM heartbeats
ORDER BY updated DESC;
```

### **D. Check the Shipper Queue Backlog (Outbound Results)**

Confirm whether match results are staged locally or backing up due to network/Ingress issues using the following commands:

```
SELECT 
    status,
    CASE status
        WHEN 0 THEN '0 - Pending (Waiting to Ship)'
        WHEN 1 THEN '1 - Processing (Shipping to Ingress)'
        WHEN 2 THEN '2 - Processed (Shipped OK)'
        WHEN 3 THEN '3 - Failed (Ingress Unreachable)'
        WHEN 4 THEN '4 - PermFailed'
    END AS shipper_status,
    COUNT(*) AS result_batches
FROM shipper_queue
GROUP BY status
ORDER BY status;
```

### **E. Inspect Failed Items & Error Reasons**

Review jobs that failed inspection to identify problematic file paths or permission locks using the following commands:

```
SELECT 
    id, 
    endpointid, 
    retry_count, 
    updated, 
    payload
FROM "search_queue_<scanId>"
WHERE status IN (3, 4)
ORDER BY updated DESC;
```