# PostgreSQL Backup and Restore Analysis

## Summary

**Finding**: Jellyfin does **NOT use pg_dump/pg_restore** for PostgreSQL backups. Instead, it uses a **generic backup mechanism** that works across all database providers.

**Date**: 2025-01-15  
**Analysis**: Migration backup and restore functionality

---

## Current Implementation

### Backup Strategy

Jellyfin uses a **provider-agnostic backup system** that:

1. **SQLite**: File-based backup (copies `jellyfin.db` file)
2. **PostgreSQL**: **No native backup** - Warns users to use pg_dump externally
3. **Generic Backup**: Uses `BackupService` with JSON serialization

---

## PostgreSQL Implementation Analysis

### MigrationBackupFast (PostgresDatabaseProvider.cs)

**Lines 395-403:**
```csharp
/// <inheritdoc />
public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
{
    var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);

    logger.LogWarning("Fast backup is not implemented for PostgreSQL. Use pg_dump for proper backups.");

    // Return a key for compatibility, but actual backup should be done externally
    return await Task.FromResult(key).ConfigureAwait(false);
}
```

**❌ NO pg_dump usage** - Just logs a warning and returns a dummy key

### RestoreBackupFast (PostgresDatabaseProvider.cs)

**Lines 406-410:**
```csharp
/// <inheritdoc />
public Task RestoreBackupFast(string key, CancellationToken cancellationToken)
{
    logger.LogWarning("Fast restore is not implemented for PostgreSQL. Use pg_restore for proper restoration.");
    return Task.CompletedTask;
}
```

**❌ NO pg_restore usage** - Just logs a warning

### DeleteBackup (PostgresDatabaseProvider.cs)

**Lines 413-417:**
```csharp
/// <inheritdoc />
public Task DeleteBackup(string key)
{
    logger.LogWarning("Backup deletion is not implemented for PostgreSQL. Manage backups externally.");
    return Task.CompletedTask;
}
```

**❌ NO backup management** - Assumes external backup management

---

## Comparison: SQLite vs PostgreSQL

### SQLite Implementation (Working)

**MigrationBackupFast:**
```csharp
public Task<string> MigrationBackupFast(CancellationToken cancellationToken)
{
    var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);
    var path = Path.Combine(applicationPaths.DataPath, "jellyfin.db");
    var backupFile = Path.Combine(applicationPaths.DataPath, BackupFolderName, $"{key}_jellyfin.db");
    
    File.Copy(path, backupFile);  // ✅ Simple file copy works
    return Task.FromResult(key);
}
```

**RestoreBackupFast:**
```csharp
public Task RestoreBackupFast(string key, CancellationToken cancellationToken)
{
    SqliteConnection.ClearAllPools();
    var path = Path.Combine(applicationPaths.DataPath, "jellyfin.db");
    var backupFile = Path.Combine(applicationPaths.DataPath, BackupFolderName, $"{key}_jellyfin.db");
    
    File.Copy(backupFile, path, true);  // ✅ Restore by copying back
    return Task.CompletedTask;
}
```

### PostgreSQL Implementation (Not Implemented)

**MigrationBackupFast:**
```csharp
public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
{
    logger.LogWarning("Fast backup is not implemented for PostgreSQL. Use pg_dump for proper backups.");
    return await Task.FromResult(key).ConfigureAwait(false);  // ❌ Does nothing
}
```

**RestoreBackupFast:**
```csharp
public Task RestoreBackupFast(string key, CancellationToken cancellationToken)
{
    logger.LogWarning("Fast restore is not implemented for PostgreSQL. Use pg_restore for proper restoration.");
    return Task.CompletedTask;  // ❌ Does nothing
}
```

---

## Generic Backup System (BackupService.cs)

Jellyfin has a **separate generic backup system** that works for both SQLite and PostgreSQL:

### CreateBackupAsync (BackupService.cs)

**Process:**
1. Runs database optimization (`VACUUM ANALYZE` for PostgreSQL)
2. Creates a ZIP archive
3. Backs up configuration files
4. **Serializes all database tables to JSON** (no pg_dump)
5. Backs up metadata, trickplay, subtitles (optional)

