cdc: Under-tested execution locality feature

Execution locality feature (changefeed processor pinning/regional sins) is under-tested.

* At the very least, add roachtest to test locality pinning

* Identify and fix observability gaps (are there any locality specific metrics we need to be tracking of)?
It appears that while aggregators will be pinned to a particular region, the actual rangefeed may
consume data from a replica that might not be the best choice (even outside of the locality; we prefer
to use proximate replicas, but I don't think it's guaranteed). If the above can happen, we should at least
have observability when it does.

Jira issue: CRDB-31395