Migrating from CRDB to PGSQL
Has anyone migrated from cockroach DB to Postgres for Zitadel running via docker? I’ve been trying but have been unsuccessful. Any guidance would be much appreciated!
12 Replies
Unknown User•3w ago
Message Not Public
Sign In & Join Server To View
Hey, so i followed this guide: https://zitadel.com/docs/self-hosting/manage/cli/mirror
setup my new config file, mirror file, secrets file and was able to initialize and setup my pg db to be migrated to. however when i run the mirror command:
I get the error:
FATA[0000] unable to connect to destination database caller="/home/runner/work/zitadel/zitadel/cmd/mirror/system.go:41" error="ERROR: unrecognized configuration parameter "enable_multiple_modifications_of_table" (SQLSTATE 42704)"
I get this error immediately, both my databases are connecting just fine - CRDB with self signed certs and PGSQL with password auth. Init and setup is working fine with 0 errors.
I also followed these github issues: https://github.com/zitadel/zitadel/issues/8558
https://github.com/zitadel/zitadel/issues/9042
I am not sure why I am getting this error from my PGSQL container and how to fix it. Any help would be much appreciated!
ZITADEL Docs
The mirror command allows you to do database to database migrations. This functionality is useful to copy data from one database to another.
GitHub
[Bug]: Mirroring/Migrating database from cockroach to postgres not ...
Preflight Checklist I could not find a solution in the documentation, the existing issues or discussions I have joined the ZITADEL chat Environment Self-hosted Version v2.61.0 Database Other (descr...
GitHub
[Bug]: Error while migrating from Cockroach to Postgres · Issue #9...
Preflight Checklist I could not find a solution in the documentation, the existing issues or discussions I have joined the ZITADEL chat Environment Self-hosted Version 2.65.1 Database CockroachDB D...
I start my migration by setting up the new compose file with the additional pgsql container, modify my config.yml, mirror.yml and secrets.yml to accept the pgsql database.
then I run:
docker compose run zitadel init --config /mirror.yml --config /config.yml --config /secrets.yml
and get the output (image attached):
then I run the command:
docker compose run zitadel setup --for-mirror --config /mirror.yml --config /config.yml --config /secrets.yml --masterkey dBtCBFdLWiGu5xcngBozqpLlfZMKwgUO --tlsMode external and get the output:
Finally, I run the mirror command:
docker compose run zitadel mirror --system --config /mirror.yml --config /config.yml --config /secrets.yml --masterkey dBtCBFdLWiGu5xcngBozqpLlfZMKwgUO --tlsMode external
to get the output:
@Raccine let me know if i can offer anything else 🙂
Hey @Raccine, I also tried the same setup with an empty CRDB setup but I get the exact same results. Which IMO points to an issue with the mirror command itself. Not sure
Hey @Rajat Singh you seem quite active. Would you maybe have time to look into this? Or pass along with another team member? From the issues on GH, seems like I’m not the only one facing this issue. Thanks!
Unknown User•3w ago
Message Not Public
Sign In & Join Server To View
Thank you Raccine! Once sorted, while I’m not a dev, I’d love to help out with the documentation!
hey @AYEEDITYA thanks for your question, I will take a look at it
hey @AYEEDITYA maybe this comment might help which leads to a fix lmk if it help or else feel free to update the GH threads so that someone from the team can have a look
GitHub
fix(cmd): ensure proper working of mirror by adlerhurst · Pull Req...
This PR fixes issues to ensure the mirror command functions correctly by updating the projections configuration and related database settings.
Additional Changes
mirror doesn't output the ...
GitHub
[Bug]: zitadel mirror command does not work correctly towards PSQL ...
Preflight Checklist I could not find a solution in the documentation, the existing issues or discussions I have joined the ZITADEL chat Environment Self-hosted Version 2.64.1 Database PostgreSQL Da...
Hey, I am not clear how it would help. I see the fix mentioned in 9509 was merged in V3 (was previsouly migrating with Zitadel V2.71.9. But using V3 results in:
FATA[0000] unable to copy encryption keys to destination caller="/home/runner/work/zitadel/zitadel/cmd/mirror/system.go:137" error="ERROR: duplicate key value violates unique constraint "encryption_keys_pkey" (SQLSTATE 23505)"
when i run the mirror command following the same flow I mentioned above
We are making progress!
https://github.com/zitadel/zitadel/issues/9042
GitHub
[Bug]: Error while migrating from Cockroach to Postgres · Issue #9...
Preflight Checklist I could not find a solution in the documentation, the existing issues or discussions I have joined the ZITADEL chat Environment Self-hosted Version 2.65.1 Database CockroachDB D...