Allow the switching of profile and corresponding backend within interpreter

[sphuber]

The mechanism of loading a profile was changed significantly in PR #2740 which make it possible in principle to dynamically load another profile and its corresponding database backend while staying in the same interpreter. For the time being this behavior is blocked and changing the backend in a non-test profile will raise. After tests are introduced to make sure this can be done safely, this limitation should be lifted.

[sphuber]

Adding this additional comments from #2813 that really just describes a sub problem of the issue described here.

Copied content below

If we want profiles to be switchable in a single interpreter, the database environment needs to be switchable as well. However, currently for Django, once the aiida.backends.djsite.settings file is imported, the variables for example the database connection, which are based on the currently loaded profile, will be locked in. So even if the profile would be swapped for another django profile, with a different database, the original database will be used anyway.

By @giovannipizzi

A comment to be taken into account: the purpose of recreate_after_fork() is to be called as a callback when the process is forked (e.g. by a demoniser - this was the case with celery), here:

https://github.com/aiidateam/aiida-core/blob/0644e7d9ff492fdb2c94d608c575c697daee6d32/aiida/backends/sqlalchemy/__init__.py#L78

By the way, I realise now that probably:

1. We should dispose of the passed engine, and not the global one
2. When switching backend, probably we should deregister the callback if it was registered

[chrisjsewell]

Fixed in #5330