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
- Non-critical services first: shlink, spliit
- Document management: paperless
- Media services: immich, audiomuse
- 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:
\dxin 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