🐘 PostgreSQL Database Installation on AlmaLinux: Complete Setup Guide

Ready to harness the power of the world’s most advanced open-source database? 🚀 Today we’ll install and configure PostgreSQL on AlmaLinux - the enterprise-grade database trusted by Apple, Netflix, Reddit, and thousands of organizations worldwide! Whether you’re building web applications, analytics systems, or enterprise software, this guide makes PostgreSQL setup simple and secure! 🎯

🤔 Why is PostgreSQL on AlmaLinux Important?

PostgreSQL on AlmaLinux delivers incredible benefits:

• 📌 Most advanced open-source database - ACID compliance, advanced indexing, and full SQL support
• 🔧 Enterprise-grade reliability - Used by major corporations for mission-critical applications
• 🚀 Powerful features - JSON support, full-text search, geospatial data, and custom data types
• 🔐 Robust security - Row-level security, SSL encryption, and advanced authentication methods
• ⭐ Perfect scalability - Handles terabytes of data with excellent performance optimization

🎯 What You Need

Before installing PostgreSQL on AlmaLinux:

• ✅ AlmaLinux 9 system (server or desktop)
• ✅ Root or sudo access
• ✅ At least 2GB RAM (4GB+ recommended for production)
• ✅ 10GB+ free disk space
• ✅ Basic command line knowledge (we’ll guide you!)

📝 Step 1: Install PostgreSQL Database Server

Let’s get PostgreSQL installed and running! 🛠️

Install PostgreSQL from AlmaLinux Repository

# Update system packages first
sudo dnf update -y

# Install PostgreSQL server and client
sudo dnf install -y postgresql-server postgresql postgresql-contrib

# Install additional PostgreSQL extensions and tools
sudo dnf install -y postgresql-devel postgresql-docs

# Verify PostgreSQL installation
postgres --version
# postgres (PostgreSQL) 13.14

# Check installed packages
dnf list installed | grep postgresql

echo "✅ PostgreSQL installed successfully!"

Install Latest PostgreSQL from Official Repository

# Install PostgreSQL official repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Update package cache
sudo dnf update -y

# List available PostgreSQL versions
sudo dnf module list postgresql

# Install PostgreSQL 16 (latest)
sudo dnf install -y postgresql16-server postgresql16 postgresql16-contrib postgresql16-devel

# Verify latest version
/usr/pgsql-16/bin/postgres --version
# postgres (PostgreSQL) 16.2

echo "✅ Latest PostgreSQL 16 installed from official repository!"

🔧 Step 2: Initialize and Start PostgreSQL

Set up the database cluster and start the service:

Initialize PostgreSQL Database

# Initialize the PostgreSQL database (for default installation)
sudo postgresql-setup --initdb

# For PostgreSQL 16 from official repo
sudo /usr/pgsql-16/bin/postgresql-16-setup initdb

# Start PostgreSQL service
sudo systemctl start postgresql

# Enable PostgreSQL to start at boot
sudo systemctl enable postgresql

# Check PostgreSQL service status
sudo systemctl status postgresql

# Verify PostgreSQL is listening on port 5432
sudo ss -tlnp | grep :5432

echo "✅ PostgreSQL database initialized and running!"

Configure Basic PostgreSQL Settings

# Switch to postgres user (database superuser)
sudo -i -u postgres

# Access PostgreSQL command line
psql

# Inside PostgreSQL, check version and status
SELECT version();
SELECT current_database(), current_user;
\conninfo

# Set password for postgres superuser
ALTER USER postgres PASSWORD 'SecurePostgresPassword123!';

# Create additional superuser for administration
CREATE USER dbadmin WITH SUPERUSER PASSWORD 'AdminPassword123!';

# Exit PostgreSQL
\q

# Exit postgres user session
exit

echo "✅ PostgreSQL basic configuration completed!"

🌟 Step 3: Configure PostgreSQL Security and Access

Set up authentication and network access:

Configure Authentication Methods

# Backup original configuration
sudo cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.backup
sudo cp /var/lib/pgsql/data/postgresql.conf /var/lib/pgsql/data/postgresql.conf.backup

# Configure authentication in pg_hba.conf
sudo tee /var/lib/pgsql/data/pg_hba.conf << 'EOF'
# PostgreSQL Client Authentication Configuration File

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             all                                     peer
local   all             postgres                                peer

