Сценарии - Сброс состояния Postgres 

Page / - Изменено Alexandr Fokin в 2024/07/01 11:01 

Сброс состояния 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	
 	How do I list all databases and tables using psql?
https://dba.stackexchange.com/questions/1285/how-do-i-list-all-databases-and-tables-using-psql
How do I list all schemas in PostgreSQL?
https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql
How can I drop all the tables in a PostgreSQL database?
https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database