zitadel icon indicating copy to clipboard operation
zitadel copied to clipboard
verified publisher icon zitadel

[Bug]: Mirroring/Migrating database from cockroach to postgres not working

Open Malex14 opened this issue 1 year ago • 3 comments

Preflight Checklist

  • [X] 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 (describe below!)

Database Version

cockroach v23.2; postgres 16:latest

Describe the problem caused by this bug

I've tried to switch the database from cockroachdb to postgres as described in the documentation, but so far i was not able to get it working.

First I made a copy of the running Instance including all docker volumes. In case something goes wrong 🙄 .

Secondly I added the "db"-portion from the example docker-compose.yaml and changed the service name to "postgres" and set a new password. Additionally I added the health-check configuration to the zitadel container.

Then I updated all configs (zitadel-config.yaml and zitadel-secrets.yaml) to use the postgres database. And added the mirror-config to zitadel-mirror.yaml

Finally I ran the the commands to mirror the db. The first two ran without errors, but the last one (the actual mirror) gave me a lot of errors about the column instance_id missing in several tables and unique constraint violations:

Logs 
$ docker compose run zitadel mirror --system --replace --config /zitadel-mirror.yaml  --config /zitadel-config.yaml --config /zitadel-secrets.yaml --masterkeyFile "/master.key" --tlsMode external
[+] Creating 3/0
 ✔ Container zitadel-new-postgres-1      Running                                                                                                                           0.0s 
 ✔ Container zitadel-new-cockroach-db-1  Running                                                                                                                           0.0s 
 ✔ Container zitadel-new-certs-1         Created                                                                                                                           0.0s 
[+] Running 2/2
 ✔ Container zitadel-new-cockroach-db-1  Healthy                                                                                                                           0.5s 
 ✔ Container zitadel-new-certs-1         Started                                                                                                                           0.2s 
