Automated PostgreSQL Installation with Vagrant: Complete Setup Guide

# Install VirtualBox (Ubuntu/Debian)
sudo apt update
sudo apt install virtualbox virtualbox-ext-pack

# Install Vagrant
wget -O- https://apt.releases.hashicorp.com/gpg | gpg --dearmor | sudo tee /usr/share/keyrings/hashicorp-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com hashicorp main" | sudo tee /etc/apt/sources.list.d/hashicorp.list
sudo apt update && sudo apt install vagrant

# Verify installations
vagrant --version
vboxmanage --version

mkdir postgresql-vagrant
cd postgresql-vagrant
mkdir -p scripts configs backups

# -*- mode: ruby -*-
# vi: set ft=ruby :

Vagrant.configure("2") do |config|
  # PostgreSQL Server Configuration
  config.vm.define "postgres-server" do |postgres|
    postgres.vm.box = "ubuntu/focal64"
    postgres.vm.hostname = "postgres-server"
    postgres.vm.network "private_network", ip: "192.168.56.10"
    
    # Port forwarding for PostgreSQL
    postgres.vm.network "forwarded_port", guest: 5432, host: 5432, auto_correct: true
    postgres.vm.network "forwarded_port", guest: 8080, host: 8080, auto_correct: true # pgAdmin
    
    postgres.vm.provider "virtualbox" do |vb|
      vb.name = "postgres-server"
      vb.memory = "2048"
      vb.cpus = 2
      vb.customize ["modifyvm", :id, "--natdnshostresolver1", "on"]
    end
    
    # Sync configuration files
    postgres.vm.synced_folder "./configs", "/vagrant/configs"
    postgres.vm.synced_folder "./scripts", "/vagrant/scripts"
    postgres.vm.synced_folder "./backups", "/vagrant/backups"
    
    # Provisioning
    postgres.vm.provision "shell", path: "scripts/install-postgresql.sh"
    postgres.vm.provision "shell", path: "scripts/configure-postgresql.sh"
  end
end

#!/bin/bash
set -e

echo "=== Starting PostgreSQL Installation ==="

# Update system
export DEBIAN_FRONTEND=noninteractive
apt-get update -y
apt-get upgrade -y

# Install required packages
apt-get install -y \
    wget \
    ca-certificates \
    software-properties-common \
    curl \
    gnupg \
    lsb-release \
    apt-transport-https

# Add PostgreSQL official repository
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

# Update package list
apt-get update -y

# Install PostgreSQL 15 and contrib packages
apt-get install -y \
    postgresql-15 \
    postgresql-contrib-15 \
    postgresql-client-15 \
    postgresql-server-dev-15

# Install additional tools
apt-get install -y \
    postgresql-15-postgis-3 \
    postgresql-15-pgaudit \
    postgresql-15-pg-stat-statements \
    postgresql-15-repmgr \
    pgbouncer \
    htop \
    iotop \
    vim \
    git

# Install Python tools for automation
apt-get install -y \
    python3-pip \
    python3-psycopg2 \
    python3-dev

pip3 install pgcli

# Create postgres user directories
mkdir -p /var/lib/postgresql/15/main/conf.d
chown postgres:postgres /var/lib/postgresql/15/main/conf.d

echo "=== PostgreSQL Installation Complete ==="

#!/bin/bash
set -e

echo "=== Starting PostgreSQL Configuration ==="

# PostgreSQL version and paths
PG_VERSION="15"
PG_CONFIG_DIR="/etc/postgresql/$PG_VERSION/main"
PG_DATA_DIR="/var/lib/postgresql/$PG_VERSION/main"
PG_LOG_DIR="/var/log/postgresql"

# Stop PostgreSQL for configuration
systemctl stop postgresql

# Backup original configuration
cp "$PG_CONFIG_DIR/postgresql.conf" "$PG_CONFIG_DIR/postgresql.conf.backup"
cp "$PG_CONFIG_DIR/pg_hba.conf" "$PG_CONFIG_DIR/pg_hba.conf.backup"

# Apply custom PostgreSQL configuration
cat > "$PG_CONFIG_DIR/postgresql.conf" << 'EOF'
# PostgreSQL configuration optimized for development/testing

