Connect to a PostgreSQL cluster

This topic describes how to configure Terraform Enterprise to connect to an external cluster of PostgreSQL database servers. You only need to complete this task under the following conditions:

You want to operate Terraform Enterprise in active-active or external mode. Refer to Configure operational mode for additional information.
You want to connect Terraform Enterprise to a PostgreSQL database cluster. To connect to a single PostgreSQL node, refer to Connect to an external PostgreSQL database.
You want to configure Terraform Enterprise to failover to a PostgreSQL database replica node. Refer to PostgreSQL database failover for additional information.

Before you connect to a highly-available PostgreSQL cluster, review the Known issues section so that you are aware of potential issues.

Introduction

Complete the following steps to configure Terraform Enterprise to store and retrieve the data in an externally-managed PostgreSQL cluster:

Prepare the PostgreSQL server to host the data. Preparation includes creating a user appropriate permissions and creating the extensions in the database.
Specify the connection settings in your deployment configuration. If your server requires additional connection parameters, you must also specify them in the configuration. You may need to take additional action to resolve issues related to a failover event.

Requirements

Before proceeding, verify that you meet the following requirements.

Server

One of the following servers is required:

PostgreSQL server, such as Amazon RDS for PostgreSQL, version 12.x, 13.x, 14.4 and up, 15.x or 16.x
Note that PostgreSQL v12 will reach end of life on November 12, 2024. As a result, Terraform Enterprise will no longer v12 after that date.
PostgreSQL-compatible database service, such as Amazon Aurora PostgreSQL.
Self-managed PostgreSQL-compatible server cluster, such as Patroni PostgreSQL

User

Create a PostgreSQL user with the following permissions on the database:

Permissions to create, modify, and read all tables and indices on all schemas within the database. Database owners commonly have these permissions.
Permissions to create extensions. If you are unable to create a user with the CREATE EXTENSION privilege, refer to Create extensions for instructions on creating the necessary extensions.

Load balancer

Terraform Enterprise requires a single URL to configure the PostgreSQL backend. In a cluster, the URL should point to the load balancer, which handles the distribution of connections to the appropriate database nodes.

Runtime

You can only connect to a database cluster when deploying Terraform Enterprise to the following runtimes:

Nomad
Kubernetes
OpenShift
Podman
Docker

For information about connecting to an external database for Replicated deployments, refer to PostgreSQL Requirements for Terraform Enterprise on Replicated.

Create extensions

Create extensions for the rails, vault, registry, and task_worker PostgreSQL schemas. The database server automatically creates these schemas if they do not already exist.

Run the following commands on the PostgreSQL server to create the extensions:

CREATE EXTENSION IF NOT EXISTS "hstore" WITH SCHEMA "rails";
CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA "rails";
CREATE EXTENSION IF NOT EXISTS "citext" WITH SCHEMA "registry";

Specify PostgreSQL connection settings

Add the following settings to your Terraform Enterprise configuration:

Set the TFE_DATABASE_HOST variable to the location of your PostgreSQL server. Format the location as HOST[:PORT], for example db.example.com or db.example.com:5432. Multi host connection strings are not supported.
Set the TFE_DATABASE_NAME variable to the name of the database you want to store the data in.
Set the TFE_DATABASE_USER variable to the user name you want to use to access the database.
Set the TFE_DATABASE_PASSWORD variable to the password for the user.
Set the TFE_DATABASE_RECONNECT_ENABLED variable to true.

Refer to Database settings in the configuration reference for addtional information.

Additional connection parameters

Add the TFE_DATABASE_PARAMETERS variable to your configuration and specify any additional connection parameters necessary to connect to the server.

`sslmode`

When providing extra keyword parameters for the database connection, the sslmode parameter only allows the following values:

When operating Terraform Enterprise in external mode, the sslmode parameter is set to require by default. When operating Terraform Enterprise in disk mode, the sslmode parameter is set to disable by default.

Terraform Enterprise provides a certificates file at /etc/ssl/private/terraform-enterprise/bundle.pem, which is required by the verify-full and verify-ca modes.

Refer to the PostgreSQL library documentation for additional information about using sslmode.

Client certificate configuration

Terraform Enterprise does not support PostgreSQL client certificates configuration due to the limitation of storing certificate files for the sslcert and sslkey connection parameters. Refer to Client Certificates in the PostgreSQL documentation for additional information.

Post-failover tasks

In the event that Terraform Enterprise fails over to the secondary database node, you may need to perform the following actions.

Restart failed runs

Runs can enter a non-terminal state, such as pending, and fail to progress when a failover occurs. This is because Terraform Enterprise may be connected to read-only instances of the database. You can perform one of the following tasks to resolve the non-terminal state:

Cancel the run: You can cancel plan operations that are unfinished. A plan operation in the pending state blocks the workspace until it is canceled. You can cancel the run on the run’s page or in the organization settings. Refer to [Runs]](/terraform/cloud-docs/users-teams-organizations/organizations#runs) in the organization settings documentation for additional information.
Start a new run: If the operation is finished, but the runs state is still non-terminal, then you cannot cancel the run. Runs in this state do not block the workspace.

Restart Terraform Enterprise

If Terraform Enterprise does not return to full operational capacity or fully go down after a failover, we recommend manually restarting the faulty Terraform Enterprise nodes. Restarting forces Terraform Enterprise to reconnect to the correct PostgreSQL node.

Restart the Vault process

A failover may affect Vault when Terraform Enterprise is connected to a Patroni cluster with HAProxy configured to check server status at an interval of one second or longer. If a failover occurs and Vault is still connected to the read-only node, then Vault can seal.

A single node can seal Vault and render it non-functional. Requests that Terraform Enterprise routes to the affected instance will fail. Some runs will also fail when Terraform Enterprise is in active-active mode and deployed with multiple nodes.

Restart the Vault process or restart the node to resolve this issue.

Known Issues

Interruptions to the database connection affect ongoing processes and lead to issues in Terraform Enterprise. These issues are related to network timing and do not occur reliably. They are also more likely to occur if a failover occurs under high load.

Read replicas are not supported. Terraform Enterprise does not distinguish between read and write endpoints. You must route all database interactions, including reads and writes, through the load balancer to the primary node. This ensures that Terraform Enterprise always interacts with the correct node.

Multi-primary topology is not supported. Terraform Enterprise is designed to operate with a strongly consistent data model. Therefore, we do not recommend using a multi-writer cluster configuration. In multi-writer setups, data written to one primary node may not be immediately available to others, leading to potential data inconsistencies. To maintain data integrity and consistency, all write operations should be directed to a single primary node. This guarantees that data is immediately consistent and available across the system.