PostgreSQL Containerization Best Practices¶

# containerization/postgres_container_manager.py
import psycopg2
import json
import docker
import subprocess
from datetime import datetime
from typing import Dict, List, Optional
import logging

class PostgreSQLContainerManager:
    def __init__(self, connection_params):
        self.conn_params = connection_params
        self.docker_client = docker.from_env()
        self.setup_logging()

    def setup_logging(self):
        """Setup logging."""
        logging.basicConfig(level=logging.INFO)
        self.logger = logging.getLogger(__name__)

    def get_connection(self):
        """Get database connection."""
        return psycopg2.connect(**self.conn_params)

    def create_postgres_container(self, container_name: str, 
                                 postgres_password: str, 
                                 postgres_db: str = 'production',
                                 postgres_user: str = 'postgres',
                                 port: int = 5432,
                                 volumes: Dict[str, str] = None,
                                 environment: Dict[str, str] = None):
        """Create PostgreSQL container."""
        try:
            # Default volumes
            if volumes is None:
                volumes = {
                    'postgres_data': '/var/lib/postgresql/data',
                    './config/postgresql.conf': '/etc/postgresql/postgresql.conf:ro'
                }

            # Default environment
            if environment is None:
                environment = {
                    'POSTGRES_DB': postgres_db,
                    'POSTGRES_USER': postgres_user,
                    'POSTGRES_PASSWORD': postgres_password
                }

            # Create container
            container = self.docker_client.containers.run(
                'postgres:15-alpine',
                name=container_name,
                environment=environment,
                ports={5432: port},
                volumes=volumes,
                detach=True,
                restart_policy={'Name': 'unless-stopped'},
                healthcheck={
                    'test': ['CMD-SHELL', 'pg_isready -U postgres -d production'],
                    'interval': 30000000000,  # 30 seconds in nanoseconds
                    'timeout': 10000000000,   # 10 seconds in nanoseconds
                    'retries': 3,
                    'start_period': 40000000000  # 40 seconds in nanoseconds
                }
            )

            self.logger.info(f"PostgreSQL container {container_name} created successfully")
            return container

        except Exception as e:
            self.logger.error(f"Error creating PostgreSQL container: {e}")
            raise

    def start_container(self, container_name: str):
        """Start container."""
        try:
            container = self.docker_client.containers.get(container_name)
            container.start()
            self.logger.info(f"Container {container_name} started")

        except Exception as e:
            self.logger.error(f"Error starting container {container_name}: {e}")
            raise

    def stop_container(self, container_name: str):
        """Stop container."""
        try:
            container = self.docker_client.containers.get(container_name)
            container.stop()
            self.logger.info(f"Container {container_name} stopped")

        except Exception as e:
            self.logger.error(f"Error stopping container {container_name}: {e}")
            raise

    def restart_container(self, container_name: str):
        """Restart container."""
        try:
            container = self.docker_client.containers.get(container_name)
            container.restart()
            self.logger.info(f"Container {container_name} restarted")

        except Exception as e:
            self.logger.error(f"Error restarting container {container_name}: {e}")
            raise

    def get_container_status(self, container_name: str):
        """Get container status."""
        try:
            container = self.docker_client.containers.get(container_name)
            status = container.status
            health = container.attrs.get('State', {}).get('Health', {}).get('Status', 'unknown')

            return {
                'status': status,
                'health': health,
                'created': container.attrs['Created'],
                'started_at': container.attrs['State'].get('StartedAt', None)
            }

        except Exception as e:
            self.logger.error(f"Error getting container status: {e}")
            return None

    def get_container_metrics(self, container_name: str):
        """Get container metrics."""
        try:
            container = self.docker_client.containers.get(container_name)
            stats = container.stats(stream=False)

            # Calculate CPU usage
            cpu_delta = stats['cpu_stats']['cpu_usage']['total_usage'] - stats['precpu_stats']['cpu_usage']['total_usage']
            system_delta = stats['cpu_stats']['system_cpu_usage'] - stats['precpu_stats']['system_cpu_usage']
            cpu_usage = (cpu_delta / system_delta) * 100.0 if system_delta > 0 else 0

            # Calculate memory usage
            memory_usage = stats['memory_stats']['usage']
            memory_limit = stats['memory_stats']['limit']
            memory_percentage = (memory_usage / memory_limit) * 100.0 if memory_limit > 0 else 0

            # Calculate disk usage
            disk_usage = 0
            for device in stats['blkio_stats']['io_service_bytes_recursive']:
                if device['op'] == 'Read':
                    disk_usage += device['value']
                elif device['op'] == 'Write':
                    disk_usage += device['value']

            # Calculate network I/O
            network_io = 0
            for network in stats['networks'].values():
                network_io += network['rx_bytes'] + network['tx_bytes']

            return {
                'cpu_usage': cpu_usage,
                'memory_usage': memory_percentage,
                'disk_usage': disk_usage,
                'network_io': network_io
            }

        except Exception as e:
            self.logger.error(f"Error getting container metrics: {e}")
            return None

    def record_container_health(self, container_name: str, health_status: str, 
                               metrics: Dict[str, float]):
        """Record container health in database."""
        conn = self.get_connection()

        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT record_container_health(%s, %s, %s, %s, %s, %s, %s)
                """, (
                    container_name, container_name, health_status,
                    metrics.get('cpu_usage'), metrics.get('memory_usage'),
                    metrics.get('disk_usage'), metrics.get('network_io')
                ))

                conn.commit()
                self.logger.info(f"Container health recorded for {container_name}")

        except Exception as e:
            conn.rollback()
            self.logger.error(f"Error recording container health: {e}")
            raise
        finally:
            conn.close()

    def monitor_containers(self, container_names: List[str]):
        """Monitor container health and metrics."""
        for container_name in container_names:
            try:
                # Get container status
                status = self.get_container_status(container_name)
                if not status:
                    continue

                # Get container metrics
                metrics = self.get_container_metrics(container_name)
                if not metrics:
                    continue

                # Record health
                self.record_container_health(container_name, status['health'], metrics)

            except Exception as e:
                self.logger.error(f"Error monitoring container {container_name}: {e}")

    def get_container_health_report(self, container_name: str, 
                                   start_date: datetime = None, 
                                   end_date: datetime = None):
        """Get container health report."""
        conn = self.get_connection()

        try:
            with conn.cursor() as cur:
                cur.execute("""
                    SELECT * FROM get_container_health_report(%s, %s, %s)
                """, (container_name, start_date, end_date))

                report = cur.fetchone()
                if report:
                    return {
                        'container_name': report[0],
                        'avg_cpu_usage': report[1],
                        'avg_memory_usage': report[2],
                        'avg_disk_usage': report[3],
                        'avg_network_io': report[4],
                        'health_status_count': report[5]
                    }
                return None

        except Exception as e:
            self.logger.error(f"Error getting container health report: {e}")
            return None
        finally:
            conn.close()

# Usage
if __name__ == "__main__":
    manager = PostgreSQLContainerManager({
        'host': 'localhost',
        'database': 'production',
        'user': 'container_manager_user',
        'password': 'container_manager_password'
    })

    # Create PostgreSQL container
    container = manager.create_postgres_container(
        'postgres-production',
        'secure_password',
        'production',
        'postgres',
        5432
    )

    # Monitor containers
    manager.monitor_containers(['postgres-production'])

    print("PostgreSQL containerization setup complete")

# Complete PostgreSQL containerization setup
def setup_postgresql_containerization():
    # 1. Docker configuration
    # 2. Container security
    # 3. Container orchestration
    # 4. Container monitoring
    # 5. Health checks
    # 6. Resource management
    # 7. Network security
    # 8. Performance optimization

    print("PostgreSQL containerization setup complete!")