Setup custom schema and setup temporal specific in Postgres

Hi,
I am trying to setup schema and update schema to a specific schema, not using the public schema. Please help out on the process on doing so as I had gone through certain threads but couldn’t find a proper working example to setup the specifics using the temporal sql tool?

Tried out the following as well https://github.com/temporalio/temporal/issues/3383#issue-1373532593 - --ca flag seems to be missing.

SQL_DATABASE=temporal ./temporal-sql-tool --ca search_path=temporal setup-schema -v 0.0
SQL_DATABASE=temporal ./temporal-sql-tool update --ca search_path=temporal -schema-dir schema/postgresql/v96/temporal/versioned
SQL_DATABASE=temporal_visibility ./temporal-sql-tool --ca search_path=temporal setup-schema -v 0.0
SQL_DATABASE=temporal_visibility ./temporal-sql-tool update --ca search_path=temporal -schema-dir schema/postgresql/v96/visibility/versioned

Do we need to manually create the schema as well?

Hello @Anjula_Paulus ,

Are you still running into this issue? I have seen some comments on the issue mentioned above.

Yes this is sorted.

SQL_DATABASE=temporal ./temporal-sql-tool --ca search_path=temporal setup-schema -v 0.0
SQL_DATABASE=temporal ./temporal-sql-tool --ca search_path=temporal update -schema-dir schema/postgresql/v96/temporal/versioned

SQL_DATABASE=temporalvisibility ./temporal-sql-tool --ca search_path=temporal setup-schema -v 0.0
SQL_DATABASE=temporalvisibility ./temporal-sql-tool --ca search_path=temporal update -schema-dir schema/postgresql/v96/visibility/versioned
              

@Anjula_Paulus I’m running into the same issue. In your example, did you run the custom code in a custom setup script? I’m just wondering how to achieve this is in the docker-compose example.

@antonio.perez any advice?

Here is the auto-setup script if that helps, check the the schema setup function for your db type.