diff options
Diffstat (limited to 'doc/development/database/database_lab.md')
| -rw-r--r-- | doc/development/database/database_lab.md | 96 |
1 files changed, 89 insertions, 7 deletions
diff --git a/doc/development/database/database_lab.md b/doc/development/database/database_lab.md index 162fc597cc4..357133d8bca 100644 --- a/doc/development/database/database_lab.md +++ b/doc/development/database/database_lab.md @@ -12,6 +12,17 @@ on replicated production data. Unlike a typical read-only production replica, in also create, update, and delete rows. You can also test the performance of schema changes, like additional indexes or columns, in an isolated copy of production data. +## Database Lab quick start + +1. [Visit the console](https://console.postgres.ai/). +1. Select **Sign in with Google**. (Not GitLab, as you need Google SSO to connect with our project.) +1. After you sign in, select the GitLab organization and then visit "Ask Joe" in the sidebar. +1. Select the database you're testing against: + - Most queries for the GitLab project run against `gitlab-production-tunnel-pg12`. + - If the query is for a CI table, select `gitlab-production-ci`. + - If the query is for the container registry, select `gitlab-production-registry`. +1. Type `explain <Query Text>` in the chat box to get a plan. + ## Access Database Lab Engine Access to the DLE is helpful for: @@ -21,27 +32,25 @@ Access to the DLE is helpful for: To access the DLE's services, you can: -- Perform query testing in the `#database_lab` Slack channel, or in the Postgres.ai web console. +- Perform query testing in the Postgres.ai web console. Employees access both services with their GitLab Google account. Query testing provides `EXPLAIN` (analyze, buffers) plans for queries executed there. - Migration testing by triggering a job as a part of a merge request. - Direct `psql` access to DLE instead of a production replica. Available to authorized users only. - To request `psql` access, file an [access request](https://about.gitlab.com/handbook/business-technology/team-member-enablement/onboarding-access-requests/access-requests/#individual-or-bulk-access-request). + To request `psql` access, file an [access request](https://about.gitlab.com/handbook/business-technology/end-user-services/onboarding-access-requests/access-requests/#individual-or-bulk-access-request). For more assistance, use the `#database` Slack channel. NOTE: If you need only temporary access to a production replica, instead of a Database Lab clone, follow the runbook procedure for connecting to the -[database console with Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/Teleport/Connect_to_Database_Console_via_Teleport.md). -This procedure is similar to [Rails console access with Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/Teleport/Connect_to_Rails_Console_via_Teleport.md#how-to-use-teleport-to-connect-to-rails-console). +[database console with Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/teleport/Connect_to_Database_Console_via_Teleport.md). +This procedure is similar to [Rails console access with Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/teleport/Connect_to_Rails_Console_via_Teleport.md#how-to-use-teleport-to-connect-to-rails-console). ### Query testing You can access Database Lab's query analysis features either: -- In the `#database_lab` Slack channel. Shows everyone's commands and results, but - your own commands are still isolated in their own clone. - In [the Postgres.ai web console](https://console.postgres.ai/GitLab/joe-instances). Shows only the commands you run. @@ -86,7 +95,7 @@ Caveats: [`ci_builds`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/docs/ci_builds.yml#L14), use `gitlab-production-ci`. - Database Lab typically has a small delay of a few hours. If more up-to-date information - is required, you can instead request access to a replica [via Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/Teleport/Connect_to_Database_Console_via_Teleport.md) + is required, you can instead request access to a replica [via Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/teleport/Connect_to_Database_Console_via_Teleport.md) For example: `\d index_design_management_designs_on_project_id` produces: @@ -121,6 +130,79 @@ For information on testing migrations, review our ### Access the console with `psql` +NOTE: +You must have `AllFeaturesUser` [`psql` access](#access-database-lab-engine) to access the console with `psql`. + +#### Simplified access through `pgai` Ruby gem + +[@mbobin](https://gitlab.com/mbobin) created the [`pgai` Ruby Gem](https://gitlab.com/mbobin/pgai/#pgai) that +greatly simplifies access to a database clone, with support for: + +- Access to all database clones listed in the [Postgres.ai instances page](https://console.postgres.ai/gitlab/instances); +- Multiple `psql` sessions on the same clone. + +If you have `AllFeaturesUser` [`psql` access](#access-database-lab-engine), you can follow the steps below to configure +the `pgai` Gem: + +1. To get started, you need to gather some values from the [Postgres.ai instances page](https://console.postgres.ai/gitlab/instances): + + 1. Navigate to the instance that you want to configure and the on right side of the screen. + 1. Under **Connection**, select **Connect**. The menu might be collapsed. + + A pop-up with everything that's needed for configuration appears, using this format: + + ```shell + dblab init --url http://127.0.0.1:1234 --token TOKEN --environment-id <environment-id> + ``` + + ```shell + ssh -NTML 1234:localhost:<environment-port> <postgresai-user>@<postgresai-proxy> -i ~/.ssh/id_rsa + ``` + +1. Add the following snippet to your SSH configuration file at `~/.ssh/config`, replacing the variable values: + + ```plaintext + Host pgai-proxy + HostName <postgresai-proxy> + User <postgresai-user> + IdentityFile ~/.ssh/id_ed25519 + ``` + +1. Run the following command so you can accept the server key fingerprint: + + ```shell + ssh pgai-proxy + ``` + +1. Run the following commands: + + ```shell + gem install pgai + + # Grab an access token: https://console.postgres.ai/gitlab/tokens + # GITLAB_USER is your GitLab handle + pgai config --dbname=gitlabhq_dblab --prefix=$GITLAB_USER --proxy=pgai-proxy + + # Grab the respective port values from https://console.postgres.ai/gitlab/instances + # for the instances you'll be using (in this case, for the `main` database instance) + pgai env add --alias main --id <environment-id> --port <environment-port> + ``` + +1. Once this one-time configuration is done, you can use `pgai connect` to connect to a particular database. For + instance, to connect to the `main` database: + + ```shell + pgai connect main + ``` + +1. Once done with the clone, you can destroy it: + + ```shell + pgai destroy main + ``` + +#### Manual access through the Postgres.ai instances page + Team members with [`psql` access](#access-database-lab-engine), can gain direct access to a clone via `psql`. Access to `psql` enables you to see data, not just metadata. |