#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
data_directory = '/var/lib/postgresql/15/main'
hba_file = '/etc/postgresql/15/main/pg_hba.conf'
ident_file = '/etc/postgresql/15/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/15-main.pid'

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'
port = 5432
max_connections = 200
superuser_reserved_connections = 3

# Authentication
authentication_timeout = 1min
password_encryption = scram-sha-256

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
shared_buffers = 256MB
huge_pages = try
temp_buffers = 8MB
max_prepared_transactions = 0
work_mem = 4MB
hash_mem_multiplier = 1.0
maintenance_work_mem = 64MB
autovacuum_work_mem = -1
max_stack_depth = 2MB
shared_memory_type = mmap
dynamic_shared_memory_type = posix

#------------------------------------------------------------------------------
# WRITE-AHEAD LOG
#------------------------------------------------------------------------------
wal_level = replica
fsync = on
synchronous_commit = on
wal_sync_method = fsync
full_page_writes = on
wal_compression = off
wal_log_hints = off
wal_buffers = 16MB
wal_writer_delay = 200ms
wal_writer_flush_after = 1MB

# Checkpoints
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
checkpoint_flush_after = 256kB
checkpoint_warning = 30s

# Archiving
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/15/archive/%f && cp %p /var/lib/postgresql/15/archive/%f'

#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------
max_wal_senders = 10
max_replication_slots = 10
wal_keep_size = 0
max_slot_wal_keep_size = -1
wal_sender_timeout = 60s
track_commit_timestamp = off

#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on
enable_indexonlyscan = on
enable_material = on
enable_mergejoin = on
enable_nestloop = on
enable_parallel_append = on
enable_parallel_hash = on
enable_partition_pruning = on
enable_partitionwise_join = off
enable_partitionwise_aggregate = off
enable_seqscan = on
enable_sort = on
enable_tidscan = on

# Planner cost constants
seq_page_cost = 1.0
random_page_cost = 4.0
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
parallel_tuple_cost = 0.1
parallel_setup_cost = 1000.0
jit_above_cost = 100000
jit_inline_above_cost = 500000
jit_optimize_above_cost = 500000

effective_cache_size = 1GB

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 10MB
log_truncate_on_rotation = off

# When to log
log_min_messages = info
log_min_error_statement = error
log_min_duration_statement = 1000

# What to log
debug_print_parse = off
debug_print_rewritten = off
debug_print_plan = off
debug_pretty_print = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = off
log_error_verbosity = default
log_hostname = off
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = off
log_parameter_max_length = -1
log_parameter_max_length_on_error = 0
log_statement = 'none'
log_replication_commands = off
log_temp_files = -1
log_timezone = 'UTC'

#------------------------------------------------------------------------------
# PROCESS TITLE
#------------------------------------------------------------------------------
cluster_name = 'vagrant-postgres'
update_process_title = on

#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------
track_activities = on
track_activity_query_size = 1024
track_counts = on
track_io_timing = on
track_functions = none
track_wal_io_timing = off
stats_temp_directory = '/var/run/postgresql/15-main.pg_stat_tmp'

#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------
autovacuum = on
log_autovacuum_min_duration = -1
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_insert_threshold = 1000
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_insert_scale_factor = 0.2
autovacuum_analyze_scale_factor = 0.1
autovacuum_freeze_max_age = 200000000
autovacuum_multixact_freeze_max_age = 400000000
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = -1

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
search_path = '"$user", public'
row_security = on
default_table_access_method = 'heap'
default_tablespace = ''
temp_tablespaces = ''
default_toast_compression = 'pglz'
check_function_bodies = on
default_transaction_isolation = 'read committed'
default_transaction_read_only = off
default_transaction_deferrable = off
session_replication_role = 'origin'
statement_timeout = 0
lock_timeout = 0
idle_in_transaction_session_timeout = 0
vacuum_freeze_min_age = 50000000
vacuum_freeze_table_age = 150000000
vacuum_multixact_freeze_min_age = 5000000
vacuum_multixact_freeze_table_age = 150000000
vacuum_cleanup_index_scale_factor = 0.1
bytea_output = 'hex'
xmlbinary = 'base64'
xmloption = 'content'
gin_pending_list_limit = 4MB
createrole_self_grant = ''

#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------
deadlock_timeout = 1s
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
max_pred_locks_per_relation = -2
max_pred_locks_per_page = 2

