As you, the reader, found this article, it is likely that you want to manually migrate the ZITADEL PostgreSQL database to a new version.

Now, normally this step is being done by the zitadel setup command with the --init-projections=true flag but, as we all know since “2001: A Space Odyssey”, computers can’t be trusted - so you are either paranoid or something went terribly wrong.

For manually updating the database, we should first understand the structure a bit. So far, the Zitadel Team seems to use versioning for their tables - meaning they create a new table for every change instead of just altering the current table. This leads to some beautifully structured schemas full of tables like users6, users7 and users8 up to users10.

I don’t know if this structure is still there at the time of you reading this, but in this post I will just go with the assumption that Zitadel Developers are afraid of altering and love version numbers.

Common Problems⌗

The reason, I write this article, is my database wasn’t migrated correctly. In my case the command said everything is migrated, but then I encountered errors like unable to retrieve client by id, migration already started, will check again in 5 seconds or {"error":"invalid_request","error_description":"Errors.App.NotFound"}.

All of these errors point to the database. In my case all the new tables were created but some of the data was still only in the old tables and in other cases only some of the rows got copied into the new tables.

Manually Migrating⌗

Before continuing, I would recommend that all the following steps are being done on a cloned instance, not on the production setup. You should also write down every SQL query you create so that you can reproduce the migration in case it was successful on the test instance.

Identifying the right tables⌗

To migrate the database, you should first be sure what the current tables are. At the time of me writing this, this is normally the table with the highest number - e.g. projections.apps6 instead of projections.apps4.

Now, you should make sure that this table is actually the right table - what if the table didn’t even get created? You can take a look at the source code.

Currently, the projections are in the zitadel/internal/query/projection folder. Example: the app.go file has the following lines:

AppProjectionTable = "projections.apps6"
AppAPITable        = AppProjectionTable + "_" + appAPITableSuffix
AppOIDCTable       = AppProjectionTable + "_" + appOIDCTableSuffix
AppSAMLTable       = AppProjectionTable + "_" + appSAMLTableSuffix

This gives us already 4 current table names. You should make sure that you check out the right version tag on GitHub so that you don’t look at outdated or Code which is still in development.

We should also first do the tables without foreign keys, only after that the tables with foreign keys.

Checking Columns⌗

Next, we should make sure that the tables we want to migrate have the same columns. Some older tables have e.g. owner_removed columns which are not needed anymore.

This can be done by e.g.:

DROP INDEX projections.apps5_owner_removed_idx;
ALTER TABLE projections.apps5 DROP COLUMN owner_removed;

Migrating Rows⌗

If the old and the new table have the same columns, we can easily transfer the data:

INSERT INTO projections.apps6 SELECT * FROM projections.apps4;

In some cases, like when only some data got copied, we might need to only transfer unique entries:

SELECT * 
FROM projections.users8 source
WHERE NOT EXISTS (
    SELECT 1 
    FROM projections.users10 destination 
    WHERE destination.username = source.username
);

Only in a few rare cases you will need to specify the column names:

INSERT INTO login_names3_users (id, user_name, resource_owner, instance_id)
SELECT id, user_name, resource_owner, instance_id
FROM login_names2_users source
WHERE NOT EXISTS (
    SELECT 1
    FROM login_names3_users destination
    WHERE destination.user_name = source.user_name
);

Deleting the old table⌗

After being sure that we migrated all the content into the right table, we can delete the old one:

DROP TABLE projections.idp_templates2_google;

Finishing⌗

This whole process might take some time, but if you do everything correct, it will work. As I wrote before, it is best done in a test system, not in production, and normally you shouldn’t need this article because the setup step from Zitadel should do this work for you.

If you searched for this article, I guess you could have found some bug, and probably writing a GitHub Issue is also a good idea.