tonym/NastyNAS
1
0
Fork 0
Code Issues 70 Pull Requests Actions Packages Projects Releases Wiki Activity

Configure PostgreSQL for Immich #61

New Issue
Open
opened 2025-09-14 08:14:17 +00:00 by tonym · 0 comments
tonym commented 2025-09-14 08:14:17 +00:00
Owner
Copy Link

PostgreSQL Database Server Configuration

Overview

PostgreSQL is a powerful, open-source relational database system. This configuration provides a robust database backend for services like Immich, BookStack, and other applications requiring SQL database support.

Installation

From Community Applications

  1. Open Apps → Community Applications
  2. Search for "PostgreSQL"
  3. Select "postgres" by LinuxServer.io
  4. Click Install

Docker Configuration

Container Settings:

Repository: lscr.io/linuxserver/postgres:latest
Network Type: Custom br0 (192.168.0.15)
Fixed IP Address: 192.168.0.15

Port Mappings:

Container Port: 5432 → Host Port: 5432 (TCP)

Volume Mappings:

Host Path: /mnt/cache/appdata/postgres → Container Path: /config
Host Path: /mnt/cache/appdata/postgres/data → Container Path: /var/lib/postgresql/data

Environment Variables:

PUID=99
PGID=100
TZ=America/Chicago
POSTGRES_DB=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=secure_postgres_password_2024
POSTGRES_INITDB_ARGS=--encoding=UTF-8 --lc-collate=C --lc-ctype=C

Advanced Settings:

Privileged: No
Console shell command: Bash
Restart Policy: unless-stopped

Initial Setup

1. First Run Configuration

# Connect to container
docker exec -it postgres bash

# Connect to PostgreSQL as postgres user
psql -U postgres

2. Create Application Databases

-- Create database for Immich
CREATE DATABASE immich;
CREATE USER immich WITH ENCRYPTED PASSWORD 'immich_secure_password_2024';
GRANT ALL PRIVILEGES ON DATABASE immich TO immich;

-- Create database for BookStack
CREATE DATABASE bookstack;
CREATE USER bookstack WITH ENCRYPTED PASSWORD 'bookstack_secure_password_2024';
GRANT ALL PRIVILEGES ON DATABASE bookstack TO bookstack;

-- Create database for Directus
CREATE DATABASE directus;
CREATE USER directus WITH ENCRYPTED PASSWORD 'directus_secure_password_2024';
GRANT ALL PRIVILEGES ON DATABASE directus TO directus;

-- List all databases
\\l

-- Exit
\\q
exit

3. Performance Optimization

Create /mnt/cache/appdata/postgres/postgresql.conf:

# Memory Configuration
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

# Connection Settings
max_connections = 100
listen_addresses = '*'
port = 5432

# Logging
log_statement = 'all'
log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 10MB

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB

4. Security Configuration

Edit /mnt/cache/appdata/postgres/pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             192.168.0.0/24          md5

Service Integration

Connection Strings for Applications

Immich:

DB_HOSTNAME=192.168.0.15
DB_PORT=5432
DB_USERNAME=immich
DB_PASSWORD=immich_secure_password_2024
DB_DATABASE_NAME=immich

BookStack:

DB_HOST=192.168.0.15:5432
DB_DATABASE=bookstack
DB_USERNAME=bookstack
DB_PASSWORD=bookstack_secure_password_2024

Directus:

DB_CLIENT=pg
DB_HOST=192.168.0.15
DB_PORT=5432
DB_DATABASE=directus
DB_USER=directus
DB_PASSWORD=directus_secure_password_2024

Backup Integration

Kopia Backup Configuration

Add to Kopia backup policy:

# Database dumps directory
/mnt/cache/appdata/postgres/dumps/

# Configuration files
/mnt/cache/appdata/postgres/postgresql.conf
/mnt/cache/appdata/postgres/pg_hba.conf

Automated Database Backups

Create backup script /mnt/cache/appdata/postgres/backup.sh:

#!/bin/bash
BACKUP_DIR="/mnt/cache/appdata/postgres/dumps"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

# Backup all databases
docker exec postgres pg_dumpall -U postgres > "$BACKUP_DIR/all_databases_$DATE.sql"

