Database Management and SQL for DevOps Engineers: A Comprehensive Guide

version: '3.8'
services:
  postgres:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: dbuser
      POSTGRES_PASSWORD: securepassword
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init-scripts:/docker-entrypoint-initdb.d
    ports:
      - "5432:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U dbuser -d myapp"]
      interval: 30s
      timeout: 10s
      retries: 3

volumes:
  postgres_data:

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - name: postgres
        image: postgres:15-alpine
        env:
        - name: POSTGRES_DB
          value: "myapp"
        - name: POSTGRES_USER
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: username
        - name: POSTGRES_PASSWORD
          valueFrom:
            secretKeyRef:
              name: postgres-secret
              key: password
        volumeMounts:
        - name: postgres-storage
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: postgres-storage
    spec:
      accessModes: ["ReadWriteOnce"]
      resources:
        requests:
          storage: 10Gi

resource "aws_db_instance" "main" {
  identifier = "myapp-postgres"
  engine     = "postgres"
  engine_version = "15.4"
  instance_class = "db.t3.micro"
  
  allocated_storage     = 20
  max_allocated_storage = 100
  storage_encrypted     = true
  
  db_name  = "myapp"
  username = var.db_username
  password = var.db_password
  
  vpc_security_group_ids = [aws_security_group.rds.id]
  db_subnet_group_name   = aws_db_subnet_group.main.name
  
  backup_retention_period = 7
  backup_window          = "03:00-04:00"
  maintenance_window     = "sun:04:00-sun:05:00"
  
  skip_final_snapshot = false
  final_snapshot_identifier = "myapp-postgres-final-snapshot"
  
  tags = {
    Name = "myapp-postgres"
    Environment = var.environment
  }
}

-- Connect to database
\c myapp;

-- List databases
\l

-- List tables
\dt

-- Describe table structure
\d table_name

-- Show current connections
SELECT * FROM pg_stat_activity;

-- Database size
SELECT pg_size_pretty(pg_database_size('myapp'));

-- Show databases
SHOW DATABASES;

-- Use database
USE myapp;

-- Show tables
SHOW TABLES;

-- Describe table
DESCRIBE table_name;

-- Show processlist
SHOW PROCESSLIST;

-- Database size
SELECT 
  schema_name AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
WHERE s.schema_name = 'myapp'
GROUP BY s.schema_name;

-- Find slow queries
SELECT 
  query,
  mean_exec_time,
  calls,
  total_exec_time,
  rows
FROM pg_stat_statements 
ORDER BY mean_exec_time DESC 
LIMIT 10;

-- Check for blocking queries
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
JOIN pg_catalog.pg_stat_activity blocking_activity 
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Table sizes
SELECT 
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Without Index: Full Table Scan (SLOW)
SELECT * FROM users WHERE email = 'john@example.com';
-- Database scans ALL rows to find matching email
-- Time Complexity: O(n) where n = total rows

-- With Index on email column: Index Seek (FAST)
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- Database uses index to jump directly to matching row
-- Time Complexity: O(log n) - exponentially faster

-- Table structure
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER,
    order_date TIMESTAMP,
    total_amount DECIMAL(10,2),
    status VARCHAR(20)
);

-- WITHOUT INDEX: Sequential scan through 10M records
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;
/*
Result WITHOUT index:
Seq Scan on orders  (cost=0.00..180000.00 rows=100 width=32) 
                    (actual time=0.123..1247.890 rows=100 loops=1)
Filter: (customer_id = 12345)
Rows Removed by Filter: 9999900
Execution time: 1248.234 ms
*/

-- CREATE INDEX
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- WITH INDEX: Direct lookup
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;
/*
Result WITH index:
Index Scan using idx_orders_customer_id on orders  
                    (cost=0.43..8.45 rows=100 width=32) 
                    (actual time=0.012..0.156 rows=100 loops=1)
Index Cond: (customer_id = 12345)
Execution time: 0.189 ms
*/