**Code (lines 260-400+):**
```csharp
public async Task<BackupManifestDto> CreateBackupAsync(BackupOptionsDto backupOptions)
{
    // ... optimization
    await _jellyfinDatabaseProvider.RunScheduledOptimisation(cancellationToken);
    
    using (var zipArchive = new ZipArchive(fileStream, ZipArchiveMode.Create))
    {
        var dbContext = await _dbProvider.CreateDbContextAsync();
        await using (dbContext)
        {
            // For each entity type
            foreach (var entityType in entityTypes)
            {
                // Serialize entities to JSON
                var jsonSerializer = new Utf8JsonWriter(zipEntryStream);
                await foreach (var item in set)
                {
                    using var document = JsonSerializer.SerializeToDocument(item);
                    document.WriteTo(jsonSerializer);
                }
            }
        }
        
        // Copy config/data folders
        CopyDirectory("Config", ...);
        CopyDirectory("Data", ...);
    }
}
```

**❌ NOT using pg_dump** - Uses JSON serialization instead

### RestoreBackupAsync (BackupService.cs)

**Process:**
1. Extracts ZIP archive
2. Reads manifest
3. Restores configuration files
4. **Deserializes JSON back to database** (no pg_restore)
5. Restores migration history manually

**Code (lines 87-246):**
```csharp
public async Task RestoreBackupAsync(string archivePath)
{
    using var zipArchive = new ZipArchive(fileStream, ZipArchiveMode.Read);
    
    var dbContext = await _dbProvider.CreateDbContextAsync();
    await using (dbContext)
    {
        // Purge database tables
        await _jellyfinDatabaseProvider.PurgeDatabase(dbContext, tableNames);
        
        // Restore each table from JSON
        foreach (var entityType in entityTypes)
        {
            await foreach (var item in JsonSerializer.DeserializeAsyncEnumerable<JsonObject>(zipEntryStream))
            {
                var entity = item.Deserialize(entityType);
                dbContext.Add(entity);
            }
        }
        
        await dbContext.SaveChangesAsync();
    }
}
```

**❌ NOT using pg_restore** - Uses JSON deserialization instead

---

## Why pg_dump/pg_restore Are NOT Used

### Design Philosophy
Jellyfin's backup system is **database-agnostic** to support:
- SQLite (file-based)
- PostgreSQL (server-based)
- Potential future databases (MySQL, SQL Server, etc.)

### Current Approach
- **Generic serialization**: Works with any EF Core provider
- **JSON format**: Human-readable and portable
- **Cross-database**: Can potentially restore SQLite backup to PostgreSQL (with schema migration)

### Limitations of Current Approach
1. **Slow for large databases**: JSON serialization is slower than native tools
2. **No differential backups**: Always full backup
3. **Memory intensive**: Loads entire tables into memory
4. **No compression within JSON**: ZIP compression only
5. **No transaction log**: Point-in-time recovery not possible

---

## PurgeDatabase Implementation

### PostgreSQL (lines 420-446)

```csharp
public async Task PurgeDatabase(JellyfinDbContext dbContext, IEnumerable<string>? tableNames)
{
    var deleteQueries = new List<string>();
    foreach (var tableName in tableNames)
    {
        var schema = entityType.GetSchema() ?? "public";
        deleteQueries.Add($"TRUNCATE TABLE \"{schema}\".\"{tableName}\" CASCADE;");
    }

    var deleteAllQuery = string.Join('\n', deleteQueries);
    await dbContext.Database.ExecuteSqlRawAsync(deleteAllQuery).ConfigureAwait(false);
}
```

**Uses TRUNCATE** - Fast table clearing with CASCADE for foreign keys

### SQLite (lines 193-211)

```csharp
public async Task PurgeDatabase(JellyfinDbContext dbContext, IEnumerable<string>? tableNames)
{
    var deleteQueries = new List<string>();
    foreach (var tableName in tableNames)
    {
        deleteQueries.Add($"DELETE FROM \"{tableName}\";");
    }

    var deleteAllQuery =
    $"""
    PRAGMA foreign_keys = OFF;
    {string.Join('\n', deleteQueries)}
    PRAGMA foreign_keys = ON;
    """;

    await dbContext.Database.ExecuteSqlRawAsync(deleteAllQuery).ConfigureAwait(false);
}
```

**Uses DELETE** - SQLite doesn't support TRUNCATE as efficiently

---

## Migration Backup Attribute Usage

### Example from MigrateRatingLevels.cs