INFO[0000] assets migrated                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/system.go:92" count=8 took=67.457942ms
INFO[0000] encryption keys migrated                      caller="/home/runner/work/zitadel/zitadel/cmd/mirror/system.go:138" count=10 took=21.471084ms
INFO[0000] auth requests migrated                        caller="/home/runner/work/zitadel/zitadel/cmd/mirror/auth.go:90" count=125 took=51.543894ms
INFO[0000] start event migration                         caller="/home/runner/work/zitadel/zitadel/cmd/mirror/event_store.go:97" from=1.725544554365596e+18 to=1.7255456439500618e+18
INFO[0000] events migrated                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/event_store.go:180" count=0 took=106.489236ms
INFO[0000] unique constraints migrated                   caller="/home/runner/work/zitadel/zitadel/cmd/mirror/event_store.go:249" count=130 took=20.704267ms
INFO[0001] start projections                             caller="/home/runner/work/zitadel/zitadel/cmd/mirror/projections.go:255" instance=234744630166421507
ERRO[0001] statement execution failed                    caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/v2/handler.go:626" error="ID=CRDB-pKtsr Message=exec failed Parent=(ERROR: duplicate key value violates unique constraint \"smtp_configs2_pkey\" (SQLSTATE 23505))" projection=projections.smtp_configs2
INFO[0001] process events failed                         caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/v2/handler.go:413" error="statement failed: ID=CRDB-pKtsr Message=exec failed Parent=(ERROR: duplicate key value violates unique constraint \"smtp_configs2_pkey\" (SQLSTATE 23505))" projection=projections.smtp_configs2
INFO[0001] trigger failed                                caller="/home/runner/work/zitadel/zitadel/cmd/mirror/projections.go:260" error="statement failed: ID=CRDB-pKtsr Message=exec failed Parent=(ERROR: duplicate key value violates unique constraint \"smtp_configs2_pkey\" (SQLSTATE 23505))" instance=234744630166421507
ERRO[0001] projection failed                             caller="/home/runner/work/zitadel/zitadel/cmd/mirror/projections.go:234" instance=234744630166421507
INFO[0001] projections executed                          caller="/home/runner/work/zitadel/zitadel/cmd/mirror/projections.go:250" took=1.087701714s
INFO[0001] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-1 source=1 table=adminapi.locks
INFO[0001] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-1 source=1 table=adminapi.current_sequences
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-10 source=10 table=auth.users2
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-4 source=4 table=auth.locks
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-4 source=4 table=auth.current_sequences
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-109 source=109 table=eventstore.fields
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-109 source=109 table=projections.locks
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-115 source=115 table=projections.current_sequences
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=0 diff=-1 source=1 table=projections.failed_events
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=1 diff=1 source=0 table=projections.failed_events2
INFO[0002] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=65 diff=-37 source=102 table=projections.current_states
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.instances
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.instances
INFO[0003] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=2 diff=-409 source=411 table=projections.keys4
INFO[0003] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=1 diff=-410 source=411 table=projections.keys4_private
INFO[0003] unequal count                                 caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:55" dest=2 diff=-409 source=411 table=projections.keys4_public
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.system_features
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.system_features
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=system.encryption_keys
ERRO[0003] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0003] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=system.encryption_keys

When I then start the new instance I cannot login with the admin user and the design of the loginpage doesn't show the customizations.

To reproduce

  1. Have an Instance running with cockroach

  2. Add postgres container to docker-compose

  3. update configs to use postgres

  4. create mirror config with cockroach as source and postgres as destination

  5. run the "init" command

    docker compose run zitadel init --config /zitadel-mirror.yaml  --config /zitadel-config.yaml --config /zitadel-secrets.yaml

  6. run the "setup" command

    docker compose run zitadel setup --for-mirror --config /zitadel-mirror.yaml  --config /zitadel-config.yaml --config /zitadel-secrets.yaml --masterkeyFile "/master.key" --tlsMode external

  7. run the "mirror" command and observe the errors

    docker compose run zitadel mirror --system --config /zitadel-mirror.yaml  --config /zitadel-config.yaml --config /zitadel-secrets.yaml --masterkeyFile "/master.key" --tlsMode external

Screenshots

No response

Expected behavior

The mirror command creates an exact copy of the original database.

Operating System

Ubuntu 22.04 LTS Docker 27.1.2

Relevant Configuration

Redacted information replaced with XXX

docker-compose.yaml

version: '3.8'

services:
  zitadel:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'ghcr.io/zitadel/zitadel:v2.61.0'
    command: 'start-from-init --config /zitadel-config.yaml --config /zitadel-secrets.yaml --steps /zitadel-init-steps.yaml --masterkeyFile "/master.key" --tlsMode external'
    depends_on:
      certs:
        condition: 'service_completed_successfully'
      postgres:
        condition: 'service_healthy'
    ports:
      - '127.0.0.1:28081:8080'
    volumes:
      - './zitadel-config.yaml:/zitadel-config.yaml:ro'
      - './zitadel-secrets.yaml:/zitadel-secrets.yaml:ro'
      - './zitadel-init-steps.yaml:/zitadel-init-steps.yaml:ro'
      - './zitadel-mirror.yaml:/zitadel-mirror.yaml:ro'
      - './master.key:/master.key:ro'
      - 'zitadel-certs:/crdb-certs:ro'

  certs:
    image: 'cockroachdb/cockroach:latest-v23.2'
    entrypoint: [ '/bin/bash', '-c' ]
    command: [ 'cp /certs/* /zitadel-certs/ && cockroach cert create-client --overwrite --certs-dir /zitadel-certs/ --ca-key /zitadel-certs/ca.key zitadel_user && chown 1000:1000 /zitadel-certs/*' ]
    volumes:
      - 'certs:/certs:ro'
      - 'zitadel-certs:/zitadel-certs:rw'
    depends_on:
      cockroach-db:
        condition: 'service_healthy'

  cockroach-db:
    restart: 'always'
    networks:
      - 'zitadel'
    image: 'cockroachdb/cockroach:latest-v23.2'
    command: 'start-single-node --advertise-addr cockroach-db'
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:8080/health?ready=1"]
      interval: '10s'
      timeout: '30s'
      retries: 5
      start_period: '20s'
    ports:
      - '127.0.0.1:29091:8080'
    volumes:
      - 'certs:/cockroach/certs:rw'
      - 'data:/cockroach/cockroach-data:rw'
  postgres:
    image: postgres:16-alpine
    restart: always
    environment:
      POSTGRES_USER: 'root'
      POSTGRES_PASSWORD: 'XXX'
    networks:
      - 'zitadel'
    healthcheck:
      test: ["CMD-SHELL", "pg_isready", "-d", "root"]
      interval: 10s
      timeout: 60s
      retries: 5
      start_period: 10s 
    volumes:
      - 'pgdata:/var/lib/postgresql/data:rw'
networks:
  zitadel:

volumes:
  certs:
  zitadel-certs:
  data:
  pgdata:

zitadel-config.yaml

# All possible options and their defaults: https://github.com/zitadel/zitadel/blob/main/cmd/defaults.yaml
Log:
  Level: 'info'

Metrics:
  Type: 'none'

Telemetry:
  Enabled: false

WebAuthNName: 'XXX'

# Make ZITADEL accessible over HTTP, not HTTPS
ExternalSecure: true
ExternalDomain: XXX
ExternalPort: 443

Database:
#  cockroach:
#    Host: 'cockroach-db'
#      #    Port: '26258'
#    User:
#      SSL:
#        Mode: 'verify-full'
#        RootCert: "/crdb-certs/ca.crt"
#        Cert: "/crdb-certs/client.zitadel_user.crt"
#        Key: "/crdb-certs/client.zitadel_user.key"
#    Admin:
#      SSL:
#        Mode: 'verify-full'
#        RootCert: "/crdb-certs/ca.crt"
#        Cert: "/crdb-certs/client.root.crt"
#        Key: "/crdb-certs/client.root.key"
  postgres:
    Host: 'postgres'
    Port: '5432'
    Database: 'zitadel'
    User:
      SSL:
        Mode: 'disable'
    Admin:
      SSL:
        Mode: 'disable'

LogStore:
  Access:
    Stdout:
      Enabled: true

zitadel-secrets.yaml

# All possible options and their defaults: https://github.com/zitadel/zitadel/blob/main/cmd/defaults.yaml

Database:
#  cockroach:
#    User:
#      # If the user doesn't exist already, it is created
#      Username: 'zitadel_user'
#      Password: 'XXX'
#    Admin:
#      Username: 'root'
#      Password: 'XXX'
  postgres:
    User:
      # If the user doesn't exist already, it is created
      Username: 'zitadel_user'
      Password: 'XXX'
    Admin:
      Username: 'root'
      Password: 'XXX'

zitadel-mirror.yaml

# The source database the data are copied from. Use either cockroach or postgres, by default cockroach is used
Source:
  cockroach:
    Host: 'cockroach-db'
    User:
      Username: 'root'
      Password: 'XXX'
      SSL:
        Mode: 'require'
        RootCert: "/crdb-certs/ca.crt"
        Cert: "/crdb-certs/client.root.crt"
        Key: "/crdb-certs/client.root.key"
  postgres:
    Host: # ZITADEL_SOURCE_POSTGRES_HOST
    Port: # ZITADEL_SOURCE_POSTGRES_PORT
    Database: # ZITADEL_SOURCE_POSTGRES_DATABASE
    MaxOpenConns: # ZITADEL_SOURCE_POSTGRES_MAXOPENCONNS
    MaxIdleConns: # ZITADEL_SOURCE_POSTGRES_MAXIDLECONNS
    MaxConnLifetime: # ZITADEL_SOURCE_POSTGRES_MAXCONNLIFETIME
    MaxConnIdleTime: # ZITADEL_SOURCE_POSTGRES_MAXCONNIDLETIME
    Options: # ZITADEL_SOURCE_POSTGRES_OPTIONS
    User:
      Username: # ZITADEL_SOURCE_POSTGRES_USER_USERNAME
      Password: # ZITADEL_SOURCE_POSTGRES_USER_PASSWORD
      SSL:
        Mode: # ZITADEL_SOURCE_POSTGRES_USER_SSL_MODE
        RootCert: # ZITADEL_SOURCE_POSTGRES_USER_SSL_ROOTCERT
        Cert: # ZITADEL_SOURCE_POSTGRES_USER_SSL_CERT
        Key: # ZITADEL_SOURCE_POSTGRES_USER_SSL_KEY

# The destination database the data are copied to. Use either cockroach or postgres, by default cockroach is used
Destination:
  # Postgres is used as soon as a value is set
  # The values describe the possible fields to set values
  postgres:
    Host: 'postgres' # ZITADEL_DESTINATION_POSTGRES_HOST
    Port: '5432' # ZITADEL_DESTINATION_POSTGRES_PORT
    Database: 'zitadel' # ZITADEL_DESTINATION_POSTGRES_DATABASE
    #MaxOpenConns: # ZITADEL_DESTINATION_POSTGRES_MAXOPENCONNS
    #MaxIdleConns: # ZITADEL_DESTINATION_POSTGRES_MAXIDLECONNS
    #MaxConnLifetime: # ZITADEL_DESTINATION_POSTGRES_MAXCONNLIFETIME
    #MaxConnIdleTime: # ZITADEL_DESTINATION_POSTGRES_MAXCONNIDLETIME
    #Options: # ZITADEL_DESTINATION_POSTGRES_OPTIONS
    User:
      Username: 'root' # ZITADEL_DESTINATION_POSTGRES_USER_USERNAME
      Password: 'XXX' # ZITADEL_DESTINATION_POSTGRES_USER_PASSWORD
      SSL:
        Mode: 'disable' # ZITADEL_DESTINATION_POSTGRES_USER_SSL_MODE
        RootCert: # ZITADEL_DESTINATION_POSTGRES_USER_SSL_ROOTCERT
        Cert: # ZITADEL_DESTINATION_POSTGRES_USER_SSL_CERT
        Key: # ZITADEL_DESTINATION_POSTGRES_USER_SSL_KEY

zitadel-init-steps.yaml

# All possible options and their defaults: https://github.com/zitadel/zitadel/blob/main/cmd/setup/steps.yaml
FirstInstance:
  DefaultLanguage: de
  Org:
    Name: 'XXX'
    Human:
      # use the loginname [email protected]
      Username: 'XXX'
      Password: 'XXX'
      PasswordChangeRequired: true
      Email:
        Address: 'XXX'
        Verified: true
      PreferedLanguage: de
    LoginPolicy:
      AllowRegister: false
      AllowExternalIDP: false
      ForceMFA: true
      IgnoreUnknownUsernames: true

Additional Context

No response

Malex14 avatar Sep 05 '24 14:09 Malex14

can confirm, I'm seeing this as well.

from the postgres logs side it's showing:

<snip>
pg-1  | 2024-09-19 14:13:22.628 UTC [226] LOG:  statement: begin isolation level read committed read only                                                                                                                                                                                  
pg-1  | 2024-09-19 14:13:22.630 UTC [226] ERROR:  column "instance_id" does not exist at character 50                                        
pg-1  | 2024-09-19 14:13:22.630 UTC [226] STATEMENT:  SELECT COUNT(*) FROM projections.instances WHERE instance_id <> ''                                                                                                                                                                   
pg-1  | 2024-09-19 14:13:22.630 UTC [226] LOG:  statement: rollback  
pg-1  | 2024-09-19 14:13:23.260 UTC [251] ERROR:  column "instance_id" does not exist at character 56
pg-1  | 2024-09-19 14:13:23.260 UTC [251] STATEMENT:  SELECT COUNT(*) FROM projections.system_features WHERE instance_id <> ''
pg-1  | 2024-09-19 14:13:23.497 UTC [260] LOG:  statement: begin isolation level read committed read only                                                                                                                                                                                  
pg-1  | 2024-09-19 14:13:23.498 UTC [260] ERROR:  column "instance_id" does not exist at character 51                                   
pg-1  | 2024-09-19 14:13:23.498 UTC [260] STATEMENT:  SELECT COUNT(*) FROM system.encryption_keys WHERE instance_id <> ''                                                                                                                                                                  
pg-1  | 2024-09-19 14:13:23.499 UTC [260] LOG:  statement: rollback

from the verify code it looks like it's just looping over a set of schemas and querying all of the tables for fmt.Sprintf("SELECT COUNT(*) FROM %s %s", table, instanceClause()), whether or not it has an instance_id column?.

i guess the question i currently have is whether or not those tables SHOULD have an instance_id column and my existing cockroachdb db is somehow out of sync. although it seems weird projections.instances would have an instance_id column given it's ID is the instance's id.

adasauce avatar Sep 19 '24 14:09 adasauce

Unfortunately I solved this by brute force. I did a custom build where projections could continue to mirror even when one failed instead of bailing out on the unique key constraint failure (I think correctly assumed that if the data it was trying to insert was already there as the row already existed), the rest of the data would flow through fine. I tested it a few times on some backups, and then rolled it out once I was confident the mirror was working as expected.

adasauce avatar Sep 20 '24 18:09 adasauce

I also encountered this problem.

I am using:

  • zitadel v2.62.2
  • cockroachdb v23.2.5
  • PostgreSQL v15.7 (Azure Database for PostgreSQL flexible server)
ERRO[0008] statement execution failed                    caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/v2/handler.go:626" error="ID=CRDB-pKtsr Message=exec failed Parent=(ERROR: duplicate key value violates unique constraint \"smtp_configs4_pkey\" (SQLSTATE 23505))" projection=projections.smtp_configs4
DEBU[0008] execution of statements failed                caller="/home/runner/work/zitadel/zitadel/internal/eventstore/handler/v2/handler.go:518" error="statement failed: ID=CRDB-pKtsr Message=exec failed Parent=(ERROR: duplicate key value violates unique constraint \"smtp_configs4_pkey\" (SQLSTATE 23505))" lastProcessedIndex=0 projection=projections.smtp_configs4
ERRO[0025] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0025] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.instances
ERRO[0025] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0025] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.instances
ERRO[0025] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0025] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.system_features
ERRO[0025] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0025] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=projections.system_features
ERRO[0026] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0026] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=system.encryption_keys
ERRO[0026] unexpected query error                        caller="/home/runner/work/zitadel/zitadel/internal/database/database.go:88" error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)"
ERRO[0026] unable to count                               caller="/home/runner/work/zitadel/zitadel/cmd/mirror/verify.go:108" db=zitadel error="ERROR: column \"instance_id\" does not exist (SQLSTATE 42703)" table=system.encryption_keys