-- Best for queries filtering on one column
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_status ON orders(status);

-- Optimizes queries like:
SELECT * FROM users WHERE email = 'user@domain.com';
SELECT COUNT(*) FROM orders WHERE status = 'pending';

-- Best for queries filtering on multiple columns
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Optimizes queries like:
SELECT * FROM orders 
WHERE customer_id = 12345 
  AND order_date >= '2024-01-01';

-- Index column order matters!
-- This index helps with:
-- ✅ WHERE customer_id = X
-- ✅ WHERE customer_id = X AND order_date = Y
-- ❌ WHERE order_date = Y (only) - won't use index efficiently

-- Index only subset of data to save space
CREATE INDEX idx_orders_pending ON orders(customer_id) 
WHERE status = 'pending';

-- Optimizes specific queries:
SELECT * FROM orders 
WHERE customer_id = 12345 AND status = 'pending';

-- Include additional columns in index to avoid table lookup
CREATE INDEX idx_orders_covering ON orders(customer_id) 
INCLUDE (order_date, total_amount, status);

-- This query can be satisfied entirely from the index:
SELECT order_date, total_amount, status 
FROM orders 
WHERE customer_id = 12345;

-- Slow query taking 5+ seconds
SELECT p.*, c.name as category_name, AVG(r.rating) as avg_rating
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.price BETWEEN 100 AND 500
  AND p.in_stock = true
  AND c.name = 'Electronics'
GROUP BY p.id, c.name
HAVING AVG(r.rating) >= 4.0
ORDER BY p.created_at DESC
LIMIT 20;

-- 1. Index for WHERE clause filtering
CREATE INDEX idx_products_price_stock ON products(price, in_stock) 
WHERE in_stock = true;

-- 2. Index for category lookup
CREATE INDEX idx_categories_name ON categories(name);

-- 3. Index for reviews aggregation
CREATE INDEX idx_reviews_product_rating ON reviews(product_id, rating);

-- 4. Index for ORDER BY
CREATE INDEX idx_products_created_at ON products(created_at DESC);

-- 5. Composite index for common filter combinations
CREATE INDEX idx_products_category_price_stock 
ON products(category_id, price, in_stock, created_at DESC) 
WHERE in_stock = true;

SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT 
  schemaname,
  tablename,
  seq_scan,
  seq_tup_read,
  seq_tup_read / seq_scan as avg_tuples_per_scan
FROM pg_stat_user_tables 
WHERE seq_scan > 1000
  AND seq_tup_read / seq_scan > 10000
ORDER BY seq_tup_read DESC;

-- Find tables with high table scans
SELECT 
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_READ / COUNT_STAR as scan_ratio
FROM performance_schema.table_io_waits_summary_by_table 
WHERE COUNT_STAR > 1000
ORDER BY scan_ratio DESC;

-- Show slow queries
SELECT 
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  AVG_TIMER_WAIT/1000000000 as avg_ms,
  SUM_TIMER_WAIT/1000000000 as total_ms
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY AVG_TIMER_WAIT DESC 
LIMIT 10;

-- Check for locked tables
SELECT * FROM information_schema.INNODB_LOCKS;

-- Table sizes
SELECT 
  table_schema AS 'Database',
  table_name AS 'Table',
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

-- Create user
CREATE USER devops_user WITH PASSWORD 'secure_password';

-- Grant privileges
GRANT CONNECT ON DATABASE myapp TO devops_user;
GRANT USAGE ON SCHEMA public TO devops_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO devops_user;

-- Create read-only user
CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE myapp TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- List users and permissions
SELECT 
  u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper THEN 'Superuser' ELSE 'Normal' END AS "Type"
FROM pg_user u;

-- Create user
CREATE USER 'devops_user'@'%' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'devops_user'@'%';

-- Create read-only user
CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'readonly_password';
GRANT SELECT ON myapp.* TO 'readonly_user'@'%';

-- Show user privileges
SHOW GRANTS FOR 'devops_user'@'%';

