homelab-docker-config/central-database/README.md

Central PostgreSQL Database Consolidation

This directory contains the configuration for consolidating all PostgreSQL databases into a single PostgreSQL 18 instance with schema isolation.

Architecture

Before (Multiple Containers)

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│ Synapse DB      │    │ Paperless DB    │    │ Immich DB       │
│ PostgreSQL 17    │    │ PostgreSQL 17    │    │ PostgreSQL 14    │
└─────────────────┘    └─────────────────┘    └─────────────────┘
      Port 5442          Port 5434          Port 5433

After (Single Container)

┌─────────────────────────────────────────────────────┐
│ PostgreSQL 18 (container name: postgres)          │
│                                                     │
│  ┌─────────────────┐  ┌─────────────────┐          │
│  │ synapse        │  │ paperless       │          │
│  │ database       │  │ database       │          │
│  └─────────────────┘  └─────────────────┘          │
│                                                     │
│  ┌─────────────────┐  ┌─────────────────┐          │
│  │ immich          │  │ shlink          │          │
│  │ database       │  │ database       │          │
│  └─────────────────┘  └─────────────────┘          │
│                                                     │
│  Extensions: vector, pg_vectorscale                │
└─────────────────────────────────────────────────────┘
                     Port 5432

Setup Instructions

1. Setup Environment

cd central-database
cp .env.template .env
# Edit .env with your actual passwords (do not commit this file)

2. Build and Start PostgreSQL Container

docker-compose up -d --build postgres

3. Verify Database is Running

docker-compose logs -f postgres
docker-compose exec postgres psql -U admin -c "\l"

4. Migrate Services

Use the generic migration script for each service:

# Set environment variables (or use .env file)
export SYNAPSE_POSTGRES_PASSWORD="your_actual_password"

# Example: Migrate Synapse
./migrate_service.sh synapse localhost 5442 synapse synapse_password synapse

# Example: Migrate Paperless
./migrate_service.sh paperless localhost 5434 paperless paperless_password paperless

5. Update Service Configurations

After migration, update each service's docker-compose.yml to point to the central postgres container:

# Example for Synapse
environment:
  POSTGRES_HOST: postgres
  POSTGRES_PORT: 5432
  POSTGRES_DB: synapse
  POSTGRES_USER: synapse_user
  POSTGRES_PASSWORD: ${SYNAPSE_POSTGRES_PASSWORD}

Migration Order Recommendation

1. Non-critical services first: shlink, spliit
2. Document management: paperless
3. Media services: immich, audiomuse
4. Matrix ecosystem: mas, signal, whatsapp, synapse

Backup Strategy

Full Backup (all databases)

docker-compose exec postgres pg_dumpall -U admin > full_backup_$(date +%Y%m%d).sql

Individual Database Backup

# Backup specific database
docker-compose exec postgres pg_dump -U synapse_user -d synapse > synapse_backup_$(date +%Y%m%d).sql

# Backup all databases individually
for db in synapse mas signal whatsapp paperless immich shlink spliit audiomuse; do
  docker-compose exec postgres pg_dump -U ${db}_user -d $db > ${db}_backup_$(date +%Y%m%d).sql
done

Automated Backup (add to cron)

#!/bin/bash
# Daily backup script
BACKUP_DIR="/backups/postgres"
mkdir -p $BACKUP_DIR

# Full backup
docker-compose -f /path/to/central-database/docker-compose.yml exec postgres pg_dumpall -U admin | gzip > $BACKUP_DIR/full_backup_$(date +%Y%m%d_%H%M%S).sql.gz

# Individual database backups
for db in synapse mas signal whatsapp paperless immich shlink spliit audiomuse; do
  docker-compose -f /path/to/central-database/docker-compose.yml exec postgres pg_dump -U ${db}_user -d $db | gzip > $BACKUP_DIR/${db}_backup_$(date +%Y%m%d_%H%M%S).sql.gz
done

# Keep last 7 days
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

Troubleshooting

Connection Issues

• Verify central database is running: docker-compose ps
• Check logs: docker-compose logs central_postgres
• Test connection: psql -h localhost -p 5432 -U admin -d central_db

Permission Issues

• Ensure service users have correct permissions on their schemas
• Check schema ownership: \dn+ in psql

Extension Issues

• Verify extensions are loaded: \dx in psql
• Check PostgreSQL logs for extension errors

Security Notes

• Use strong passwords for all service users
• Rotate passwords after migration
• Consider using PostgreSQL role attributes for additional security
• Enable SSL for production environments