# IPv4 local connections
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

# Remote connections (adjust IP range as needed)
host    all             all             192.168.1.0/24          md5

# SSL connections (recommended for remote access)
hostssl all             all             0.0.0.0/0               md5

# Replication connections
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
EOF

# Configure main PostgreSQL settings
sudo tee -a /var/lib/pgsql/data/postgresql.conf << 'EOF'

#------------------------------------------------------------------------------
# CUSTOM CONFIGURATION
#------------------------------------------------------------------------------

# Connection Settings
listen_addresses = 'localhost'          # Change to '*' for remote access
port = 5432
max_connections = 200

# Memory Settings
shared_buffers = 256MB                  # 25% of available RAM
effective_cache_size = 1GB              # 75% of available RAM
work_mem = 4MB                          # Per-query memory
maintenance_work_mem = 64MB

# Write-Ahead Logging (WAL)
wal_buffers = 16MB
checkpoint_completion_target = 0.7
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

# Query Planner
random_page_cost = 1.1                  # SSD optimization
effective_io_concurrency = 200          # SSD optimization

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'all'                   # Change to 'ddl' or 'none' for production
log_duration = on

# Security
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
password_encryption = scram-sha-256

# Statistics
track_activities = on
track_counts = on
track_functions = all
track_io_timing = on

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
EOF

# Restart PostgreSQL to apply changes
sudo systemctl restart postgresql

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

echo "✅ PostgreSQL security and access configured!"

Set Up SSL Certificates (Optional but Recommended)

# Generate self-signed SSL certificate (for testing)
sudo -u postgres openssl req -new -x509 -days 365 -nodes \
    -text -out /var/lib/pgsql/data/server.crt \
    -keyout /var/lib/pgsql/data/server.key \
    -subj "/CN=postgresql.local"

# Set proper permissions
sudo -u postgres chmod 600 /var/lib/pgsql/data/server.key
sudo -u postgres chmod 644 /var/lib/pgsql/data/server.crt

# Test SSL connection
sudo -u postgres psql "sslmode=require" -c "SELECT version();"

echo "✅ SSL certificates configured for secure connections!"

✅ Step 4: Create Databases and Manage Users

Set up application databases and user accounts:

Create Application Database and Users

# Connect to PostgreSQL as postgres user
sudo -u postgres psql

# Inside PostgreSQL, create application database
CREATE DATABASE webapp_db
    WITH ENCODING 'UTF8'
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE = 'en_US.UTF-8'
         TEMPLATE = template0;

# Create application user
CREATE USER webapp_user WITH PASSWORD 'WebAppSecurePass123!';

# Grant privileges to the user
GRANT CONNECT ON DATABASE webapp_db TO webapp_user;
GRANT USAGE ON SCHEMA public TO webapp_user;
GRANT CREATE ON SCHEMA public TO webapp_user;
GRANT ALL PRIVILEGES ON DATABASE webapp_db TO webapp_user;

# Create read-only user for reporting
CREATE USER readonly_user WITH PASSWORD 'ReadOnlyPass123!';
GRANT CONNECT ON DATABASE webapp_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

# Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO readonly_user;

# Show databases and users
\l
\du

# Exit PostgreSQL
\q

echo "✅ Application databases and users created!"

Create Sample Data and Test Connectivity

# Connect to the application database
sudo -u postgres psql -d webapp_db

# Create sample tables and data
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);

-- Insert sample data
INSERT INTO users (username, email, full_name) VALUES
('john_doe', '[email protected]', 'John Doe'),
('jane_smith', '[email protected]', 'Jane Smith'),
('bob_wilson', '[email protected]', 'Bob Wilson');

INSERT INTO posts (user_id, title, content, published) VALUES
(1, 'Getting Started with PostgreSQL', 'PostgreSQL is an amazing database...', TRUE),
(1, 'Advanced SQL Queries', 'Learn about complex joins and subqueries...', TRUE),
(2, 'Database Design Best Practices', 'Proper normalization is crucial...', TRUE),
(3, 'Performance Optimization Tips', 'Indexing strategies for better performance...', FALSE);

-- Test queries
SELECT u.username, u.email, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE p.published = TRUE
GROUP BY u.id, u.username, u.email
ORDER BY post_count DESC;

