How to verify the on-hold runs on Terraform Enterprise

Introduction

In Terraform Enterprise, the Admin dashboard provides a view of runs that are 'On Hold'. While the UI is useful for a small number of runs, it can become difficult to manage at scale. This guide provides several SQL queries to run directly against the Terraform Enterprise PostgreSQL database for a more detailed and scalable overview of run statuses.

Expected Outcome

After completing this guide, you will be able to query the Terraform Enterprise database to identify runs that are on hold, pending, or actively running, allowing for easier troubleshooting and environment overview.

Prerequisites

• Administrative access to the Terraform Enterprise instance.
• Access to the PostgreSQL database used by Terraform Enterprise, either directly or by executing commands within the TFE container.

Procedure

1. Connect to the PostgreSQL database used by Terraform Enterprise. If you have shell access to the TFE instance, you can connect from within the container using the application's environment variables.

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

2. Execute one of the following queries to retrieve information about the runs in your environment.

SQL Queries for Run Analysis

Query 1: View a summary of runs by status

The following query provides a summary of all runs that are currently waiting or running, grouped by organization, workspace, and status.

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;

Example Output

This output shows one run is awaiting confirmation (cost_estimated) and seven other runs are pending as a result.

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

Query 2: View detailed information for each run

This query lists all runs that are on-hold or active, including their unique run ID and last modification time.

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;

Example Output

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: View only actively running plans and applies

Use this query to see only the runs that are currently in the planning or applying state.

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;

Example Output

This output shows a single run that is currently executing an apply.

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