Full log:

full-log.v2.62.2.log

cmj2002 avatar Sep 27 '24 15:09 cmj2002

The SMTP setting duplicate key errors I fixed by removing them from the target database and then running the mirror command again with flag --replace. This puts the newest SMTP configuration in place in that table.

For example:

zitadel=# delete from projections.smtp_configs5; 
DELETE 1
zitadel=#

The ERROR: column \"instance_id\" does not exist errors I fixed with #8852. Not the prettiest, but it worked.

chris-1o avatar Nov 01 '24 15:11 chris-1o

Thank you 👍. I will try it again when your change gets merged

Malex14 avatar Nov 01 '24 16:11 Malex14

please try to upgrade the deployed version first to latest v2.65.x and then run the mirror with the same version. We implement fixes on that minor version because it's the last one compatible with cockroachdb

adlerhurst avatar May 14 '25 07:05 adlerhurst

Thanks for your reply. I've got it to work, but only with help from this comment (https://github.com/zitadel/zitadel/issues/9120#issuecomment-2888582544). With the 2.XX binary there was a SQL-error as described in the other issue. It would be nice, if it was mentioned in the documentation, that you need a version of Zitadel >= 3.0.0 to mirror the database successfully.

For now im closing this issue, since i've migrated to postgresql successfully.

Malex14 avatar Jun 25 '25 19:06 Malex14