How to merge multiple Hydra instances with different system.secrets

[vinckr]

All credit for this goes to @bnm12 , thanks a ton for the write-up 🐝

Problem:

I have 5 different Hydra instances in different environments (all with different system keys) and I need to attempt some kind of merge of all of them into one large Hydra instance in an effort to clean up our infrastructure and a few other maturity things.

Solution:

So to recap we had 5 separate Hydra instances we wanted to merge into one big multi-tenant instance/deployment but they were all set up with their own individual system.secret keys so we couldn't just merge the databases.

What we ended up testing and executing was a process of taking all the system.secret keys and adding them to the target instance environment variables like this:

SECRETS_SYSTEM=new-secret,secret-1,secret-2,secret-3,secret-4,secret-5

Then we ran this pg_dump command against the databases we needed 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>

From there we went into each of the dump files and manually edited hydra_clients.pk to be entirely set to the value default (SQL keyword) to make it follow the sequence in the target DB correctly.
We also deleted the last line of the dump which sets the value of the pk sequence to the number of the donor DB, so get rid of that one.
After that the next step is importing the data which we did with this command:

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

And that did it, we successfully merged 5 Hydra instances with different system.secrets

[aeneasr]

Should we add this to the docs?

[bnm12]

It should probably be noted a bit more strongly ala. default that we changed the hydra_client.pk values to the actual SQL keyword default

[vinckr]

Thanks for the addition @bnm12 !

Maybe we could add it to this section @aeneasr Secrets and Key Rotation ?

We can also add the Guide to recovering lost SECRETS_SYSTEM there.
Let me know if you think this is a good place for it, or if there is one that would fit better.

[aeneasr]

I would add a new guide