```csharp
[JellyfinMigration("2025-04-20T22:00:00", nameof(MigrateRatingLevels))]
[JellyfinMigrationBackup(JellyfinDb = true)]  // ← Requests backup
internal class MigrateRatingLevels : IDatabaseMigrationRoutine
{
    // Migration code
}
```

**What Happens:**
1. Migration system sees `JellyfinMigrationBackup` attribute
2. Calls `MigrationBackupFast()` on the database provider
3. **For PostgreSQL**: Just logs a warning, no actual backup
4. **For SQLite**: Creates a file-based backup
5. Runs the migration
6. If migration fails, calls `RestoreBackupFast()`
7. **For PostgreSQL**: Logs warning, no actual restore

---

## Recommendations

### 🔴 CRITICAL: PostgreSQL Has No Automatic Backup!

**Current Behavior:**
- Migration attribute `[JellyfinMigrationBackup(JellyfinDb = true)]` does **NOTHING** for PostgreSQL
- Users are at risk if migrations fail
- Manual pg_dump is required before starting Jellyfin

### ✅ Recommendation 1: Implement pg_dump Integration

**Add to PostgresDatabaseProvider.cs:**

```csharp
public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
{
    var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);
    var backupPath = Path.Combine(_applicationPaths.DataPath, "backups", $"{key}_jellyfin_pg.backup");
    
    // Get connection details from DbContextFactory
    var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken);
    await using (context)
    {
        var connectionString = context.Database.GetConnectionString();
        var builder = new NpgsqlConnectionStringBuilder(connectionString);
        
        // Use pg_dump for native PostgreSQL backup
        var pgDumpPath = FindPgDumpExecutable() ?? "pg_dump";
        var arguments = $"-h {builder.Host} -p {builder.Port} -U {builder.Username} " +
                       $"-d {builder.Database} -F c -f \"{backupPath}\"";
        
        var processInfo = new ProcessStartInfo
        {
            FileName = pgDumpPath,
            Arguments = arguments,
            Environment = { ["PGPASSWORD"] = builder.Password },
            RedirectStandardOutput = true,
            RedirectStandardError = true,
            UseShellExecute = false
        };
        
        using var process = Process.Start(processInfo);
        await process.WaitForExitAsync(cancellationToken);
        
        if (process.ExitCode != 0)
        {
            throw new InvalidOperationException($"pg_dump failed with exit code {process.ExitCode}");
        }
        
        logger.LogInformation("PostgreSQL backup created at {BackupPath}", backupPath);
        return key;
    }
}
```

### ✅ Recommendation 2: Implement pg_restore Integration

```csharp
public async Task RestoreBackupFast(string key, CancellationToken cancellationToken)
{
    var backupPath = Path.Combine(_applicationPaths.DataPath, "backups", $"{key}_jellyfin_pg.backup");
    
    if (!File.Exists(backupPath))
    {
        logger.LogCritical("Backup file not found: {BackupPath}", backupPath);
        return;
    }
    
    var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken);
    await using (context)
    {
        var connectionString = context.Database.GetConnectionString();
        var builder = new NpgsqlConnectionStringBuilder(connectionString);
        
        // Drop and recreate database
        await DropAndRecreateDatabaseAsync(builder, cancellationToken);
        
        // Use pg_restore
        var pgRestorePath = FindPgRestoreExecutable() ?? "pg_restore";
        var arguments = $"-h {builder.Host} -p {builder.Port} -U {builder.Username} " +
                       $"-d {builder.Database} \"{backupPath}\"";
        
        var processInfo = new ProcessStartInfo
        {
            FileName = pgRestorePath,
            Arguments = arguments,
            Environment = { ["PGPASSWORD"] = builder.Password },
            RedirectStandardOutput = true,
            RedirectStandardError = true,
            UseShellExecute = false
        };
        
        using var process = Process.Start(processInfo);
        await process.WaitForExitAsync(cancellationToken);
        
        if (process.ExitCode != 0)
        {
            throw new InvalidOperationException($"pg_restore failed with exit code {process.ExitCode}");
        }
        
        logger.LogInformation("PostgreSQL backup restored from {BackupPath}", backupPath);
    }
}
```

### ✅ Recommendation 3: Alternative - Use SQL Dump Format

**Simpler approach** without requiring pg_dump/pg_restore binaries:

