# PostgreSQL Deployment Guide

This guide provides step-by-step instructions for deploying PostgreSQL on Raspberry Pi 4 for any project.

## 🎯 Deployment Options

### Option 1: Manual Deployment (Recommended for first-time setup)

### Option 2: Jenkins CI/CD (Recommended for ongoing maintenance)

### Option 3: Docker Compose (For development/testing)

---

## 📋 Prerequisites

### Hardware Requirements

- **Raspberry Pi 4** (4GB RAM recommended, 8GB preferred)
- **MicroSD Card**: 32GB or larger (Class 10 or better)
- **External Storage**: USB 3.0 SSD (highly recommended for production)
- **Network**: Reliable internet connection
- **Power Supply**: Official Raspberry Pi 4 power supply (3.0A recommended)

### Software Requirements

- **Raspberry Pi OS**: 64-bit version (Bullseye or later)
- **Docker**: 20.10 or later
- **Docker Compose**: 2.0 or later
- **Git**: Latest version
- **Jenkins**: Optional, for CI/CD automation

### System Preparation

```bash
# Update system packages
sudo apt update && sudo apt upgrade -y

# Install required packages
sudo apt install -y git curl wget htop

# Add user to docker group (if not already done)
sudo usermod -aG docker $USER

# Reboot to apply group changes
sudo reboot
```

---

## 🔧 Option 1: Manual Deployment

### Step 1: Clone Repository

```bash
# Clone the repository
git clone <repository-url> postgresql-deployment
cd postgresql-deployment

# Verify Docker is running
docker info
```

### Step 2: Configure Environment

```bash
# Copy and configure environment file
cp .env.example .env
nano .env  # Edit with your secure password

# Example .env configuration:
# POSTGRES_DB=myapp
# POSTGRES_USER=myapp_user
# POSTGRES_PASSWORD=your_secure_password_here
# PROJECT_NAME="My Application"
# PROJECT_SCHEMA=none
# BACKUP_RETENTION=7
```

### Step 3: Prepare Directories

```bash
# Create necessary directories
mkdir -p backups logs postgresql/data

# Set proper permissions
chmod 755 postgresql/scripts/*.sh
```

### Step 4: Deploy PostgreSQL

```bash
# For production deployment
./postgresql/scripts/deploy.sh production

# For development deployment (includes Adminer)
./postgresql/scripts/deploy.sh development
```

### Step 5: Verify Deployment

```bash
# Check container status
docker ps | grep postgres

# Test database connection (adjust username/database based on your .env)
docker exec postgres-postgres pg_isready -U postgres -d postgres

# Check database schema (if using project schema)
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'pg_catalog');
"
```

### Step 6: Initial Database Setup

```bash
# Connect to database (adjust connection details based on your .env)
docker exec -it postgres-postgres psql -U postgres -d postgres

# List tables (if using project schema)
\dt *.*

# Check available schemas
\dn
```

---

## 🚀 Option 2: Jenkins CI/CD Deployment

### Step 1: Install Jenkins

```bash
# Install Java
sudo apt install -y openjdk-11-jdk

# Add Jenkins repository
wget -q -O - https://pkg.jenkins.io/debian/jenkins.io.key | sudo apt-key add -
echo deb https://pkg.jenkins.io/debian binary/ | sudo tee /etc/apt/sources.list.d/jenkins.list

# Install Jenkins
sudo apt update
sudo apt install -y jenkins

# Start and enable Jenkins
sudo systemctl start jenkins
sudo systemctl enable jenkins

# Get initial admin password
sudo cat /var/lib/jenkins/secrets/initialAdminPassword
```

### Step 2: Configure Jenkins

1. **Access Jenkins**: Open `http://your-pi-ip:8080` in browser
2. **Initial Setup**: Use the password from step 1
3. **Install Plugins**: Install recommended plugins
4. **Create Admin User**: Set up your Jenkins admin account
5. **Install Additional Plugins**: Docker Pipeline, Pipeline: Stage View

