Terraform Enterprise version upgrade to v202406-1 (776) fails with ForeignKeyViolation

Problem

When upgrading Terraform Enterprise (TFE) to version v202406-1 (776) or newer, the process may fail due to orphaned records in the memberships, teams, or subscriptions database tables.

The following error appears in the logs, indicating a foreign key violation:

StandardError: An error has occurred, all later migrations canceled:

PG::ForeignKeyViolation: ERROR: insert or update on table "memberships" violates foreign key constraint "fk_rails_ae2aedcfaf"
DETAIL: Key (team_id)=(24744) is not present in table "teams".

This guide provides steps to identify and remove these orphaned records to ensure a successful upgrade. You can perform these checks proactively before an upgrade or after a failed attempt.

Prerequisites

• Access to the Terraform Enterprise instance's underlying host.
• Familiarity with accessing the Terraform Enterprise Rails console.
• If you are recovering from a failed upgrade, roll back the environment to its previously working state before proceeding.

Cause

During the upgrade, database migrations perform checks on foreign key constraints. If a record references an entry in another table that no longer exists (an orphaned record), the constraint check fails, and the upgrade is halted. These orphaned records are often remnants from team or organization deletions in older TFE versions.

The three potential foreign key constraint violations are:

• A memberships record pointing to a non-existent team.
• A teams record pointing to a non-existent organization.
• A subscriptions record pointing to a non-existent organization.

Solutions

Run the following commands in the Terraform Enterprise Rails console to back up and delete any orphaned records.

Important: You must perform these cleanup steps in the specified order. Deleting orphaned teams can create new orphaned memberships. To avoid causing further issues, you must clean up teams before cleaning up memberships.

Solution 1: Clean Up Orphaned Teams

1. Check for orphaned teams that reference a non-existent organization. Run the following command to count them:

   Team.left_joins(:organization).where(organizations: {id: nil}).count

2. If the count is greater than zero, run the following command to back up the orphaned records to a new table named teams_orphans_deleted_20240904 and then delete them.

   sql = <<~SQL
     CREATE TABLE teams_orphans_deleted_20240904 AS
     WITH deleted AS (
       DELETE FROM teams
       WHERE organization_id NOT IN (SELECT id FROM organizations)
       RETURNING *
     )
     SELECT * FROM deleted;
   SQL
   ActiveRecord::Base.connection.execute(sql)

Solution 2: Clean Up Orphaned Memberships

1. Check for orphaned memberships that reference a non-existent team. Run the following command to count them:

   Membership.left_joins(:team).where(teams: {id: nil}).count

2. If the count is greater than zero, run the following command to back up the orphaned records to a new table named memberships_orphans_deleted_20240904 and then delete them.

   sql = <<~SQL
     CREATE TABLE memberships_orphans_deleted_20240904 AS
     WITH deleted AS (
       DELETE FROM memberships
       WHERE team_id NOT IN (SELECT id FROM teams)
       RETURNING *
     )
     SELECT * FROM deleted;
   SQL
   ActiveRecord::Base.connection.execute(sql)

Solution 3: Clean Up Orphaned Subscriptions

1. Check for orphaned subscriptions that reference a non-existent organization. Run the following command to count them:

   Subscription.left_joins(:organization).where(organizations: {id: nil}).count

2. If the count is greater than zero, run the following command to back up the orphaned records to a new table named subscriptions_orphans_deleted_20240904 and then delete them.

   sql = <<~SQL
     CREATE TABLE subscriptions_orphans_deleted_20240904 AS
     WITH deleted AS (
       DELETE FROM subscriptions
       WHERE organization_id NOT IN (SELECT id FROM organizations)
       RETURNING *
     )
     SELECT * FROM deleted;
   SQL
   ActiveRecord::Base.connection.execute(sql)

Outcome

After removing all orphaned records, you can proceed with the upgrade to Terraform Enterprise v202406-1 (776), which should now complete successfully.

Additional Information

• How To Access the Terraform Enterprise Rails Console