How to organize a project structure in PostgreSQL?

Question

PostgreSQL

Hello,
there is a database on PostgreSQL, data is stored in it (a lot, but not very much, 20GB). It has a relatively large amount of business logic - procedures, triggers, views (including materialized ones) and the code of all this is very non-trivial, including parsing xml with xpath requests and other magic, respectively, all this is densely covered with pgTAP tests.
In the end, the task arose of how to store the schema in the repository and be able to deploy it to a working or staging base.
So, how I tried to solve this problem:

• Since in the database, all of a sudden, there is data, you need to change the schema using migrations. It was decided to use Flywaydb
  
• Part of the scheme does not fit the format of migrations, for example, the same storages, it is more convenient to scatter them into directories and edit them if necessary
  
• Accordingly, in order to force Flywaydb to roll them, I thought to consider migrations as a build artifact, I thought using gradle (I still need java for flyway anyway) to concatenate outOfDate files in the migration and add them to a separate folder with generated migrations
  

However, a big BUT has arisen here: during development, you will have to edit the stored files before they pass the tests, this will lead to a large number of unnecessary migrations that I don’t know how to automatically clean up, as a result, there will again be a garbage dump in the repository.
Of course, the decision to split the assembly for the development server comes to mind (just roll sql files from gradle using pgadmin) and use the above mechanism to fix changes in the migration, but I would like to know how others solve this problem first.

Answer 1

There was a similar question here, there is an answer How to control stored procedures and triggers through VCS?