# PostgreSQL Backup - Local-Only Implementation Summary
## Overview
The PostgreSQL backup functionality using `pg_dump` and `pg_restore` has been updated to **ONLY work with local databases** (localhost or 127.0.0.1). For remote PostgreSQL servers, users must manage backups externally.
## Changes Made
### 1. **PostgresDatabaseProvider.cs**
#### Added Fields:
```csharp
private readonly IConfigurationManager? configurationManager;
private PostgresBackupService? backupService;
private string? currentHost;
```
#### Updated Constructor:
- Added optional `IConfigurationManager` parameter
- Allows backup service initialization when configuration is available
#### New Method: `IsLocalHost()`
```csharp
private static bool IsLocalHost(string? host)
{
return host.Equals("localhost", StringComparison.OrdinalIgnoreCase) ||
host.Equals("127.0.0.1", StringComparison.Ordinal) ||
host.Equals("::1", StringComparison.Ordinal); // IPv6 localhost
}
```
#### Updated `Initialise()` Method:
- Captures the database host from connection string
- Initializes `PostgresBackupService` **only if host is local**
- Logs appropriate messages based on whether database is local or remote
```csharp
// Store the host for backup operations
currentHost = connectionBuilder.Host;
// Initialize backup service if host is local and configuration manager is available
if (IsLocalHost(currentHost) && configurationManager is not null)
{
backupService = new PostgresBackupService(backupLogger, configurationManager);
logger.LogInformation("PostgreSQL backup service initialized for local database");
}
else if (!IsLocalHost(currentHost))
{
logger.LogInformation("PostgreSQL database is on remote server ({Host}). Backup operations via pg_dump/pg_restore are disabled", currentHost);
}
```
#### Updated Backup Methods:
**`MigrationBackupFast()`**:
- **Local database**: Uses `PostgresBackupService.CreateBackupAsync()`
- **Remote database**: Returns timestamp key, logs warning
**`RestoreBackupFast()`**:
- **Local database**: Uses `PostgresBackupService.RestoreBackupAsync()`
- **Remote database**: Logs warning only
**`DeleteBackup()`**:
- **Local database**: Deletes backup file from disk
- **Remote database**: Logs warning
### 2. **Updated Log Messages**
All messages now specify "on a remote server" for clarity:
| Old Message | New Message |
|-------------|-------------|
| "Backup deletion is not implemented for PostgreSQL. Manage backups externally." | "Backup deletion is not implemented for PostgreSQL **on a remote server**. Manage backups externally." |
| "Fast backup is not implemented for PostgreSQL. Use pg_dump for proper backups." | "Fast backup is not implemented for PostgreSQL **on a remote server**. Use pg_dump manually for proper backups." |
| "Fast restore is not implemented for PostgreSQL. Use pg_restore for proper restoration." | "Fast restore is not implemented for PostgreSQL **on a remote server**. Use pg_restore manually for proper restoration." |
### 3. **Updated Configuration Documentation**
**database.xml.example**:
```xml
```
**BACKUP_CONFIGURATION.md**:
- Added prominent warning about local-only requirement
- Clarified behavior for remote databases
- Updated examples and troubleshooting sections
## Behavior Matrix
| Database Host | Backup Service Initialized? | Backup Operations | Log Message |
|---------------|---------------------------|-------------------|-------------|
| `localhost` | ✅ Yes | Fully functional via pg_dump/pg_restore | "PostgreSQL backup service initialized for local database" |
| `127.0.0.1` | ✅ Yes | Fully functional via pg_dump/pg_restore | "PostgreSQL backup service initialized for local database" |
| `::1` (IPv6) | ✅ Yes | Fully functional via pg_dump/pg_restore | "PostgreSQL backup service initialized for local database" |
| `192.168.1.10` (remote) | ❌ No | Disabled, external management required | "PostgreSQL database is on remote server ({Host}). Backup operations via pg_dump/pg_restore are disabled" |
| `db.example.com` (remote) | ❌ No | Disabled, external management required | "PostgreSQL database is on remote server ({Host}). Backup operations via pg_dump/pg_restore are disabled" |
## Example Configurations
### Local Database (Backups Enabled):
```xml
Jellyfin-PostgreSQL
Jellyfin-PostgreSQL
Jellyfin.Database.Providers.Postgres
Host=localhost;Port=5432;Database=jellyfin;Username=jellyfin;Password=pwd
pg_dump
custom
6
```
**Result**: ✅ Backups work automatically via pg_dump/pg_restore
### Remote Database (Backups Disabled):
```xml
Jellyfin-PostgreSQL
Jellyfin-PostgreSQL
Jellyfin.Database.Providers.Postgres
Host=192.168.1.50;Port=5432;Database=jellyfin;Username=jellyfin;Password=pwd
pg_dump
```
**Result**: ❌ Backup operations log warnings; users must manually run:
```bash
# Manual backup on remote server
pg_dump -h 192.168.1.50 -p 5432 -U jellyfin -d jellyfin -F c -f backup.dump
# Manual restore on remote server
pg_restore -h 192.168.1.50 -p 5432 -U jellyfin -d jellyfin backup.dump
```
## Security Rationale
This restriction exists because:
1. **Local Access Required**: `pg_dump` and `pg_restore` must be executed on the same machine as the PostgreSQL server for reliable filesystem access
2. **Credentials**: Running pg_dump against remote servers requires storing remote credentials
3. **Network Overhead**: Large backups over network connections can be slow and unreliable
4. **Best Practice**: Remote PostgreSQL deployments should have their own backup infrastructure (scheduled pg_dump jobs, WAL archiving, replication, etc.)
## Migration Path
If you previously had a remote PostgreSQL setup expecting automatic backups:
### Before (Not Working):
- Jellyfin connected to remote PostgreSQL
- Expected automatic backups (didn't work)
### After (Clear Behavior):
- Jellyfin connects to remote PostgreSQL
- Clear log messages indicate backups are disabled
- Documentation provides manual backup commands
### Recommended Setup for Remote Databases:
1. **On the PostgreSQL server**, set up a cron job:
```bash
# /etc/cron.daily/jellyfin-backup.sh
pg_dump -U jellyfin -d jellyfin -F c -f /backups/jellyfin_$(date +%Y%m%d).dump
```
2. **Use PostgreSQL's native backup features**:
- Continuous archiving (WAL archiving)
- Streaming replication
- Point-in-time recovery (PITR)
- pgBackRest or Barman for enterprise backups
3. **Cloud-native solutions**:
- AWS RDS automated backups
- Azure Database for PostgreSQL automated backups
- Google Cloud SQL automated backups
## Testing
To test the local vs. remote behavior:
```csharp
// Test with localhost
var localConfig = new DatabaseConfigurationOptions
{
CustomProviderOptions = new CustomDatabaseOptions
{
Options = new[]
{
new CustomDatabaseOption { Key = "host", Value = "localhost" }
}
}
};
// Result: Backup service initialized
// Test with remote host
var remoteConfig = new DatabaseConfigurationOptions
{
CustomProviderOptions = new CustomDatabaseOptions
{
Options = new[]
{
new CustomDatabaseOption { Key = "host", Value = "192.168.1.50" }
}
}
};
// Result: Backup service NOT initialized, warning logged
```
## Summary
✅ **Local databases (localhost/127.0.0.1)**: Full automated backup support via pg_dump/pg_restore
❌ **Remote databases**: Backup operations disabled, clear messaging, external management required
📝 **Documentation**: Updated to clearly explain the local-only restriction
🔒 **Security**: Follows best practices for remote database backup management
This implementation provides a clear, safe, and maintainable approach to PostgreSQL backups in Jellyfin.