On Tuesday, January 11, 2022, Dominique Devienne <ddevienne@gmail.com> wrote:
This means the template-schema name is part of the DDL for the schema, and a clone would need to use its own search-path, not the original.
This is your major limitation. You are required to create new objects from code and cannot leverage any kind of copy of existing objects.
But how to avoid that limitation?
Sometimes limitations cannot be avoided...in this case however:
Get rid of the libpq requirement and you have options...namely I would create a template database (with 4 schemas) during a "build" stage and backup the containing cluster using pg_basebackup; and then during the "testing" I would restore that cluster in the testing environment as part of its setup and then copy the template database as needed to support the testing. That avoids all of the parse/execute of SQL that happens in the server when using libpq.
Note: pg_dump/pg_restore is a libpq implemented procedure.
Beyond that there is too little detail to provide suggestions.
What kind of further details would you need?
The precise nature of these tests. How many there are of what general types/purpose. How long they take to run. What kind of changes to the build/test system are reasonable. How inflexible are the constraints that have been provided (or, at minimum, why are they there at all).
I would observe that for testing the meaningful values of N are 1 and 2. Having a setup where N = 5 is not materially different than the N = 2 case (usually).
W/o more context, sure, that seems logical. But in this case, that's unlikely, to simulate the "real world".
It doesn't really need context - it's a base premise. There are only three kinds of meaningful cardinality. Zero, One, More than One (which is sufficiently handled by using a quantity of 2). Abide by that unless you can argue why breaking down "more than one" is important.
Unless you are doing a performance test, simulating the "real world" to the fullest is not something that your testing environment needs to do.
