Сброс состояния Postgres

Редактировал(а) Alexandr Fokin 2024/07/01 11:01
 #region

public static async Task CleanAsync(
    PostgreSqlContainer container
    )
{
   var dbNames = await GetDbNames(container);

   //await CleanV1Async(container, dbNames);
    await CleanV2Async(container, dbNames);
}

private static async Task<List<string>> GetDbNames(
    PostgreSqlContainer container
    )
{
    List<string> dbNames = new List<string>(5);

   var connectionString = /* connectionString */;

   await using var connection = new NpgsqlConnection(connectionString);
   await connection.OpenAsync();

   var query = @"SELECT datname as dbname FROM pg_database WHERE datistemplate = false and datname <> 'postgres'";
   using var command = connection.CreateCommand();
    command.CommandText = query;

   await using var reader = await command.ExecuteReaderAsync();
   while (await reader.ReadAsync())
    {
       var dbName = (string)reader["dbname"];
        dbNames.Add(dbName);
    }

   return dbNames;
}

/// <summary>
/// Пересоздание схем (быстрее и нет проблем с пулами подключений)
/// </summary>
private static async Task CleanV2Async(
    PostgreSqlContainer container,
    IEnumerable<string> dbNames
    )
{
   var resetDbTasks = dbNames                    
        .Select(e => ResetSchemesAsync(container, e))                    
        .ToArray();

   await Task.WhenAll(resetDbTasks);
}

#endregion

private static async Task ResetSchemesAsync(
    PostgreSqlContainer container,
   string dbName
    )
{
   var connectionString = /* connectionString to dbName */;
   await using var connection = new NpgsqlConnection(connectionString);
   await connection.OpenAsync();

    List<string> schemas = new List<string>(10);
    {
       var query = @"select schema_name from information_schema.schemata where schema_name <> 'information_schema' and schema_name not like 'pg_%'";
       using var command = connection.CreateCommand();
        command.CommandText = query;

       await using var reader = await command.ExecuteReaderAsync();
       while (await reader.ReadAsync())
        {
           var schema = (string)reader["schema_name"];
            schemas.Add(schema);
        }
    }
    {
       var query = $"DROP SCHEMA {string.Join(", ", schemas.Select(e => $@"""{e}"""))} CASCADE";
       using var command = connection.CreateCommand();
        command.CommandText = query;

       await command.ExecuteNonQueryAsync();
    }
    {
       var query = @"CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;";
       using var command = connection.CreateCommand();
        command.CommandText = query;

       await command.ExecuteNonQueryAsync();
    }
}
 Testcontainers, Containers pool
 
Теги: