using System.Net; using System.Net.Http.Json; using Backend.Maintenance; using Microsoft.EntityFrameworkCore; using Microsoft.Extensions.Configuration; using Microsoft.Extensions.DependencyInjection; using Shared.Contracts.Api; using Shared.Contracts.Serialization; using Xunit; namespace Backend.Tests; /// /// The server on a real Postgres (PG* variables; skipped without): migrations and seeding, Settings › Database insight and /// VACUUM (ANALYZE), and pg_dump backups (when pg_dump is on PATH): a custom-format archive that pg_restore reads back into /// another database, both destinations, and retention that leaves another provider's backups alone. /// public sealed class PostgresDatabaseTests(PostgresBackendFixture fixture) : IClassFixture { private sealed record BackupList(List Backups, string? ObjectStoreError); private static CancellationToken Ct => TestContext.Current.CancellationToken; private DatabaseTaskRunner Runner => fixture.Services.GetRequiredService(); private string BackupFolder => fixture.Services.GetRequiredService().Folder; private PostgresTestDatabase Database { get { if (fixture.Database is null) { Assert.Skip(PostgresTestDatabase.SkipReason); } return fixture.Database!; } } private PostgresTools Tools => fixture.Services.GetRequiredService(); private static async Task ReadAsync(HttpResponseMessage response) { Assert.True(response.IsSuccessStatusCode, $"{(int)response.StatusCode}: {await response.Content.ReadAsStringAsync()}"); return (await response.Content.ReadFromJsonAsync(JsonDefaults.Options, Ct))!; } private static async Task SaveAsync(HttpClient client, Func change) { var current = await client.GetAsync(ApiRoutes.DatabaseSettings, Ct); var settings = await ReadAsync(current); using var put = new HttpRequestMessage(HttpMethod.Put, ApiRoutes.DatabaseSettings) { Content = JsonContent.Create(change(settings), options: JsonDefaults.Options) }; put.Headers.TryAddWithoutValidation("If-Match", current.Headers.ETag!.Tag); return await client.SendAsync(put, Ct); } private async Task RunAsync(HttpClient client, string route) { var started = await client.PostAsync(route, null, Ct); Assert.True(started.StatusCode == HttpStatusCode.Accepted, await started.Content.ReadAsStringAsync(Ct)); await Runner.Current; return await ReadAsync(await client.GetAsync(ApiRoutes.DatabaseStatus, Ct)); } private async Task ResetAsync() { await Runner.Current; await fixture.WithDbAsync(db => db.AppSettings.Where(s => s.Key.StartsWith(DatabaseSettingsStore.Key)).ExecuteDeleteAsync(Ct)); fixture.Services.GetRequiredService().Invalidate(); if (Directory.Exists(BackupFolder)) { Directory.Delete(BackupFolder, recursive: true); } fixture.BackupStore.Objects.Clear(); fixture.BackupStore.Unavailable = "S3 is not configured."; fixture.BackupStore.FailUploads = null; } [Fact] public async Task TheServerRunsOnPostgresWithInsightAndMaintenance() { _ = Database; await ResetAsync(); using var admin = await fixture.SignedInAsync("pg-insight-admin"); var status = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseStatus, Ct)); Assert.Equal("postgres", status.Provider); Assert.Equal(Tools.Unavailable, status.BackupUnavailable); Assert.Equal("S3 is not configured.", status.ObjectStoreUnavailable); var insight = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct)); Assert.Equal("postgres", insight.Provider); Assert.False(string.IsNullOrWhiteSpace(insight.Version)); Assert.True(insight.SizeBytes > 0); Assert.True(insight.RowsApproximate); Assert.Contains(insight.Tables, t => t.Name == "Users"); Assert.Contains(insight.Tables, t => t.Name == "__EFMigrationsHistory"); status = await RunAsync(admin, ApiRoutes.DatabaseMaintenance); Assert.Equal(DatabaseOutcomes.Succeeded, status.LastMaintenance?.Outcome); Assert.Contains("VACUUM", status.MaintenanceSummary); // VACUUM (ANALYZE) refreshed the statistics the estimates come from. insight = await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseInsight, Ct)); Assert.True(insight.Tables.Single(t => t.Name == "Users").Rows > 0); Assert.NotNull(insight.DeadRows); } [Fact] public async Task ABackupIsACustomFormatDumpThatRestoresIntoAnotherDatabase() { var database = Database; if (Tools.PgDump is null) { Assert.Skip("pg_dump is not on PATH."); } await ResetAsync(); using var admin = await fixture.SignedInAsync("pg-backup-admin"); fixture.BackupStore.Unavailable = null; Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupToLocal = true, BackupToObjectStore = true })).StatusCode); var status = await RunAsync(admin, ApiRoutes.DatabaseBackups); Assert.True(status.LastBackup?.Outcome == DatabaseOutcomes.Succeeded, status.LastBackup?.Detail); Assert.Contains("to the server folder and the S3 store", status.LastBackup!.Detail); var file = Assert.Single(Directory.GetFiles(BackupFolder)); Assert.Matches(@"executer-postgres-\d{8}-\d{6}-\d{3}\.dump$", file); Assert.Equal("PGDMP"u8.ToArray(), File.ReadAllBytes(file)[..5]); var (key, stored) = Assert.Single(fixture.BackupStore.Objects); Assert.Equal(DatabaseBackups.ObjectPrefix + Path.GetFileName(file), key); Assert.Equal(await File.ReadAllBytesAsync(file, Ct), stored.Content); var listed = (await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups; Assert.Equal(2, listed.Count); Assert.All(listed, b => Assert.Equal(Path.GetFileName(file), b.Name)); await admin.StepUpAsync(); var local = listed.Single(b => b.Location == DatabaseBackupLocations.Local); var downloaded = await admin.PostAsync(ApiRoutes.DatabaseBackupDownload .Replace("{location}", local.Location, StringComparison.Ordinal).Replace("{name}", local.Name, StringComparison.Ordinal), null, Ct); Assert.Equal(HttpStatusCode.OK, downloaded.StatusCode); Assert.Equal("application/octet-stream", downloaded.Content.Headers.ContentType?.MediaType); // pg_restore puts it into an empty database: the same rows come back. using var restored = database.Sibling("executer_restore_tests"); await PostgresTools.RunAsync(Tools.PgRestore!, ["--no-owner", "--exit-on-error", $"--dbname={restored.Name}", file], PostgresTools.ConnectionEnvironment(restored.ConnectionString), null, Ct); var users = await fixture.WithDbAsync(db => db.Users.LongCountAsync(Ct)); Assert.Equal(users, restored.Scalar("SELECT count(*) FROM \"Users\"")); Assert.Equal(database.Scalar("SELECT count(*) FROM \"__EFMigrationsHistory\""), restored.Scalar("SELECT count(*) FROM \"__EFMigrationsHistory\"")); } [Fact] public async Task RetentionKeepsThisProvidersNewestAndLeavesOtherBackupsAlone() { _ = Database; if (Tools.PgDump is null) { Assert.Skip("pg_dump is not on PATH."); } await ResetAsync(); using var admin = await fixture.SignedInAsync("pg-retention-admin"); Assert.Equal(HttpStatusCode.OK, (await SaveAsync(admin, s => s with { BackupKeep = 2, BackupToLocal = true })).StatusCode); // A SQLite backup from before the move to Postgres. Directory.CreateDirectory(BackupFolder); var before = Path.Combine(BackupFolder, "executer-sqlite-20260101-000000-000.db.gz"); await File.WriteAllBytesAsync(before, [0x1f, 0x8b], Ct); for (var i = 0; i < 3; i++) { Assert.Equal(DatabaseOutcomes.Succeeded, (await RunAsync(admin, ApiRoutes.DatabaseBackups)).LastBackup?.Outcome); await Task.Delay(5, Ct); } Assert.Equal(2, Directory.GetFiles(BackupFolder, "executer-postgres-*.dump").Length); Assert.True(File.Exists(before)); var listed = (await ReadAsync(await admin.GetAsync(ApiRoutes.DatabaseBackups, Ct))).Backups; Assert.Equal(3, listed.Count); Assert.Equal("executer-sqlite-20260101-000000-000.db.gz", listed[^1].Name); } [Fact] public async Task BackupsNeedADestinationAndAreRefusedWithoutPgDump() { _ = Database; await ResetAsync(); using var admin = await fixture.SignedInAsync("pg-refusal-admin"); Assert.Equal(HttpStatusCode.BadRequest, (await SaveAsync(admin, s => s with { BackupEnabled = true, BackupToLocal = false, BackupToObjectStore = false })).StatusCode); // Without pg_dump the server says so, as it did before the client tools were bundled. var missing = new PostgresTools(new ConfigurationBuilder() .AddInMemoryCollection(new Dictionary { ["PG_DUMP_PATH"] = Path.Combine(Path.GetTempPath(), "no-such-pg_dump") }) .Build()); Assert.Null(missing.PgDump); Assert.Contains("PG_DUMP_PATH", DatabaseBackups.Unavailable(Backend.Data.DatabaseProvider.Postgres, missing)); Assert.Null(DatabaseBackups.Unavailable(Backend.Data.DatabaseProvider.Sqlite, missing)); Assert.Contains("PG_DUMP_PATH", DatabaseSettingsStore.Validate( DatabaseSettingsStore.Defaults with { BackupEnabled = true }, Backend.Data.DatabaseProvider.Postgres, missing.Unavailable)["backupEnabled"][0]); } [Fact] public void TheToolsGetTheServersConnectionAndNothingElse() { var environment = PostgresTools.ConnectionEnvironment("Host=db;Port=6543;Database=executer;Username=executer;Password=p;SSL Mode=Require;Timeout=7"); Assert.Equal("db", environment["PGHOST"]); Assert.Equal("6543", environment["PGPORT"]); Assert.Equal("executer", environment["PGDATABASE"]); Assert.Equal("executer", environment["PGUSER"]); Assert.Equal("p", environment["PGPASSWORD"]); Assert.Equal("require", environment["PGSSLMODE"]); Assert.Equal("7", environment["PGCONNECT_TIMEOUT"]); Assert.Equal("prefer", PostgresTools.ConnectionEnvironment("Host=postgres;Username=u")["PGSSLMODE"]); Assert.Equal("u", PostgresTools.ConnectionEnvironment("Host=postgres;Username=u")["PGDATABASE"]); } }