Core Wagtail id sequences are out of whack when restoring a Heroku Postgres instance from a rollback

[hancush]

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:

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;

Wanted to log this somewhere for posterity.

[hancush]

Shit happens: https://www.cybertec-postgresql.com/en/gaps-in-sequences-postgresql/#gaps-in-sequences-caused-by-rollback