Fails to start with `IncorrectDatabaseSetup` error (inconsistent stream positions with sequences)

### Description

Synapse fails to start with an `IncorrectDatabaseSetup` exception, logging the following:

> Postgres sequence 'account_data_sequence' is inconsistent with associated stream position of 'account_data' in the 'stream_positions' table.
>
> This is likely a programming error and should be reported at https://github.com/matrix-org/synapse.

### Steps to reproduce

Try to start synapse.

### Homeserver

fsmpi.rwth-aachen.de

### Synapse Version

v1.128.0

### Installation Method

Docker (matrixdotorg/synapse)

### Database

Postgres 14

### Workers

Single process

### Platform

Running in a Kubernetes cluster using the `ghcr.io/element-hq/synapse` image

### Configuration

_No response_

### Relevant log output

```shell
{"log":"Exception during startup","namespace":"synapse.app._base","level":"ERROR","time":1749741788.04,"request":"main","server_name":"fsmpi.rwth-aachen.de","exc_type":"IncorrectDatabaseSetup","exc_value":"\nPostgres sequence 'account_data_sequence' is inconsistent with associated stream position\nof 'account_data' in the 'stream_positions' table.\n\nThis is likely a programming error and should be reported at\nhttps://github.com/matrix-org/synapse.\n\nA temporary workaround to fix this error is to shut down Synapse (including\nany and all workers) and run the following SQL:\n\n DELETE FROM stream_positions WHERE stream_name = 'account_data';\n\nThis will need to be done every time the server is restarted.\n"}
**********************************************************************************
Error during initialisation:
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/synapse/app/homeserver.py", line 373, in setup
hs.setup()
File "/usr/local/lib/python3.12/site-packages/synapse/server.py", line 359, in setup
self.datastores = Databases(self.DATASTORE_CLASS, self)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/__init__.py", line 104, in __init__
main = main_store_class(database, db_conn, hs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/__init__.py", line 174, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/events_bg_updates.py", line 117, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/experimental_features.py", line 44, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/devices.py", line 1770, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/devices.py", line 95, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/roommember.py", line 1935, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 2418, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 1961, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/room.py", line 150, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/registration.py", line 2324, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/profile.py", line 53, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/presence.py", line 80, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/presence.py", line 61, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/transactions.py", line 81, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/state.py", line 974, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/state.py", line 118, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/state.py", line 765, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_federation.py", line 2075, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/media_repository.py", line 226, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/media_repository.py", line 119, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/filtering.py", line 49, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 1894, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/metrics.py", line 75, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/event_push_actions.py", line 263, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/end_to_end_keys.py", line 1512, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/e2e_room_keys.py", line 69, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/search.py", line 433, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/search.py", line 144, in __init__
super().__init__(database, db_conn, hs)
File "/usr/local/lib/python3.12/site-packages/synapse/storage/databases/main/account_data.py", line 73, in __init__
self._account_data_id_gen = MultiWriterIdGenerator(
^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/id_generators.py", line 295, in __init__
self._sequence_gen.check_consistency(
File "/usr/local/lib/python3.12/site-packages/synapse/storage/util/sequence.py", line 192, in check_consistency
raise IncorrectDatabaseSetup(
synapse.storage.engines._base.IncorrectDatabaseSetup:
Postgres sequence 'account_data_sequence' is inconsistent with associated stream position
of 'account_data' in the 'stream_positions' table.

This is likely a programming error and should be reported at
https://github.com/matrix-org/synapse.

A temporary workaround to fix this error is to shut down Synapse (including
any and all workers) and run the following SQL:

DELETE FROM stream_positions WHERE stream_name = 'account_data';

This will need to be done every time the server is restarted.


There may be more information in the logs.
**********************************************************************************
Found file in config directory that does not end in '.yaml': '/config-ro/fsmpi.rwth-aachen.de.log.config'. IGNORING.
Found file in config directory that does not end in '.yaml': '/config-ro/database.yaml.tmpl'. IGNORING.
Found file in config directory that does not end in '.yaml': '/config-ro/oidc.yaml.tmpl'. IGNORING.
Found file in config directory that does not end in '.yaml': '/config-ro/fsmpi.rwth-aachen.de.signing.key'. IGNORING.
Found file in config directory that does not end in '.yaml': '/config-ro/oidc_client_secret'. IGNORING.
Found subdirectory in config directory: '/config-ro/..data'. IGNORING.
Found subdirectory in config directory: '/config-ro/..2025_06_12_15_06_27.209063034'. IGNORING.
Stream closed EOF for matrix-synapse/synapse-7946c4bb9b-6x9qf (matrix-synapse)
```

### Anything else that would be useful to know?

The URL it says to report the bug at is wrong.

Start with synapse/storage/util/sequence.py at check_consistency, then trace the account_data generator setup in synapse/storage/util/id_generators.py and synapse/storage/databases/main/account_data.py. Reproduce startup with PostgreSQL 14 and inspect the existing database consistency tests. Done means a valid database no longer fails startup, the workaround is unnecessary, and a regression test covers the inconsistent stream-position case.

Written by the indexing model from the issue text.