Files
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

103 lines
3.6 KiB
C#

using Npgsql;
namespace Backend.Tests;
/// <summary>
/// A throwaway database on the PostgreSQL server the PG* variables name (PGHOST, PGPORT, PGUSER, PGPASSWORD), created for a
/// test and dropped after it. <see cref="TryCreate"/> is null when no server is configured; the tests then skip.
/// </summary>
public sealed class PostgresTestDatabase : IDisposable
{
private readonly string admin;
private PostgresTestDatabase(string admin, string name)
{
this.admin = admin;
Name = name;
ConnectionString = new NpgsqlConnectionStringBuilder(admin) { Database = name, Pooling = false }.ConnectionString;
Execute(admin, $"CREATE DATABASE \"{name}\"");
}
public const string SkipReason = "No PostgreSQL to test against (PGHOST, PGUSER, PGPASSWORD).";
public string Name { get; }
/// <summary>The database's connection string, password included.</summary>
public string ConnectionString { get; }
/// <summary>The server's connection, for the server's own database.</summary>
public static string? AdminConnectionString()
{
var host = Environment.GetEnvironmentVariable("PGHOST");
var user = Environment.GetEnvironmentVariable("PGUSER");
var password = Environment.GetEnvironmentVariable("PGPASSWORD");
if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(user) || password is null)
{
return null;
}
return new NpgsqlConnectionStringBuilder
{
Host = host,
Port = int.TryParse(Environment.GetEnvironmentVariable("PGPORT"), out var port) ? port : 5432,
Username = user,
Password = password,
Database = "postgres",
Pooling = false,
}.ConnectionString;
}
public static PostgresTestDatabase? TryCreate(string prefix)
=> AdminConnectionString() is { } admin ? new PostgresTestDatabase(admin, $"{prefix}_{Guid.NewGuid():N}"[..40]) : null;
/// <summary>Another throwaway database on the same server (a restore target).</summary>
public PostgresTestDatabase Sibling(string prefix) => new(admin, $"{prefix}_{Guid.NewGuid():N}"[..40]);
public long Scalar(string sql)
{
using var connection = new NpgsqlConnection(ConnectionString);
connection.Open();
using var command = new NpgsqlCommand(sql, connection);
return Convert.ToInt64(command.ExecuteScalar(), System.Globalization.CultureInfo.InvariantCulture);
}
private static void Execute(string connectionString, string sql)
{
using var connection = new NpgsqlConnection(connectionString);
connection.Open();
using var command = new NpgsqlCommand(sql, connection);
command.ExecuteNonQuery();
}
public void Dispose()
{
NpgsqlConnection.ClearAllPools();
Execute(admin, $"DROP DATABASE IF EXISTS \"{Name}\" WITH (FORCE)");
}
}
/// <summary>The backend on a throwaway Postgres database (SQLite when no server is configured: the tests skip then).</summary>
public sealed class PostgresBackendFixture : BackendTestFixture
{
public PostgresBackendFixture()
{
Database = PostgresTestDatabase.TryCreate("executer_backend_tests");
if (Database is not null)
{
Settings["DATABASE_PROVIDER"] = "postgres";
Settings["DATABASE_CONNECTION_STRING"] = Database.ConnectionString;
}
}
public PostgresTestDatabase? Database { get; }
protected override void Dispose(bool disposing)
{
base.Dispose(disposing);
if (disposing)
{
Database?.Dispose();
}
}
}