Using your own PostgreSQL server

As of Sourcegraph v6.0.0, the minimum supported version of Postgres is v16

Please review the PostgreSQL documentation for a complete list of requirements

Recommendations

Use a managed Postgres service, as it provides you with much greater capabilities to manage your databases, ex. snapshots / restores, scale, etc.
Use a separate server / host for each of the 3 Postgres databases, to isolate and manage performance load and storage growth separately
Take regular backups of all the Postgres databases

Instructions

Docker Compose

Configure your database connection parameters as environment variables, as shown in the External Databases example docker-compose.override.yaml
See Docker's documentation Environment variables in Compose for secure methods to pass in sensitive values

Helm

Configure your database connection parameters as Kubernetes secrets, as shown in the External Databases example Helm override.yaml

Notes

If your PostgreSQL server does not support SSL, set PGSSLMODE: disable instead of PGSSLMODE: require. Note that this is less secure.

Most standard PostgreSQL environment variables may be specified (PGPORT, etc). See the Postgres documentation for details.

Usage with AWS RDS IAM Auth

For AWS RDS for Postgres, you have the option to use IAM database authentication to avoid using static database credentials. See the AWS documentation for details.

To enable IAM Auth:

Enable IAM authentication on the RDS instance
Create the database user account using IAM authentication
Create IAM policy for IAM database access
Create an IAM role for your compute workload and grant the role with the above policy
Have your compute instances assume the IAM role
- For EKS, see IAM roles for service accounts
- For EC2 (docker-compose), see IAM roles for Amazon EC2
Configure the PG_CONNECTION_UPDATER: EC2_ROLE_CREDENTIALS environment variable
- In Docker Compose, add it to migrator, and all sourcegraph-frontend-* services, as shown in the External Databases example docker-compose.override.yaml
- In Helm, add it to the migrator and frontend values, as shown in the AWS example Helm override.yaml
Optional: If you prefer to use regional STS endpoints for improved latency, also set the AWS_STS_REGIONAL_ENDPOINTS: regional environment variable. See AWS documentation for details.

The Postgres user/role your Sourcegraph instance uses should have SUPERUSER permissions to complete the first migration query in each database, when initializing fresh new databases, as it installs extensions. This may be prohibited in your environment, so we have workarounds available below.

At minimum, we expect that the user has the ALL permission on the Database objects, which includes:

SHELL
CONNECT
CREATE
DELETE
EXECUTE
INSERT
REFERENCES
SELECT
TEMPORARY
TRIGGER
TRUNCATE
UPDATE
USAGE

Using restricted permissions

When initializing fresh new databases, the first migration query in each database installs extensions, so it must be performed by a user with SUPERUSER permissions. Find your SUPERUSER username and password, to connect to your databases and execute the below queries.

On AWS RDS, you will need to perform these operations using the rds_superuser role, because RDS does not grant SUPERUSER privileges to other database users.

Pgsql (frontend) database

Update these variables to match your deployment of the Sourcegraph pgsql (frontend) database following the guidance from the instructions section

BASH
# Export the environment variables to connect to the database
export PGHOST=psql
export PGPASSWORD=rds_superuser_password
export PGPORT=5432
export PGSSLMODE=require
export PGUSER=rds_superuser
 
# Export the environment variables to create the database and service account
export PGDATABASE=sg
export SERVICE_ACCOUNT_PASSWORD=sg_service_account_password
export SERVICE_ACCOUNT_USERNAME=sg_service_account_username
 
# Connect to the database and execute the query in one command
psql -d postgres -f - << EOF
 
CREATE DATABASE $PGDATABASE;
 
CREATE USER $SERVICE_ACCOUNT_USERNAME with encrypted password $SERVICE_ACCOUNT_PASSWORD;
 
GRANT ALL PRIVILEGES ON DATABASE $PGDATABASE to $SERVICE_ACCOUNT_USERNAME;
 
\c $PGDATABASE;
 
CREATE extension citext;
CREATE extension hstore;
CREATE extension intarray;
CREATE extension pg_stat_statements;
CREATE extension pg_trgm;
CREATE extension pgcrypto;
 
EOF

After the databases are configured, start up migrator, it will attempt to run migrations, and will fail on the first privileged migration; manually mark it as completed, then start up migrator normally, and it should complete the rest of the migrations. For details, see How to Troubleshoot a Dirty Database.

Codeintel database

Repeat the above, with the correct environment variables for the codeintel database

Codeinsights database

Repeat the above, with the correct environment variables for the codeinsights database

Usage with PgBouncer

PgBouncer is a connection pooler for PostgreSQL. It allows more clients to connect with the PostgreSQL databases without running into connection limits.

When PgBouncer is used, we need to include statement_cache_mode=describe in the PostgreSQL connection URI. This can be done by configuring the PGDATASOURCE environment variables.

Docker Compose

Add/modify the following environment variables on migrator, and all sourcegraph-frontend-* services:

YAML
sourcegraph-frontend-0:
  environment:
    CODEINSIGHTS_PGDATASOURCE: "postgres://user:password@codeinsights-pgbouncer.mycompany.com:5432/sg?statement_cache_mode=describe"
    CODEINTEL_PGDATASOURCE: "postgres://user:password@codeintel-pgbouncer.mycompany.com:5432/sg?statement_cache_mode=describe"
    PGDATASOURCE: "postgres://user:password@pgsql-pgbouncer.mycompany.com:5432/sg?statement_cache_mode=describe"

Helm

Update the Secret to store the PgBouncer credentials, for each database

YAML
apiVersion: v1
kind: Secret
metadata:
  name: [database]-pgbouncer-credentials
data:
  password: "" # note: secrets data has to be base64-encoded

Add the PGDATASOURCE environment variables for frontend in your Helm values override.yaml file:

YAML
frontend:
  env:
 
    - name: CODEINSIGHTS_PGPASSWORD
      valueFrom:
        secretKeyRef:
          name: codeintel-pgbouncer-credentials
          key: password
    - name: CODEINSIGHTS_PGDATASOURCE
      value: postgres://$(CODEINSIGHTS_PGUSER):$(CODEINSIGHTS_PGPASSWORD)@$(CODEINSIGHTS_PGHOST):$(CODEINSIGHTS_PGPORT)/$(CODEINSIGHTS_PGDATABASE)?statement_cache_mode=describe
 
    - name: CODEINTEL_PGPASSWORD
      valueFrom:
        secretKeyRef:
          name: codeintel-pgbouncer-credentials
          key: password
    - name: CODEINTEL_PGDATASOURCE
      value: postgres://$(CODEINTEL_PGUSER):$(CODEINTEL_PGPASSWORD)@$(CODEINTEL_PGHOST):$(CODEINTEL_PGPORT)/$(CODEINTEL_PGDATABASE)?statement_cache_mode=describe
 
    - name: PGPASSWORD
      valueFrom:
        secretKeyRef:
          name: pgsql-pgbouncer-credentials
          key: password
    - name: PGDATASOURCE
      value: postgres://$(PGUSER):$(PGPASSWORD)@$(PGHOST):$(PGPORT)/$(PGDATABASE)?statement_cache_mode=describe