How to verify the on-hold runs on Terraform Enterprise

Introduction

Terraform Enterprise has the Admin Settings where you can view all the runs that are currently On Hold.

Go to the Admin section:

And select the Runs:

The On Hold view shows runs that are not started and this can be for different reasons.
The details can be found on the Needs Attention page:

On the above screenshots, the 7 runs that are On Hold are waiting for someone to confirm and apply the run that was shown on the Needs Attention page:

This can be easy to see with just a few On Hold runs but with many it might become more difficult.

Expected Outcome

Provide SQL queries to get a better understanding of the runs and which are On Hold for more easy troubleshooting or overview of your environment and current runs. 

Prerequisites 

• Ability to access to PostgreSQL database directly
  or
• Ability to access the Terraform Enterprise container

Use Case

• Login to the PostgreSQL database used by Terraform Enterprise.
  
  
• If you have access to the Terraform Enterprise, you can do the following steps from inside the Terraform Enterprise container:

psql postgres://$TFE_DATABASE_USER:$TFE_DATABASE_PASSWORD@$TFE_DATABASE_HOST/$TFE_DATABASE_NAME?$TFE_DATABASE_PARAMETERS

Query 1:

See all the runs that are currently waiting or running based

SELECT 
  organizations.name AS organization_name,
  workspaces.name AS workspace_name,
  COUNT(runs.external_id) AS total_runs,
  runs.status
FROM rails.runs AS runs
  JOIN rails.workspaces AS workspaces 
    ON runs.workspace_id = workspaces.id
  JOIN rails.organizations AS organizations 
    ON workspaces.organization_id = organizations.id
WHERE 
  runs.status IN ('pending', 'planned', 'applying', 'planning', 'cost_estimated', 'policy_checked')
GROUP BY organizations.name, workspaces.name, runs.status
ORDER BY workspaces.name;

Output example:

The output shows 1 run has cost_estimated and therefore waiting for someone to confirm the apply. The 7 other runs are pending because of this. 

 organization_name | workspace_name | total_runs | status 
-------------------+----------------+------------+----------------
test               | null_resource  | 1          | cost_estimated
test               | null_resource  | 7          | pending

Query 2:

See all the runs running and on-hold with there details

SELECT 
  organizations.name "organization_name",
  workspaces.name "workspace_name",
  runs.external_id "run_id", 
  runs.status "run_status", 
  runs.updated_at "last_change" 
FROM rails.runs AS runs 
  JOIN rails.workspaces AS workspaces 
    ON runs.workspace_id = workspaces.id
  JOIN rails.organizations AS organizations 
    ON workspaces.organization_id = organizations.id
where 
  runs.status in ('pending','planned', 'applying','planning','cost_estimated','policy_checked')
order by 1,2,3,4,5;

Output example:

You see all the runs and run_id of runs and there current status that require action or need to run.

 organization_name | workspace_name | run_id               | run_status     | last_change 
-------------------+----------------+----------------------+----------------+----------------------------
test               | null_resource  | run-7k88d2Z6mykddBqv | pending        | 2025-02-21 12:38:07.296518
test               | null_resource  | run-BsveDT2FB9Ty2z5M | pending        | 2025-02-21 12:37:34.277917
test               | null_resource  | run-fYLzT1KNngjCCwLM | pending        | 2025-02-21 12:38:01.604247
test               | null_resource  | run-gAARegBdUsyhyTxg | pending        | 2025-02-21 12:37:39.756474
test               | null_resource  | run-hCpPiALcBpvHDHdD | pending        | 2025-02-21 12:37:52.136427
test               | null_resource  | run-k7XXy5H49jKfUscp | pending        | 2025-02-21 12:37:47.540492
test               | null_resource  | run-V2Gsjo5UmNin5dw2 | pending        | 2025-02-21 12:37:43.851403
test.              | null_resource  | run-ypv34BGTus1AWkuU | cost_estimated | 2025-02-21 12:37:21.670256

Query 3:

Runs that are currently planning and applying

SELECT 
  organizations.name AS organization_name,
  workspaces.name AS workspace_name,
  runs.external_id AS run_id,
  runs.status AS run_status,
  runs.updated_at AS last_change
FROM rails.runs AS runs 
  JOIN rails.workspaces AS workspaces 
    ON runs.workspace_id = workspaces.id
  JOIN rails.organizations AS organizations 
    ON workspaces.organization_id = organizations.id
WHERE runs.status IN ('applying', 'planning')
ORDER BY 
  organization_name, 
  workspace_name, 
  run_id, 
  run_status, 
  last_change;

Output example:

Runs that are actually executing now for workspaces.

 organization_name | workspace_name | run_id               | run_status | last_change 
-------------------+----------------+----------------------+------------+---------------------------
test               | null_resource  | run-ypv34BGTus1AWkuU | applying   | 2025-02-21 13:18:00.81913