github/databasus
1
1
Fork 0
mirror of https://github.com/databasus/databasus.git synced 2026-04-06 00:32:03 +02:00
Code Issues 3 Packages Projects Releases Wiki Activity

FEATURE: Cluster-based PostgreSQL backup management & bulk import (1.x line) #364

New Issue
Closed
opened 2026-04-05 16:16:17 +02:00 by MrUnknownDE · 0 comments
MrUnknownDE commented 2026-04-05 16:16:17 +02:00
Owner
Copy Link

Originally created by @omerkarabacak on 12/10/2025

PR description

Context / Versioning

  • Code line: This work targets the pre-2.x “1.x” codebase, not the current main.
  • Base versions:
    • The branch includes the 1.x history from v1.3.0 up to v1.33.0, plus this cluster feature.
    • The upstream main branch is currently at v2.0.3.
  • Implication: The diff in this PR is against the 1.x line that started at v1.33.0, not against v2.0.3. If you want this feature on main, it would need to be ported to the v2.x codebase.

What this PR adds (high level)

  • Cluster-based backup management

    • New backend feature clusters that lets you:
      • Register a PostgreSQL cluster (host/port/user/password/version/HTTPS).
      • Bind a cluster to a workspace.
      • Define cluster-level backup defaults (storage, schedule, retention, notifications, CPU).
      • Maintain a list of excluded databases for that cluster.
    • New UI “Clusters” tab to:
      • Create / edit clusters for a workspace.
      • See cluster connection details (sanitized).
      • Run on-demand cluster backups.
      • Exclude specific DBs from cluster management.

  • Workspace-aware, cluster-level scheduler

    • A cluster background service periodically checks cluster schedules and triggers backups when due.
    • For each cluster run, the service:
      • Discovers accessible databases on the cluster connection.
      • Creates missing Database records within the workspace (excluding templates/system DBs).
      • Ensures each database has a BackupConfig derived from cluster defaults.
      • Triggers backups in parallel (up to a small concurrency limit) when enabled.

  • Bulk import from cluster (frontend)

    • Wizard-style UI CreateDatabasesFromClusterComponent:
      • Connects to a cluster once, lists all accessible DBs (via existing list-databases-direct endpoint).
      • Lets the user:
        • Select multiple DBs (with helpers like “exclude system DBs”).
        • Configure a shared backup schedule and storage.
        • Configure notifications.
      • Creates all selected DBs and their backup configs, and optionally runs an initial backup.

  • Safer propagation of cluster defaults

    • New backend support to preview and apply cluster settings to existing DBs:
    • UI modal “Force-apply cluster settings”:
      • Shows which DBs would change, and how (storage / schedule / enabled).
      • Applies the chosen subset across all matching DBs in the cluster.

Backend changes

  • New clusters feature (backend)
    Files under backend/internal/features/clusters/…:

    • Models

    • Service

      • ClusterService:
        • Creates/updates clusters with permission checks via WorkspaceService.
        • Discovers databases on the cluster using existing PostgresqlDatabase logic.
        • For each discovered DB (non-template, not excluded):
          • Creates a Database if missing.
          • Ensures/updates BackupConfig to reflect cluster defaults.
          • Optionally triggers an immediate backup when enabled.
        • Provides preview/apply propagation APIs for cluster defaults.

    • Controller & routes

      • ClusterController.RegisterRoutes exposes:
        • POST /clusters – create cluster.
        • GET /clusters – list clusters by workspace_id.
        • PUT /clusters/{id} – update cluster.
        • POST /clusters/{id}/run-backup – run cluster backup once.
        • GET /clusters/{id}/databases – list accessible DBs in a cluster.
        • GET /clusters/{id}/propagation/preview – preview changes to existing DBs.
        • POST /clusters/{id}/propagation/apply – apply those changes.
      • Swagger annotations are added for all new endpoints.

    • Background scheduler

  • Backup configuration enhancements

  • Migrations introduced by this PR (cluster-related)

    New SQL migrations under backend/migrations:

    These are additive and don’t change semantics for existing deployments that don’t use clusters.

