CLI: migrate database command

[adlerhurst]

As an admin of the ZITADEL system I want to automatically migrate my data from one database to another so that I can switch databases when my system grows or I need to change providers.

Acceptance criteria

• [x] an event is created when the migration starts
• [x] an event is created when the migration ends
  • [x] succeeded
  • [x] failed
• [x] events are migrated since the last successful migration until the event created for migration start
• [x] a flag is added that previous migrations are ignored
  • [x] if this flag is set previous migrations are ignored
• [x] a flag is provided to define the instances which will be migrated, if the flag is not provided all instances are migrated
  • [x] if the flag is set only specified instances are migrated
• [x] following tables are copied
  • [x] eventstore.events2
  • [x] eventstore.unique_constraints
  • [x] system.encryption_keys
  • [x] system.assets
  • [x] auth.auth_requests
• [x] projections are recomputed after copy
• [x] the config allows to set a source and destination database
• [x] verify command allows to check if destination and source tables have the same amount of rows
• [ ] We have a guide that describes the whole migration process
  • [ ] Have an example on how to migrate from CockroachDB to PostgreSQL
• [x] copy to the same database type works
• [ ] ~~decrypt event payload if necessary on export (moved to new issue: https://github.com/zitadel/zitadel/issues/7964)~~
• [ ] ~~encrypt event payload if necessary on import (moved to new issue: https://github.com/zitadel/zitadel/issues/7964)~~
• [ ] ~~ensure pgcrypto-extension is enabled (moved to new issue: https://github.com/zitadel/zitadel/issues/7964)~~
• [ ] ~~allow file binary file import/export (moved to new issue: https://github.com/zitadel/zitadel/issues/7966)~~

Additional Info

Existing branch (used for zitadel.cloud migration): https://github.com/zitadel/zitadel/tree/cmd/migrate

[adlerhurst]

estimation:

sum: 2.5 days

0.5 day merge/refactor existing branch, including pgx v5 0.5 day simplify command 0.5 day same database type (config currently overwrites same database type) 0.5 day testing 0.5 day guide

[adlerhurst]

We need to refactor the old handlers in auth and admin. This will consume additional 2 days.

The refactoring is needed to ensure that the projections are able to compute bigger amounts of events in a single transaction, if we would not implement the change the chances are high that migrations would get stuck during projections.

@hifabienne FYI