Can results schema be on a different database than cdm?

[Diego_Bosca_Tomas (Diego Boscá Tomás)]

After reading the documentation, it is not clear to me if it is allowed to have results schema in a different database than the cdm itself. In our current context, due to volume, cdm instance is in virtual layer offering a postgres access, but results (and temp) need writing access which the virtual layer won’t allow. I can see it being problematic as different “sources” in configuration usually have the 4 daemons assigned.
If it is possible, how would that look like in the configuration?

[rookie_crewkie]

Hello @Diego_Bosca_Tomas,

PostgreSQL does not allow cross-database queries, and database name is part of the JDBC connection string so the server connection will always be single-db.
There’s also an old post by Chris Knoll on the forum, I believe this rule still holds:

Question About Using Multiple Database Connections and DDL

Yes, there’s no harm in setting up a blank schema in the DB and having Achilles write to that new schema. Achilles will create the tables it needs to write to. You can NOT have the schema in another database, because many of the queries involve reading from the CDM schema and then writing into the schema that Achilles uses.

You could try looking into postgres_fdw and importing your OMOP data into a writeable database (with results / temp data) as a foreign schema.