-- List all users
SELECT User, Host FROM mysql.user;

# Full database backup
pg_dump -h localhost -U dbuser -d myapp > myapp_backup.sql

# Restore database
psql -h localhost -U dbuser -d myapp < myapp_backup.sql

# Custom format backup (compressed)
pg_dump -h localhost -U dbuser -Fc myapp > myapp_backup.dump

# Restore from custom format
pg_restore -h localhost -U dbuser -d myapp myapp_backup.dump

# Full database backup
mysqldump -u dbuser -p myapp > myapp_backup.sql

# Restore database
mysql -u dbuser -p myapp < myapp_backup.sql

# Backup with compression
mysqldump -u dbuser -p myapp | gzip > myapp_backup.sql.gz

# Restore from compressed backup
gunzip < myapp_backup.sql.gz | mysql -u dbuser -p myapp

-- Connect to Oracle
sqlplus username/password@hostname:port/service_name

-- Check tablespace usage
SELECT 
  tablespace_name,
  ROUND(used_mb, 2) used_mb,
  ROUND(free_mb, 2) free_mb,
  ROUND(total_mb, 2) total_mb,
  ROUND((used_mb/total_mb)*100, 2) pct_used
FROM (
  SELECT 
    tablespace_name,
    SUM(bytes)/1024/1024 used_mb
  FROM dba_segments 
  GROUP BY tablespace_name
) used
JOIN (
  SELECT 
    tablespace_name,
    SUM(bytes)/1024/1024 free_mb
  FROM dba_free_space 
  GROUP BY tablespace_name
) free USING (tablespace_name)
JOIN (
  SELECT 
    tablespace_name,
    SUM(bytes)/1024/1024 total_mb
  FROM dba_data_files 
  GROUP BY tablespace_name
) total USING (tablespace_name);

-- Find top SQL statements by execution time
SELECT 
  sql_id,
  executions,
  elapsed_time/1000000 elapsed_seconds,
  cpu_time/1000000 cpu_seconds,
  sql_text
FROM v$sql 
WHERE executions > 0 
ORDER BY elapsed_time DESC 
FETCH FIRST 10 ROWS ONLY;

-- Check database sizes
SELECT 
  name AS 'Database Name',
  size * 8/1024 AS 'Size (MB)'
FROM sys.master_files 
WHERE type = 0;

-- Find blocking processes
SELECT 
  r.session_id,
  r.blocking_session_id,
  s.login_name,
  s.host_name,
  t.text
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id != 0;

-- Check wait statistics
SELECT 
  wait_type,
  waiting_tasks_count,
  wait_time_ms,
  max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC;

-- Check active queries
SELECT 
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query 
FROM pg_stat_activity 
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
AND state = 'active';

-- Kill long-running query
SELECT pg_terminate_backend(pid);

-- Check for missing indexes
SELECT 
  schemaname,
  tablename,
  attname,
  n_distinct,
  correlation
FROM pg_stats
WHERE schemaname = 'public'
AND n_distinct > 100;

-- Show running processes
SHOW FULL PROCESSLIST;

-- Kill problematic query
KILL QUERY process_id;

-- Check for queries without indexes
SELECT 
  SCHEMA_NAME,
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_NO_INDEX_USED,
  SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest 
WHERE SUM_NO_INDEX_USED > 0 
   OR SUM_NO_GOOD_INDEX_USED > 0;

-- Check current connections
SELECT 
  count(*),
  state
FROM pg_stat_activity 
GROUP BY state;

-- Check connection limits
SHOW max_connections;

-- Find idle connections
SELECT 
  pid,
  state,
  query_start,
  now() - query_start AS duration
FROM pg_stat_activity 
WHERE state = 'idle'
ORDER BY query_start;

-- Check connection usage
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

-- Check connection history
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Aborted_connects';

-- PostgreSQL: Check database and table sizes
SELECT 
  pg_database.datname,
  pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database
