temporal-sql-tool default postgres database

[loopingrage]

Expected Behavior

We expected that setting temporal-sql-tool --database defaultdb would allow us to set the admin database to use when it differs from the default of postgres.

Actual Behavior

When attempting to use an Aiven Postgres instance for Temporal we quickly ran into an issue where temporal-sql-tool seems is hard-coded to postgres as the admin database name. As a result, the create-database operation fails with:

database "postgres" does not exist

Aiven clusters have an admin db named defaultdb. We tried setting the database name but it seems to be ignored:

./temporal-sql-tool --plugin postgres --endpoint XXXXXXX.aivencloud.com --port 15355 --user avnadmin --password XXXXXXX --tls --tls-enable-host-verification --tls-ca-file ca.pem --database defaultdb create-database -database temporal

error creating database:pq: database "postgres" does not exist

[loopingrage]

I changed this line and build the tool from source and it works. Unless I'm missing something, there doesn't seems to be way to set cfg.DatabaseName via the command line.

[loopingrage]

Hey @wxing1292. Any suggestions here?

[wxing1292]

We expected that setting temporal-sql-tool --database defaultdb would allow us to set the admin database to use when it differs from the default of postgres.

according to my memory, the target database must be specified when a connection is establish to PostgreSQL
this is why postgres is used, e.g.

• first establish a connection to the default / prebuilt DB (postgres)
• create a new DB for either prod use case, or for testing purpose

(not familiar with Aiven)
to unblock you, can you create a DB postgres?

BTW, we currently only support MySQL 5.7, PostgreSQL 9.6 and Cassandra 3.11

[loopingrage]

Aiven Cloud doesn't have a default postgres database. Their admin database is named defaultdb. Seems it would be useful to support setting the name of the default DB in temporal-sql-tool no? I'm happy to craft a PR if you can point me in the right direction.

[wxing1292]

Seems it would be useful to support setting the name of the default DB in temporal-sql-tool no?

doing so will create a special behavior for temporal-sql-tool, e.g. a new config which will only be useful for postgresql

seems #1413 which adds the ability to try a set of default DB names