### Step 3: Configure Jenkins Credentials

1. Go to **Manage Jenkins** → **Manage Credentials**
2. Click **(global)** → **Add Credentials**
3. **Kind**: Secret text
4. **Scope**: Global
5. **Secret**: Your PostgreSQL password (from your .env file)
6. **ID**: `postgres-password`
7. **Description**: PostgreSQL Database Password

### Step 4: Create Jenkins Pipeline

1. **New Item**: Create a new pipeline job
2. **Name**: `postgresql-deploy`
3. **Pipeline**: Select Pipeline script from SCM
4. **SCM**: Git
5. **Repository URL**: Your Git repository URL
6. **Credentials**: Add Git credentials if needed
7. **Branch Specifier**: `*/main`
8. **Script Path**: `Jenkinsfile`

### Step 5: Run Initial Deployment

1. **Build Now**: Trigger the first build
2. **Monitor Progress**: Watch the pipeline execution
3. **Verify Results**: Check build logs and deployment status

---

## 🐳 Option 3: Docker Compose Direct

### Docker Compose Deployment

```bash
# Start PostgreSQL services
docker-compose up -d

# View logs
docker-compose logs -f

# Stop services
docker-compose down
```

---

## 🔍 Verification Steps

### Container Health Check

```bash
# Check all containers
docker ps

# Detailed container info
docker inspect postgres-postgres

# Resource usage
docker stats postgres-postgres
```

### Database Health Check

```bash
# Connection test (adjust based on your .env configuration)
docker exec postgres-postgres pg_isready -U postgres -d postgres

# Database size
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT pg_size_pretty(pg_database_size('postgres'));
"

# Connection count
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT count(*) FROM pg_stat_activity;
"
```

### Application Connection Test

```bash
# Test from host machine (adjust based on your .env configuration)
psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT 1;"

# Test sample query (if using project schema)
psql -h localhost -p 5432 -U postgres -d postgres -c "
    SELECT table_name FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema', 'pg_catalog');
"
```

---

## 🔧 Configuration

### Environment Variables

Create a `.env` file for custom configuration:

```bash
# Database Configuration
POSTGRES_DB=postgres
POSTGRES_USER=postgres
POSTGRES_PASSWORD=your-secure-password

# Project Configuration
PROJECT_NAME="My Project"
PROJECT_SCHEMA=none  # Set to schema file name or leave as 'none'

# Backup Configuration
BACKUP_RETENTION=7
BACKUP_SCHEDULE="0 2 * * *"

# Resource Limits
POSTGRES_MEMORY_LIMIT=2G
POSTGRES_CPU_LIMIT=2
```

### Performance Tuning

Edit `postgresql/conf/postgresql.conf` for custom settings:

```bash
# Memory settings (adjust based on available RAM)
shared_buffers = 512MB          # 25% of RAM
effective_cache_size = 2GB      # 50% of RAM
work_mem = 8MB                  # Per connection

# Connection settings
max_connections = 200
shared_preload_libraries = 'pg_stat_statements'

# Logging
log_min_messages = warning
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
```

---

## 📊 Monitoring and Maintenance

### Health Monitoring

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

# Monitor backup system
docker logs postgres-backup

# Check backup files
ls -la backups/
```

### Performance Monitoring

```bash
# Monitor database activity (adjust based on your .env configuration)
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        state,
        COUNT(*) as connections
    FROM pg_stat_activity
    GROUP BY state;
"

# Check slow queries
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        query,
        calls,
        total_time,
        mean_time
    FROM pg_stat_statements
    ORDER BY mean_time DESC
    LIMIT 10;
"
```

### System Monitoring

```bash
# System resources
htop

# Disk usage
df -h

# Docker system usage
docker system df

