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

Version requirements

We support any version starting from 12.

Role requirements

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

citext
hstore
intarray
pg_stat_statements
pg_trgm

Upgrading PostgreSQL

Sourcegraph uses PostgreSQL as its main internal database and this documentation describes how to upgrade PostgreSQL between major versions.

Upgrading Kubernetes PostgreSQL instances

The upgrade process is different for Sourcegraph cluster deployments because by default, it uses sourcegraph/postgres-12.6:21-03-26_5d7084279 which can be customized with environment variables .

If you have changed PGUSER, PGDATABASE or PGDATA, then the PG*OLD and PG*NEW environment variables are required. Below are the defaults and documentation on what each variable is used for:

  • POSTGRES_PASSWORD='': Password of PGUSERNEW if it is newly created (i.e when PGUSERNEW didn’t exist in the old database).
  • PGUSEROLD=sg: A user that exists in the old database that can be used to authenticate intermediate upgrade operations.
  • PGUSERNEW=sg: A user that must exist in the new database after the upgrade is done (i.e. it’ll be created if it didn’t exist already).
  • PGDATABASEOLD=sg: A database that exists in the old database that can be used to authenticate intermediate upgrade operations. (e.g psql -d)
  • PGDATABASENEW=sg: A database that must exist in the new database after the upgrade is done (i.e. it’ll be created if it didn’t exist already).
  • PGDATAOLD=/data/pgdata-11: The data directory containing the files of the old PostgreSQL database to be upgraded.
  • PGDATANEW=/data/pgdata-12: The data directory containing the upgraded PostgreSQL data files, used by the new version of PostgreSQL.

Additionally, the upgrade process assumes it can write to the parent directory of PGDATAOLD.

(For Sourcegraph version: 3.27) Requirements for upgrading from Postgres 11 to 12 for Kubernetes

The migration to Postgres 12 using the Sourcegraph provided Postgres images is largely automated. However, you should take some steps before the migration. For the 3.27 release, these apply to the pgsql & codeintel-db deployments.

  1. Inform your users prior to starting the upgrade that Sourcegraph will take downtime. This downtime scales with the size and resources allocated to the Postgres database instances.

  2. Take snapshots of all Sourcegraph Postgres databases. You can find the backing disk in your cloud by describing the relevant PersistentVolumes.

  3. Ensure that the size of the disks backing the databases are under 50% utilization. (You will need up-to 2x the size of your current database to do the migration). In most cases Kubernetes can expand the size of the volume for you, see the relevant kubernetes docs here.

You can use kubctl exec -it $DB_POD_NAME -- df -h. By default, the database will be mounted under /data/.

Upgrading single node Docker deployments

When running a new version of Sourcegraph, it will check if the PostgreSQL data needs upgrading upon initialization.

See the following steps to upgrade between major postgresql versions

Upgrade the postgresql db using an external postgres upgrade container

1. Stop the sourcegraph/server container.

2. Save this script and give it executable permissions (chmod + x).

#!/usr/bin/env bash

set -xeuo pipefail

export OLD=${OLD:-"11"}
export NEW=${NEW:-"12"}
export SRC_DIR=${SRC_DIR:-"$HOME/.sourcegraph"}

docker run \
  -w /tmp/upgrade \
  -v "$SRC_DIR/data/postgres-$NEW-upgrade:/tmp/upgrade" \
  -v "$SRC_DIR/data/postgresql:/var/lib/postgresql/$OLD/data" \
  -v "$SRC_DIR/data/postgresql-$NEW:/var/lib/postgresql/$NEW/data" \
  "tianon/postgres-upgrade:$OLD-to-$NEW"

mv "$SRC_DIR/data/"{postgresql,postgresql-$OLD}
mv "$SRC_DIR/data/"{postgresql-$NEW,postgresql}

curl -fsSL -o "$SRC_DIR/data/postgres-$NEW-upgrade/optimize.sh" https://raw.githubusercontent.com/sourcegraph/sourcegraph/master/cmd/server/rootfs/postgres-optimize.sh

docker run \
  --entrypoint "/bin/bash" \
  -w /tmp/upgrade \
  -v "$SRC_DIR/data/postgres-$NEW-upgrade:/tmp/upgrade" \
  -v "$SRC_DIR/data/postgresql:/var/lib/postgresql/data" \
  "postgres:$NEW" \
  -c 'chown -R postgres $PGDATA . && gosu postgres bash ./optimize.sh $PGDATA'

3. Execute the script.

4. Start the sourcegraph/server container.

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:
  4. Turn Sourcegraph back on connecting to the now-upgraded database.

IMPORTANT: 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])