b242509675
docker-compose.yml gains a postgres service (EXECUTER-DB-00001, official
image pinned by major and digest like NATS, INTERNAL network only, no host
port, pg_isready health check, shm_size, runs as PUID:PGID, data in
${STACK_BINDMOUNTROOT}/${STACK_NAME}/Postgres/Data). It is in the "postgres"
Compose profile; COMPOSE_PROFILES in .env turns it on, and the backend waits
for it healthy (depends_on required: false, so SQLite and external databases
start without it). The backend defaults to DATABASE_PROVIDER=postgres with
DATABASE_CONNECTION_STRING and DATABASE_PASSWORD from .env.
docker-compose.postgres-port.yml publishes a host port, opt-in through
POSTGRES_BIND.
deploy.sh decides the database: what .env names when the operator set it;
else SQLite when API/Data/app.db exists (written to .env, with a note on how
to move); else the bundled Postgres, its password generated into .env (and
exported), never regenerated over an existing database. COMPOSE_PROFILES
follows. The decisions are functions that scripts/tests/Test-DeployDatabase.sh
runs on fixtures without Docker. Compose 2.20.2+ is checked; a new .env is
mode 600. New-LocalSecrets.ps1 generates the password for a new .env and
keeps an existing .env's database settings on -Force.
README: deploy, secrets, Compose, database providers, Postgres backups and
pg_restore, and Moving from SQLite to Postgres (migrate-database).
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
182 lines
8.9 KiB
C#
182 lines
8.9 KiB
C#
using Backend.Data;
|
|
using Microsoft.Data.Sqlite;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using Microsoft.Extensions.Configuration;
|
|
using Xunit;
|
|
|
|
namespace Backend.Tests;
|
|
|
|
/// <summary>
|
|
/// migrate-database: a SQLite server's database (a copy of a running test server's, with rows the copy must keep exactly)
|
|
/// into a throwaway Postgres database, every table counted on both sides; refusals for a non-empty target, a SQLite file
|
|
/// still in use and a server not configured for Postgres. Skipped without PG* variables.
|
|
/// </summary>
|
|
public sealed class DatabaseMigrationTests(BackendTestFixture fixture) : IClassFixture<BackendTestFixture>
|
|
{
|
|
private static CancellationToken Ct => TestContext.Current.CancellationToken;
|
|
|
|
private static IConfiguration Configuration(string sqlite, string? postgres) => new ConfigurationBuilder()
|
|
.AddInMemoryCollection(new Dictionary<string, string?>
|
|
{
|
|
["DATABASE_PATH"] = sqlite,
|
|
["DATABASE_PROVIDER"] = postgres is null ? "sqlite" : "postgres",
|
|
["DATABASE_CONNECTION_STRING"] = postgres,
|
|
})
|
|
.Build();
|
|
|
|
/// <summary>
|
|
/// The test server's database as a stopped server leaves it: in WAL mode (the server sets it), its -wal and -shm
|
|
/// gone with the last connection, which is the case the copy opens read-only.
|
|
/// </summary>
|
|
private string Snapshot()
|
|
{
|
|
var path = Path.Combine(Path.GetTempPath(), $"migrate-source-{Guid.NewGuid():N}.db");
|
|
using (var from = new SqliteConnection($"Data Source={fixture.DatabasePath}"))
|
|
using (var to = new SqliteConnection(new SqliteConnectionStringBuilder { DataSource = path, Pooling = false }.ToString()))
|
|
{
|
|
from.Open();
|
|
to.Open();
|
|
from.BackupDatabase(to);
|
|
using var wal = to.CreateCommand();
|
|
wal.CommandText = "PRAGMA journal_mode = WAL";
|
|
Assert.Equal("wal", wal.ExecuteScalar());
|
|
}
|
|
|
|
Assert.False(File.Exists(path + "-wal"));
|
|
return path;
|
|
}
|
|
|
|
private static async Task<(int Code, string Output, string Error)> RunAsync(IConfiguration configuration, params string[] args)
|
|
{
|
|
var output = new StringWriter();
|
|
var error = new StringWriter();
|
|
var code = await DatabaseMigrationCli.RunAsync(args, configuration, output, error);
|
|
return (code, output.ToString(), error.ToString());
|
|
}
|
|
|
|
[Fact]
|
|
public async Task EveryRowIsCopiedAsItWasAndCountedOnBothSides()
|
|
{
|
|
using var target = PostgresTestDatabase.TryCreate("executer_migrate_tests");
|
|
if (target is null)
|
|
{
|
|
Assert.Skip(PostgresTestDatabase.SkipReason);
|
|
}
|
|
|
|
using var _ = await fixture.SignedInAsync("migrate-admin");
|
|
|
|
// Rows whose values a careless copy would change: identity keys, a value equal to the column's
|
|
// default sentinel (StaggerBatchSize 0, which an insert leaves to the column default of 1, here too: so it is set after), a stamp, timestamps.
|
|
var scriptId = Guid.NewGuid();
|
|
var scheduleId = Guid.NewGuid();
|
|
var stamp = Guid.NewGuid();
|
|
var created = new DateTimeOffset(2026, 3, 4, 5, 6, 7, 891, TimeSpan.Zero);
|
|
long[] memberIds = [];
|
|
await fixture.WithDbAsync(async db =>
|
|
{
|
|
db.ScriptTriggers.Add(new ScriptTriggerEntity
|
|
{
|
|
Id = scheduleId, ScriptId = scriptId, Name = "migrated", StaggerSeconds = 30, Enabled = false,
|
|
CreatedUtc = created, CreatedBy = "migrate-test",
|
|
});
|
|
var group = Guid.NewGuid();
|
|
for (var i = 0; i < 2500; i++)
|
|
{
|
|
db.DeviceGroupMembers.Add(new DeviceGroupMemberEntity { GroupId = group, ClientId = $"device-{i:0000}", AddedUtc = created.AddSeconds(i) });
|
|
}
|
|
|
|
await db.SaveChangesAsync(Ct);
|
|
// Ids with a gap, as deletes leave them.
|
|
await db.DeviceGroupMembers.Where(m => m.GroupId == group && m.ClientId.StartsWith("device-00")).ExecuteDeleteAsync(Ct);
|
|
await db.ScriptTriggers.Where(t => t.Id == scheduleId).ExecuteUpdateAsync(s => s.SetProperty(t => t.ConcurrencyStamp, stamp).SetProperty(t => t.StaggerBatchSize, 0), Ct);
|
|
memberIds = await db.DeviceGroupMembers.Where(m => m.GroupId == group).OrderBy(m => m.Id).Select(m => m.Id).ToArrayAsync(Ct);
|
|
});
|
|
|
|
var source = Snapshot();
|
|
try
|
|
{
|
|
var (code, output, error) = await RunAsync(Configuration(source, target.ConnectionString));
|
|
Assert.True(code == 0, output + error);
|
|
Assert.Contains("Verifying", output);
|
|
|
|
await using var sqlite = new SqliteAppDbContext(new DbContextOptionsBuilder<SqliteAppDbContext>()
|
|
.UseSqlite(new SqliteConnectionStringBuilder { DataSource = source, Pooling = false }.ToString()).Options);
|
|
await using var postgres = new PostgresAppDbContext(new DbContextOptionsBuilder<PostgresAppDbContext>().UseNpgsql(target.ConnectionString).Options);
|
|
|
|
// Every table, the same count on both sides, and the summary says so.
|
|
foreach (var type in DatabaseCopier.InDependencyOrder(postgres.Model))
|
|
{
|
|
var table = type.GetTableName()!;
|
|
Assert.Contains(table, output);
|
|
}
|
|
|
|
Assert.Equal(await sqlite.Users.CountAsync(Ct), await postgres.Users.CountAsync(Ct));
|
|
Assert.Equal(await sqlite.AuditEvents.CountAsync(Ct), await postgres.AuditEvents.CountAsync(Ct));
|
|
Assert.Equal(await sqlite.Roles.CountAsync(Ct), await postgres.Roles.CountAsync(Ct));
|
|
Assert.Equal(await sqlite.AppSettings.CountAsync(Ct), await postgres.AppSettings.CountAsync(Ct));
|
|
Assert.Equal(await sqlite.Sessions.CountAsync(Ct), await postgres.Sessions.CountAsync(Ct));
|
|
|
|
var schedule = await postgres.ScriptTriggers.SingleAsync(t => t.Id == scheduleId, Ct);
|
|
Assert.Equal(0, schedule.StaggerBatchSize);
|
|
Assert.Equal(30, schedule.StaggerSeconds);
|
|
Assert.False(schedule.Enabled);
|
|
Assert.Equal(stamp, schedule.ConcurrencyStamp);
|
|
Assert.Equal(created, schedule.CreatedUtc);
|
|
|
|
Assert.Equal(memberIds, await postgres.DeviceGroupMembers.Where(m => memberIds.Contains(m.Id)).OrderBy(m => m.Id).Select(m => m.Id).ToArrayAsync(Ct));
|
|
var users = await sqlite.Users.AsNoTracking().OrderBy(u => u.Id).ToListAsync(Ct);
|
|
var copied = await postgres.Users.AsNoTracking().OrderBy(u => u.Id).ToListAsync(Ct);
|
|
Assert.Equal(users.Select(u => (u.Id, u.UserName, u.Kind)), copied.Select(u => (u.Id, u.UserName, u.Kind)));
|
|
|
|
// The identity sequence continues after the copied ids.
|
|
postgres.DeviceGroupMembers.Add(new DeviceGroupMemberEntity { GroupId = Guid.NewGuid(), ClientId = "after-the-move", AddedUtc = DateTimeOffset.UtcNow });
|
|
await postgres.SaveChangesAsync(Ct);
|
|
Assert.True(await postgres.DeviceGroupMembers.Where(m => m.ClientId == "after-the-move").Select(m => m.Id).SingleAsync(Ct) > memberIds.Max());
|
|
|
|
// A second run finds rows there; --overwrite replaces them.
|
|
var again = await RunAsync(Configuration(source, target.ConnectionString));
|
|
Assert.Equal(2, again.Code);
|
|
Assert.Contains("--overwrite", again.Error);
|
|
var overwrite = await RunAsync(Configuration(source, target.ConnectionString), "--overwrite");
|
|
Assert.True(overwrite.Code == 0, overwrite.Output + overwrite.Error);
|
|
Assert.False(await postgres.DeviceGroupMembers.AnyAsync(m => m.ClientId == "after-the-move", Ct));
|
|
Assert.Equal(await sqlite.DeviceGroupMembers.CountAsync(Ct), await postgres.DeviceGroupMembers.CountAsync(Ct));
|
|
}
|
|
finally
|
|
{
|
|
SqliteConnection.ClearAllPools();
|
|
File.Delete(source);
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task ItRefusesWhatItShouldNotCopy()
|
|
{
|
|
var source = Snapshot();
|
|
try
|
|
{
|
|
// The server is not configured for Postgres.
|
|
var notPostgres = await RunAsync(Configuration(source, null));
|
|
Assert.Equal(2, notPostgres.Code);
|
|
Assert.Contains("DATABASE_PROVIDER=postgres", notPostgres.Error);
|
|
|
|
// No such SQLite file.
|
|
var missing = await RunAsync(Configuration(source, "Host=127.0.0.1;Database=x;Username=x"), "--from", source + ".missing");
|
|
Assert.Equal(2, missing.Code);
|
|
Assert.Contains("No SQLite database", missing.Error);
|
|
|
|
// A -wal with content: a server may be writing it.
|
|
await File.WriteAllBytesAsync(source + "-wal", [1, 2, 3], Ct);
|
|
var inUse = await RunAsync(Configuration(source, "Host=127.0.0.1;Database=x;Username=x"));
|
|
Assert.Equal(2, inUse.Code);
|
|
Assert.Contains("--force", inUse.Error);
|
|
}
|
|
finally
|
|
{
|
|
SqliteConnection.ClearAllPools();
|
|
File.Delete(source);
|
|
File.Delete(source + "-wal");
|
|
}
|
|
}
|
|
}
|