ORDER BY pg_database_size(pg_database.datname) DESC;

-- MySQL: Check database sizes
SELECT 
  table_schema AS "Database",
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables 
GROUP BY table_schema;

# First, check system resources
top -p $(pgrep postgres)
iostat -x 1 5
free -h

# Check PostgreSQL activity
psql -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

-- Find blocking processes
SELECT 
  blocked_locks.pid AS blocked_pid,
  blocked_activity.usename AS blocked_user,
  blocking_locks.pid AS blocking_pid,
  blocking_activity.usename AS blocking_user,
  blocked_activity.query AS blocked_statement,
  blocking_activity.query_start,
  now() - blocked_activity.query_start AS blocked_duration
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks 
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
  AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

-- Find top CPU and I/O consuming queries
SELECT 
  pid,
  usename,
  application_name,
  client_addr,
  query_start,
  now() - query_start AS duration,
  state,
  wait_event,
  query
FROM pg_stat_activity 
WHERE state = 'active' 
  AND (now() - query_start) > interval '30 seconds'
ORDER BY query_start;

-- This query was scanning entire orders table (10M+ rows)
SELECT o.*, u.email, p.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
JOIN products p ON o.product_id = p.id 
WHERE o.created_at >= '2024-11-25' 
  AND o.status IN ('pending', 'processing')
ORDER BY o.created_at DESC;

-- Kill the long-running blocking queries
SELECT pg_terminate_backend(12345);  -- Replace with actual PID
SELECT pg_terminate_backend(12346);
SELECT pg_terminate_backend(12347);

-- Create indexes concurrently to avoid blocking
CREATE INDEX CONCURRENTLY idx_orders_created_status 
ON orders(created_at, status) 
WHERE status IN ('pending', 'processing');

CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
CREATE INDEX CONCURRENTLY idx_orders_product_id ON orders(product_id);

[databases]
myapp_prod = host=localhost port=5432 dbname=myapp_prod

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_lifetime = 1200
server_idle_timeout = 600

-- Rewritten query with proper indexing
WITH recent_orders AS (
  SELECT o.id, o.user_id, o.product_id, o.created_at, o.status
  FROM orders o 
  WHERE o.created_at >= '2024-11-25' 
    AND o.status IN ('pending', 'processing')
  ORDER BY o.created_at DESC
  LIMIT 1000
)
SELECT ro.*, u.email, p.name 
FROM recent_orders ro
JOIN users u ON ro.user_id = u.id 
JOIN products p ON ro.product_id = p.id 
ORDER BY ro.created_at DESC;

#!/bin/bash
# production_db_monitor.sh

LOG_FILE="/var/log/db_monitor.log"
DB_HOST="prod-db.company.com"
DB_NAME="myapp_prod"
DB_USER="monitor"

monitor_critical_metrics() {
    echo "$(date): Starting database health check" >> $LOG_FILE
    
    # Check connection count
    ACTIVE_CONN=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';")
    TOTAL_CONN=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity;")
    
    echo "Active connections: $ACTIVE_CONN, Total: $TOTAL_CONN" >> $LOG_FILE
    
    # Check for long-running queries
    LONG_QUERIES=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "
        SELECT count(*) FROM pg_stat_activity 
        WHERE (now() - query_start) > interval '5 minutes' 
        AND state = 'active' AND query NOT LIKE '%pg_stat_activity%';")
    
    if [ $LONG_QUERIES -gt 0 ]; then
        echo "ALERT: $LONG_QUERIES long-running queries detected" >> $LOG_FILE
        # Send alert to DevOps team
        curl -X POST "https://hooks.slack.com/services/YOUR/SLACK/WEBHOOK" \
             -H 'Content-type: application/json' \
             --data '{"text":"Database Alert: '"$LONG_QUERIES"' long-running queries detected on production"}'
    fi
    
    # Check slow queries from the last 5 minutes
    SLOW_QUERIES=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "
        SELECT COUNT(*) FROM pg_stat_statements 
        WHERE last_exec > (now() - interval '5 minutes') 
        AND mean_exec_time > 5000;")
    
    if [ $SLOW_QUERIES -gt 10 ]; then
        echo "ALERT: High number of slow queries: $SLOW_QUERIES" >> $LOG_FILE
    fi
    
    # Check database size growth
    DB_SIZE=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "
        SELECT pg_size_pretty(pg_database_size('$DB_NAME'));")
    echo "Database size: $DB_SIZE" >> $LOG_FILE
}

