pgsql-jellyfin/docs/database/postgres-provider-readme.md

# Jellyfin.Database.Providers.Postgres

PostgreSQL database provider for Jellyfin.

## ⚠️ Important Note

This provider currently uses `Npgsql.EntityFrameworkCore.PostgreSQL 9.0.2` with EF Core 10.0.3, which requires overriding package version constraints. This is a temporary workaround until Npgsql releases a version compatible with EF Core 10.

**Compatibility Warning**: This may cause runtime issues. Monitor for:
- Npgsql.EntityFrameworkCore.PostgreSQL 10.x release
- Update `Directory.Packages.props` when available

## Configuration

To use PostgreSQL as the database backend, configure the following options in your Jellyfin configuration:

```json
{
  "Database": {
    "Provider": "Jellyfin-PostgreSQL",
    "CustomProviderOptions": {
      "Options": [
        { "Key": "host", "Value": "localhost" },
        { "Key": "port", "Value": "5432" },
        { "Key": "database", "Value": "jellyfin" },
        { "Key": "username", "Value": "jellyfin" },
        { "Key": "password", "Value": "your_secure_password" },
        { "Key": "pooling", "Value": "true" },
        { "Key": "max-pool-size", "Value": "100" },
        { "Key": "min-pool-size", "Value": "0" },
        { "Key": "command-timeout", "Value": "30" },
        { "Key": "connection-timeout", "Value": "15" }
      ]
    }
  }
}
```

## Database Setup

Before using this provider, ensure PostgreSQL is installed and create the database:

```sql
CREATE DATABASE jellyfin;
CREATE USER jellyfin WITH PASSWORD 'your_secure_password';
GRANT ALL PRIVILEGES ON DATABASE jellyfin TO jellyfin;
```

## Creating Migrations

To create a new migration:

```bash
cd src/Jellyfin.Database/Jellyfin.Database.Providers.Postgres
dotnet ef migrations add YourMigrationName --context JellyfinDbContext
```

## Applying Migrations

Migrations will be applied automatically when Jellyfin starts. You can also apply them manually:

```bash
dotnet ef database update --context JellyfinDbContext
```

## Backup and Restore

Unlike SQLite, PostgreSQL backups should be managed externally using PostgreSQL tools:

```bash
# Backup
pg_dump -U jellyfin -h localhost jellyfin > jellyfin_backup.sql

# Restore
psql -U jellyfin -h localhost jellyfin < jellyfin_backup.sql
```

## Configuration Options

| Key | Default | Description |
|-----|---------|-------------|
| host | localhost | PostgreSQL server hostname |
| port | 5432 | PostgreSQL server port |
| database | jellyfin | Database name |
| username | jellyfin | Database username |
| password | (empty) | Database password |
| pooling | true | Enable connection pooling |
| max-pool-size | 100 | Maximum number of connections in the pool |
| min-pool-size | 0 | Minimum number of connections in the pool |
| command-timeout | 30 | Command timeout in seconds |
| connection-timeout | 15 | Connection timeout in seconds |
| multiplexing | false | ⚠️ **Advanced**: Enable command multiplexing (requires all async operations) |
| EnableSensitiveDataLogging | false | Enable sensitive data logging (for debugging) |

### ⚠️ Multiplexing Warning

**Multiplexing is disabled by default** because it requires all database operations to be asynchronous. Enabling multiplexing will cause errors like:

```
System.NotSupportedException: Synchronous command execution is not supported when multiplexing is on
```

Only enable multiplexing if you have modified Jellyfin code to use fully async database operations.

## Performance Tuning

For better performance, consider:

1. **Indexes**: The provider will create necessary indexes through migrations
2. **Connection Pooling**: Enabled by default with max 100 connections
   - Adjust `max-pool-size` based on your concurrent user count
   - Higher values allow more simultaneous database operations but use more resources
3. **Vacuum**: Scheduled optimization runs `VACUUM ANALYZE` periodically
4. **PostgreSQL Configuration**: Adjust `shared_buffers`, `effective_cache_size`, etc. in postgresql.conf

### Connection Pool Sizing

A good rule of thumb for `max-pool-size`:
- **Small deployments** (1-10 users): 20-50 connections
- **Medium deployments** (10-50 users): 50-100 connections  
- **Large deployments** (50+ users): 100-200 connections

Monitor your PostgreSQL server's active connections with:
```sql
SELECT count(*) FROM pg_stat_activity WHERE datname = 'jellyfin';
```

## Notes

- Migrations from SQLite are not automatically handled. You'll need to export data from SQLite and import into PostgreSQL manually.
- The provider uses UTC timestamps throughout for consistency.
- Transaction isolation level and other PostgreSQL-specific features can be configured through Npgsql connection string parameters.