# DataShield PostgreSQL Production Deployment

Production-ready PostgreSQL deployment optimized for Raspberry Pi 4, designed specifically for DataShield workloads.

## 🎯 Overview

This project provides a complete PostgreSQL deployment solution for DataShield, featuring:
- ARM64-optimized configuration for Raspberry Pi 4
- Automated deployment via Jenkins CI/CD
- Backup and monitoring systems
- Production security best practices
- High-performance settings for file sync and analytics workloads

## 🏗️ Architecture

```
DataShield PostgreSQL Architecture
┌─────────────────────────────────────────┐
│              Jenkins CI/CD             │
│         (Automated Deployment)         │
└─────────────┬───────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────┐
│           Docker Compose               │
│     (Service Orchestration)            │
└─────────────┬───────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────┐
│        PostgreSQL 15 Container          │
│   - ARM64 Optimized                    │
│   - DataShield Schema                  │
│   - Materialized Views                 │
│   - Production Settings                │
└─────────────┬───────────────────────────┘
              │
              ▼
┌─────────────────────────────────────────┐
│         Persistent Storage              │
│    - Database Files                    │
│    - Configuration                     │
│    - Backups                           │
└─────────────────────────────────────────┘
```

## 🚀 Quick Start

### Prerequisites

- Raspberry Pi 4 (4GB RAM recommended)
- Docker and Docker Compose installed
- Git
- Jenkins (for automated deployment)
- At least 16GB free disk space

### Manual Deployment

1. **Clone the repository**
   ```bash
   git clone <repository-url>
   cd juntete
   ```

2. **Run the deployment script**
   ```bash
   # For production deployment
   ./postgresql/scripts/deploy.sh production

   # For development deployment
   ./postgresql/scripts/deploy.sh development
   ```

3. **Verify deployment**
   ```bash
   # Check container status
   docker ps | grep datashield

   # Test database connection
   docker exec datashield-postgres psql -U collabhubmaster -d datashield -c "SELECT 1;"
   ```

### Database Connection

- **Host**: localhost
- **Port**: 5432
- **Database**: datashield
- **Username**: collabhubmaster
- **Password**: [configured in environment]

### Development Tools

For development deployments, Adminer is available at:
- **URL**: http://localhost:8080
- **Server**: postgres
- **Username**: collabhubmaster
- **Password**: [configured in environment]
- **Database**: datashield

## 📁 Project Structure

```
juntete/
├── docker-compose.postgres.yml          # Development Docker Compose
├── docker-compose.postgres.prod.yml     # Production override
├── Jenkinsfile.postgres                 # Jenkins CI/CD pipeline
├── postgresql/
│   ├── conf/
│   │   ├── postgresql.conf              # PostgreSQL configuration
│   │   └── pg_hba.conf                  # Host-based authentication
│   ├── init/
│   │   └── 01-init-database.sh          # Database initialization
│   └── scripts/
│       ├── deploy.sh                    # Deployment script
│       ├── backup.sh                    # Backup script
│       └── backup_check.sh              # Health check script
├── backups/                             # Backup storage
├── docs/                                # Documentation
└── README.md                            # This file
```

## ⚙️ Configuration

### PostgreSQL Settings

The configuration is optimized for Raspberry Pi 4 (4GB RAM):

- **Shared Buffers**: 512MB (25% of RAM)
- **Effective Cache Size**: 2GB (50% of RAM)
- **Work Memory**: 8MB per connection
- **Max Connections**: 200
- **Checkpoint Configuration**: Optimized for SSD
- **WAL Settings**: Production-ready with compression

### Docker Configuration

- **Image**: postgres:15-alpine (ARM64 compatible)
- **Restart Policy**: unless-stopped
- **Health Checks**: Built-in PostgreSQL readiness checks
- **Resource Limits**: 2GB memory, 2 CPU cores
- **Security**: no-new-privileges, non-root user

### Environment Variables

```yaml
environment:
  POSTGRES_DB: datashield
  POSTGRES_USER: collabhubmaster
  POSTGRES_PASSWORD: Y5hNb3T8k75WW0Np2i6hxabp
  POSTGRES_INITDB_ARGS: "--encoding=UTF8 --locale=C"
  BACKUP_RETENTION: "7"
```

## 🔄 Operations

### Starting Services

```bash
# Development mode
docker-compose -f docker-compose.postgres.yml up -d

# Production mode
docker-compose -f docker-compose.postgres.yml -f docker-compose.postgres.prod.yml up -d
```

### Stopping Services

```bash
docker-compose -f docker-compose.postgres.yml down
```

### Viewing Logs

```bash
# PostgreSQL logs
docker logs -f datashield-postgres

# Backup service logs
docker logs -f datashield-backup
```

### Database Management

```bash
# Connect to database
docker exec -it datashield-postgres psql -U collabhubmaster -d datashield

# Refresh materialized view
docker exec datashield-postgres psql -U collabhubmaster -d datashield -c "SELECT refresh_user_analytics();"
```

## 💾 Backup Strategy

### Automated Backups

- **Schedule**: Daily at 2:00 AM (configurable)
- **Retention**: 7 days (configurable)
- **Format**: Compressed custom format
- **Integrity**: SHA256 checksums
- **Location**: `/backups` directory

### Manual Backup