# Run every 2 minutes
while true; do
    monitor_critical_metrics
    sleep 120
done

-- Updated PostgreSQL settings for production workload
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM SET effective_cache_size = '6GB';
ALTER SYSTEM SET work_mem = '256MB';
ALTER SYSTEM SET maintenance_work_mem = '512MB';
ALTER SYSTEM SET max_connections = '300';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

# Prometheus PostgreSQL exporter configuration
version: '3.8'
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:password@postgres:5432/myapp?sslmode=disable"
    ports:
      - "9187:9187"
    deploy:
      replicas: 1

#!/bin/bash
# database_monitor.sh - DevOps database monitoring script

DB_HOST="localhost"
DB_NAME="myapp"
DB_USER="monitor_user"
ALERT_THRESHOLD=80

# PostgreSQL monitoring
monitor_postgres() {
    # Check connection count
    CONN_COUNT=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity;")
    MAX_CONN=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "SHOW max_connections;")
    CONN_PERCENT=$(( CONN_COUNT * 100 / MAX_CONN ))
    
    if [ $CONN_PERCENT -gt $ALERT_THRESHOLD ]; then
        echo "ALERT: Connection usage at ${CONN_PERCENT}% (${CONN_COUNT}/${MAX_CONN})"
    fi
    
    # Check for long-running queries
    LONG_QUERIES=$(psql -h $DB_HOST -U $DB_USER -d $DB_NAME -t -c "SELECT count(*) FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '10 minutes' AND state = 'active';")
    
    if [ $LONG_QUERIES -gt 0 ]; then
        echo "ALERT: $LONG_QUERIES long-running queries detected"
        psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT pid, now() - query_start as duration, query FROM pg_stat_activity WHERE (now() - query_start) > interval '10 minutes' AND state = 'active';"
    fi
}

# MySQL monitoring
monitor_mysql() {
    # Check connection count
    CONN_COUNT=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2{print $2}')
    MAX_CONN=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2{print $2}')
    CONN_PERCENT=$(( CONN_COUNT * 100 / MAX_CONN ))
    
    if [ $CONN_PERCENT -gt $ALERT_THRESHOLD ]; then
        echo "ALERT: Connection usage at ${CONN_PERCENT}% (${CONN_COUNT}/${MAX_CONN})"
    fi
}

# Run monitoring based on database type
case "$1" in
    postgres)
        monitor_postgres
        ;;
    mysql)
        monitor_mysql
        ;;
    *)
        echo "Usage: $0 {postgres|mysql}"
        exit 1
        ;;
esac

-- ❌ NEVER run these in production without extreme caution
DROP DATABASE production_db;           -- Destroys entire database
DROP TABLE orders;                     -- Loses all order data
DROP SCHEMA public CASCADE;            -- Destroys schema and all objects
TRUNCATE TABLE user_payments;          -- Deletes all payment records

-- ✅ ALWAYS follow this checklist before DROP operations:
-- 1. Create full backup
pg_dump -h prod-db -U admin -d myapp > backup_$(date +%Y%m%d_%H%M%S).sql

-- 2. Verify backup integrity
psql -h test-db -U admin -d test_restore < backup_20241128_143022.sql

-- 3. Use transactions (when supported)
BEGIN;
DROP TABLE temp_table;  -- Test on small/temp tables first
-- ROLLBACK;  -- If something goes wrong
COMMIT;  -- Only after verification

