Regira.DAL.PostgreSQL 6.6.1

Regira DAL — PostgreSQL

Regira DAL.PostgreSQL provides PostgreSQL backup/restore via pg_dump/pg_restore (settings, options, and backup/restore services — no CRUD or communicator surface).

Projects

Installation

<PackageReference Include="Regira.DAL.PostgreSQL" Version="6.*" />

PgSettings

var settings = new PgSettings("localhost", "mydb", "postgres", "pass");

PgBackupService / PgRestoreService

Requires pg_dump / pg_restore executables. Supports schema-specific backups.

var settings = new PgSettings("localhost", "mydb", "postgres", "pass");
IProcessHelper processHelper = new ProcessHelper();   // Regira.System

var options = new PgOptions
{
    DbSettings     = settings,
    ToolsDirectory = "/usr/lib/postgresql/16/bin",
    BackupSchemas  = ["public", "reports"],
    Overwrite      = true
};

IMemoryFile backup = await new PgBackupService(options, processHelper).Backup();
await new PgRestoreService(options, processHelper).Restore(backup);

Both services start pg_dump / pg_restore directly, without a shell in between, so they run on Windows, Linux and macOS alike, and a database, user or schema name reaches the tool exactly as written. An IProcessHelper of your own sees ExecuteFile rather than ExecuteCommand.

The password reaches the tools through the process environment (PGPASSWORD), never through the arguments — ProcessHelper sets it on the process itself. A custom IProcessHelper has to override the environment overload of ExecuteFile to pass it on; the default implementation throws NotSupportedException rather than start the tool without it.

PgOptions

Restore creates the target database itself, connecting to MaintenanceDatabase to do so — CREATE DATABASE cannot run from a connection to the database it creates. When the target already exists, Overwrite drops and recreates it (so anything the backup does not contain is lost) and without Overwrite the restore fails. The maintenance connection never joins an ambient TransactionScope, inside which PostgreSQL refuses to create or drop a database.

Overwrite onto an existing database needs PostgreSQL 13 or later and a role that may create databases (CREATEDB) and end the sessions on the target (superuser, a member of their role, or pg_signal_backend); otherwise the restore fails before anything is dropped. It ends those sessions (DROP DATABASE … WITH (FORCE)) and clears the Npgsql pools of connections this process opened from a connection string, so an app can restore its own database while it runs:

• a query running on the target at that moment fails;
• a pool the clear does not reach, an NpgsqlDataSource of the app's own (AddNpgsqlDataSource, or EF Core's UseNpgsql with MapEnum or a plugin) or another process, fails its next query once, unless you call its Clear() after the restore;
• until pg_restore finishes, the app sees the database missing, then partly restored, so keep its writes out of that window; a failure after the drop leaves the database missing or partly restored.

The three database operations are also available on their own, against a connection to any other database on the same server. Drop ends no session: PostgreSQL refuses it while other sessions are connected.

bool exists = await pgRestore.Exists(connection, "staging-db");
await pgRestore.Drop(connection, "staging-db");
await pgRestore.Create(connection, "staging-db");

BackupRestoreManager

Standalone manager — useful when you want both backup and restore from the same object.

var settings = new PgSettings("localhost", "mydb", "postgres", "pass");
var options  = new PgOptions { DbSettings = settings, ToolsDirectory = "/usr/lib/postgresql/16/bin" };
IProcessHelper processHelper = new ProcessHelper();   // Regira.System

var mgr = new BackupRestoreManager(processHelper, options);
mgr.Backup(settings, "source-db", "/backups/snapshot.dump");   // synchronous (void)
// overwrite: ends target-db's sessions, drops it and recreates it from the backup
await mgr.Restore(settings, "target-db", "/backups/snapshot.dump", overwrite: true);

Backup/Restore contracts

Both services implement the shared contracts from Common:

public interface IDbBackupService  { Task<IMemoryFile> Backup(); }
public interface IDbRestoreService { Task Restore(IMemoryFile file); }

Overview

1. Index — Settings, backup/restore, and BackupRestoreManager
2. Examples — Schema-specific backup, copying a database, managing it yourself

License

Apache License 2.0 — this package contains no license validation and no runtime limits. See LICENSE. A few companion packages are commercially licensed with a free tier; see the licensing overview.