Terraform Enterprise upgrade fails with error: "Dirty database version 94"

Problem

When upgrading a Replicated deployment of Terraform Enterprise (TFE), the process fails and the application does not start.

Logs from the tfe-registry-migrations container show one of the following errors:

error: Dirty database version 94. Fix and force version.

migration failed: sequence must have same owner as table it is linked to in line 0: -- Start a transaction.

Cause

This issue occurs when tables in the registry schema of the TFE database are owned by a user other than the designated TFE database user, such as a cloudsqlsuperuser.

You can verify the table ownership by connecting to the database and running a query.

1. Connect to the tfe-atlas container on your TFE host.

   $ sudo docker exec -ti tfe-atlas /bin/bash

2. From within the container, connect to the PostgreSQL database. Replace the placeholder values (<..._name>) with the credentials for your environment.

   $ psql -h <database_hostname> -p <port_number> -U <username> -d <database_name>

3. Run the following query to check the owner of the registry tables.

   select * from pg_tables where schemaname = 'registry';

   The command returns output where the tableowner column does not match the TFE database user.

   schemaname | tablename            | tableowner        | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
   -----------+----------------------+-------------------+------------+------------+----------+-------------+-------------
   registry   | categories           | cloudsqlsuperuser |            | t          | f        | t           | f
   registry   | categories_providers | cloudsqlsuperuser |            | t          | f        | t           | f
   ## ...

Solution

Follow these steps to reassign table ownership to the correct TFE database user and complete the upgrade.

1. Roll back the environment

   Restore both the Terraform Enterprise instance and the PostgreSQL database to their last working state before the failed upgrade attempt. You can accomplish this using a snapshot or by reinstalling the previous version of Terraform Enterprise.

2. Identify the TFE database user

   On the TFE host, run the following command to retrieve the database connection details, including the correct username (pg_user).

   $ replicatedctl app-config export | grep -A2 -e pg_user -e pg_netloc -e pg_dbname -e pg_password

   The command returns the configuration values.

   "pg_dbname": { "value": "tfe"},
   "pg_netloc": { "value": "****.rds.amazonaws.com:5432"},
   "pg_password": { "value": "<password>"},
   "pg_user": { "value": "<user>"},

3. Connect to the database

   Use the values from the previous step to connect to the PostgreSQL database.

   $ psql -h <pg_netloc_host> -p <pg_netloc_port> -U <pg_user> -d <pg_dbname>

4. Reassign table ownership

   Execute the REASSIGN OWNED command to transfer ownership of all database objects from the incorrect user (e.g., cloudsqlsuperuser) to the correct TFE database user (<pg_user>).

   REASSIGN OWNED BY cloudsqlsuperuser TO <pg_user>;

5. Verify the ownership change

   Run the query again to confirm that the tableowner is now the correct TFE user.

   select * from pg_tables where schemaname = 'registry';

   The output should now show the correct owner.

   schemaname | tablename            | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
   -----------+----------------------+------------+------------+------------+----------+-------------+-------------
   registry   | categories           | <pg_user>  |            | t          | f        | t           | f
   registry   | categories_providers | <pg_user>  |            | t          | f        | t           | f
   ## ...

6. Proceed with the upgrade

   You may now retry the Terraform Enterprise upgrade.

Outcome

The Terraform Enterprise upgrade should now complete successfully.