[GPRD] Install pg_stat_kcache package in DR postgresql databases

Production Change

Change Summary

This change should allow us to deploy/load auto_explain,pg_stat_kcache,pg_wait_sampling modules in our DR nodes:

• postgres-ci-dr-archive-2004-01-db-gprd.c.gitlab-production.internal
• postgres-ci-dr-delayed-2004-01-db-gprd.c.gitlab-production.internal
• postgres-dr-main-archive-2004-01-db-gprd.c.gitlab-production.internal
• postgres-dr-main-delayed-2004-01-db-gprd.c.gitlab-production.internal
• postgres-registry-dr-archive-01-db-gprd.c.gitlab-production.internal
• postgres-registry-dr-delayed-01-db-gprd.c.gitlab-production.internal

Change Details

1. Services Impacted - ServicePostgresArchive ServicePostgresDelayed
2. Change Technician - @rhenchen.gitlab
3. Change Reviewer - TBD
4. Time tracking - 2 hours
5. Downtime Component - no

Detailed steps for the change

Change Steps - steps to take to execute the change

Estimated Time to Complete (mins) - 2 hours

• Set label changein-progress /label ~change::in-progress
• Merge Cookbook: TODO
• Bump Cookbook version: TODO
• Merge Chef Role: TODO
• Execute following for node postgres-registry-dr-delayed-01-db-gprd.c.gitlab-production.internal
• • Verify config files:

    sudo cat /etc/gitlab/gitlab.rb | grep postgresql | grep shared_preload

  • Reconfigure Omnibus

    sudo gitlab-ctl reconfigure
    sudo cat /var/opt/gitlab/postgresql/data/postgresql.conf | grep shared_preload

  • Restart PostgreSQL

    sudo gitlab-ctl restart postgresql

  • Verify: gitlab-psql -c "SELECT * FROM pg_available_extensions;" shows extension pg_stat_kcache is available
  • Create Extension: gitlab-psql -c "CREATE EXTENSION pg_stat_kcache;"
  • Verify: gitlab-psql -c "SELECT * FROM pg_extension;" shows extension pg_stat_kcache is installed
• Execute the above for node postgres-registry-dr-archive-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-ci-dr-archive-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-ci-dr-delayed-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-dr-main-archive-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-dr-main-delayed-2004-01-db-gprd.c.gitlab-production.internal
• Set label changecomplete /label ~change::complete

Rollback

Rollback steps - steps to be taken in the event of a need to rollback this change

Estimated Time to Complete (mins) - Estimated Time to Complete in Minutes

• Execute following for node postgres-registry-dr-delayed-01-db-gprd.c.gitlab-production.internal
  • Create Extension: gitlab-psql -c "DROP EXTENSION pg_stat_kcache;"
  • Verify: gitlab-psql -c "SELECT * FROM pg_extension;" shows extension pg_stat_kcache is installed
• Execute the above for node postgres-registry-dr-archive-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-ci-dr-archive-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-ci-dr-delayed-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-dr-main-archive-2004-01-db-gprd.c.gitlab-production.internal
• Execute the above for node postgres-dr-main-delayed-2004-01-db-gprd.c.gitlab-production.internal
• Set label changeaborted /label ~change::aborted

Monitoring

Key metrics to observe

• Metric: replication lag
  • Location: https://thanos.gitlab.net/graph?g0.expr=pg_replication_lag%7Benv%3D%22gprd%22%2Cfqdn%3D~%22postgres.archive.%22%7D&g0.tab=0&g0.stacked=0&g0.range_input=1h&g0.max_source_resolution=0s&g0.deduplicate=1&g0.partial_response=0&g0.store_matches=%5B%5D&g1.expr=pg_replication_lag%7Benv%3D%22gprd%22%2Cfqdn%3D~%22postgres.delayed.%22%7D&g1.tab=0&g1.stacked=0&g1.range_input=1h&g1.max_source_resolution=0s&g1.deduplicate=1&g1.partial_response=0&g1.store_matches=%5B%5D
  • What changes to this metric should prompt a rollback: no changes should prompt a rollback

Change Reviewer checklist

C4 C3 C2 C1:

• Check if the following applies:
  • The scheduled day and time of execution of the change is appropriate.
  • The change plan is technically accurate.
  • The change plan includes estimated timing values based on previous testing.
  • The change plan includes a viable rollback plan.
  • The specified metrics/monitoring dashboards provide sufficient visibility for the change.

C2 C1:

• Check if the following applies:
  • The complexity of the plan is appropriate for the corresponding risk of the change. (i.e. the plan contains clear details).
  • The change plan includes success measures for all steps/milestones during the execution.
  • The change adequately minimizes risk within the environment/service.
  • The performance implications of executing the change are well-understood and documented.
  • The specified metrics/monitoring dashboards provide sufficient visibility for the change.
    • If not, is it possible (or necessary) to make changes to observability platforms for added visibility?
  • The change has a primary and secondary SRE with knowledge of the details available during the change window.
  • The labels blocks deployments and/or blocks feature-flags are applied as necessary

Change Technician checklist

• Check if all items below are complete:
  • The change plan is technically accurate.
  • This Change Issue is linked to the appropriate Issue and/or Epic
  • Change has been tested in staging and results noted in a comment on this issue.
  • A dry-run has been conducted and results noted in a comment on this issue.
  • The change execution window respects the Production Change Lock periods.
  • For C1 and C2 change issues, the change event is added to the GitLab Production calendar.
  • For C1 and C2 change issues, the SRE on-call has been informed prior to change being rolled out. (In #production channel, mention @sre-oncall and this issue and await their acknowledgement.)
  • For C1 and C2 change issues, the SRE on-call provided approval with the eoc_approved label on the issue.
  • For C1 and C2 change issues, the Infrastructure Manager provided approval with the manager_approved label on the issue.
  • Release managers have been informed (If needed! Cases include DB change) prior to change being rolled out. (In #production channel, mention @release-managers and this issue and await their acknowledgment.)
  • There are currently no active incidents that are severity1 or severity2
  • If the change involves doing maintenance on a database host, an appropriate silence targeting the host(s) should be added for the duration of the change.