Исходный код вики Сброс состояния Postgres

Редактировал(а) Alexandr Fokin 2024/07/01 11:01
Последние авторы
1 |(% style="width:77px" %) |(% style="width:1377px" %){{code language="c#"}}#region
2
3 public static async Task CleanAsync(
4 PostgreSqlContainer container
5 )
6 {
7 var dbNames = await GetDbNames(container);
8
9 //await CleanV1Async(container, dbNames);
10 await CleanV2Async(container, dbNames);
11 }
12
13 private static async Task<List<string>> GetDbNames(
14 PostgreSqlContainer container
15 )
16 {
17 List<string> dbNames = new List<string>(5);
18
19 var connectionString = /* connectionString */;
20
21 await using var connection = new NpgsqlConnection(connectionString);
22 await connection.OpenAsync();
23
24 var query = @"SELECT datname as dbname FROM pg_database WHERE datistemplate = false and datname <> 'postgres'";
25 using var command = connection.CreateCommand();
26 command.CommandText = query;
27
28 await using var reader = await command.ExecuteReaderAsync();
29 while (await reader.ReadAsync())
30 {
31 var dbName = (string)reader["dbname"];
32 dbNames.Add(dbName);
33 }
34
35 return dbNames;
36 }
37
38 /// <summary>
39 /// Пересоздание схем (быстрее и нет проблем с пулами подключений)
40 /// </summary>
41 private static async Task CleanV2Async(
42 PostgreSqlContainer container,
43 IEnumerable<string> dbNames
44 )
45 {
46 var resetDbTasks = dbNames
47 .Select(e => ResetSchemesAsync(container, e))
48 .ToArray();
49
50 await Task.WhenAll(resetDbTasks);
51 }
52
53 #endregion
54
55 private static async Task ResetSchemesAsync(
56 PostgreSqlContainer container,
57 string dbName
58 )
59 {
60 var connectionString = /* connectionString to dbName */;
61 await using var connection = new NpgsqlConnection(connectionString);
62 await connection.OpenAsync();
63
64 List<string> schemas = new List<string>(10);
65 {
66 var query = @"select schema_name from information_schema.schemata where schema_name <> 'information_schema' and schema_name not like 'pg_%'";
67 using var command = connection.CreateCommand();
68 command.CommandText = query;
69
70 await using var reader = await command.ExecuteReaderAsync();
71 while (await reader.ReadAsync())
72 {
73 var schema = (string)reader["schema_name"];
74 schemas.Add(schema);
75 }
76 }
77 {
78 var query = $"DROP SCHEMA {string.Join(", ", schemas.Select(e => $@"""{e}"""))} CASCADE";
79 using var command = connection.CreateCommand();
80 command.CommandText = query;
81
82 await command.ExecuteNonQueryAsync();
83 }
84 {
85 var query = @"CREATE SCHEMA public; GRANT ALL ON SCHEMA public TO postgres; GRANT ALL ON SCHEMA public TO public;";
86 using var command = connection.CreateCommand();
87 command.CommandText = query;
88
89 await command.ExecuteNonQueryAsync();
90 }
91 }{{/code}}
92 |(% style="width:77px" %) |(% style="width:1377px" %)[[Testcontainers>>doc:Разработка.Тестирование.TestContainers.WebHome]], [[Containers pool>>doc:Разработка.Тестирование.TestContainers.Containers pool.WebHome]]
93 |(% style="width:77px" %) |(% style="width:1377px" %)(((
94 How do I list all databases and tables using psql?
95 [[https:~~/~~/dba.stackexchange.com/questions/1285/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]]
96
97 How do I list all schemas in PostgreSQL?
98 [[https:~~/~~/dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql>>https://dba.stackexchange.com/questions/40045/how-do-i-list-all-schemas-in-postgresql]]
99
100 How can I drop all the tables in a PostgreSQL database?
101 [[https:~~/~~/stackoverflow.com/questions/3327312/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]]
102 )))