-- 4. Production DROP template with safeguards
DO $$
BEGIN
    -- Double-check we're not in production
    IF current_database() = 'production_db' THEN
        RAISE EXCEPTION 'BLOCKED: Cannot drop table in production without additional confirmation';
    END IF;
    
    -- Additional checks
    IF EXISTS (SELECT 1 FROM orders WHERE created_at > NOW() - INTERVAL '1 day') THEN
        RAISE EXCEPTION 'BLOCKED: Recent data detected, manual review required';
    END IF;
    
    DROP TABLE IF EXISTS old_temp_table;
END $$;

-- ❌ These can delete millions of records instantly
DELETE FROM orders;                    -- Deletes ALL orders (revenue loss)
UPDATE users SET password_hash = '';   -- Locks out ALL users
UPDATE products SET price = 0;         -- Makes all products free
DELETE FROM user_sessions;             -- Logs out entire user base

-- Developer intended to delete test data:
-- DELETE FROM orders WHERE environment = 'test';
-- But accidentally ran:
DELETE FROM orders;  -- Deleted 2.3M orders, $50M in revenue tracking lost

-- ✅ ALWAYS use these safety patterns:

-- 1. SELECT before DELETE/UPDATE
SELECT COUNT(*) FROM orders WHERE created_at < '2023-01-01';  -- Check count first
-- Only proceed if count matches expectation

-- 2. Use transactions with verification
BEGIN;
DELETE FROM orders WHERE created_at < '2023-01-01';
SELECT COUNT(*) FROM orders;  -- Verify remaining count
-- ROLLBACK;  -- If count is wrong
COMMIT;  -- Only if verification passes

-- 3. Use LIMIT for large operations
DELETE FROM old_logs WHERE created_at < '2023-01-01' LIMIT 1000;
-- Repeat in batches to avoid locking

-- 4. Implement safeguards
CREATE OR REPLACE FUNCTION safe_delete(table_name TEXT, where_clause TEXT)
RETURNS INTEGER AS $$
DECLARE
    affected_rows INTEGER;
    max_allowed_deletes INTEGER := 1000;  -- Safety limit
BEGIN
    EXECUTE format('DELETE FROM %I WHERE %s', table_name, where_clause);
    GET DIAGNOSTICS affected_rows = ROW_COUNT;
    
    IF affected_rows > max_allowed_deletes THEN
        RAISE EXCEPTION 'SAFETY BLOCK: Attempted to delete % rows, maximum allowed is %', 
                       affected_rows, max_allowed_deletes;
    END IF;
    
    RETURN affected_rows;
END;
$$ LANGUAGE plpgsql;

-- ❌ These can lock tables for hours, bringing down applications
ALTER TABLE orders ADD COLUMN new_field TEXT;           -- Locks 100M row table
CREATE INDEX idx_name ON huge_table(column);            -- Blocks all writes
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(500); -- Rewrites entire table

-- ✅ Use non-blocking approaches

-- 1. Add columns with defaults safely
ALTER TABLE orders ADD COLUMN new_field TEXT DEFAULT NULL;  -- Instant on most DBs