#------------------------------------------------------------------------------
# VERSION AND PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------
array_nulls = on
backslash_quote = safe_encoding
escape_string_warning = on
lo_compat_privileges = off
operator_precedence_warning = off
quote_all_identifiers = off
standard_conforming_strings = on
synchronize_seqscans = on

#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------
exit_on_error = off
restart_after_crash = on

#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------
include_dir = 'conf.d'

#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
shared_preload_libraries = 'pg_stat_statements'

# pg_stat_statements
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = off
pg_stat_statements.save = on
EOF

# Configure pg_hba.conf for authentication
cat > "$PG_CONFIG_DIR/pg_hba.conf" << 'EOF'
# PostgreSQL Client Authentication Configuration File

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.56.0/24         md5
host    all             all             10.0.2.0/24             md5

# IPv6 local connections:
host    all             all             ::1/128                 md5

# Allow replication connections from localhost
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             192.168.56.0/24         md5
host    replication     all             ::1/128                 md5
EOF

# Create archive directory
mkdir -p /var/lib/postgresql/15/archive
chown postgres:postgres /var/lib/postgresql/15/archive

# Create conf.d directory for additional configurations
mkdir -p "$PG_CONFIG_DIR/conf.d"

# Set proper permissions
chown postgres:postgres "$PG_CONFIG_DIR/postgresql.conf"
chown postgres:postgres "$PG_CONFIG_DIR/pg_hba.conf"
chmod 600 "$PG_CONFIG_DIR/postgresql.conf"
chmod 600 "$PG_CONFIG_DIR/pg_hba.conf"

# Enable and start PostgreSQL
systemctl enable postgresql
systemctl start postgresql

# Wait for PostgreSQL to start
sleep 5

# Set up postgres user password
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres123';"

# Create sample database and user
sudo -u postgres createdb sampledb
sudo -u postgres psql -c "CREATE USER appuser WITH PASSWORD 'appuser123';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE sampledb TO appuser;"

# Install pg_stat_statements extension
sudo -u postgres psql -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
sudo -u postgres psql -d sampledb -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

# Create monitoring user
sudo -u postgres psql -c "CREATE USER monitor WITH PASSWORD 'monitor123';"
sudo -u postgres psql -c "GRANT CONNECT ON DATABASE postgres TO monitor;"
sudo -u postgres psql -c "GRANT pg_monitor TO monitor;"

echo "=== PostgreSQL Configuration Complete ==="

# Display connection information
echo "=== Connection Information ==="
echo "PostgreSQL is running on:"
echo "  Host: 192.168.56.10 (or localhost)"
echo "  Port: 5432"
echo "  Superuser: postgres / postgres123"
echo "  Application User: appuser / appuser123"
echo "  Monitor User: monitor / monitor123"
echo "  Sample Database: sampledb"
echo ""
echo "Connect using: psql -h 192.168.56.10 -U postgres -d postgres"

# -*- mode: ruby -*-
# vi: set ft=ruby :

# PostgreSQL Multi-Node Cluster Configuration
Vagrant.configure("2") do |config|
  # Cluster configuration
  NODES = {
    "postgres-master" => { ip: "192.168.56.10", memory: 2048, cpus: 2, role: "master" },
    "postgres-slave1" => { ip: "192.168.56.11", memory: 1536, cpus: 2, role: "slave" },
    "postgres-slave2" => { ip: "192.168.56.12", memory: 1536, cpus: 2, role: "slave" }
  }
  
  # Base configuration
  config.vm.box = "ubuntu/focal64"
  config.vm.synced_folder "./configs", "/vagrant/configs"
  config.vm.synced_folder "./scripts", "/vagrant/scripts"
  
  NODES.each do |name, conf|
    config.vm.define name do |node|
      node.vm.hostname = name
      node.vm.network "private_network", ip: conf[:ip]
      
      # Port forwarding for master node
      if conf[:role] == "master"
        node.vm.network "forwarded_port", guest: 5432, host: 5432
        node.vm.network "forwarded_port", guest: 8080, host: 8080
      end
      
      node.vm.provider "virtualbox" do |vb|
        vb.name = name
        vb.memory = conf[:memory]
        vb.cpus = conf[:cpus]
        vb.customize ["modifyvm", :id, "--natdnshostresolver1", "on"]
      end
      
      # Common provisioning
      node.vm.provision "shell", path: "scripts/install-postgresql.sh"
      
      # Role-specific provisioning
      if conf[:role] == "master"
        node.vm.provision "shell", path: "scripts/setup-master.sh"
      else
        node.vm.provision "shell", path: "scripts/setup-slave.sh", 
                         args: [conf[:ip], "192.168.56.10"]
      end
    end
  end
