We had to restore the NOAH database from a rollback after some data loss. When we did this, the id sequences for pages, revisions, and subscriptions (from Wagtail) were out of whack. This created integrity errors like this when trying to access the CMS and manage pages programmatically:
Traceback (most recent call last):
File "/usr/local/lib/python3.11/site-packages/django/db/backends/utils.py", line 105, in _execute
return self.cursor.execute(sql, params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "wagtailcore_pagerevision_pkey"
DETAIL: Key (id)=(36) already exists.
python manage.py fixtree, with and without the --full flag, did not resolve the issue. I wound up having to connect to the database and reset the sequences by hand:
# in your terminal
heroku pg:psql -a tpc-ihs-noah-map
# once connected to postgres
select setval(pg_get_serial_sequence('wagtailcore_page', 'id'), coalesce(MAX(id), 1)) from wagtailcore_page;
select setval(pg_get_serial_sequence('wagtailcore_revision', 'id'), coalesce(MAX(id), 1)) from wagtailcore_revision;
select setval(pg_get_serial_sequence('wagtailcore_pagesubscription', 'id'), coalesce(MAX(id), 1)) from wagtailcore_pagesubscription;
hancush
commented
1 week ago
Description
We had to restore the NOAH database from a rollback after some data loss. When we did this, the id sequences for pages, revisions, and subscriptions (from Wagtail) were out of whack. This created integrity errors like this when trying to access the CMS and manage pages programmatically:
python manage.py fixtree, with and without the--fullflag, did not resolve the issue. I wound up having to connect to the database and reset the sequences by hand:Wanted to log this somewhere for posterity.