PostgreSQL

Sourcegraph uses several PostgreSQL databases to support various functionality. These databases are:

• pgsql or primary: responsible for user data and account information
• codeintel-db: provides support for lsif data and part of the code-intelligence
• codeinsights-db: provides support for code insights data

Version requirements

We support Postgres 16 and above. Older versions of Sourcegraph supported 12 see our Postgres 12 deprecation notice for more details.

Role requirements

The user provided to Sourcegraph must have full access to the sg database and be able to create the following extensions:

SHELL
citext
hstore
intarray
pg_stat_statements
pg_trgm
pgcrypto
vector

Configuring PostgreSQL

Upgrading Built-in PostgreSQL

Sourcegraph deployments come packaged with containers using the postgresql-16 image for pgsql and codeintel-db deployments and postgresql-16-codeinsights for codeinsights. These images contain an entryscript which will detect and upgrade Postgres instances at version 12 (our previous images Postgres version). See the (Postgres 12 end of life notice)[/admin/prostgres12_end_of_life_notice#] for more details.

WARNING: Upgrading the PostgreSQL database requires stopping your Sourcegraph deployment which will result in downtime.

Additionally, once the upgrade process is started via the database container, interrupting the container before the upgrade is complete could result in corrupting the underlying Postgres database. We strongly advise taking a backup before the upgrade.

The PG_UPGRADE_EXTRA_ARGS env var can be set in the built-in postgresql-16 and postgresql-16-codeinsights deployments to provide extra arguments to the pg_upgrade command.

Setting the env var PG_UPGRADE_EXTRA_ARGS=--link provides the --link option to the underlying pg_update; creating a hard links between the old and new data directories instead of copying data. This option is faster and requires less disk space, however if the database containers are interrupted during the upgrade process there is a strong possibility of data corruption. This can still be a viable choice for users who are confident in their container scheduling and backups and would like to avoid longer downtimes.

If not using the --link option, you will need enough disk space available for Postgres to make a copy of the existing data. For example, if your database is currently using 100GiB of storage, you will need an additional 100GiB free space in order for the upgrade process to succeed.

Upgrading external PostgreSQL instances

When running an external PostgreSQL instance, please do the following:

1. Back up the Postgres DB so that you can restore to the old version should anything go wrong.
2. Turn off Sourcegraph entirely (bring down all containers and pods so they cannot talk to Postgres.)
3. Upgrade Postgres to the latest version following your provider's instruction or your preferred method:

• AWS RDS
• AWS Aurora
• GCP CloudSQL
• Azure DB
• Heroku
• EnterpriseDB
• Citus
• Aiven PostgreSQL
• Your own PostgreSQL

4. Turn Sourcegraph back on connecting to the now-upgraded database.

WARNING: Do not allow Sourcegraph to run/connect to the new Postgres database until it has been fully populated with your data. Doing so could result in Sourcegraph trying to create e.g. a new DB schema and partially migrating. If this happens to you, restore from the backup you previously took or contact us ([email protected])

Single-container Sourcegraph

Sourcegraph Single-container is not a supported production deployment and currently doesn't support upgrades to Postgres 16. Admins with production data in a single container deployment are advised to reach out to [email protected] for assistance.