```bash
# Create manual backup
docker exec datashield-backup /scripts/backup.sh

# Check backup integrity
docker exec datashield-backup /scripts/backup_check.sh
```

### Restore from Backup

```bash
# List available backups
ls -la backups/

# Restore from backup (replace with actual backup file)
docker exec -i datashield-postgres pg_restore \
  -U collabhubmaster \
  -d datashield \
  --clean --if-exists \
  < backups/datashield_backup_YYYYMMDD_HHMMSS.sql
```

## 📊 Monitoring

### Health Checks

```bash
# Run comprehensive health check
docker exec datashield-backup /scripts/backup_check.sh
```

### Performance Monitoring

The deployment includes several performance monitoring features:

1. **pg_stat_statements**: Query performance tracking
2. **Materialized Views**: Pre-computed analytics
3. **Connection Pooling**: Built-in connection management
4. **Resource Limits**: Memory and CPU constraints

### Key Metrics

- Database size and growth
- Connection counts
- Query performance
- Cache hit ratios
- Backup success rates

## 🔒 Security

### Implemented Measures

- **Authentication**: SCRAM-SHA-256 password encryption
- **Authorization**: Schema-based access control
- **Network**: Isolated Docker networks
- **File Permissions**: Restricted access to data files
- **Container Security**: no-new-privileges flag
- **Environment Variables**: Sensitive data in Docker secrets

### Recommendations

1. **Regular Updates**: Keep PostgreSQL and Docker images updated
2. **Network Access**: Use VPN or SSH tunnels for remote access
3. **Monitoring**: Set up alerting for failed logins and unusual activity
4. **Backup Verification**: Regularly test restore procedures
5. **Access Control**: Limit database access to required applications only

## 🚀 Performance Tuning

### Database Optimizations

1. **Indexes**: Comprehensive indexing strategy for DataShield queries
2. **Materialized Views**: Pre-computed analytics for dashboard queries
3. **Connection Pooling**: Efficient connection management
4. **Query Planning**: Optimized for ARM64 architecture

### System Optimizations

1. **Memory Management**: Configured for 4GB Raspberry Pi 4
2. **Disk I/O**: Optimized for SSD storage
3. **CPU Usage**: Balanced for concurrent operations
4. **Network**: Optimized for local application access

## 🔧 Troubleshooting

### Common Issues

#### Database Won't Start

```bash
# Check container logs
docker logs datashield-postgres

# Check system resources
free -h
df -h

# Check port availability
netstat -tlnp | grep 5432
```

#### Connection Issues

```bash
# Test database connection
docker exec datashield-postgres pg_isready -U collabhubmaster -d datashield

# Check network connectivity
docker network ls
docker network inspect juntete_datashield-network
```

#### Performance Issues

```bash
# Check active connections
docker exec datashield-postgres psql -U collabhubmaster -d datashield -c "
    SELECT count(*) FROM pg_stat_activity WHERE state = 'active';
"

# Check database size
docker exec datashield-postgres psql -U collabhubmaster -d datashield -c "
    SELECT pg_size_pretty(pg_database_size('datashield'));
"
```

### Recovery Procedures

1. **Service Recovery**: Restart containers if needed
2. **Data Recovery**: Restore from recent backup
3. **Emergency Procedures**: Use PostgreSQL recovery tools

## 🔄 Maintenance

### Regular Tasks

- **Weekly**: Check backup integrity
- **Monthly**: Review performance metrics
- **Quarterly**: Update PostgreSQL and Docker images
- **Annually**: Review and update security configurations

### Updating PostgreSQL

```bash
# Stop services
docker-compose -f docker-compose.postgres.yml down

# Update image
docker pull postgres:15-alpine

# Restart services
docker-compose -f docker-compose.postgres.yml up -d

# Verify functionality
docker exec datashield-postgres pg_isready -U collabhubmaster -d datashield
```

## 📋 Jenkins Integration

### Pipeline Features

- **Automated Deployment**: On main branch updates
- **Configuration Validation**: Syntax and file checks
- **Health Monitoring**: System resource checks
- **Performance Testing**: Basic query performance validation
- **Backup Creation**: Automatic backups on deployments
- **Notifications**: Success/failure alerts

### Jenkins Setup

1. Create new pipeline job
2. Point to repository
3. Use `Jenkinsfile.postgres` as Jenkinsfile name
4. Configure `postgres-password` credential
5. Set up build triggers as needed

## 🤝 Contributing

### Development Workflow

1. Create feature branch
2. Test changes in development mode
3. Update documentation
4. Submit pull request
5. Pipeline will test and deploy to main branch

### Code Standards

- Follow shell scripting best practices
- Use meaningful variable names
- Include error handling
- Document complex procedures
- Test thoroughly before deployment

## 📄 License

This project is licensed under the MIT License - see the [LICENSE](LICENSE) file for details.

## 🆘 Support

For issues and questions:

1. Check this documentation
2. Review troubleshooting section
3. Check container logs
4. Create GitHub issue with detailed information
5. Include system specifications and error messages

## 🔄 Version History

- **v1.0.0**: Initial production deployment
  - PostgreSQL 15 ARM64 optimization
  - DataShield schema and indexes
  - Backup and monitoring systems
  - Jenkins CI/CD integration
  - Comprehensive documentation