Frontend changes

  • New “Clusters” tab

    • MainScreenComponent:
      • Adds a “Clusters” navigation tab, visible when a workspace is selected.
      • Passes workspace and permission info to ClustersComponent.
    • ClustersComponent:
      • Lists clusters for the selected workspace.
      • “Add cluster” modal to configure:
        • PG connection (version, host, port, username, password, HTTPS).
        • Whether backups are enabled for this cluster.
        • Store period, backup interval (hourly/daily/weekly/monthly, time of day, weekday/day-of-month).
        • CPU count and notifications.
        • Optional storage.
      • Edit view to:
        • Update connection and backup defaults.
        • See and edit excluded DBs (using live cluster DB discovery).
        • Run an on-demand backup for the whole cluster.
        • Force-apply cluster defaults to existing DBs (with preview).

  • Bulk DB creation from cluster

    • CreateDatabasesFromClusterComponent:
      • Step 1: enter connection params (PG version, host, port, username, password, HTTPS) and load DBs via databaseApi.listDatabasesDirect.
      • Step 2: select which DBs to import (with helpers: select all, clear, invert, exclude system).
      • Step 3: configure backup settings via existing EditBackupConfigComponent (no extra API calls yet).
      • Step 4: configure notifiers via EditDatabaseNotifiersComponent.
      • Step 5: creates all selected DBs and their backup configs and optionally triggers an initial backup.

  • Cluster API and models

  • Docs

    • Root README.md:
      • Extended Features and Usage sections to document:
        • “Cluster-based setup”.
        • “Import multiple databases from a cluster”.

Changes vs v1.3.0 (high level)

Compared to the older v1.3.0 tag, this branch already includes upstream 1.x features such as:

  • Workspaces & access control

    • Workspace entities, membership, per-workspace roles and permissions.
    • Workspace-level settings and audit logs.

  • Extended storage and notifier support

    • NAS storage integration and related UI.
    • Additional notifier improvements (including MS Teams and enhanced email fields).
    • Metrics and monitoring-related DB changes.

  • User management & OAuth

    • Richer user model (statuses, roles).
    • OAuth sign-in for GitHub and Google.
    • Settings-driven policies for inviting/registering users.
    • User profile & settings UIs.

This PR builds on top of those 1.x additions and adds cluster-based backup management on top.

Migration & compatibility notes

  • Existing installations without clusters

    • New migrations run, but behaviour for existing per-DB backups is unchanged.
    • Existing BackupConfig records continue to be scheduled by the legacy scheduler; only cluster-managed configs are excluded from that path.

  • When using clusters

    • New clusters, postgresql_clusters, cluster_excluded_databases, and cluster-related backup_configs columns are required.
    • Databases managed by a cluster will have ManagedByCluster = true and ClusterID set; they are then driven by the cluster scheduler.
    • Exclusions prevent the cluster from creating or updating backup configs for specific DB names.

Testing

Things I’d recommend verifying/that this PR is designed to support:

  • Backend

    • Migrations apply cleanly on top of the 1.x schema (from v1.3.0 line).
    • Creating/updating clusters with and without backups enabled.
    • Cluster-run correctly discovers DBs, respects exclusions, and creates/updates Database + BackupConfig.
    • Background scheduler runs and updates LastRunAt as expected.

  • Frontend

    • Clusters tab:
      • Creating/editing clusters.
      • Running on-demand cluster backups.
      • Excluding/including DBs and seeing them reflected.
      • Preview/apply propagation behaves as shown in the UI.
      • “Create databases from cluster” wizard:
      • Successfully imports multiple DBs and sets their backup configs & notifiers.