```csharp
public async Task<string> MigrationBackupFast(CancellationToken cancellationToken)
{
    var key = DateTime.UtcNow.ToString("yyyyMMddHHmmss", CultureInfo.InvariantCulture);
    var backupPath = Path.Combine(_applicationPaths.DataPath, "backups", $"{key}_jellyfin_pg.sql");
    
    var context = await DbContextFactory!.CreateDbContextAsync(cancellationToken);
    await using (context)
    {
        // Use PostgreSQL COPY command to export data
        foreach (var schema in Schemas.All)
        {
            var tables = context.Model.GetEntityTypes()
                .Where(et => et.GetSchema() == schema)
                .Select(et => et.GetTableName());
            
            foreach (var table in tables)
            {
                // Export using COPY TO
                var query = $"COPY \"{schema}\".\"{table}\" TO STDOUT WITH (FORMAT CSV, HEADER true)";
                // Save to backup file
            }
        }
    }
    
    return key;
}
```

---

## Risk Analysis

### Current Risks with PostgreSQL Migrations

| Risk | Severity | Impact | Mitigation |
|------|----------|--------|------------|
| **No automatic backup** | 🔴 HIGH | Data loss if migration fails | Users must manually backup |
| **No rollback capability** | 🔴 HIGH | Cannot undo failed migrations | Manual pg_restore required |
| **Migration failure** | 🟡 MEDIUM | Database corruption possible | Transaction rollback helps |
| **User awareness** | 🟡 MEDIUM | Users may not backup | Documentation needed |

### Current Behavior

**If a PostgreSQL migration fails:**
1. ❌ No automatic backup exists
2. ❌ No automatic rollback
3. ❌ Database may be left in inconsistent state
4. ⚠️ User must manually restore from their own pg_dump backup

**For SQLite:**
1. ✅ Automatic file backup created
2. ✅ Can restore backup automatically
3. ✅ Rollback possible
4. ✅ User protected from data loss

---

## Generic Backup System (BackupService)

### How It Works

**Create Backup (lines 260-420):**
1. Optimize database (`VACUUM ANALYZE` for PostgreSQL)
2. Create ZIP archive
3. Serialize all database tables to JSON
4. Include migration history
5. Copy config/data folders
6. Create manifest.json

**Restore Backup (lines 87-246):**
1. Extract ZIP archive
2. Read manifest
3. **Purge all tables** (TRUNCATE CASCADE for PostgreSQL)
4. Restore migration history
5. Deserialize JSON and insert records
6. Restore config/data folders

### Advantages
- ✅ Works with any database provider
- ✅ Human-readable format (JSON)
- ✅ Portable between databases (potentially)
- ✅ Includes all Jellyfin data (config, metadata)

### Disadvantages
- ❌ Slow for large databases (serialization overhead)
- ❌ Memory intensive
- ❌ No differential/incremental backups
- ❌ Not using native database features
- ❌ No point-in-time recovery

---

## Proposed Solutions

### Option 1: Implement Native pg_dump/pg_restore (Recommended)

**Pros:**
- ✅ Fast and efficient
- ✅ Industry-standard PostgreSQL backup
- ✅ Supports large databases
- ✅ Point-in-time recovery possible
- ✅ Compressed backups

**Cons:**
- ❌ Requires pg_dump/pg_restore binaries
- ❌ Platform-dependent (Windows/Linux paths)
- ❌ Requires PostgreSQL client tools installed
- ❌ More complex error handling

**Implementation Effort**: Medium (1-2 days)

### Option 2: Use PostgreSQL SQL Commands (Alternative)

**Pros:**
- ✅ No external dependencies
- ✅ Works through EF Core connection
- ✅ Cross-platform
- ✅ Simpler implementation

**Cons:**
- ❌ Slower than native tools
- ❌ Limited features
- ❌ Still requires custom format

**Implementation Effort**: Low (4-8 hours)

### Option 3: Document Manual Backup (Current)

**Pros:**
- ✅ No implementation required
- ✅ Users have full control
- ✅ Can use their preferred tools

**Cons:**
- ❌ Users may forget to backup
- ❌ No automatic rollback
- ❌ Risk of data loss

**Implementation Effort**: None (just documentation)

---

## Recommendation for Your Project

### Immediate Action (Current Status)

**✅ GOOD NEWS**: The generic `BackupService` **DOES work** with PostgreSQL:
- It serializes all tables to JSON
- It can restore from JSON
- It's just slower than pg_dump