# Container logs
docker logs --tail 50 postgres-postgres
```

---

## 🔒 Security Hardening

### Network Security

```bash
# Configure firewall (if using ufw)
sudo ufw allow 22/tcp      # SSH
sudo ufw allow 8080/tcp    # Jenkins (if remote access needed)
sudo ufw allow 5432/tcp    # PostgreSQL (if remote access needed)
sudo ufw enable
```

### Database Security

```bash
# Review PostgreSQL connections (adjust based on your .env configuration)
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        datname,
        usename,
        client_addr,
        state
    FROM pg_stat_activity;
"

# Check user permissions
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        grantee,
        privilege_type
    FROM information_schema.role_table_grants
    WHERE table_schema NOT IN ('information_schema', 'pg_catalog');
"
```

### File System Security

```bash
# Set proper permissions
sudo chmod 600 postgresql/conf/postgresql.conf
sudo chmod 600 postgresql/conf/pg_hba.conf

# Secure backup directory
sudo chmod 700 backups/
sudo chown $USER:$USER backups/
```

---

## 🚨 Troubleshooting

### Common Issues and Solutions

#### Container Won't Start

```bash
# Check Docker status
sudo systemctl status docker

# Check available disk space
df -h

# Check system resources
free -h

# View container logs
docker logs postgres-postgres
```

#### Database Connection Issues

```bash
# Test connection from container (adjust based on your .env configuration)
docker exec postgres-postgres psql -U postgres -d postgres -c "SELECT 1;"

# Check port binding
netstat -tlnp | grep 5432

# Test from host
psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT 1;"
```

#### Performance Issues

```bash
# Check system load
top
htop

# Check database locks (adjust based on your .env configuration)
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        pid,
        relation,
        mode,
        granted
    FROM pg_locks
    WHERE NOT granted;
"

# Check long-running queries
docker exec postgres-postgres psql -U postgres -d postgres -c "
    SELECT
        pid,
        now() - pg_stat_activity.query_start AS duration,
        query
    FROM pg_stat_activity
    WHERE state = 'active'
    ORDER BY duration DESC;
"
```

### Recovery Procedures

#### Service Recovery

```bash
# Restart services
docker-compose restart postgres

# Full restart
docker-compose down
docker-compose up -d
```

#### Data Recovery

```bash
# List available backups
ls -la backups/*_backup_*.sql

# Restore from backup (stop services first)
docker-compose down

# Remove old data volume (check volume name first)
docker volume ls

# Remove postgres data volume (replace with actual volume name)
docker volume rm postgresql_postgres_data

# Start fresh deployment
./postgresql/scripts/deploy.sh production

# Restore data (from backup - adjust connection details)
docker exec -i postgres-postgres psql \
  -U postgres \
  -d postgres \
  < backups/postgres_backup_YYYYMMDD_HHMMSS.sql
```

---

## 📈 Scaling and Optimization

### Performance Optimization

1. **Memory Tuning**: Adjust PostgreSQL memory settings based on available RAM
2. **Storage**: Use external SSD for better I/O performance
3. **Network**: Use wired Ethernet connection for stability
4. **Monitoring**: Set up regular performance monitoring

### Scaling Considerations

1. **Read Replicas**: For read-heavy workloads
2. **Connection Pooling**: PgBouncer for high connection counts
3. **Partitioning**: For very large tables
4. **Caching**: Redis for frequently accessed data

### High Availability

1. **Backup Strategy**: Regular automated backups
2. **Monitoring**: Alert system for failures
3. **Failover**: Manual failover procedures
4. **Documentation**: Keep recovery procedures updated

---

## 📚 Additional Resources

### Documentation

- [PostgreSQL 15 Documentation](https://www.postgresql.org/docs/15/)
- [Docker Compose Reference](https://docs.docker.com/compose/)
- [Jenkins Pipeline Documentation](https://www.jenkins.io/doc/book/pipeline/)

### Community Support

- [PostgreSQL Community](https://www.postgresql.org/community/)
- [Docker Forums](https://forums.docker.com/)
- [Jenkins Community](https://www.jenkins.io/participate/)

### Performance Tools

- pgAdmin for database administration
- Prometheus for monitoring
- Grafana for visualization
- pt-query-digest for query analysis