PostgreSQL backup tool with Point-In-Time-Recovery and restore verification
TypeScript backupbackupsdatabasedatabase-backupdevopsdockergolangkubernetesmariadbmongodbmysqlpgpostgrespostgresqls3self-hostedsystem-administrationtoolsweb-ui
package databases
import (
"context"
"errors"
"log/slog"
"slices"
"time"
"github.com/google/uuid"
"databasus-backend/internal/features/databases/databases/mariadb"
"databasus-backend/internal/features/databases/databases/mongodb"
"databasus-backend/internal/features/databases/databases/mysql"
postgresql_logical "databasus-backend/internal/features/databases/databases/postgresql/logical"
postgresql_physical "databasus-backend/internal/features/databases/databases/postgresql/physical"
"databasus-backend/internal/features/notifiers"
"databasus-backend/internal/features/sshtunnel"
"databasus-backend/internal/util/encryption"
)
type Database struct {
ID uuid.UUID `json:"id" gorm:"column:id;primaryKey;type:uuid;default:gen_random_uuid()"`
// WorkspaceID can be null when a database is created via restore operation
// outside the context of any workspace
WorkspaceID *uuid.UUID `json:"workspaceId" gorm:"column:workspace_id;type:uuid"`
Name string `json:"name" gorm:"column:name;type:text;not null"`
Type DatabaseType `json:"type" gorm:"column:type;type:text;not null"`
PostgresqlLogical *postgresql_logical.PostgresqlLogicalDatabase `json:"postgresqlLogical,omitzero" gorm:"foreignKey:DatabaseID"`
PostgresqlPhysical *postgresql_physical.PostgresqlPhysicalDatabase `json:"postgresqlPhysical,omitzero" gorm:"foreignKey:DatabaseID"`
Mysql *mysql.MysqlDatabase `json:"mysql,omitzero" gorm:"foreignKey:DatabaseID"`
Mariadb *mariadb.MariadbDatabase `json:"mariadb,omitzero" gorm:"foreignKey:DatabaseID"`
Mongodb *mongodb.MongodbDatabase `json:"mongodb,omitzero" gorm:"foreignKey:DatabaseID"`
Notifiers []notifiers.Notifier `json:"notifiers" gorm:"many2many:database_notifiers;"`
// these fields are not reliable, but
// they are used for pretty UI
LastBackupTime *time.Time `json:"lastBackupTime,omitzero" gorm:"column:last_backup_time;type:timestamp with time zone"`
LastBackupErrorMessage *string `json:"lastBackupErrorMessage,omitempty" gorm:"column:last_backup_error_message;type:text"`
HealthStatus *HealthStatus `json:"healthStatus" gorm:"column:health_status;type:text;not null"`
}
func (d *Database) Validate() error {
if d.Name == "" {
return errors.New("name is required")
}
switch d.Type {
case DatabaseTypePostgresLogical:
if d.PostgresqlLogical == nil {
return errors.New("postgresql database is required")
}
return d.PostgresqlLogical.Validate()
case DatabaseTypePostgresPhysical:
if d.PostgresqlPhysical == nil {
return errors.New("postgresql physical database is required")
}
return d.PostgresqlPhysical.Validate()
case DatabaseTypeMysql:
if d.Mysql == nil {
return errors.New("mysql database is required")
}
return d.Mysql.Validate()
case DatabaseTypeMariadb:
if d.Mariadb == nil {
return errors.New("mariadb database is required")
}
return d.Mariadb.Validate()
case DatabaseTypeMongodb:
if d.Mongodb == nil {
return errors.New("mongodb database is required")
}
return d.Mongodb.Validate()
default:
return errors.New("invalid database type: " + string(d.Type))
}
}
func (d *Database) ValidateUpdate(old, new Database) error {
// Database type cannot be changed after creation — the entire backup
// structure (storage files, schedulers, etc.) is tied to the type at
// creation time. Recreating that state automatically is error-prone;
// it is safer for the user to create a new database and remove the old.
if old.Type != new.Type {
return errors.New("database type cannot be changed; create a new database instead")
}
if old.Type == DatabaseTypePostgresLogical && old.PostgresqlLogical != nil && new.PostgresqlLogical != nil {
if err := new.PostgresqlLogical.ValidateUpdate(old.PostgresqlLogical); err != nil {
return err
}
}
if old.Type == DatabaseTypePostgresPhysical &&
old.PostgresqlPhysical != nil && new.PostgresqlPhysical != nil {
if err := new.PostgresqlPhysical.ValidateUpdate(old.PostgresqlPhysical); err != nil {
return err
}
}
return nil
}
func (d *Database) TestConnection(
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) error {
switch d.Type {
case DatabaseTypePostgresLogical:
if d.PostgresqlLogical == nil {
return errors.New("postgresql logical config is not set")
}
return d.PostgresqlLogical.TestConnection(logger, encryptor)
case DatabaseTypePostgresPhysical:
if d.PostgresqlPhysical == nil {
return errors.New("postgresql physical config is not set")
}
return d.PostgresqlPhysical.TestReplicationConnection(logger, encryptor)
case DatabaseTypeMysql:
if d.Mysql == nil {
return errors.New("mysql config is not set")
}
return d.Mysql.TestConnection(logger, encryptor)
case DatabaseTypeMariadb:
if d.Mariadb == nil {
return errors.New("mariadb config is not set")
}
return d.Mariadb.TestConnection(logger, encryptor)
case DatabaseTypeMongodb:
if d.Mongodb == nil {
return errors.New("mongodb config is not set")
}
return d.Mongodb.TestConnection(logger, encryptor)
default:
return errors.New("connection test not supported for database type: " + string(d.Type))
}
}
// ProbeLiveness backs the scheduled healthcheck. Only the physical type swaps its full connection
// test for a plain liveness probe, because only its test opens a replication connection that
// competes with backups for WAL sender slots; the other types share nothing with their backups and
// keep the full test.
func (d *Database) ProbeLiveness(
ctx context.Context,
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) error {
if d.Type != DatabaseTypePostgresPhysical {
return d.TestConnection(logger, encryptor)
}
if d.PostgresqlPhysical == nil {
return errors.New("postgresql physical config is not set")
}
return d.PostgresqlPhysical.ProbeLiveness(ctx, logger, encryptor)
}
func (d *Database) GetRawDbSizeMb(
ctx context.Context,
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) (float64, error) {
switch d.Type {
case DatabaseTypePostgresLogical:
if d.PostgresqlLogical == nil {
return 0, errors.New("postgresql logical config is not set")
}
return d.PostgresqlLogical.GetRawDbSizeMb(ctx, logger, encryptor)
case DatabaseTypeMysql:
if d.Mysql == nil {
return 0, errors.New("mysql config is not set")
}
return d.Mysql.GetRawDbSizeMb(ctx, logger, encryptor)
case DatabaseTypeMariadb:
if d.Mariadb == nil {
return 0, errors.New("mariadb config is not set")
}
return d.Mariadb.GetRawDbSizeMb(ctx, logger, encryptor)
case DatabaseTypeMongodb:
if d.Mongodb == nil {
return 0, errors.New("mongodb config is not set")
}
return d.Mongodb.GetRawDbSizeMb(ctx, logger, encryptor)
default:
return 0, errors.New("logical backup not supported for database type: " + string(d.Type))
}
}
func (d *Database) ShouldSuggestReadOnlyUser(
ctx context.Context,
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) (bool, []string, error) {
switch d.Type {
case DatabaseTypePostgresLogical:
return d.PostgresqlLogical.ShouldSuggestReadOnlyUser(ctx, logger, encryptor)
case DatabaseTypePostgresPhysical:
return d.PostgresqlPhysical.ShouldSuggestReplicationOnlyUser(ctx, logger, encryptor)
case DatabaseTypeMysql:
return d.Mysql.ShouldSuggestReadOnlyUser(ctx, logger, encryptor)
case DatabaseTypeMariadb:
return d.Mariadb.ShouldSuggestReadOnlyUser(ctx, logger, encryptor)
case DatabaseTypeMongodb:
return d.Mongodb.ShouldSuggestReadOnlyUser(ctx, logger, encryptor)
default:
return false, nil, errors.New("limited user suggestion not supported for this database type")
}
}
func (d *Database) HideSensitiveData() {
if d.PostgresqlLogical != nil {
d.PostgresqlLogical.HideSensitiveData()
}
if d.PostgresqlPhysical != nil {
d.PostgresqlPhysical.HideSensitiveData()
}
if d.Mysql != nil {
d.Mysql.HideSensitiveData()
}
if d.Mariadb != nil {
d.Mariadb.HideSensitiveData()
}
if d.Mongodb != nil {
d.Mongodb.HideSensitiveData()
}
}
// Here and on every engine model the copy starts from the struct rather than a field list, so a
// field added later travels with it instead of being silently dropped; only identity, per-operation
// state and shared pointers are spelled out. Only the sub-model matching Type is carried over: the
// shallow copy would otherwise alias all five engine pointers onto the new row.
func (d *Database) CopyForNewDatabase() *Database {
if d == nil {
return nil
}
copiedDatabase := *d
copiedDatabase.ID = uuid.Nil
// The copy has no backup history of its own, and showing the source's would be a lie.
copiedDatabase.LastBackupTime = nil
copiedDatabase.LastBackupErrorMessage = nil
copiedDatabase.Notifiers = slices.Clone(d.Notifiers)
copiedDatabase.PostgresqlLogical = nil
copiedDatabase.PostgresqlPhysical = nil
copiedDatabase.Mysql = nil
copiedDatabase.Mariadb = nil
copiedDatabase.Mongodb = nil
if d.HealthStatus != nil {
copiedDatabase.HealthStatus = new(*d.HealthStatus)
}
switch d.Type {
case DatabaseTypePostgresLogical:
copiedDatabase.PostgresqlLogical = d.PostgresqlLogical.CopyForNewDatabase()
case DatabaseTypePostgresPhysical:
copiedDatabase.PostgresqlPhysical = d.PostgresqlPhysical.CopyForNewDatabase()
case DatabaseTypeMysql:
copiedDatabase.Mysql = d.Mysql.CopyForNewDatabase()
case DatabaseTypeMariadb:
copiedDatabase.Mariadb = d.Mariadb.CopyForNewDatabase()
case DatabaseTypeMongodb:
copiedDatabase.Mongodb = d.Mongodb.CopyForNewDatabase()
}
return &copiedDatabase
}
// The agent restores into a container it owns, so it never dials the source and has no use for the
// bastion's address or account.
func (d *Database) ClearSshTunnelConfig() {
if d.PostgresqlLogical != nil {
d.PostgresqlLogical.SshTunnel = sshtunnel.Config{}
}
if d.PostgresqlPhysical != nil {
d.PostgresqlPhysical.SshTunnel = sshtunnel.Config{}
}
if d.Mysql != nil {
d.Mysql.SshTunnel = sshtunnel.Config{}
}
if d.Mariadb != nil {
d.Mariadb.SshTunnel = sshtunnel.Config{}
}
if d.Mongodb != nil {
d.Mongodb.SshTunnel = sshtunnel.Config{}
}
}
func (d *Database) EncryptSensitiveFields(encryptor encryption.FieldEncryptor) error {
if d.PostgresqlLogical != nil {
return d.PostgresqlLogical.EncryptSensitiveFields(encryptor)
}
if d.PostgresqlPhysical != nil {
return d.PostgresqlPhysical.EncryptSensitiveFields(encryptor)
}
if d.Mysql != nil {
return d.Mysql.EncryptSensitiveFields(encryptor)
}
if d.Mariadb != nil {
return d.Mariadb.EncryptSensitiveFields(encryptor)
}
if d.Mongodb != nil {
return d.Mongodb.EncryptSensitiveFields(encryptor)
}
return nil
}
func (d *Database) PopulateDbData(
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) error {
if d.PostgresqlLogical != nil {
return d.PostgresqlLogical.PopulateDbData(logger, encryptor)
}
if d.PostgresqlPhysical != nil {
return d.PostgresqlPhysical.PopulateDbData(logger, encryptor)
}
if d.Mysql != nil {
return d.Mysql.PopulateDbData(logger, encryptor)
}
if d.Mariadb != nil {
return d.Mariadb.PopulateDbData(logger, encryptor)
}
if d.Mongodb != nil {
return d.Mongodb.PopulateDbData(logger, encryptor)
}
return nil
}
// Dispatches on Type rather than on the first non-nil sub-model, because OpenTunnel dispatches on
// Type too: a row carrying two engine configs would otherwise open the tunnel for one and connect
// with the other, straight past the bastion.
func (d *Database) PopulateVersion(
logger *slog.Logger,
encryptor encryption.FieldEncryptor,
) error {
switch d.Type {
case DatabaseTypePostgresLogical:
if d.PostgresqlLogical == nil {
return errors.New("postgresql logical config is not set")
}
return d.PostgresqlLogical.PopulateVersion(logger, encryptor)
case DatabaseTypeMysql:
if d.Mysql == nil {
return errors.New("mysql config is not set")
}
return d.Mysql.PopulateVersion(logger, encryptor)
case DatabaseTypeMariadb:
if d.Mariadb == nil {
return errors.New("mariadb config is not set")
}
return d.Mariadb.PopulateVersion(logger, encryptor)
case DatabaseTypeMongodb:
if d.Mongodb == nil {
return errors.New("mongodb config is not set")
}
return d.Mongodb.PopulateVersion(logger, encryptor)
default:
return errors.New("version detection not supported for database type: " + string(d.Type))
}
}
func (d *Database) Update(incoming *Database) {
d.Name = incoming.Name
d.Type = incoming.Type
d.Notifiers = incoming.Notifiers
switch d.Type {
case DatabaseTypePostgresLogical:
if d.PostgresqlLogical != nil && incoming.PostgresqlLogical != nil {
d.PostgresqlLogical.Update(incoming.PostgresqlLogical)
}
case DatabaseTypePostgresPhysical:
if d.PostgresqlPhysical != nil && incoming.PostgresqlPhysical != nil {
d.PostgresqlPhysical.Update(incoming.PostgresqlPhysical)
}
case DatabaseTypeMysql:
if d.Mysql != nil && incoming.Mysql != nil {
d.Mysql.Update(incoming.Mysql)
}
case DatabaseTypeMariadb:
if d.Mariadb != nil && incoming.Mariadb != nil {
d.Mariadb.Update(incoming.Mariadb)
}
case DatabaseTypeMongodb:
if d.Mongodb != nil && incoming.Mongodb != nil {
d.Mongodb.Update(incoming.Mongodb)
}
}
}