PostgreSQL

This guide walks you through deploying Coder with an external PostgreSQL database.

Background

For convenience and ease of installation, Coder's default Helm Chart settings will deploy a PostgreSQL database within the installation's Kubernetes namespace. This is useful for evaluation purposes; however, we recommend using an out-of-cluster database for production to streamline maintenance operations, such as backups and upgrades. The database state is not backed up and will be lost when deleting the Kubernetes namespace or cluster.

Configuration steps

For optimal performance, it is important to ensure that the round-trip latency between the Coder control plane services and the database is low. We recommend ensuring that the database is within the same data center as the control plane, such as within the same cloud availability zone.

Set up a PostgreSQL database for use with Coder. If you do not already have an instance available, consider using the service from your cloud provider:
Configure a private IP address for use with your PostgreSQL instance. You will set this in the Helm settings (values file)).
If your PostgreSQL instance requires a password, you will need to create a Kubernetes Secret containing the password:
```
kubectl create secret generic <NAME> --from-file=password=/dev/stdin
```
We recommend using the syntax provided above, which reads credentials from the console, to avoid inadvertently storing credentials in shell history files.

Normally, we set the PostgreSQL password as an environment variable in the coderd deployment with a reference to the Kubernetes secret. If this is not desirable, you can instead mount the secret as a file which Coder will read at startup. To do this, set the Helm value postgres.noPasswordEnv to true. This will mount the secret under /run/secrets/<.Values.postgres.passwordSecret>/password and set the environment variable DB_PASSWORD_PATH for coderd to that value.
Get the port number for your PostgreSQL instance:
```
SELECT *
FROM pg_settings
WHERE name = 'port';
```
Get the user of the PostgreSQL instance:
```
\du
```
Get the name of the database within your PostgreSQL instance in which you're currently working:
```
SELECT current_database();
```
Get the name of the secret you created for your PostgreSQL instance's password:
```
kubectl get secrets --namespace=<your-coder-namespace>
```

Set the database name, port number, user, and password secret created in the prior steps in your Helm values file. Coder will use these credentials to connect to your PostgreSQL instance:

postgres:
  host: "<your-postgres-private-ip>"
  port: "<your-postgres-port>"
  user: "<your-postgres-user>"
  database: "<your-db-name>"
  passwordSecret: "<your-postgres-secret-name>"

Ensure that there are no trailing white spaces in your password secret.

mTLS connections

Coder supports mutual TLS (mTLS) connections to Postgres databases; if you'd like to enable this feature, provide the necessary values in postgres.ssl.

Using AWS IAM to authenticate with an RDS instance

You can set the Postgres connector option in the Helm chart. If you'd like to use the environment's AWS IAM account to authenticate with an RDS instance, set postgres.connector accordingly.

Upgrade Coder

At this point, you can install/upgrade your Coder instance using the updated Helm chart.

To install Coder for the first time:

helm install coder coder/coder --namespace=<your-coder-namespace> \
  --version=<VERSION> --values=current-values.yml --wait

To upgrade Coder:

helm upgrade coder coder/coder --namespace=<your-coder-namespace> \
  --version=<VERSION> --values=current-values.yml --wait

If the upgrade fails for any reason, please run the helm rollback command and contact our support team for assistance.

Once you complete this process, you'll be able to access Coder using the external IP address of the ingress controller in your cluster.

Scaling Coder & PostgreSQL

Each Coder replica maintains a pool of connections to Postgres, capped at a maximum of 30 connections by default. This value strikes a balance between allowing multiple concurrent requests and using up connections on the Postgres database.

To reconfigure this value, set the environment variable CODER_MAX_DB_CONNECTIONS to your desired value in the coderd.extraEnvs section of the Helm chart.

Important: You must ensure that Coder's maximum connections per replica times the number of Coder replicas is less than the maximum number of connections your Postgres instance allows, assuming Coder is the only application using the Postgres instance (less if other applications will also consume connections). For example, with 3 Coder replicas and the default max connections of 30, you must ensure Postgres is configured to allow 90 connections. If Postgres runs out of connections Coder may fail API requests or builds.

By default, Postgres allows a maximum of 100 open connections (less 3 reserved for superusers). Consult Postgres documentation, or your database administrator for information on how to change this value.

Consider monitoring the go_sql_open_connections metric to see how many connections Coder uses in different load scenarios.