Interactions with Azure PostgreSQL-Databases

[cymed]

Some hosted PostgreSQL - servers (i.e. Azure PaaS) do not give out superuser rights to their clients. Also, Azure Database for Postgres does not have a user named "postgres" This complicates things when loading non-plain database backups into these systems. I therefore suggest that all TEKSI backups (i.e. the release assets of the datamodel) are stored as plain text sql files instead of .backup

Workaround for other service providers encountering this problem:

• Load .backup on a different postgres server
• backup as plain sql
• import on Azure PostgreSQL using psql.

[ponceta]

That's a great observation, but the problem is actually the opposite when you deal with small entities relying only on pgAdmin interface for management. Of course it includes a psql client but the usage is much more complicated for the end user.

As it is now almost fully automated, we should provide both .backup and .sql files.

Since the demo_data_backup can't be restore in a sql structure, I'm wondering the utility of it. I would propose to have :

• project_x.x.x_structure_with_value_lists.sql/.backup
• project_x.x.x_demo.sql/.backup (being the full demo with value lists and structure)

Technical assets can be generated only in the required format.

• (project_x.x.x_structure.sql) and (demo_data.backup)

@cymed we should also add the import to Azure PostgreSQL as a supported and documented framework for TEKSI modules.

[cymed]

The problem arises solely when a potential TEKSI user wishes to import the structure_and_demo_data.backup on an Azure-based DB. In my opinion it is ok to rely on a service provider for this very specific task. For Clients on a managed service we cannot automatically assume that there is a user named postgres. we should take that into consideration when creating deltas - grant all schemas/functions etc to qgep_sysadmin instead of postgres.