Problem to solve

On Gitlab K8s Helm, self hosted.

After upgrade there’s an issue with the pg_statistic table, ie missing chunk number 0 for toast value 815441 in pg_toast_2619

Stackoverflow suggests to TRUNCATE,VACUUM the table and refill it with ANAYLZE
https stackoverflow com /questions/47533639/postgres-log-file-contains-missing-chunk-number-0-for-toast-value-815441-in-pg

Gitlab Troubleshooting Docs show this way to access the postgresql pod:
https docs.gitlab com /charts/troubleshooting/kubernetes_cheat_sheet.html#gitlab-specific-kubernetes-information
→ kubectl exec -it – gitlab-rails dbconsole --include-password --database main

Proceeding as suggested leads to permission denied error.

gitlab@gitlab ~ # microk8s kubectl -n gitl exec -it gitlab-toolbox-797654799b-94cnv -- gitlab-rails dbconsole --include-password --database main
Defaulted container "toolbox" out of: toolbox, certificates (init), configure (init)
psql (15.1, server 14.12)
Type "help" for help.

gitlabhq_production=> DELETE FROM pg_catalog.pg_statistic;
ERROR:  permission denied for table pg_statistic

Gitlab Troubleshooting Docs hint at single User Mode:
https docs.gitlab com/ee/administration/troubleshooting/postgresql.html?tab=Helm+chart+%28Kubernetes%29#database-is-not-accepting-commands-to-avoid-wraparound-data-loss

To resolve the error, run VACUUM manually:

    Stop GitLab with the command gitlab-ctl stop.

    Place the database in single-user mode with the command:

    /opt/gitlab/embedded/bin/postgres --single -D /var/opt/gitlab/postgresql/data gitlabhq_production

In the backend> prompt, run VACUUM;. This command can take several minutes to complete.
Wait for the command to complete, then press Control + D to exit.
Start GitLab with the command gitlab-ctl start. 

Describe your question in as much detail as possible:

1. How to resolve the original issue? Is there an undocumented rake job to fix that?
2. How to Place the database in single-user mode when the system runs on Gitlab Helm?
3. How to access the postgresql pod with sufficient privileges if single-user mode isn’t needed?

Versions

• Self-managed k8s

Versions
gitlab-rake gitlab:env:info
Defaulted container “toolbox” out of: toolbox, certificates (init), configure (init)

System information
System:
Current User: git
Using RVM: no
Ruby Version: 3.1.5p253
Gem Version: 3.5.17
Bundler Version:2.5.17
Rake Version: 13.0.6
Redis Version: 7.0.15
Sidekiq Version:7.2.4
Go Version: unknown

GitLab information
Version: 17.4.2
Revision: 2c7e66ebdb6
Directory: /srv/gitlab
DB Adapter: PostgreSQL
DB Version: 14.12
URL: https gitlab.example com
HTTP Clone URL: https gitlab.example com/some-group/some-project.git
SSH Clone URL: git@gitlab.example.com:some-group/some-project.git
Using LDAP: no
Using Omniauth: no

GitLab Shell
Version: 14.39.0
Repository storages:

• default: tcp://gitlab-gitaly-0.gitlab-gitaly.gitl.svc:8075
  GitLab Shell path: /home/git/gitlab-shell

Gitaly

• default Address: tcp://gitlab-gitaly-0.gitlab-gitaly.gitl.svc:8075
• default Version: 17.4.2
• default Git Version: 2.46.0

I had to restore due to time constraints.

For whoever is in a similar situation, here some thoughts:

there are two passwords stored in the secret

this one seems to be the gitlab gitlabhq_production user:
microk8s kubectl -n gitl get secret gitlab-postgresql-password -o=jsonpath='{.data.postgresql-password}' | base64 -d

and this one is the actual postgres user (ie root)
microk8s kubectl -n gitl get secret gitlab-postgresql-password -o=jsonpath='{.data.postgresql-postgres-password}' | base64

so that way you get access to the db to do the needful in there
microk8s kubectl -n gitlab-prod exec -it gitlab-postgresql-0 -- psql -U postgres

there is some documentation about index issues when upgrading os for postgres