Tracking: remaining refactoring and tests for sql meta store

[yezizp2012]

Is your feature request related to a problem? Please describe.

• [ ] re-enable backward compatible tests.
• [ ] deprecate TableFragments together with actor and actor_dispatcher tables. Let build, scale and recovery support the generation of actors in memory, reduce the writing of metastore, and reduce the memory occupation of meta node. related: https://github.com/risingwavelabs/risingwave/issues/18094
• [ ] unify the object id in meta: currently we have identified multiple duplicate IDs, resulting in significant convert within the codebase.
• [ ] re-enable backup unit tests.
• [x] add e2e test for pg/mysql metastore in pr/main-cron

  • 19152

• [ ] investigate whether in-memory sqlite can be used in the simulation test, including reorganization of the integration simulation test that needs kill operation.
• [ ] support transactional commit of metadata for replace table.
• [ ] remove compatible id allocation for table, actor and fragment.

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response

[yezizp2012]

Cc @shanicky @zwang28 @kwannoel @fuyufjh @BugenZhao for awareness.