145 lines
5.4 KiB
Markdown
145 lines
5.4 KiB
Markdown
# 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
|
|
```bash
|
|
cd central-database
|
|
cp .env.template .env
|
|
# Edit .env with your actual passwords (do not commit this file)
|
|
```
|
|
|
|
### 2. Build and Start PostgreSQL Container
|
|
```bash
|
|
docker-compose up -d --build postgres
|
|
```
|
|
|
|
### 3. Verify Database is Running
|
|
```bash
|
|
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:
|
|
|
|
```bash
|
|
# 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:
|
|
|
|
```yaml
|
|
# 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)
|
|
```bash
|
|
docker-compose exec postgres pg_dumpall -U admin > full_backup_$(date +%Y%m%d).sql
|
|
```
|
|
|
|
### Individual Database Backup
|
|
```bash
|
|
# 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)
|
|
```bash
|
|
#!/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 |