74399e7fc6
Settings › Database backs up Postgres: a pg_dump custom-format archive (executer-postgres-<stamp>.dump), checked with pg_restore --list, to the server folder and/or the S3 store with the same retention as SQLite (pruning only this provider's backups). pg_dump comes from PG_DUMP_PATH or PATH; without it the console says so, as before. The backend image installs postgresql-client-18 from apt.postgresql.org and sets PG_DUMP_PATH. migrate-database copies a SQLite database into the configured Postgres one through EF (both at the same migration), in foreign-key order, in one transaction, keeping keys, identity values (sequences moved past them), concurrency stamps and values equal to a column default, and checks every table's row count on both sides before committing. --overwrite empties a target a server already started on; a non-empty -wal is refused without --force. Tests run the server on a throwaway Postgres database (insight, VACUUM (ANALYZE), backups restored with pg_restore, retention) and the copy end to end; both skip without PG* variables. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
222 lines
11 KiB
C#
222 lines
11 KiB
C#
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;
|
||
|
||
/// <summary>
|
||
/// 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.
|
||
/// </summary>
|
||
public sealed class PostgresDatabaseTests(PostgresBackendFixture fixture) : IClassFixture<PostgresBackendFixture>
|
||
{
|
||
private sealed record BackupList(List<DatabaseBackupDto> Backups, string? ObjectStoreError);
|
||
|
||
private static CancellationToken Ct => TestContext.Current.CancellationToken;
|
||
|
||
private DatabaseTaskRunner Runner => fixture.Services.GetRequiredService<DatabaseTaskRunner>();
|
||
|
||
private string BackupFolder => fixture.Services.GetRequiredService<BackupPaths>().Folder;
|
||
|
||
private PostgresTestDatabase Database
|
||
{
|
||
get
|
||
{
|
||
if (fixture.Database is null)
|
||
{
|
||
Assert.Skip(PostgresTestDatabase.SkipReason);
|
||
}
|
||
|
||
return fixture.Database!;
|
||
}
|
||
}
|
||
|
||
private PostgresTools Tools => fixture.Services.GetRequiredService<PostgresTools>();
|
||
|
||
private static async Task<T> ReadAsync<T>(HttpResponseMessage response)
|
||
{
|
||
Assert.True(response.IsSuccessStatusCode, $"{(int)response.StatusCode}: {await response.Content.ReadAsStringAsync()}");
|
||
return (await response.Content.ReadFromJsonAsync<T>(JsonDefaults.Options, Ct))!;
|
||
}
|
||
|
||
private static async Task<HttpResponseMessage> SaveAsync(HttpClient client, Func<DatabaseSettingsDto, DatabaseSettingsDto> change)
|
||
{
|
||
var current = await client.GetAsync(ApiRoutes.DatabaseSettings, Ct);
|
||
var settings = await ReadAsync<DatabaseSettingsDto>(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<DatabaseStatusDto> 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<DatabaseStatusDto>(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<DatabaseSettingsStore>().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<DatabaseStatusDto>(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<DatabaseInsightDto>(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<DatabaseInsightDto>(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<BackupList>(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<BackupList>(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<string, string?> { ["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"]);
|
||
}
|
||
}
|