# Backup individual databases
docker exec postgres pg_dump -U postgres immich > "$BACKUP_DIR/immich_$DATE.sql"
docker exec postgres pg_dump -U postgres bookstack > "$BACKUP_DIR/bookstack_$DATE.sql"
docker exec postgres pg_dump -U postgres directus > "$BACKUP_DIR/directus_$DATE.sql"

# Cleanup old backups (keep 7 days)
find $BACKUP_DIR -name "*.sql" -mtime +7 -delete

echo "PostgreSQL backup completed: $DATE"

Make executable: chmod +x /mnt/cache/appdata/postgres/backup.sh

Add to cron: 0 2 * * * /mnt/cache/appdata/postgres/backup.sh

Monitoring Setup

Uptime Kuma Integration

Monitor Name: PostgreSQL Database
Monitor Type: Port
Hostname: 192.168.0.15
Port: 5432
Heartbeat Interval: 60 seconds
Retries: 3

Health Check Script

Create /mnt/cache/appdata/postgres/health_check.sh:

#!/bin/bash
result=$(docker exec postgres pg_isready -U postgres)
if [[ $result == *"accepting connections"* ]]; then
    echo "PostgreSQL is healthy"
    exit 0
else
    echo "PostgreSQL is not responding"
    exit 1
fi

Testing Procedures

1. Connection Test

# Test from Unraid terminal
docker exec postgres psql -U postgres -c "SELECT version();"

# Test remote connection
psql -h 192.168.0.15 -U postgres -c "\\l"

2. Performance Test

-- Connect to PostgreSQL
psql -U postgres

-- Check active connections
SELECT * FROM pg_stat_activity;

-- Check database sizes
SELECT 
    datname,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

-- Check table statistics
SELECT 
    schemaname,
    tablename,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables;

3. Backup Test

# Test backup script
/mnt/cache/appdata/postgres/backup.sh

# Verify backup files
ls -la /mnt/cache/appdata/postgres/dumps/

# Test restore (to test database)
docker exec postgres createdb -U postgres test_restore
docker exec -i postgres psql -U postgres test_restore < /mnt/cache/appdata/postgres/dumps/immich_latest.sql

Troubleshooting

Common Issues

Connection Refused:

# Check container status
docker ps | grep postgres

# Check logs
docker logs postgres

# Restart container
docker restart postgres

Authentication Failed:

# Reset postgres password
docker exec postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'new_password';"

# Check pg_hba.conf
docker exec postgres cat /config/pg_hba.conf

Performance Issues:

-- Check slow queries
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

-- Check locks
SELECT * FROM pg_locks WHERE granted = false;

Storage Issues:

# Check disk usage
du -sh /mnt/cache/appdata/postgres/

# Check database sizes
docker exec postgres psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

Maintenance Tasks

Daily

  • Monitor connection count and performance
  • Check backup completion
  • Review error logs

Weekly

  • Analyze database statistics
  • Review and optimize slow queries
  • Check disk space usage

Monthly

  • Update PostgreSQL version
  • Review and update backup retention
  • Performance tuning review
  • Security audit

Quarterly

  • Full database maintenance
  • Index rebuilding
  • Configuration optimization
  • Disaster recovery testing

Security Considerations

Authentication

  • Use strong passwords for all database users
  • Regularly rotate passwords
  • Limit connection sources via pg_hba.conf

Network Security

  • Use fixed IP address for predictable access
  • Implement firewall rules if needed
  • Monitor for unauthorized connection attempts

Data Security

  • Regular encrypted backups
  • Database user privilege separation
  • Audit trail maintenance

Dependencies

  • Before PostgreSQL: None (base infrastructure)
  • After PostgreSQL: Immich, BookStack, Directus, pgAdmin4

Notes

  • PostgreSQL is critical infrastructure - ensure high availability
  • Monitor resource usage as databases grow
  • Plan for scaling if multiple heavy applications use the database
  • Consider PostgreSQL clustering for high availability in production

Configuration updated for Unraid 7.0.1 - NastyNAS Server