-- 2. Create indexes concurrently (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

-- 3. Schema changes during maintenance windows
-- Schedule during low-traffic periods (2-4 AM)
-- Use pt-online-schema-change for MySQL
pt-online-schema-change --alter "ADD COLUMN new_field TEXT" 
  --execute h=localhost,D=myapp,t=orders

-- 4. Gradual migration pattern
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN email_v2 VARCHAR(500);

-- Step 2: Populate gradually
UPDATE users SET email_v2 = email WHERE id BETWEEN 1 AND 10000;
-- Continue in batches...

-- Step 3: Switch application to use new column
-- Step 4: Drop old column after verification

-- ❌ These can consume all server resources
SELECT * FROM orders o1 CROSS JOIN orders o2;         -- Cartesian product
SELECT * FROM huge_table ORDER BY random();            -- Forces full table sort
WITH RECURSIVE infinite_loop AS (                      -- Infinite recursion
  SELECT 1 as n 
  UNION ALL 
  SELECT n+1 FROM infinite_loop
) SELECT * FROM infinite_loop;

-- ✅ Implement query safeguards

-- 1. Set query timeouts
SET statement_timeout = '30min';  -- PostgreSQL
SET max_execution_time = 1800000; -- MySQL (30 min)

-- 2. Use query governors
-- PostgreSQL: pg_stat_statements + monitoring
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements 
WHERE mean_exec_time > 60000  -- Queries taking > 1 minute
ORDER BY mean_exec_time DESC;

-- 3. Implement connection limits per user
ALTER USER reporting_user CONNECTION LIMIT 5;

-- 4. Use read replicas for analytical queries
-- Route heavy queries to read replicas, not production master

-- ❌ These can compromise entire system security
GRANT ALL PRIVILEGES ON *.* TO 'app_user'@'%';        -- Gives superuser access
GRANT SUPER ON *.* TO 'developer'@'%';                 -- Admin rights to developer
UPDATE mysql.user SET password='' WHERE user='root';   -- Removes root password

-- ✅ Principle of least privilege

-- 1. Application users - minimal permissions
CREATE USER 'app_read'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON myapp.* TO 'app_read'@'%';

CREATE USER 'app_write'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.orders TO 'app_write'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.users TO 'app_write'@'%';
-- Never grant on mysql.* or information_schema.*

-- 2. Monitoring user - read-only access
CREATE USER 'monitor'@'%' IDENTIFIED BY 'strong_password';
GRANT SELECT ON performance_schema.* TO 'monitor'@'%';
GRANT SELECT ON information_schema.* TO 'monitor'@'%';
GRANT PROCESS ON *.* TO 'monitor'@'%';  -- For viewing processes only

#!/bin/bash
# high_risk_db_operation_checklist.sh

echo "🔍 PRE-OPERATION SAFETY CHECKLIST"
echo "================================="

# 1. Verify environment
read -p "Confirm database environment [prod/staging/dev]: " env
if [ "$env" = "prod" ]; then
    echo "⚠️  PRODUCTION ENVIRONMENT DETECTED"
    read -p "Type 'I-UNDERSTAND-THE-RISKS' to continue: " confirmation
    if [ "$confirmation" != "I-UNDERSTAND-THE-RISKS" ]; then
        echo "❌ Operation cancelled for safety"
        exit 1
    fi
fi

# 2. Backup verification
echo "📦 Checking recent backups..."
latest_backup=$(ls -t /backups/*.sql | head -1)
if [ -z "$latest_backup" ]; then
    echo "❌ No recent backup found. Create backup first!"
    exit 1
fi
echo "✅ Latest backup: $latest_backup"

# 3. Test on staging first
read -p "Has this operation been tested on staging? [y/N]: " tested
if [ "$tested" != "y" ]; then
    echo "❌ Test on staging environment first!"
    exit 1
fi

# 4. Rollback plan
read -p "Describe your rollback plan: " rollback_plan
if [ ${#rollback_plan} -lt 10 ]; then
    echo "❌ Rollback plan too short. Provide detailed plan!"
    exit 1
fi

echo "✅ All safety checks passed. Proceed with caution!"
echo "📝 Document this operation in the change log"

-- IMMEDIATE DAMAGE CONTROL

-- 1. Stop application connections
ALTER SYSTEM SET max_connections = 1;  -- PostgreSQL emergency
SELECT pg_reload_conf();

-- 2. Kill all active connections (except admin)
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'myapp' 
  AND usename != 'postgres' 
  AND pid != pg_backend_pid();

-- 3. Enable read-only mode (if supported)
ALTER DATABASE myapp SET default_transaction_read_only = true;

-- 4. Immediate backup of current state
pg_dump -h localhost -U postgres -d myapp > emergency_backup_$(date +%Y%m%d_%H%M%S).sql