-- Exit PostgreSQL
\q

# Test connection as application user
PGPASSWORD='WebAppSecurePass123!' psql -h localhost -U webapp_user -d webapp_db -c "SELECT COUNT(*) FROM users;"

echo "✅ Sample data created and connectivity tested!"

🎮 Quick Examples

Example 1: Complete Web Application Database Setup 🌐

# Complete database setup for web applications
echo "=== Web Application Database Setup ==="

sudo -u postgres psql << 'EOF'
-- Create e-commerce database
CREATE DATABASE ecommerce_db
    WITH ENCODING 'UTF8'
         LC_COLLATE = 'en_US.UTF-8'
         LC_CTYPE = 'en_US.UTF-8'
         TEMPLATE = template0;

-- Connect to the database
\c ecommerce_db

-- Create extension for UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Create users and roles
CREATE ROLE ecommerce_app_role;
CREATE USER ecommerce_app WITH PASSWORD 'EcommerceAppPass123!' IN ROLE ecommerce_app_role;

-- Create tables for e-commerce application
CREATE TABLE categories (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id UUID REFERENCES categories(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id UUID REFERENCES categories(id),
    sku VARCHAR(50) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    date_of_birth DATE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
    total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    shipping_address JSONB,
    billing_address JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    total_price DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Create indexes for better performance
CREATE INDEX idx_products_category ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_name ON products USING GIN (to_tsvector('english', name));
CREATE INDEX idx_customers_email ON customers(email);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Grant permissions to application role
GRANT CONNECT ON DATABASE ecommerce_db TO ecommerce_app_role;
GRANT USAGE ON SCHEMA public TO ecommerce_app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO ecommerce_app_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ecommerce_app_role;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ecommerce_app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT USAGE, SELECT ON SEQUENCES TO ecommerce_app_role;

-- Insert sample data
INSERT INTO categories (name, description) VALUES
('Electronics', 'Electronic devices and accessories'),
('Clothing', 'Clothing and fashion items'),
('Books', 'Books and educational materials');

INSERT INTO products (name, description, price, stock_quantity, category_id, sku) VALUES
('Laptop Computer', 'High-performance laptop for professionals', 999.99, 50, (SELECT id FROM categories WHERE name = 'Electronics'), 'LAPTOP-001'),
('Wireless Headphones', 'Noise-cancelling wireless headphones', 199.99, 100, (SELECT id FROM categories WHERE name = 'Electronics'), 'HEADPHONES-001'),
('T-Shirt', 'Comfortable cotton t-shirt', 29.99, 200, (SELECT id FROM categories WHERE name = 'Clothing'), 'TSHIRT-001');

-- Create a sample customer
INSERT INTO customers (email, password_hash, first_name, last_name, phone) VALUES
('[email protected]', crypt('password123', gen_salt('bf')), 'John', 'Customer', '+1-555-0123');

-- Show database structure
\dt
EOF

echo "✅ E-commerce database setup completed!"
echo "Database: ecommerce_db"
echo "Application user: ecommerce_app"
echo "Tables: categories, products, customers, orders, order_items"

Example 2: Database Backup and Restore Strategy 💾

# Complete backup and restore solution
echo "=== PostgreSQL Backup and Restore Strategy ==="

# Create backup directory
sudo mkdir -p /backup/postgresql
sudo chown postgres:postgres /backup/postgresql

# Create backup script
sudo tee /usr/local/bin/postgres-backup.sh << 'EOF'
#!/bin/bash
# PostgreSQL Backup Script

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
POSTGRES_USER="postgres"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Full cluster backup (all databases)
echo "Starting full cluster backup..."
sudo -u postgres pg_dumpall -c > $BACKUP_DIR/full_backup_$DATE.sql

# Individual database backups
echo "Starting individual database backups..."
for db in $(sudo -u postgres psql -lqt | cut -d \| -f 1 | grep -vw template0 | grep -vw template1 | grep -v "^$"); do
    if [ "$db" != " " ]; then
        echo "Backing up database: $db"
        sudo -u postgres pg_dump -Fc $db > $BACKUP_DIR/${db}_backup_$DATE.dump
        sudo -u postgres pg_dump -f $BACKUP_DIR/${db}_backup_$DATE.sql $db
    fi
done

# Compress SQL backups
echo "Compressing backups..."
gzip $BACKUP_DIR/*.sql

# Remove backups older than 7 days
echo "Cleaning up old backups..."
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

# Log backup completion
echo "$(date): PostgreSQL backup completed successfully" >> $BACKUP_DIR/backup.log

# Display backup size
echo "Backup sizes:"
du -sh $BACKUP_DIR/*_$DATE.*

echo "PostgreSQL backup completed: $DATE"
EOF

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

# Create restore script
sudo tee /usr/local/bin/postgres-restore.sh << 'EOF'
#!/bin/bash
# PostgreSQL Restore Script

if [ $# -lt 2 ]; then
    echo "Usage: $0 <backup_file> <database_name> [restore_type]"
    echo "restore_type: sql (default) or dump"
    exit 1
fi

BACKUP_FILE=$1
DATABASE_NAME=$2
RESTORE_TYPE=${3:-sql}

if [ ! -f "$BACKUP_FILE" ]; then
    echo "Error: Backup file $BACKUP_FILE not found"
    exit 1
fi

echo "Restoring database $DATABASE_NAME from $BACKUP_FILE..."

if [ "$RESTORE_TYPE" = "dump" ]; then
    # Restore from custom format dump
    sudo -u postgres pg_restore -d $DATABASE_NAME -v $BACKUP_FILE
elif [ "$RESTORE_TYPE" = "sql" ]; then
    # Restore from SQL file
    if [[ $BACKUP_FILE == *.gz ]]; then
        gunzip -c $BACKUP_FILE | sudo -u postgres psql $DATABASE_NAME
    else
        sudo -u postgres psql $DATABASE_NAME < $BACKUP_FILE
    fi
else
    echo "Error: Unknown restore type $RESTORE_TYPE"
    exit 1
fi

echo "Restore completed for database: $DATABASE_NAME"
EOF

sudo chmod +x /usr/local/bin/postgres-restore.sh

# Test backup script
/usr/local/bin/postgres-backup.sh

# Set up automated backups with cron
echo "0 2 * * * /usr/local/bin/postgres-backup.sh" | sudo crontab -u postgres -

# Create database maintenance script
sudo tee /usr/local/bin/postgres-maintenance.sh << 'EOF'
#!/bin/bash
# PostgreSQL Maintenance Script

echo "Starting PostgreSQL maintenance tasks..."

# Update statistics
echo "Updating database statistics..."
sudo -u postgres psql -c "ANALYZE;"

# Vacuum databases
echo "Vacuuming databases..."
sudo -u postgres vacuumdb --all --analyze --verbose

# Reindex system tables
echo "Reindexing system databases..."
sudo -u postgres reindexdb --system --all

# Check for database issues
echo "Checking database integrity..."
sudo -u postgres pg_checksums --check --verbose

# Log maintenance completion
echo "$(date): PostgreSQL maintenance completed" >> /var/log/postgresql/maintenance.log

echo "PostgreSQL maintenance completed!"
EOF

sudo chmod +x /usr/local/bin/postgres-maintenance.sh

# Set up weekly maintenance
echo "0 3 * * 0 /usr/local/bin/postgres-maintenance.sh" | sudo crontab -u postgres -

echo "✅ Backup and maintenance strategy implemented!"
echo "📋 Manual backup: /usr/local/bin/postgres-backup.sh"
echo "📋 Manual restore: /usr/local/bin/postgres-restore.sh <file> <database>"
echo "📋 Maintenance: /usr/local/bin/postgres-maintenance.sh"

Example 3: Performance Monitoring and Optimization ⚡

# PostgreSQL performance monitoring and optimization
echo "=== PostgreSQL Performance Monitoring Setup ==="

# Install additional extensions for monitoring
sudo -u postgres psql << 'EOF'
-- Enable query statistics tracking
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Enable connection logging
CREATE EXTENSION IF NOT EXISTS pg_stat_activity;

-- Show current configuration
SHOW shared_buffers;
SHOW effective_cache_size;
SHOW work_mem;
SHOW maintenance_work_mem;

-- Create monitoring views
CREATE OR REPLACE VIEW top_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 total_time DESC
LIMIT 10;

CREATE OR REPLACE VIEW database_sizes AS
SELECT 
    datname as database_name,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

CREATE OR REPLACE VIEW table_sizes AS
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_stats
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- Create performance monitoring function
CREATE OR REPLACE FUNCTION get_performance_stats()
RETURNS TABLE(
    metric VARCHAR(50),
    value TEXT
) AS $$
BEGIN
    RETURN QUERY
    SELECT 'Active Connections'::VARCHAR(50), 
           COUNT(*)::TEXT 
    FROM pg_stat_activity 
    WHERE state = 'active'
    
    UNION ALL
    
    SELECT 'Database Size'::VARCHAR(50),
           pg_size_pretty(SUM(pg_database_size(datname))::BIGINT)
    FROM pg_database
    
    UNION ALL
    
    SELECT 'Cache Hit Ratio'::VARCHAR(50),
           ROUND((SUM(blks_hit) / (SUM(blks_hit) + SUM(blks_read)) * 100)::NUMERIC, 2) || '%'
    FROM pg_stat_database
    
    UNION ALL
    
    SELECT 'Transactions per Second'::VARCHAR(50),
           ROUND((SUM(xact_commit + xact_rollback) / EXTRACT(EPOCH FROM (NOW() - stats_reset)))::NUMERIC, 2)::TEXT
    FROM pg_stat_database;
END;
$$ LANGUAGE plpgsql;

\q
EOF

# Create performance monitoring script
sudo tee /usr/local/bin/postgres-monitor.sh << 'EOF'
#!/bin/bash
# PostgreSQL Performance Monitor

echo "=== PostgreSQL Performance Report $(date) ==="
echo

# Database sizes
echo "📊 Database Sizes:"
sudo -u postgres psql -c "SELECT * FROM database_sizes;"
echo

# Top queries by execution time
echo "🚀 Top Queries by Total Time:"
sudo -u postgres psql -c "SELECT * FROM top_queries;"
echo

# Connection statistics
echo "🔗 Connection Statistics:"
sudo -u postgres psql -c "
SELECT 
    state,
    COUNT(*) as connection_count,
    ROUND(AVG(EXTRACT(EPOCH FROM (NOW() - query_start)))::NUMERIC, 2) as avg_duration_seconds
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY state
ORDER BY connection_count DESC;"
echo

# Performance stats
echo "⚡ Performance Metrics:"
sudo -u postgres psql -c "SELECT * FROM get_performance_stats();"
echo

# Lock information
echo "🔒 Current Locks:"
sudo -u postgres psql -c "
SELECT 
    l.mode,
    l.granted,
    l.relation::regclass as relation_name,
    l.pid,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation IS NOT NULL
ORDER BY l.granted, l.mode;"
echo

# Check for long-running queries
echo "⏰ Long Running Queries (>30 seconds):"
sudo -u postgres psql -c "
SELECT 
    pid,
    now() - pg_stat_activity.query_start AS duration,
    query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '30 seconds'
    AND state = 'active'
ORDER BY duration DESC;"

echo "=== End Performance Report ==="
EOF

sudo chmod +x /usr/local/bin/postgres-monitor.sh

# Create optimization recommendations script
sudo tee /usr/local/bin/postgres-optimize.sh << 'EOF'
#!/bin/bash
# PostgreSQL Optimization Recommendations

echo "=== PostgreSQL Optimization Analysis ==="

# Get system memory
TOTAL_RAM=$(free -m | awk 'NR==2{printf "%.0f", $2}')
SHARED_BUFFERS=$((TOTAL_RAM / 4))
EFFECTIVE_CACHE=$((TOTAL_RAM * 3 / 4))

echo "💾 Memory Recommendations (Total RAM: ${TOTAL_RAM}MB):"
echo "shared_buffers = ${SHARED_BUFFERS}MB (25% of RAM)"
echo "effective_cache_size = ${EFFECTIVE_CACHE}MB (75% of RAM)"
echo "work_mem = $((TOTAL_RAM / 50))MB"
echo "maintenance_work_mem = $((TOTAL_RAM / 10))MB"
echo

# Check for missing indexes
echo "🔍 Potential Missing Indexes:"
sudo -u postgres psql -c "
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    seq_scan / seq_tup_read as ratio
FROM pg_stat_user_tables
WHERE seq_scan > 100
    AND seq_tup_read / seq_scan > 1000
ORDER BY seq_tup_read DESC
LIMIT 10;"
echo

# Check for unused indexes
echo "🗑️ Potentially Unused Indexes:"
sudo -u postgres psql -c "
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_tup_read < 100
    AND idx_tup_fetch < 100
ORDER BY idx_tup_read;"
echo

echo "=== Optimization Analysis Complete ==="
EOF

sudo chmod +x /usr/local/bin/postgres-optimize.sh

# Test monitoring scripts
/usr/local/bin/postgres-monitor.sh
/usr/local/bin/postgres-optimize.sh

echo "✅ Performance monitoring and optimization tools installed!"
echo "📊 Monitor performance: /usr/local/bin/postgres-monitor.sh"
echo "🔧 Get optimization tips: /usr/local/bin/postgres-optimize.sh"

🚨 Fix Common Problems

Problem 1: PostgreSQL Service Won’t Start ❌

Symptoms:

• Service fails to start
• Connection refused errors

Try this:

# Check PostgreSQL service status
sudo systemctl status postgresql

# Check PostgreSQL logs
sudo journalctl -u postgresql -f

# Check disk space
df -h /var/lib/pgsql/

# Check PostgreSQL log files
sudo tail -f /var/lib/pgsql/data/log/postgresql-*.log

# Try starting manually for debugging
sudo -u postgres postgres -D /var/lib/pgsql/data

# Check data directory ownership
ls -la /var/lib/pgsql/
sudo chown -R postgres:postgres /var/lib/pgsql/

# Reinitialize if database is corrupted (CAUTION: This will delete data)
# sudo postgresql-setup --initdb --force

Problem 2: Can’t Connect to PostgreSQL Database ❌

Try this:

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check if PostgreSQL is listening
sudo ss -tlnp | grep :5432

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

# Check authentication configuration
sudo cat /var/lib/pgsql/data/pg_hba.conf

# Reset postgres user password if needed
sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'NewPassword123!';"

# Check PostgreSQL configuration
sudo grep -E "^listen_addresses|^port" /var/lib/pgsql/data/postgresql.conf

# Test connection with specific parameters
PGPASSWORD='password' psql -h localhost -U postgres -d postgres -c "SELECT 1;"

Problem 3: Poor Database Performance ❌

Check these things:

# Check current connections
sudo -u postgres psql -c "SELECT COUNT(*) FROM pg_stat_activity;"

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

# Check table sizes and bloat
sudo -u postgres psql -c "
SELECT 
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename::regclass)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;"

# Run database maintenance
sudo -u postgres vacuumdb --all --analyze --verbose

# Check index usage
sudo -u postgres psql -c "
SELECT 
    tablename,
    indexname,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_tup_read DESC;"

📋 Simple Commands Summary

💡 Tips for Success

1. Regular backups 🌟 - Automate daily database backups with pg_dump
2. Monitor performance 🔐 - Use pg_stat_statements to track slow queries
3. Proper indexing 🚀 - Create indexes on frequently queried columns
4. Security first 📝 - Use SSL, strong passwords, and proper authentication
5. Keep updated 🔄 - Regularly update PostgreSQL for security and features

🏆 What You Learned

Congratulations! Now you can:

• ✅ Install and configure PostgreSQL database server on AlmaLinux
• ✅ Set up secure authentication and user management systems
• ✅ Create and manage databases with proper permissions and security
• ✅ Implement comprehensive backup and restore strategies
• ✅ Monitor and optimize database performance for production workloads

🎯 Why This Matters

Your PostgreSQL installation on AlmaLinux provides:

• 🚀 Enterprise-grade database ready for mission-critical applications
• 🔐 Advanced security features with SSL encryption and robust authentication
• 📊 Scalable performance handling everything from small apps to large enterprises
• ⚡ Professional reliability with ACID compliance and data integrity guarantees

Remember: PostgreSQL powers some of the world’s largest applications including Instagram, Spotify, and Reddit - with proper setup and maintenance, your AlmaLinux server can handle any data challenge! From simple web apps to complex analytics systems, you now have the foundation for enterprise-grade data management! ⭐

You’ve successfully mastered PostgreSQL installation and configuration on AlmaLinux! Your database server is now ready to power modern applications with enterprise reliability and performance! 🙌