Сценарии - Сброс состояния 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