pg_dump: query to get data of sequence returns 0

https://gitlab.zendesk.com/agent/tickets/114958 (internal use only)

This user has reported the following error when running sudo gitlab-rake gitlab:backup:create --trace after upgrading from 11.7.5-ee to 11.8.0-ee:

Dumping database ...

Dumping PostgreSQL database gitlabhq_production ... pg_dump: query to get data of sequence "ci_build_trace_chunks_id_seq" returned 0 rows (expected 1)

[FAILED]

rake aborted!

Backup::Error: Backup failed

Referencing: https://gitlab.com/gitlab-org/gitlab-ce/blob/master/db/migrate/20180326202229_create_ci_build_trace_chunks.rb#L14, my understanding is the :bigserial pseudotype creates a sequence, and the error is usually returned when either

1. The sequence was altered/renamed
2. Their data was corrupted?

If the sequence was altered, I'm assuming it might have been from a previous pg_restore where the sequence value was not maintained/updated properly.

Should we restore the sequence directly from the db console? We can try on a test instance to confirm. I found a similar issue here. Perhaps we can use a similar query:

DROP SEQUENCE ci_build_trace_chunks_id_seq CASCADE;
CREATE SEQUENCE public.ci_build_trace_chunks_id_seq  START WITH 1 INCREMENT BY 1  NO MINVALUE   NO MAXVALUE  CACHE 1;
ALTER TABLE ONLY public.ci_build_trace_chunks ALTER COLUMN id SET DEFAULT nextval('public.ci_build_trace_chunks_id_seq'::regclass);
ALTER TABLE public.ci_build_trace_chunks_id_seq OWNER TO gitlab; 

I will not suggest that query until I get the okay from dev.

For now, the user will let us know the output of the command below to ensure nextval is higher than MAX id:

SELECT MAX(id) FROM ci_build_trace_chunks;
SELECT nextval('ci_build_trace_chunks_id_seq');