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
- The sequence was altered/renamed
- 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');