Files
ExecuteR/tests/Backend.Tests/PostgresDatabaseTests.cs
Alphaeus Mote 74399e7fc6 feat(database): Postgres backups with pg_dump and a migrate-database verb [skip ci]
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>
2026-11-07 05:07:40 -05:00

222 lines
11 KiB
C#
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
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"]);
}
}