PostgreSQL deployments
For deployments other than Cloud and Sourcegraph.com please use the information here to access the database.
Sourcegraph.com specific
We currently run two separate databases. The sg-cloud
database is the primary database, and the code-intel team uses the sg-cloud-code-intel
.
You can also directly view the database in GCP.
We utilize the Google Cloud SDK utility Cloud SQL Proxy to connect to our production databases. By default, our Cloud SQL databases are not accessible.
There are two ways of connecting: either using the gcloud beta sql connect
command, which will use the pgsql
client, or running the cloud_sql_proxy
on a port locally to utilize your preferred tools.
For read-only access, there is also an option of using BigQuery and their EXTERNAL_QUERY
syntax.
Using BigQuery, if you want to run a query
SELECT name::text,created_at::text FROM repo LIMIT 5;
against the Prod CloudSQL database, you need to run
SELECT * FROM EXTERNAL_QUERY("sourcegraph-dev.us.sg-cloud", "SELECT name::text,created_at::text FROM repo LIMIT 5;");
in the BigQuery editor (passing the PostgreSQL query in the second parameter to EXTERNAL_QUERY).
Connecting to Postgres
Install the command line tools
If you didn’t yet, install Google Cloud SDK. Ensure, that gcloud
command is reachable on your path.
Install the Cloud SQL proxy by running this command with gcloud
:
gcloud components install cloud_sql_proxy
Command line only use (pgsql)
You may use these gcloud commands to connect directly to the databases:
-
Default db {Password}
gcloud beta sql connect --project sourcegraph-dev sg-cloud-732a936743 --user=dev-readonly -d=sg
-
Code intel db {Password}
gcloud beta sql connect --project sourcegraph-dev sg-cloud-code-intel-9fc67e507c --user=dev-readonly -d=sg
Go to Example Queries to continue
Proxy for advanced use
Run the cloud_sql_proxy
against our production instance
cloud_sql_proxy -instances=sourcegraph-dev:us-central1:sg-cloud-732a936743=tcp:5555
Now, in a new terminal, run the command below. The database will be running on localhost:5555
export PGPASSWORD='<$PASSWORD>'
psql -h localhost -p 5555 -d sg -U 'dev-readonly'
Note, that to connect to localhost:5555
you still need to supply the postgres password stored in 1Password (mentioned above).
Example queries
🔥 You are directly interfacing with the production database. If you are unsure of any commands, please reach out in #dev-chat or #dev-ops. Please prefer using the readonly user
frontend-dev
- See all fields on a table (ie the
repo
table)\d+ repo
- See the total number of rows in the
repo
tableSELECT COUNT(*) FROM repo;
Performance monitoring
We run a PgHero deployment as well you can use to analyze slow queries and overall database performance.
kubectl port-forward -n monitoring deploy/pghero 8080:8080
And then navigate to http://localhost:8080 to view the dashboard
See additional Postgres tips in our incident docs
Dogfood specific
Dogfood runs Sourcegraph completely on Kubernetes. You can port-forward the pgsql deployment:
kubectl port-forward -n dogfood-k8s deploy/pgsql 8080:5432
And access it locally:
pgcli -h localhost -p 8080 -d sg -U 'sg'