end

#!/bin/bash
set -e

echo "=== Setting up PostgreSQL Master Node ==="

# Source common configuration
source /vagrant/scripts/configure-postgresql.sh

# Additional master-specific configuration
cat >> /etc/postgresql/15/main/postgresql.conf << 'EOF'

# Master-specific settings
wal_level = replica
max_wal_senders = 5
max_replication_slots = 5
synchronous_standby_names = 'slave1,slave2'
synchronous_commit = on
EOF

# Update pg_hba.conf for replication
cat >> /etc/postgresql/15/main/pg_hba.conf << 'EOF'

# Replication connections
host    replication     replicator      192.168.56.0/24         md5
EOF

# Restart PostgreSQL
systemctl restart postgresql
sleep 5

# Create replication user
sudo -u postgres psql -c "CREATE USER replicator WITH REPLICATION PASSWORD 'replicator123';"

# Create replication slots for slaves
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('slave1');"
sudo -u postgres psql -c "SELECT pg_create_physical_replication_slot('slave2');"

# Set up sample data for testing
sudo -u postgres psql -d sampledb << 'EOF'
CREATE TABLE test_replication (
    id SERIAL PRIMARY KEY,
    data TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO test_replication (data) VALUES 
('Initial data from master'),
('Test replication data'),
('More test data');
EOF

echo "=== Master Node Setup Complete ==="
echo "Replication user: replicator / replicator123"
echo "Replication slots created: slave1, slave2"

#!/bin/bash
set -e

SLAVE_IP=$1
MASTER_IP=$2
SLAVE_NAME=$(hostname | sed 's/postgres-//')

echo "=== Setting up PostgreSQL Slave Node: $SLAVE_NAME ==="
echo "Slave IP: $SLAVE_IP, Master IP: $MASTER_IP"

# Stop PostgreSQL
systemctl stop postgresql

# Remove existing data directory
rm -rf /var/lib/postgresql/15/main/*

# Create base backup from master
sudo -u postgres pg_basebackup \
    -h $MASTER_IP \
    -D /var/lib/postgresql/15/main \
    -U replicator \
    -W \
    -v \
    -P \
    -R \
    -X stream \
    -C \
    -S $SLAVE_NAME << 'EOF'
replicator123
EOF

# Create recovery configuration
cat > /var/lib/postgresql/15/main/postgresql.auto.conf << EOF
# Automatically generated by pg_basebackup
primary_conninfo = 'user=replicator password=replicator123 channel_binding=prefer host=$MASTER_IP port=5432 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = '$SLAVE_NAME'
EOF

# Create standby.signal file
touch /var/lib/postgresql/15/main/standby.signal

# Set permissions
chown -R postgres:postgres /var/lib/postgresql/15/main/
chmod 700 /var/lib/postgresql/15/main/

# Update slave-specific configuration
cat >> /etc/postgresql/15/main/postgresql.conf << 'EOF'

# Slave-specific settings
hot_standby = on
hot_standby_feedback = on
max_standby_streaming_delay = 30s
max_standby_archive_delay = 30s
wal_receiver_status_interval = 10s
wal_receiver_timeout = 60s
EOF

# Start PostgreSQL
systemctl start postgresql

# Wait for startup
sleep 10

# Verify replication status
echo "=== Checking Replication Status ==="
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"

echo "=== Slave Node Setup Complete ==="

#!/bin/bash
set -e

NODE_ROLE=$1  # master, slave1, slave2
NODE_ID=$2    # 1, 2, 3

echo "=== Setting up repmgr for High Availability ==="

# Install repmgr
apt-get install -y postgresql-15-repmgr

# Create repmgr user and database
if [ "$NODE_ROLE" = "master" ]; then
    sudo -u postgres createuser -s repmgr
    sudo -u postgres createdb repmgr -O repmgr
    sudo -u postgres psql -c "ALTER USER repmgr SET search_path TO repmgr, public;"
fi

# Configure repmgr
cat > /etc/repmgr.conf << EOF
node_id=$NODE_ID
node_name='$NODE_ROLE'
conninfo='host=$(hostname -I | awk '{print $2}') user=repmgr dbname=repmgr connect_timeout=2'
data_directory='/var/lib/postgresql/15/main'
config_directory='/etc/postgresql/15/main'
replication_user='replicator'

# Failover settings
failover=automatic
promote_command='repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

# Logging
log_level=INFO
log_facility=STDERR
log_file='/var/log/repmgr.log'

# Event notification
event_notification_command='/vagrant/scripts/repmgr-event-handler.sh %n %e %s "%t" "%d"'

# Connection settings
reconnect_attempts=6
reconnect_interval=10
master_response_timeout=60
EOF

# Set permissions
chown postgres:postgres /etc/repmgr.conf
chmod 600 /etc/repmgr.conf

# Register node with repmgr
if [ "$NODE_ROLE" = "master" ]; then
    sudo -u postgres repmgr primary register
else
    sudo -u postgres repmgr standby register
fi

echo "=== repmgr setup complete for $NODE_ROLE ==="

#!/bin/bash

NODE_ID=$1
EVENT=$2
SUCCESS=$3
TIME=$4
DETAILS=$5

echo "$(date): repmgr event - Node: $NODE_ID, Event: $EVENT, Success: $SUCCESS, Time: $TIME, Details: $DETAILS" >> /var/log/repmgr-events.log

# Send notifications based on event type
case $EVENT in
    "primary_register")
        echo "Primary node $NODE_ID registered at $TIME"
        ;;
    "standby_register")
        echo "Standby node $NODE_ID registered at $TIME"
        ;;
    "failover_promote")
        echo "CRITICAL: Failover promoted node $NODE_ID to primary at $TIME"
        # Add email notification here
        ;;
    "standby_follow")
        echo "Standby node $NODE_ID following new primary at $TIME"
        ;;
esac

#!/bin/bash
set -e

echo "=== Setting up PostgreSQL Monitoring ==="

# Install monitoring tools
apt-get install -y python3-pip prometheus-node-exporter
pip3 install psutil pg_activity

# Install postgres_exporter
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.11.1/postgres_exporter-0.11.1.linux-amd64.tar.gz
tar xzf postgres_exporter-0.11.1.linux-amd64.tar.gz
mv postgres_exporter-0.11.1.linux-amd64/postgres_exporter /usr/local/bin/
rm -rf postgres_exporter-0.11.1*

# Create postgres_exporter user
sudo -u postgres psql -c "CREATE USER postgres_exporter PASSWORD 'exporter123';"
sudo -u postgres psql -c "GRANT CONNECT ON DATABASE postgres TO postgres_exporter;"
sudo -u postgres psql -c "GRANT pg_monitor TO postgres_exporter;"

# Configure postgres_exporter
cat > /etc/default/postgres_exporter << 'EOF'
DATA_SOURCE_NAME="postgresql://postgres_exporter:exporter123@localhost:5432/postgres?sslmode=disable"
EOF

# Create systemd service for postgres_exporter
cat > /etc/systemd/system/postgres_exporter.service << 'EOF'
[Unit]
Description=PostgreSQL Exporter
After=network.target

[Service]
Type=simple
User=postgres
EnvironmentFile=/etc/default/postgres_exporter
ExecStart=/usr/local/bin/postgres_exporter
Restart=always

[Install]
WantedBy=multi-user.target
EOF

# Enable and start services
systemctl daemon-reload
systemctl enable postgres_exporter
systemctl start postgres_exporter
systemctl enable prometheus-node-exporter
systemctl start prometheus-node-exporter

# Create monitoring database and tables
sudo -u postgres psql << 'EOF'
CREATE DATABASE monitoring;
\c monitoring;

CREATE TABLE pg_stat_activity_history (
    recorded_at TIMESTAMP DEFAULT NOW(),
    datname TEXT,
    usename TEXT,
    application_name TEXT,
    client_addr INET,
    state TEXT,
    query TEXT
);

CREATE TABLE pg_stat_database_history (
    recorded_at TIMESTAMP DEFAULT NOW(),
    datname TEXT,
    numbackends INTEGER,
    xact_commit BIGINT,
    xact_rollback BIGINT,
    blks_read BIGINT,
    blks_hit BIGINT,
    tup_returned BIGINT,
    tup_fetched BIGINT,
    tup_inserted BIGINT,
    tup_updated BIGINT,
    tup_deleted BIGINT
);
EOF

echo "=== Monitoring Setup Complete ==="
echo "postgres_exporter running on port 9187"
echo "node_exporter running on port 9100"

#!/bin/bash
set -e

echo "=== Setting up PostgreSQL Backup System ==="

# Create backup directories
mkdir -p /var/backups/postgresql/{full,incremental,wal}
chown -R postgres:postgres /var/backups/postgresql

# Install backup tools
apt-get install -y postgresql-client-common barman-cli

# Create backup script
cat > /usr/local/bin/postgres-backup.sh << 'EOF'
#!/bin/bash

BACKUP_DIR="/var/backups/postgresql"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

# Function to log messages
log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S'): $1" | tee -a /var/log/postgres-backup.log
}

# Full backup function
full_backup() {
    log_message "Starting full backup"
    
    sudo -u postgres pg_basebackup \
        -D "$BACKUP_DIR/full/backup_$TIMESTAMP" \
        -Ft \
        -z \
        -P \
        -v \
        --wal-method=stream
    
    if [ $? -eq 0 ]; then
        log_message "Full backup completed successfully"
        
        # Create latest symlink
        rm -f "$BACKUP_DIR/full/latest"
        ln -s "backup_$TIMESTAMP" "$BACKUP_DIR/full/latest"
        
        # Cleanup old backups
        find "$BACKUP_DIR/full" -maxdepth 1 -type d -name "backup_*" -mtime +$RETENTION_DAYS -exec rm -rf {} \;
        
        return 0
    else
        log_message "Full backup failed"
        return 1
    fi
}

# Logical backup function
logical_backup() {
    log_message "Starting logical backup"
    
    sudo -u postgres pg_dumpall \
        --clean \
        --if-exists \
        --verbose | gzip > "$BACKUP_DIR/full/logical_backup_$TIMESTAMP.sql.gz"
    
    if [ $? -eq 0 ]; then
        log_message "Logical backup completed successfully"
        
        # Cleanup old logical backups
        find "$BACKUP_DIR/full" -name "logical_backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete
        
        return 0
    else
        log_message "Logical backup failed"
        return 1
    fi
}

# Main execution
case "${1:-full}" in
    "full")
        full_backup
        ;;
    "logical")
        logical_backup
        ;;
    *)
        echo "Usage: $0 {full|logical}"
        exit 1
        ;;
esac
EOF

chmod +x /usr/local/bin/postgres-backup.sh

# Set up cron jobs for automated backups
cat > /etc/cron.d/postgres-backup << 'EOF'
# PostgreSQL Backup Schedule
0 2 * * 0 postgres /usr/local/bin/postgres-backup.sh full > /dev/null 2>&1
0 3 * * 1-6 postgres /usr/local/bin/postgres-backup.sh logical > /dev/null 2>&1
EOF

echo "=== Backup Setup Complete ==="
echo "Full backup: Sundays at 2:00 AM"
echo "Logical backup: Monday-Saturday at 3:00 AM"
echo "Manual backup: /usr/local/bin/postgres-backup.sh {full|logical}"

#!/bin/bash
set -e

echo "=== Starting PostgreSQL Performance Optimization ==="

# Get system information
TOTAL_MEM=$(free -m | awk 'NR==2{printf "%.0f", $2}')
CPU_CORES=$(nproc)

# Calculate optimal settings
SHARED_BUFFERS=$((TOTAL_MEM / 4))
EFFECTIVE_CACHE_SIZE=$((TOTAL_MEM * 3 / 4))
WORK_MEM=$((TOTAL_MEM / 32))
MAINTENANCE_WORK_MEM=$((TOTAL_MEM / 16))

echo "System Information:"
echo "  Total Memory: ${TOTAL_MEM}MB"
echo "  CPU Cores: $CPU_CORES"
echo "  Calculated Settings:"
echo "    shared_buffers: ${SHARED_BUFFERS}MB"
echo "    effective_cache_size: ${EFFECTIVE_CACHE_SIZE}MB"
echo "    work_mem: ${WORK_MEM}MB"
echo "    maintenance_work_mem: ${MAINTENANCE_WORK_MEM}MB"

# Create performance configuration
cat > /etc/postgresql/15/main/conf.d/performance.conf << EOF
# Performance optimizations based on system resources

# Memory settings
shared_buffers = ${SHARED_BUFFERS}MB
effective_cache_size = ${EFFECTIVE_CACHE_SIZE}MB
work_mem = ${WORK_MEM}MB
maintenance_work_mem = ${MAINTENANCE_WORK_MEM}MB

# Checkpoint settings
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min
max_wal_size = 1GB
min_wal_size = 256MB

# Connection settings
max_connections = 200

# Planner settings
random_page_cost = 1.1  # SSD optimized
effective_io_concurrency = 200

# Worker settings
max_worker_processes = $CPU_CORES
max_parallel_workers = $CPU_CORES
max_parallel_workers_per_gather = $((CPU_CORES / 2))
max_parallel_maintenance_workers = $((CPU_CORES / 2))

# Logging for performance analysis
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_lock_waits = on
log_temp_files = 0

# Autovacuum tuning
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

# Statistics
track_io_timing = on
track_functions = all
EOF

# Restart PostgreSQL to apply changes
systemctl restart postgresql

# Wait for startup
sleep 5

# Create performance monitoring views
sudo -u postgres psql << 'EOF'
-- Create performance monitoring schema
CREATE SCHEMA IF NOT EXISTS monitoring;

-- View for slow queries
CREATE OR REPLACE VIEW monitoring.slow_queries AS
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_time DESC;

-- View for table statistics
CREATE OR REPLACE VIEW monitoring.table_stats AS
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables;

-- View for index usage
CREATE OR REPLACE VIEW monitoring.index_usage AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- View for database statistics
CREATE OR REPLACE VIEW monitoring.database_stats AS
SELECT 
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    round(100.0 * blks_hit / (blks_hit + blks_read), 2) AS cache_hit_ratio,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');
EOF

echo "=== Performance Optimization Complete ==="
echo "Performance views created in monitoring schema"
echo "Check performance: SELECT * FROM monitoring.slow_queries;"

# Check PostgreSQL status
systemctl status postgresql

# Check listening ports
netstat -tlnp | grep 5432

# Test local connection
sudo -u postgres psql -c "SELECT version();"

# Check pg_hba.conf configuration
sudo -u postgres psql -c "SHOW hba_file;"
cat /etc/postgresql/15/main/pg_hba.conf

# Check replication status on master
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

# Check replication lag
sudo -u postgres psql -c "
SELECT 
    client_addr,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (extract(epoch from now()) - extract(epoch from reply_time))::int as lag_seconds
FROM pg_stat_replication;
"

# Check slave status
sudo -u postgres psql -c "SELECT pg_is_in_recovery();"
sudo -u postgres psql -c "SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();"

# Check slow queries
sudo -u postgres psql -c "SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"

# Check locks
sudo -u postgres psql -c "SELECT * FROM pg_locks WHERE NOT granted;"

# Check active connections
sudo -u postgres psql -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

#!/bin/bash

echo "=== PostgreSQL Diagnostic Report ==="
echo "Generated: $(date)"
echo ""

echo "=== System Information ==="
echo "Hostname: $(hostname)"
echo "OS: $(lsb_release -d | cut -f2)"
echo "Memory: $(free -h | awk 'NR==2{print $2}')"
echo "CPU: $(nproc) cores"
echo ""

echo "=== PostgreSQL Status ==="
systemctl is-active postgresql && echo "Service: Active" || echo "Service: Inactive"
sudo -u postgres psql -c "SELECT version();" 2>/dev/null && echo "Database: Accessible" || echo "Database: Not accessible"
echo ""

echo "=== Connection Information ==="
netstat -tlnp | grep 5432
echo ""

echo "=== Database Statistics ==="
sudo -u postgres psql -c "
SELECT 
    datname,
    numbackends as connections,
    xact_commit,
    xact_rollback,
    round(100.0 * blks_hit / (blks_hit + blks_read), 2) as cache_hit_ratio
FROM pg_stat_database 
WHERE datname NOT IN ('template0', 'template1');
" 2>/dev/null
echo ""

echo "=== Replication Status ==="
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;" 2>/dev/null
echo ""

echo "=== Log Tail ==="
tail -20 /var/log/postgresql/postgresql-*.log