How to verify TFE version in PostgreSQL data

Introduction

The version of the Terraform Enterprise application you install/run must be compatible with the schema data in the associated PostgreSQL database. 

If these versions don't match there are different scenario's that can happen.

1. Current TFE version is lower than the schema data version in the associate PostgreSQL database. 
So TFE will fail to start properly and different error messages are seen. 

Example errors:

replicatedctl app status
[
 {
  "AppID": "15d424fba1e64bf47618f01b764e765d",
  "Sequence": 607,
  "PatchSequence": 0,
  "State": "stopped",
  "DesiredState": "started",
  "Error": "Container ptfe_registry_migrations failed: Container 77966a9d05c331d80fad1620ede19d763bab3b0b4bba897e71b61310ad585608 exited with non-zero exit status 1: ",
  "IsCancellable": false,
  "IsTransitioning": false,
  "LastModifiedAt": "2023-03-20T13:16:09.155874968Z"
 }
]

replicatedctl app status
[
 {
  "AppID": "ff26b23cbe3042f852c5ec5ab0ee27d2",
  "Sequence": 655,
  "PatchSequence": 0,
  "State": "started",
  "DesiredState": "started",
  "Error": "Ready state command canceled: context deadline exceeded",
  "IsCancellable": false,
  "IsTransitioning": false,
  "LastModifiedAt": "2023-03-20T14:28:10.714955728Z"
 }
]

2. Terraform Enterprise version is higher than the DB schema version in the associate PostgreSQL database. TFE will try upgrading the schema data in the associated PostgreSQL database. If that succeeds TFE becomes available. 

3.  Current TFE version is higher than the DB schema version in the associate PostgreSQL database.
TFE will try upgrading the schema data in the associated PostgreSQL database. This fails because a mandatory release version has been skipped. TFE will fail to start properly with an error message. Please see the release table here for required releases.

Example error:

replicatedctl app status
[
 {
  "AppID": "837ee5c406334c744b60eb3086f09573",
  "Sequence": 636,
  "PatchSequence": 0,
  "State": "stopped",
  "DesiredState": "started",
  "Error": "Container tfe-nginx failed: Timeout waiting for event nginx started-80",
  "IsCancellable": false,
  "IsTransitioning": false,
  "LastModifiedAt": "2023-03-22T06:23:18.964966651Z"
 }
]

Verify the PostgreSQL schema version

There are different ways to verify the DB schema version within PostgreSQL. It depends on the configuration and the status of the environment which one will work for you. 

1. Using the atlas container

docker exec -it tfe-atlas /usr/bin/init.sh /app/scripts/wait-for-token -- bash -i -c 'cd /app && ./bin/rake db:version'

 The output will show something like the following

Current version: 20230206193105

2. Using psql to login to your PostgreSQL database

• Login to the TFE PostgreSQL environment

sudo docker exec -ti tfe-atlas /bin/bash
psql -h <fqdn> -p 5432 -U <username>

• Connect to the database you have installed TFE in. This example it is in the tfe database

postgres=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 rdsadmin  | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | rdsadmin=CTc/rdsadmin
 template0 | rdsadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/rdsadmin          +
           |          |          |             |             | rdsadmin=CTc/rdsadmin
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 tfe       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(5 rows)

postgres=> \c tfe

• Execute the following query to get the details

tfe=> SELECT * FROM rails.schema_migrations ORDER BY version DESC LIMIT 1;
    version     
----------------
 20230206193105

3. Create a support bundle.

4. In the file primary/app/logs/ptfe_migrations.stdout or primary/app/logs/tfe-migrations.stdout you should find an entry like

Current version: 20230206193105

Releases with FDO

• Login to rails

tfectl support console

• Database version

ActiveRecord::Base.connection.execute("SELECT version();").first["version"]

• TFE schema application version

ActiveRecord::Base.connection.execute("SELECT * FROM rails.schema_migrations ORDER BY version DESC LIMIT 1").first["version"]

• TFE private registry version

result = ActiveRecord::Base.connection.execute("SELECT * FROM registry.schema_migrations;").first
version = result["version"]
dirty = result["dirty"]

puts "Version: #{version}, Dirty: #{dirty}"

Overview TFE release vs PostgreSQL schema data

Additional Information

• Terraform release overview can be found here