**⚠️ CONCERN**: `MigrationBackupFast` does nothing for PostgreSQL:
- Migration backups are not created
- Rollback is not possible
- Users are at risk during migrations

### Short-term Solution

**Add warning to documentation:**
```
For PostgreSQL users: Before running Jellyfin updates, always run:
pg_dump -h localhost -U jellyfin -d jellyfin -F c -f jellyfin_backup.backup
```

### Long-term Solution (If Needed)

**Implement pg_dump integration** for `MigrationBackupFast`:
1. Detect if pg_dump is available
2. Fall back to generic backup if not
3. Store backups in standard location
4. Implement restore with pg_restore

---

## Code Locations

### Key Files

1. **Interface**: `src\Jellyfin.Database\Jellyfin.Database.Implementations\IJellyfinDatabaseProvider.cs`
   - Defines `MigrationBackupFast()`, `RestoreBackupFast()`, `DeleteBackup()`

2. **PostgreSQL Provider**: `src\Jellyfin.Database\Jellyfin.Database.Providers.Postgres\PostgresDatabaseProvider.cs`
   - Lines 395-417: Backup/restore methods (NOT IMPLEMENTED)
   - Lines 420-446: `PurgeDatabase()` using TRUNCATE CASCADE

3. **SQLite Provider**: `src\Jellyfin.Database\Jellyfin.Database.Providers.Sqlite\SqliteDatabaseProvider.cs`
   - Lines 145-190: Backup/restore methods (FULLY IMPLEMENTED with file copy)

4. **Generic Backup**: `Jellyfin.Server.Implementations\FullSystemBackup\BackupService.cs`
   - Lines 260-420: `CreateBackupAsync()` using JSON serialization
   - Lines 87-246: `RestoreBackupAsync()` using JSON deserialization

5. **Backup Attribute**: `Jellyfin.Server\Migrations\JellyfinMigrationBackupAttribute.cs`
   - Decorates migrations that need backup

---

## Testing Verification

### To Verify Current Backup System Works

**1. Create a generic backup:**
```csharp
await _backupService.CreateBackupAsync(new BackupOptionsDto 
{ 
    Database = true,
    Metadata = false,
    Trickplay = false,
    Subtitles = false
});
```

**2. Check backup location:**
```
<DataPath>/backups/jellyfin-backup-yyyyMMddHHmmss.zip
```

**3. Inspect contents:**
- `manifest.json` - Backup metadata
- `Database/*.json` - Serialized tables
- `Config/` - Configuration files
- `Data/` - Data folder contents

**4. Test restore:**
```csharp
await _backupService.RestoreBackupAsync(backupPath);
```

### To Test Migration Backup (Will NOT Work for PostgreSQL)

**1. Run a migration with backup attribute:**
```csharp
[JellyfinMigrationBackup(JellyfinDb = true)]
```

**2. Check logs:**
```
[WARN] Fast backup is not implemented for PostgreSQL. Use pg_dump for proper backups.
```

**3. Verify no backup created** in data/backups folder

---

## Conclusion

### Finding Summary

❌ **pg_dump**: NOT used  
❌ **pg_restore**: NOT used  
✅ **Generic JSON backup**: Available but not triggered by migrations  
❌ **Migration backups**: Do not work for PostgreSQL  

### Implications

1. **Current**: PostgreSQL users have **no automatic migration rollback**
2. **Generic backup works**: But not used during migrations
3. **Risk**: Data loss if migration fails
4. **Mitigation**: Users must manually backup with pg_dump

### Next Steps

**For Your Project:**
1. ✅ **Fixed the immediate error** (query materialization)
2. ⚠️ **Be aware**: No automatic backup during migrations
3. 📝 **Document**: Users should run pg_dump before updates
4. 🔧 **Consider**: Implementing pg_dump integration (future enhancement)

**For Production:**
1. **Always backup before Jellyfin updates**: `pg_dump -F c -f backup.backup`
2. **Monitor migrations**: Watch logs for issues
3. **Test migrations**: Use test database first
4. **Keep backups**: Regular pg_dump backups recommended

---

**Document Version**: 1.0  
**Date**: 2025-01-15  
**Analysis**: PostgreSQL backup mechanisms  
**Status**: ❌ pg_dump/pg_restore NOT used, ⚠️ Manual backups required