# PostgreSQL Database Server Configuration ## Overview PostgreSQL is a powerful, open-source relational database system. This configuration provides a robust database backend for services like Immich, BookStack, and other applications requiring SQL database support. ## Installation ### From Community Applications 1. Open **Apps** → **Community Applications** 2. Search for **"PostgreSQL"** 3. Select **"postgres"** by **LinuxServer.io** 4. Click **Install** ### Docker Configuration **Container Settings:** ``` Repository: lscr.io/linuxserver/postgres:latest Network Type: Custom br0 (192.168.0.15) Fixed IP Address: 192.168.0.15 ``` **Port Mappings:** ``` Container Port: 5432 → Host Port: 5432 (TCP) ``` **Volume Mappings:** ``` Host Path: /mnt/cache/appdata/postgres → Container Path: /config Host Path: /mnt/cache/appdata/postgres/data → Container Path: /var/lib/postgresql/data ``` **Environment Variables:** ``` PUID=99 PGID=100 TZ=America/Chicago POSTGRES_DB=postgres POSTGRES_USER=postgres POSTGRES_PASSWORD=secure_postgres_password_2024 POSTGRES_INITDB_ARGS=--encoding=UTF-8 --lc-collate=C --lc-ctype=C ``` **Advanced Settings:** ``` Privileged: No Console shell command: Bash Restart Policy: unless-stopped ``` ## Initial Setup ### 1. First Run Configuration ```bash # Connect to container docker exec -it postgres bash # Connect to PostgreSQL as postgres user psql -U postgres ``` ### 2. Create Application Databases ```sql -- Create database for Immich CREATE DATABASE immich; CREATE USER immich WITH ENCRYPTED PASSWORD 'immich_secure_password_2024'; GRANT ALL PRIVILEGES ON DATABASE immich TO immich; -- Create database for BookStack CREATE DATABASE bookstack; CREATE USER bookstack WITH ENCRYPTED PASSWORD 'bookstack_secure_password_2024'; GRANT ALL PRIVILEGES ON DATABASE bookstack TO bookstack; -- Create database for Directus CREATE DATABASE directus; CREATE USER directus WITH ENCRYPTED PASSWORD 'directus_secure_password_2024'; GRANT ALL PRIVILEGES ON DATABASE directus TO directus; -- List all databases \\l -- Exit \\q exit ``` ### 3. Performance Optimization Create `/mnt/cache/appdata/postgres/postgresql.conf`: ```ini # Memory Configuration shared_buffers = 256MB effective_cache_size = 1GB work_mem = 4MB maintenance_work_mem = 64MB # Connection Settings max_connections = 100 listen_addresses = '*' port = 5432 # Logging log_statement = 'all' log_destination = 'stderr' logging_collector = on log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 10MB # Checkpoints checkpoint_completion_target = 0.9 wal_buffers = 16MB ``` ### 4. Security Configuration Edit `/mnt/cache/appdata/postgres/pg_hba.conf`: ``` # TYPE DATABASE USER ADDRESS METHOD local all postgres peer local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all all 192.168.0.0/24 md5 ``` ## Service Integration ### Connection Strings for Applications **Immich:** ``` DB_HOSTNAME=192.168.0.15 DB_PORT=5432 DB_USERNAME=immich DB_PASSWORD=immich_secure_password_2024 DB_DATABASE_NAME=immich ``` **BookStack:** ``` DB_HOST=192.168.0.15:5432 DB_DATABASE=bookstack DB_USERNAME=bookstack DB_PASSWORD=bookstack_secure_password_2024 ``` **Directus:** ``` DB_CLIENT=pg DB_HOST=192.168.0.15 DB_PORT=5432 DB_DATABASE=directus DB_USER=directus DB_PASSWORD=directus_secure_password_2024 ``` ## Backup Integration ### Kopia Backup Configuration Add to Kopia backup policy: ```bash # Database dumps directory /mnt/cache/appdata/postgres/dumps/ # Configuration files /mnt/cache/appdata/postgres/postgresql.conf /mnt/cache/appdata/postgres/pg_hba.conf ``` ### Automated Database Backups Create backup script `/mnt/cache/appdata/postgres/backup.sh`: ```bash #!/bin/bash BACKUP_DIR="/mnt/cache/appdata/postgres/dumps" DATE=$(date +%Y%m%d_%H%M%S) mkdir -p $BACKUP_DIR # Backup all databases docker exec postgres pg_dumpall -U postgres > "$BACKUP_DIR/all_databases_$DATE.sql" # Backup individual databases docker exec postgres pg_dump -U postgres immich > "$BACKUP_DIR/immich_$DATE.sql" docker exec postgres pg_dump -U postgres bookstack > "$BACKUP_DIR/bookstack_$DATE.sql" docker exec postgres pg_dump -U postgres directus > "$BACKUP_DIR/directus_$DATE.sql" # Cleanup old backups (keep 7 days) find $BACKUP_DIR -name "*.sql" -mtime +7 -delete echo "PostgreSQL backup completed: $DATE" ``` Make executable: `chmod +x /mnt/cache/appdata/postgres/backup.sh` Add to cron: `0 2 * * * /mnt/cache/appdata/postgres/backup.sh` ## Monitoring Setup ### Uptime Kuma Integration **Monitor Name:** PostgreSQL Database **Monitor Type:** Port **Hostname:** 192.168.0.15 **Port:** 5432 **Heartbeat Interval:** 60 seconds **Retries:** 3 ### Health Check Script Create `/mnt/cache/appdata/postgres/health_check.sh`: ```bash #!/bin/bash result=$(docker exec postgres pg_isready -U postgres) if [[ $result == *"accepting connections"* ]]; then echo "PostgreSQL is healthy" exit 0 else echo "PostgreSQL is not responding" exit 1 fi ``` ## Testing Procedures ### 1. Connection Test ```bash # Test from Unraid terminal docker exec postgres psql -U postgres -c "SELECT version();" # Test remote connection psql -h 192.168.0.15 -U postgres -c "\\l" ``` ### 2. Performance Test ```sql -- Connect to PostgreSQL psql -U postgres -- Check active connections SELECT * FROM pg_stat_activity; -- Check database sizes SELECT datname, pg_size_pretty(pg_database_size(datname)) as size FROM pg_database ORDER BY pg_database_size(datname) DESC; -- Check table statistics SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del FROM pg_stat_user_tables; ``` ### 3. Backup Test ```bash # Test backup script /mnt/cache/appdata/postgres/backup.sh # Verify backup files ls -la /mnt/cache/appdata/postgres/dumps/ # Test restore (to test database) docker exec postgres createdb -U postgres test_restore docker exec -i postgres psql -U postgres test_restore < /mnt/cache/appdata/postgres/dumps/immich_latest.sql ``` ## Troubleshooting ### Common Issues **Connection Refused:** ```bash # Check container status docker ps | grep postgres # Check logs docker logs postgres # Restart container docker restart postgres ``` **Authentication Failed:** ```bash # Reset postgres password docker exec postgres psql -U postgres -c "ALTER USER postgres PASSWORD 'new_password';" # Check pg_hba.conf docker exec postgres cat /config/pg_hba.conf ``` **Performance Issues:** ```sql -- Check slow queries SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10; -- Check locks SELECT * FROM pg_locks WHERE granted = false; ``` **Storage Issues:** ```bash # Check disk usage du -sh /mnt/cache/appdata/postgres/ # Check database sizes docker exec postgres psql -U postgres -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;" ``` ## Maintenance Tasks ### Daily - Monitor connection count and performance - Check backup completion - Review error logs ### Weekly - Analyze database statistics - Review and optimize slow queries - Check disk space usage ### Monthly - Update PostgreSQL version - Review and update backup retention - Performance tuning review - Security audit ### Quarterly - Full database maintenance - Index rebuilding - Configuration optimization - Disaster recovery testing ## Security Considerations ### Authentication - Use strong passwords for all database users - Regularly rotate passwords - Limit connection sources via pg_hba.conf ### Network Security - Use fixed IP address for predictable access - Implement firewall rules if needed - Monitor for unauthorized connection attempts ### Data Security - Regular encrypted backups - Database user privilege separation - Audit trail maintenance ## Dependencies - **Before PostgreSQL:** None (base infrastructure) - **After PostgreSQL:** Immich, BookStack, Directus, pgAdmin4 ## Notes - PostgreSQL is critical infrastructure - ensure high availability - Monitor resource usage as databases grow - Plan for scaling if multiple heavy applications use the database - Consider PostgreSQL clustering for high availability in production --- *Configuration updated for Unraid 7.0.1 - NastyNAS Server*
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
No items
No Label
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
tonym
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: tonym/NastyNAS#61
No description provided.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?