Hot-Spare for the Synapse Homeserver main

This issue has been migrated from [#15478](https://github.com/matrix-org/synapse/issues/15478).

---

**Description:**

We are required to bring our synapse fleet closer to high-availability. Ideally, we would want to enable proper HA with multiple master nodes, but it seems that this would require significant effort on Synapse. Instead, we could meet our requirements with a hot-spare setup, where only one active Homeserver is getting traffic while a passive spare is waiting. Note that our infrastructure guarantees that a node never gets traffic again after another node has been promoted to active. Instead, the node is destroyed and a new spare is warmed-up.

We have done some testing on this and identified at least four areas that need work:

1. Race condition when reading schema version if two nodes are starting at the same time.
2. Race conditions if two nodes are trying to run background DB upgrades at the same time.
3. ID generation, including Stream IDs, is using in-memory state and should be migrated to use Postgres sequence based generator.
4. Caches are warmed up on server init and will be out of date on hot-swap.

In addition to these, there may also be other issues, which we'd like your help in identifying as well.

Would you be able to guide us in making hot-spare work, by reviewing PRs and providing guidance on possible other issues that would need to be addressed and how? We are looking to work on the implementation as soon as you'd be able to guide us into it. We have started to work on patches for the above issues, and we will soon open PRs and link them here.

Thanks!

Start by breaking the request into its four stated areas: schema-version reads, concurrent background database upgrades, in-memory ID and Stream ID generation, and cache warming during server initialization. Review the existing implementation and any future linked patches for each area. Done means a passive Synapse hot-spare can be promoted without database races, conflicting IDs, or stale initialization caches.

Written by the indexing model from the issue text.