Error: 500 error on some projects / Pipelines

My team has been working in gitlab for almost a year now. Recently we ran into a 500 error on a project. It occurred after a normal host shut down. After some troubleshooting I managed to export the project from a restored backup and re-import it into Gitlab and that was an ok workaround. However later the error returned on another project. This one has a pipeline / CI that does not export / import.
gitlab-ctl tail reveals a corruption error repeated in several log files
==> /var/log/gitlab/postgresql/current <==
2019-01-30_15:27:17.49404 ERROR: could not read block 10 in file “base/16385/16713”: read only 0 of 8192 bytes
2019-01-30_15:27:17.49405 STATEMENT: SELECT “ci_pipelines”.* FROM “ci_pipelines” WHERE “ci_pipelines”.“project_id” = 3 AND “ci_pipelines”.“ref” = ‘master’ AND “ci_pipelines”.“status” = ‘success’ ORDER BY “ci_pipelines”.“id” DESC LIMIT 1
This particular project has not been touched since a backup I have, but others have and I’m not sure how to just restore this project and it’s pipline.
Would just restoring “base/16385/16713” be beneficial?

‘gitlab-rake gitlab:check --trace’ does not reveal any problems
‘gitlab-rake db:migrate:status --trace’ shows all entries as “up”. There are > 50 migration id’s who’s migration name is “********** NO FILE **********”

NOTE: I AM NOT A DB ADMINISTRATOR
NOTE: DATABASE corruption should be taken seriously, database should be backed up immediately at the file level and the cause of corruption should be investigated
That said here is how I dealt with this issue:

Trace

  • Corruption in file [/var/opt/gitlab/postgresql/data/base/16385/16713]
    • Enter PSQL user on thePolice (Bitnami omnibus installation)

~# sudo -u gitlab-psql -i bash
~$ sudo gitlab-psql -d gitlabhq_production

  • Check what is affected by file corruption

SELECT relname, relfilenode FROM pg_class WHERE relfilenode = 16713;
relname | relfilenode
--------------±------------
ci_pipelines | 16713
(1 row)

NOTE: Gitlab psql database is for the running of the gitlab website (http interface). Code / files checked into gitlab repositories are stored in a separate location and not dependent on the database to be valid.
NOTE: Per the team ci_pipeline has not been used (other than it runs nightly) in months. user is only currently interested in repository information will be used but not pipeline.
NOTE: This is the only project currently using ci_pipleline. Checked working project pages to confirm.

Repair:
- Cannot drop table due to child tables / dependencies (foreign keys)
- Truncate table w/ cascade option

TRUNCATE ci_pipelines CASCADE;
NOTICE: truncate cascades to table “ci_builds”
NOTICE: truncate cascades to table “ci_pipeline_variables”
NOTICE: truncate cascades to table “ci_stages”
NOTICE: truncate cascades to table “merge_request_metrics”
NOTICE: truncate cascades to table “merge_requests”
NOTICE: truncate cascades to table “ci_build_trace_sections”
NOTICE: truncate cascades to table “merge_request_diffs”
NOTICE: truncate cascades to table “merge_requests_closing_issues”
NOTICE: truncate cascades to table “timelogs”
NOTICE: truncate cascades to table “ci_job_artifacts”
NOTICE: truncate cascades to table “ci_builds_metadata”
NOTICE: truncate cascades to table “ci_build_trace_chunks”
NOTICE: truncate cascades to table “ci_builds_runner_session”
NOTICE: truncate cascades to table “resource_label_events”
NOTICE: truncate cascades to table “merge_request_diff_commits”
NOTICE: truncate cascades to table “merge_request_diff_files”
- exit (CTRL-D)

  • Export ci_pipelines table and dependencies from CLONE server with working backup from before the issue started applied.

      ~# sudo -u gitlab-psql -i bash
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_pipelines > ci_pipelines.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_builds > ci_builds.pg	
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_pipeline_variables > ci_pipeline_variables.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_stages > ci_stages.pg	
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_request_metrics > merge_request_metrics.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_requests > merge_requests.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_build_trace_sections > ci_build_trace_sections.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_request_diffs > merge_request_diffs.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_requests_closing_issues > merge_requests_closing_issues.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production timelogs > timelogs.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_job_artifacts > ci_job_artifacts.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_builds_metadata > ci_builds_metadata.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_build_trace_chunks > ci_build_trace_chunks.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production ci_builds_runner_session > ci_builds_runner_session.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production resource_label_events > resource_label_events.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_request_diff_commits > merge_request_diff_commits.pg
      ~$ pg_dump -h /var/opt/gitlab/postgresql/ -d gitlabhq_production merge_request_diff_files > merge_request_diff_files.pg
    
  • Imported ci_pipelines table and dependencies on CURRENT server
    Copy all pg files from clone server to new server.

      ~# sudo -u gitlab-psql -i bash
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_pipelines.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_builds.pg	
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_pipeline_variables.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_stages.pg	
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_request_metrics.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_requests.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_build_trace_sections.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_request_diffs.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_requests_closing_issues.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < timelogs.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_job_artifacts.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_builds_metadata.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_build_trace_chunks.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < ci_builds_runner_session.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < resource_label_events.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_request_diff_commits.pg
      ~$ psql -h /var/opt/gitlab/postgresql/ -d gitlabhq_production < merge_request_diff_files.pg
    
  • Tested all project pages, ci pipeline pages no further 500 error

    • As i suspected the project ci_pipline is broken and will have to be rebuilt if it needs to be used again (execution fails)

Updated gitlab to verison 11.7.3