*Originally created by @omerkarabacak on 12/10/2025* ### PR description #### Context / Versioning - **Code line:** This work targets the **pre-2.x “1.x” codebase**, not the current `main`. - **Base versions:** - The branch includes the 1.x history from **`v1.3.0` up to `v1.33.0`**, plus this cluster feature. - The upstream **`main` branch is currently at `v2.0.3`**. - **Implication:** The diff in this PR is **against the 1.x line that started at `v1.33.0`**, not against `v2.0.3`. If you want this feature on `main`, it would need to be ported to the `v2.x` codebase. --- ### What this PR adds (high level) - **Cluster-based backup management** - New backend feature `clusters` that lets you: - Register a PostgreSQL cluster (host/port/user/password/version/HTTPS). - Bind a cluster to a workspace. - Define **cluster-level backup defaults** (storage, schedule, retention, notifications, CPU). - Maintain a list of **excluded databases** for that cluster. - New UI **“Clusters”** tab to: - Create / edit clusters for a workspace. - See cluster connection details (sanitized). - Run on-demand cluster backups. - Exclude specific DBs from cluster management. - **Workspace-aware, cluster-level scheduler** - A **cluster background service** periodically checks cluster schedules and triggers backups when due. - For each cluster run, the service: - Discovers accessible databases on the cluster connection. - Creates missing `Database` records within the workspace (excluding templates/system DBs). - Ensures each database has a [BackupConfig](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:13:0-37:1) derived from cluster defaults. - Triggers backups in parallel (up to a small concurrency limit) when enabled. - **Bulk import from cluster (frontend)** - Wizard-style UI [CreateDatabasesFromClusterComponent](cci:1://file:///Users/omer/projects/personal/github/postgresus/frontend/src/features/databases/ui/CreateDatabasesFromClusterComponent.tsx:16:0-330:2): - Connects to a cluster once, lists all accessible DBs (via existing `list-databases-direct` endpoint). - Lets the user: - Select multiple DBs (with helpers like “exclude system DBs”). - Configure a shared backup schedule and storage. - Configure notifications. - Creates all selected DBs and their backup configs, and optionally runs an initial backup. - **Safer propagation of cluster defaults** - New backend support to **preview and apply** cluster settings to existing DBs: - [PreviewPropagation](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:586:0-675:1) / [ApplyPropagation](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:677:0-787:1) in [ClusterService](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:23:0-29:1). - Options to apply: - Storage, - Schedule, - “enable backups” flag, - while optionally respecting cluster exclusions. - UI modal “Force-apply cluster settings”: - Shows which DBs would change, and how (storage / schedule / enabled). - Applies the chosen subset across all matching DBs in the cluster. --- ### Backend changes - **New `clusters` feature (backend)** Files under `backend/internal/features/clusters/…`: - **Models** - [Cluster](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/models.go:13:0-35:1), [PostgresqlCluster](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/models.go:53:0-63:1), [ClusterExcludedDatabase](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/models.go:88:0-92:1). - [Cluster](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/models.go:13:0-35:1) binds to a `Workspace`, holds default backup settings and notifiers. - “Excluded databases” are stored and enforced when running backups. - **Service** - [ClusterService](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:23:0-29:1): - Creates/updates clusters with permission checks via [WorkspaceService](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/workspaces/services/workspace_service.go:19:0-26:1). - Discovers databases on the cluster using existing `PostgresqlDatabase` logic. - For each discovered DB (non-template, not excluded): - Creates a `Database` if missing. - Ensures/updates [BackupConfig](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:13:0-37:1) to reflect cluster defaults. - Optionally triggers an immediate backup when enabled. - Provides preview/apply propagation APIs for cluster defaults. - **Controller & routes** - [ClusterController.RegisterRoutes](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/controller.go:14:0-22:1) exposes: - `POST /clusters` – create cluster. - `GET /clusters` – list clusters by `workspace_id`. - `PUT /clusters/{id}` – update cluster. - `POST /clusters/{id}/run-backup` – run cluster backup once. - `GET /clusters/{id}/databases` – list accessible DBs in a cluster. - `GET /clusters/{id}/propagation/preview` – preview changes to existing DBs. - `POST /clusters/{id}/propagation/apply` – apply those changes. - Swagger annotations are added for all new endpoints. - **Background scheduler** - [ClusterBackgroundService](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/background_service.go:10:0-14:1): - Periodically loads all clusters. - Uses `BackupInterval.ShouldTriggerBackup` and `Cluster.LastRunAt` to decide when to run. - Calls [RunBackupScheduled](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:73:0-77:1) on due clusters and updates `LastRunAt`. - **Backup configuration enhancements** - [backend/internal/features/backups/config/model.go](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:0:0-0:0): - [BackupConfig](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:13:0-37:1) now has: - `ClusterID *uuid.UUID` - `ManagedByCluster bool` - Validation updated but remains strict about interval/period/CPU. - [backend/internal/features/backups/config/repository.go](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/repository.go:0:0-0:0): - [GetWithEnabledBackups()](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/repository.go:76:0-89:1) now **excludes cluster-managed configs**: - `WHERE is_backups_enabled = TRUE AND (managed_by_cluster = FALSE OR cluster_id IS NULL)` - This prevents double-scheduling the same DB from both per-DB and cluster schedulers. - [backend/internal/features/backups/config/service.go](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/service.go:0:0-0:0): - New helper [FindBackupConfigByDbIdNoInit](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/service.go:119:0-123:1) (used by cluster service). - Default config initialization preserved for non-cluster-managed DBs. - [ClusterService.ensureBackupConfig()](cci:1://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/clusters/service.go:489:0-584:1): - If a default, auto-initialized config exists, it is **upgraded** to cluster defaults: - Schedule (interval/weekday/day-of-month), - Storage, - Store period, - Notifications, - CPU & retry settings, - `ManagedByCluster = true`, `ClusterID = cluster.ID`. - If no config exists, creates a new one using cluster defaults, also marked as cluster-managed. - **Migrations introduced by this PR (cluster-related)** New SQL migrations under `backend/migrations`: - **[20251115220000_add_clusters.sql](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/migrations/20251115220000_add_clusters.sql:0:0-0:0)** - Creates `clusters` and `postgresql_clusters` tables. - **[20251116183000_add_cluster_excluded_databases.sql](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/migrations/20251116183000_add_cluster_excluded_databases.sql:0:0-0:0)** - Creates `cluster_excluded_databases` table. - **[20251117220000_add_clusters_last_run_at.sql](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/migrations/20251117220000_add_clusters_last_run_at.sql:0:0-0:0)** - Adds `last_run_at` tracking to clusters. - **[20251119100000_backup_configs_cluster_management.sql](cci:7://file:///Users/omer/projects/personal/github/postgresus/backend/migrations/20251119100000_backup_configs_cluster_management.sql:0:0-0:0)** - Adds `cluster_id` and `managed_by_cluster` columns to `backup_configs`. These are additive and don’t change semantics for existing deployments that don’t use clusters. --- ### Frontend changes - **New “Clusters” tab** - [MainScreenComponent](cci:1://file:///Users/omer/projects/personal/github/postgresus/frontend/src/widgets/main/MainScreenComponent.tsx:30:0-387:2): - Adds a **“Clusters”** navigation tab, visible when a workspace is selected. - Passes `workspace` and permission info to [ClustersComponent](cci:1://file:///Users/omer/projects/personal/github/postgresus/frontend/src/features/clusters/ui/ClustersComponent.tsx:19:0-841:2). - [ClustersComponent](cci:1://file:///Users/omer/projects/personal/github/postgresus/frontend/src/features/clusters/ui/ClustersComponent.tsx:19:0-841:2): - Lists clusters for the selected workspace. - “Add cluster” modal to configure: - PG connection (version, host, port, username, password, HTTPS). - Whether backups are enabled for this cluster. - Store period, backup interval (hourly/daily/weekly/monthly, time of day, weekday/day-of-month). - CPU count and notifications. - Optional storage. - Edit view to: - Update connection and backup defaults. - See and edit excluded DBs (using live cluster DB discovery). - Run an on-demand backup for the whole cluster. - Force-apply cluster defaults to existing DBs (with preview). - **Bulk DB creation from cluster** - [CreateDatabasesFromClusterComponent](cci:1://file:///Users/omer/projects/personal/github/postgresus/frontend/src/features/databases/ui/CreateDatabasesFromClusterComponent.tsx:16:0-330:2): - Step 1: enter connection params (PG version, host, port, username, password, HTTPS) and load DBs via `databaseApi.listDatabasesDirect`. - Step 2: select which DBs to import (with helpers: select all, clear, invert, exclude system). - Step 3: configure backup settings via existing `EditBackupConfigComponent` (no extra API calls yet). - Step 4: configure notifiers via `EditDatabaseNotifiersComponent`. - Step 5: creates all selected DBs and their backup configs and optionally triggers an initial backup. - **Cluster API and models** - New TS entities under `frontend/src/entity/clusters`: - [clusterApi.ts](cci:7://file:///Users/omer/projects/personal/github/postgresus/frontend/src/entity/clusters/api/clusterApi.ts:0:0-0:0) – HTTP client for cluster endpoints. - [Cluster.ts](cci:7://file:///Users/omer/projects/personal/github/postgresus/frontend/src/entity/clusters/model/Cluster.ts:0:0-0:0), [PostgresqlCluster.ts](cci:7://file:///Users/omer/projects/personal/github/postgresus/frontend/src/entity/clusters/model/PostgresqlCluster.ts:0:0-0:0) – client-side models. - **Docs** - Root [README.md](cci:7://file:///Users/omer/projects/personal/github/postgresus/README.md:0:0-0:0): - Extended **Features** and **Usage** sections to document: - “Cluster-based setup”. - “Import multiple databases from a cluster”. --- ### Changes vs `v1.3.0` (high level) Compared to the older `v1.3.0` tag, this branch already includes upstream 1.x features such as: - **Workspaces & access control** - Workspace entities, membership, per-workspace roles and permissions. - Workspace-level settings and audit logs. - **Extended storage and notifier support** - NAS storage integration and related UI. - Additional notifier improvements (including MS Teams and enhanced email fields). - Metrics and monitoring-related DB changes. - **User management & OAuth** - Richer user model (statuses, roles). - OAuth sign-in for GitHub and Google. - Settings-driven policies for inviting/registering users. - User profile & settings UIs. This PR **builds on top of those 1.x additions** and adds **cluster-based backup management** on top. --- ### Migration & compatibility notes - **Existing installations without clusters** - New migrations run, but behaviour for existing per-DB backups is unchanged. - Existing [BackupConfig](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:13:0-37:1) records continue to be scheduled by the legacy scheduler; only cluster-managed configs are excluded from that path. - **When using clusters** - New `clusters`, `postgresql_clusters`, `cluster_excluded_databases`, and cluster-related `backup_configs` columns are required. - Databases managed by a cluster will have `ManagedByCluster = true` and `ClusterID` set; they are then driven by the cluster scheduler. - Exclusions prevent the cluster from creating or updating backup configs for specific DB names. --- ### Testing Things I’d recommend verifying/that this PR is designed to support: - **Backend** - Migrations apply cleanly on top of the 1.x schema (from `v1.3.0` line). - Creating/updating clusters with and without backups enabled. - Cluster-run correctly discovers DBs, respects exclusions, and creates/updates `Database` + [BackupConfig](cci:2://file:///Users/omer/projects/personal/github/postgresus/backend/internal/features/backups/config/model.go:13:0-37:1). - Background scheduler runs and updates `LastRunAt` as expected. - **Frontend** - Clusters tab: - Creating/editing clusters. - Running on-demand cluster backups. - Excluding/including DBs and seeing them reflected. - Preview/apply propagation behaves as shown in the UI. - “Create databases from cluster” wizard: - Successfully imports multiple DBs and sets their backup configs & notifiers.
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
bug
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
MrUnknownDE
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github/databasus#364
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?