I’m currently working on deploying Temporal in an enterprise environment with very strict security policies regarding database management.
Our Security and DBA teams do not allow applications or automated tools to run with elevated privileges (like CREATE, ALTER, or DROP) in production environments. This means we cannot use the Temporal CLI (temporal-sql-tool) or the auto-setup images to initialize or update the schema directly.
I have a few questions regarding the manual setup:
SQL Export: Is there a recommended way to generate the raw .sql scripts (DDL) for a specific version of Temporal so our DBAs can review and execute them manually?
Schema Versioning: If we run the scripts manually, how does Temporal track the schema version? Do we need to manually update any metadata tables (like schema_version) to prevent the server from failing on startup?
Best Practices: Are there any official docs or community resources for “Manual Schema Evolution” in locked-down environments?
We are using PostgreSQL as our persistence layer.
Hi, @tihomir thank you very much for the quick response.
I understand that the directories /schema/postgresql/v12/visibility/versioned and /schema/postgresql/v12/temporal/versioned contain the versioned scripts for the temporal_visibility and temporal databases, respectively. I also understand that when Temporal starts, it validates that the database is up to date (often via a temporal-schema job) or executes the necessary scripts for the current version using temporal-sql-tools.
This execution version is then stored in the schema_version table. Please let me know if I have misunderstood any part of this process.
Our goal is to delegate the execution of these scripts and the creation of database objects to our own internal tooling. This tool is already approved by our organization to connect to PostgreSQL with the necessary privileges. Due to company security policies, we cannot grant Temporal’s automated processes the superuser privileges required to perform these updates automatically, even though we know the system handles it well.
Our main question/concern is: Can we fully rely on the scripts located in the /v12/visibility/versioned and /v12/temporal/versioned paths as our single source of truth? We want to ensure that those files contain everything Temporal needs to function correctly when executed manually by us.
Specifically, does the Temporal AdminTools (or the schema tools) use any scripts other than those versioned in those directories, or does it perform complex database operations during execution that would be difficult to replicate manually?
I hope this clarifies our needs. I look forward to your insights on this.
