Terraform Enterprise upgrade from v202302-1(681) to v202303-1(688) fails with error: "Dirty database version 94"

Problem

When upgrading your Terraform Enterprise (TFE) version from v202302-1 (681) to v202303-1 (688) it fails and TFE will not start.

In the logs of the 'tfe-registry-migrations' the following error is found:

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

Another error than can be found is:

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

Cause

In the database there are 'registry' tables that are not owned by the TFE database user, but by another user: for example 'cloudsqlsuperuser'.

To check the current owner of the registry tables, perform the following:

1. Login with ssh to your TFE host
2. Connect to the atlas container: sudo docker exec -ti tfe-atlas /bin/bash
3. Login to psql with the following command: psql -h <db_fqdn> -p <port_number> -U <username> -d <database_name>
   (Replace the values within <> with your own values.)
4. Run the following query: select * from pg_tables where schemaname = 'registry'

You will see output like this:

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

Here you can see in the 'tableowner' column there is the user 'cloudsqlsuperuser'. 

This should be your own TFE database user.

Solution:

Rollback the environment and assign the correct user to the tables with the following steps:

• Rollback both TFE and Postgres DB to latest working status prior the upgrade with old version 681  (either by snapshot, or re-installing TFE with version 681)
• Login with 'ssh' to your TFE host
  
  
• Check your TFE database user with: replicatedctl app-config export | grep -A2 -e pg_user -e pg_netloc -e pg_dbname -e pg_password

Output:

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

• Note:
  - pg_netloc
    (this will be in the format host:port, the default port is 5432 if none is given)
  
  
• Login to 'psql' with the following command: psql -h <pg_netloc host> -p <pg_netloc port> -U <pg_user> -d <pg_dbname>
  Replace the values within <> with your own values.
  
  Enter your 'pg_password' when required.
  
  
• To reassign the owner of the registry tables, issue the command: REASSIGN OWNED BY cloudsqlsuperuser TO <pg_user>;
  (Replace '<pg_user>' with your database user.)
  
  
• Check user has changed with: select * from pg_tables where schemaname = 'registry'
  

  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
  ...

  
  
• Upgrade according to your plan.

Outcome

The upgrade should now proceed successfully.

Additional Information