Merge multiple Hydra instances with different system.secrets

caution

Be advised that this can break client creation if done incorrectly!

Please follow this guide with caution and make sure you know what you are doing.

This guide provides practical steps to merge multiple Ory Hydra Postgres database instances with different system.secret values into one instance with one system.secret.

The system.secret or $SECRETS_SYSTEM is an environmental variable, that's used to encrypt Ory Hydras database.

If you are looking for information on how to change (rotate) the system.secret, please refer to the Secrets and Key Rotation Guide.

First we take all the system.secret keys and add them to the target instance environment variables like so:

SECRETS_SYSTEM=new-secret,secret-1,secret-2,secret-3,secret-n,secret-n+1

Then we run the following pg_dump command against the databases we need to migrate:

pg_dump --verbose -a  \
    --format=p \
    --no-owner \
    --no-acl \
    --quote-all-identifiers \
    -t hydra_client \
    -t hydra_oauth2_authentication_session \
    -t hydra_oauth2_authentication_request \
    -t hydra_oauth2_authentication_request_handled \
    -t hydra_oauth2_consent_request \
    -t hydra_oauth2_consent_request_handled \
    -t hydra_oauth2_code \
    -t hydra_oauth2_access \
    -t hydra_oauth2_refresh \
    -f hydra-dump.sql \
    <DSN>

Take extra care with the following manual edits!

Then we open each of the dump files and manually edit hydra_clients.pk be entirely set to the value DEFAULT (SQL keyword) to make it follow the sequence in the target DB.

We delete the last line of the dump, since it sets the value of the primary key sequence to the number of the donor database.
So we need to delete this line. This step is crucial!

If you don't remove it, it will potentially break client creation, since it will reset the sequence of hydra_clients.pk to what it was in the source DB

After that the final step is to import the data like so:

psql -o hydra_import_log \
 -f hydra-dump.sql \
 <DSN>

And that did it, we successfully merged multiple Hydra instances with different system.secret into one instance with one system.secret.