Database & Backup Documentation

Complete documentation for the ContainerPub database system, backup strategies, and disaster recovery procedures.

Overview

The ContainerPub database infrastructure provides:

Entity-Based Database - Type-safe PostgreSQL database management
Query Builder - Fluent API for complex SQL queries
Backup System - Automated database and volume backups
Disaster Recovery - Complete system restore procedures
Replication - Real-time and scheduled data replication

Quick Links

Database System

Database System - Complete database architecture and API
Database Quick Reference - Quick reference guide
Implementation History - Development timeline

Backup & Recovery

Backup Strategy - Comprehensive backup and disaster recovery
Backup Workflows - Step-by-step backup procedures
Backup Quick Reference - Quick command reference

Database System

Key Features

Entity-Based Models:

Type-safe entity definitions
Automatic table creation
Field annotations and constraints
Relationship support

Query Builder:

Fluent API for SQL queries
Parameterized queries for security
Complex joins and conditions
Aggregation and analytics

Relationship Management:

hasMany - One-to-many relationships
belongsTo - Many-to-one relationships
manyToMany - Many-to-many relationships
Eager loading support

CRUD Operations:

Create, read, update, delete
Batch operations
Transaction support
Soft deletes

Architecture

Diagram

database/
├── lib/
│   ├── database.dart              # Main Database class
│   └── src/
│       ├── entity.dart            # Base entity & annotations
│       ├── query_builder.dart     # SQL query builder
│       ├── database_manager_query.dart  # CRUD manager
│       ├── relationship_manager.dart    # Relationships
│       └── entities/              # Database entities
│           ├── user_entity.dart
│           ├── function_entity.dart
│           ├── organization.dart
│           └── ...

Core Entities

User Management:

UserEntity - User accounts and authentication
UserInformation - Extended user profiles
Organization - Organization/team management
OrganizationMember - Organization membership

Function Management:

FunctionEntity - Function metadata
FunctionDeploymentEntity - Deployment history
FunctionLogEntity - Execution logs
FunctionInvocationEntity - Invocation records

Usage Example

yaml

// Initialize database
final db = Database(
  host: 'localhost',
  port: 5432,
  databaseName: 'containerpub',
  username: 'postgres',
  password: 'password',
);

// Create entity
final user = UserEntity()
  ..email = 'user@example.com'
  ..passwordHash = hashedPassword;

await db.users.create(user);

// Query with builder
final users = await db.users
  .where('email', '=', 'user@example.com')
  .get();

// Relationships
final userFunctions = await db.users
  .hasMany<FunctionEntity>(user.id!, 'owner_id')
  .get();

Backup System

Backup Types

Database Backups:

PostgreSQL dumps with pg_dump
Compressed archives (gzip)
Schema and data preservation
Point-in-time recovery

Volume Backups:

Docker volume snapshots
Function data archives
Configuration backups
Complete state preservation

Combined Backups:

Database + volumes in single archive
Timestamped backups
Automated scheduling
Retention policies

Backup Architecture

Flow

┌─────────────────────────────────────────────┐
│         ContainerPub Backend Stack          │
├─────────────────────────────────────────────┤
│  PostgreSQL Container  │  Backend Container │
│  ↓ postgres_data       │  ↓ functions_data  │
└──────────┬──────────────┴──────────┬────────┘
           │                         │
┌──────────▼─────────────────────────▼────────┐
│            Backup System Layer              │
│  • Database Backup (pg_dump)                │
│  • Volume Backup (tar archive)              │
│  • Combined Archives                        │
└──────────┬──────────────────────────────────┘
           │
┌──────────▼──────────────────────────────────┐
│        Replication Layer (Optional)         │
│  Local Storage │ Rsync │ S3 │ Custom        │
└─────────────────────────────────────────────┘

Backup Scripts

Core Scripts:

backup_database.sh - Database backup only
backup_volumes.sh - Volume backup only
backup_all.sh - Combined backup
restore_database.sh - Database restore
restore_volumes.sh - Volume restore
restore_all.sh - Complete restore

Replication:

replicate_backups.sh - Backup replication
setup_replication.sh - Configure replication
Supports rsync, S3, and custom destinations

Automation

Cron Scheduling:

dart

# Daily database backup at 2 AM
0 2 * * * /path/to/backup_database.sh

# Weekly full backup on Sunday at 3 AM
0 3 * * 0 /path/to/backup_all.sh

# Hourly replication
0 * * * * /path/to/replicate_backups.sh

Disaster Recovery

Recovery Procedures

Database Recovery:

Stop backend services
Restore database from backup
Verify data integrity
Restart services

Volume Recovery:

Stop containers
Restore volume data
Verify file permissions
Restart containers

Complete System Recovery:

Restore database
Restore volumes
Verify configurations
Test functionality
Resume operations

Recovery Time Objectives

Database: 5-15 minutes
Volumes: 10-30 minutes
Complete System: 20-45 minutes
Verification: 10-20 minutes

Best Practices

Database Management

Use Transactions - For multi-step operations
Parameterized Queries - Prevent SQL injection
Index Optimization - For frequently queried fields
Connection Pooling - Efficient resource usage
Regular Maintenance - VACUUM and ANALYZE

Backup Management

Regular Backups - Daily database, weekly full
Test Restores - Verify backup integrity monthly
Off-site Storage - Replicate to remote location
Retention Policy - Keep 30 daily, 12 weekly, 12 monthly
Monitoring - Alert on backup failures
Documentation - Keep recovery procedures updated

Security

Encrypted Backups - Use encryption for sensitive data
Access Control - Restrict backup access
Secure Transfer - Use SSH/TLS for replication
Audit Logging - Track backup operations
Key Management - Secure encryption keys

Monitoring

Database Metrics

Connection count
Query performance
Table sizes
Index usage
Lock contention
Replication lag

Backup Metrics

Backup success/failure rate
Backup size and duration
Storage usage
Replication status
Recovery time testing

Troubleshooting

Common Database Issues

Connection Errors:

Check PostgreSQL service status
Verify connection parameters
Check firewall rules
Review connection limits

Performance Issues:

Analyze slow queries
Check index usage
Review table statistics
Optimize query patterns

Common Backup Issues

Backup Failures:

Check disk space
Verify permissions
Review error logs
Test backup scripts

Restore Issues:

Verify backup integrity
Check PostgreSQL version compatibility
Review restore logs
Ensure sufficient disk space

Documentation Structure

Database Documentation

Database System - Complete system architecture

Entity definitions
Query builder API
Relationship management
CRUD operations
Examples and best practices

Database Quick Reference - Quick command reference

Common queries
Entity operations
Relationship queries
Troubleshooting

Implementation History - Development timeline
Feature additions
Bug fixes
Performance improvements
Migration notes

Backup Documentation

Backup Strategy - Comprehensive guide

System architecture
Backup types
Replication setup
Disaster recovery
Best practices

Backup Workflows - Step-by-step procedures

Backup procedures
Restore procedures
Testing procedures
Automation setup

Backup Quick Reference - Quick commands
Backup commands
Restore commands
Replication commands
Troubleshooting

Next Steps

Read Database System for complete database documentation
Check Backup Strategy for backup and recovery procedures
Review Backend Architecture for system integration
Explore CLI Documentation for client tools

Support

For issues, questions, or contributions:

GitHub: liodali/ContainerPub
Documentation: ContainerPub Docs