Update system packages

Start by updating your package manager to ensure you get the latest versions of all components.

sudo apt update && sudo apt upgrade -y

sudo dnf update -y

Install OpenResty and dependencies

Install OpenResty web server with required development tools and libraries for Lua module compilation.

sudo apt install -y software-properties-common
curl -fsSL https://openresty.org/package/pubkey.gpg | sudo gpg --dearmor -o /usr/share/keyrings/openresty.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/usr/share/keyrings/openresty.gpg] http://openresty.org/package/ubuntu $(lsb_release -sc) main" | sudo tee /etc/apt/sources.list.d/openresty.list
sudo apt update
sudo apt install -y openresty openresty-opm build-essential libpq-dev git

sudo dnf install -y epel-release
sudo dnf config-manager --add-repo https://openresty.org/package/rhel/openresty.repo
sudo dnf install -y openresty openresty-opm gcc gcc-c++ make postgresql-devel git

Install lua-resty-postgres module

Install the PostgreSQL connector module for OpenResty using the OpenResty Package Manager.

sudo opm get openresty/lua-resty-postgres
sudo opm get openresty/lua-resty-http
sudo opm get openresty/lua-resty-upstream-healthcheck

Create OpenResty directory structure

Set up the proper directory structure for OpenResty configuration files and Lua modules.

sudo mkdir -p /usr/local/openresty/nginx/conf/conf.d
sudo mkdir -p /usr/local/openresty/nginx/lua
sudo mkdir -p /var/log/openresty
sudo chown -R www-data:www-data /var/log/openresty

Configure main nginx configuration

Create the main OpenResty configuration file with Lua module support and connection pooling settings.

user www-data;
worker_processes auto;
error_log /var/log/openresty/error.log warn;
pid /var/run/openresty.pid;

events {
    worker_connections 1024;
    use epoll;
}

http {
    include /usr/local/openresty/nginx/conf/mime.types;
    default_type application/octet-stream;

    # Lua settings
    lua_package_path "/usr/local/openresty/lualib/?.lua;/usr/local/openresty/nginx/lua/?.lua;;";
    lua_code_cache on;
    lua_socket_pool_size 100;
    lua_socket_keepalive_timeout 60000;
    lua_socket_connect_timeout 5000;
    lua_socket_send_timeout 5000;
    lua_socket_read_timeout 5000;

    # Shared memory for connection pooling
    lua_shared_dict db_pool 10m;
    lua_shared_dict healthcheck 1m;

    # Initialize connection pool on startup
    init_by_lua_block {
        require "resty.core"
        local config = require "db_config"
        config.init()
    }

    # Logging format
    log_format main '$remote_addr - $remote_user [$time_local] "$request" '
                   '$status $body_bytes_sent "$http_referer" '
                   '"$http_user_agent" rt=$request_time uct="$upstream_connect_time" '
                   'uht="$upstream_header_time" urt="$upstream_response_time"';

    access_log /var/log/openresty/access.log main;

    # Basic settings
    sendfile on;
    tcp_nopush on;
    tcp_nodelay on;
    keepalive_timeout 65;
    types_hash_max_size 2048;
    client_max_body_size 16M;

    # Gzip compression
    gzip on;
    gzip_vary on;
    gzip_proxied any;
    gzip_comp_level 6;
    gzip_types text/plain text/css text/xml text/javascript application/javascript application/xml+rss application/json;

    # Include server configurations
    include /usr/local/openresty/nginx/conf/conf.d/*.conf;
}

Create database configuration module

Create a Lua module for PostgreSQL database configuration and connection pool management.

local _M = {}

-- Database configuration
_M.db_config = {
    host = "127.0.0.1",
    port = 5432,
    database = "webapp",
    user = "webapp_user",
    password = "secure_password_here",
    charset = "utf8",
    max_packet_size = 1024 * 1024,
    -- Connection pool settings
    pool_timeout = 60000,  -- 60 seconds
    pool_size = 50,        -- Maximum connections per worker
    keepalive_timeout = 30000, -- 30 seconds
    keepalive_requests = 1000,
    connect_timeout = 5000,    -- 5 seconds
    send_timeout = 5000,       -- 5 seconds
    read_timeout = 10000       -- 10 seconds
}

-- Health check configuration
_M.health_check = {
    interval = 30,  -- seconds
    timeout = 5,    -- seconds
    max_failures = 3,
    recovery_time = 60  -- seconds
}

function _M.init()
    ngx.log(ngx.INFO, "Database configuration initialized")
end

return _M

Create connection pool module

Implement the PostgreSQL connection pool with automatic health checks and failover capabilities.

local postgres = require "resty.postgres"
local config = require "db_config"

local _M = {}

local db_pool = ngx.shared.db_pool
local healthcheck = ngx.shared.healthcheck

-- Get database connection from pool
function _M.get_connection()
    local db = postgres:new()
    
    -- Set timeouts
    db:set_timeout(config.db_config.connect_timeout)
    
    -- Connect to database
    local ok, err = db:connect({
        host = config.db_config.host,
        port = config.db_config.port,
        database = config.db_config.database,
        user = config.db_config.user,
        password = config.db_config.password,
        charset = config.db_config.charset,
        max_packet_size = config.db_config.max_packet_size
    })
    
    if not ok then
        ngx.log(ngx.ERR, "Failed to connect to PostgreSQL: ", err)
        return nil, err
    end
    
    return db, nil
end

-- Return connection to pool
function _M.close_connection(db)
    if not db then
        return
    end
    
    -- Set keepalive to pool the connection
    local ok, err = db:set_keepalive(
        config.db_config.keepalive_timeout,
        config.db_config.pool_size
    )
    
    if not ok then
        ngx.log(ngx.ERR, "Failed to set keepalive: ", err)
        db:close()
    end
end

-- Execute query with automatic connection management
function _M.query(sql, params)
    local db, err = _M.get_connection()
    if not db then
        return nil, err
    end
    
    -- Set query timeout
    db:set_timeout(config.db_config.read_timeout)
    
    local res, err, errcode, sqlstate
    
    if params and #params > 0 then
        res, err, errcode, sqlstate = db:query(sql, params)
    else
        res, err, errcode, sqlstate = db:query(sql)
    end
    
    if not res then
        ngx.log(ngx.ERR, "Query failed: ", err, " (code: ", errcode, ", state: ", sqlstate, ")")
        db:close()  -- Close on error
        return nil, err
    end
    
    -- Return connection to pool
    _M.close_connection(db)
    
    return res, nil
end

-- Health check function
function _M.health_check()
    local db, err = _M.get_connection()
    if not db then
        ngx.log(ngx.ERR, "Health check failed - connection error: ", err)
        return false
    end
    
    local res, err = db:query("SELECT 1 as health_check")
    
    if not res then
        ngx.log(ngx.ERR, "Health check failed - query error: ", err)
        db:close()
        return false
    end
    
    _M.close_connection(db)
    ngx.log(ngx.INFO, "Database health check passed")
    return true
end

-- Get connection pool statistics
function _M.get_pool_stats()
    local stats = {}
    
    -- This would require custom implementation based on your monitoring needs
    stats.active_connections = "N/A"
    stats.total_connections = "N/A"
    stats.pool_size = config.db_config.pool_size
    
    return stats
end

return _M

Create application API module

Create a sample API module demonstrating how to use the connection pool for database operations.

local cjson = require "cjson"
local db_pool = require "db_pool"

local _M = {}

-- Helper function to send JSON response
local function send_json_response(status, data)
    ngx.status = status
    ngx.header.content_type = "application/json"
    ngx.say(cjson.encode(data))
end

-- Get users endpoint
function _M.get_users()
    local sql = "SELECT id, username, email, created_at FROM users ORDER BY id LIMIT 100"
    
    local result, err = db_pool.query(sql)
    
    if not result then
        send_json_response(500, {
            error = "Database query failed",
            message = err
        })
        return
    end
    
    send_json_response(200, {
        success = true,
        data = result,
        count = #result
    })
end

-- Get user by ID endpoint
function _M.get_user_by_id(id)
    -- Validate input
    if not id or id == "" then
        send_json_response(400, {
            error = "Invalid user ID"
        })
        return
    end
    
    local sql = "SELECT id, username, email, created_at FROM users WHERE id = $1"
    local params = {id}
    
    local result, err = db_pool.query(sql, params)
    
    if not result then
        send_json_response(500, {
            error = "Database query failed",
            message = err
        })
        return
    end
    
    if #result == 0 then
        send_json_response(404, {
            error = "User not found"
        })
        return
    end
    
    send_json_response(200, {
        success = true,
        data = result[1]
    })
end

-- Health check endpoint
function _M.health_check()
    local is_healthy = db_pool.health_check()
    
    if is_healthy then
        send_json_response(200, {
            status = "healthy",
            database = "connected",
            timestamp = ngx.time()
        })
    else
        send_json_response(503, {
            status = "unhealthy",
            database = "disconnected",
            timestamp = ngx.time()
        })
    end
end

-- Pool statistics endpoint
function _M.pool_stats()
    local stats = db_pool.get_pool_stats()
    
    send_json_response(200, {
        pool_stats = stats,
        worker_pid = ngx.worker.pid(),
        timestamp = ngx.time()
    })
end

return _M

Configure virtual host

Create a virtual host configuration that uses the PostgreSQL connection pool for API endpoints.

server {
    listen 80;
    server_name example.com www.example.com;
    
    # Security headers
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-XSS-Protection "1; mode=block" always;
    add_header X-Content-Type-Options "nosniff" always;
    add_header Referrer-Policy "no-referrer-when-downgrade" always;
    add_header Content-Security-Policy "default-src 'self' http: https: data: blob: 'unsafe-inline'" always;
    
    # Root directory
    root /var/www/html;
    index index.html index.htm;
    
    # API endpoints with database integration
    location /api/users {
        access_by_lua_block {
            -- Rate limiting could be added here
        }
        
        content_by_lua_block {
            local api = require "api"
            
            if ngx.var.request_method == "GET" then
                local uri_parts = ngx.re.split(ngx.var.uri, "/")
                
                if #uri_parts == 3 then  -- /api/users
                    api.get_users()
                elseif #uri_parts == 4 then  -- /api/users/{id}
                    local user_id = uri_parts[4]
                    api.get_user_by_id(user_id)
                else
                    ngx.status = 404
                    ngx.say("Endpoint not found")
                end
            else
                ngx.status = 405
                ngx.header.allow = "GET"
                ngx.say("Method not allowed")
            end
        }
    }
    
    # Health check endpoint
    location /health {
        access_log off;
        
        content_by_lua_block {
            local api = require "api"
            api.health_check()
        }
    }
    
    # Pool statistics endpoint (restrict access in production)
    location /admin/pool-stats {
        allow 127.0.0.1;
        allow 10.0.0.0/8;
        deny all;
        
        content_by_lua_block {
            local api = require "api"
            api.pool_stats()
        }
    }
    
    # Static content
    location / {
        try_files $uri $uri/ =404;
    }
    
    # Error pages
    error_page 404 /404.html;
    error_page 500 502 503 504 /50x.html;
    
    location = /50x.html {
        root /usr/share/nginx/html;
    }
}

Create systemd service file

Set up a systemd service file for OpenResty with proper dependencies and security settings.

[Unit]
Description=OpenResty Web Server
Documentation=https://openresty.org/
After=network.target remote-fs.target nss-lookup.target
Wants=network.target

[Service]
Type=forking
PIDFile=/var/run/openresty.pid
ExecStartPre=/usr/local/openresty/nginx/sbin/nginx -t
ExecStart=/usr/local/openresty/nginx/sbin/nginx
ExecReload=/bin/sh -c "/bin/kill -s HUP $(/bin/cat /var/run/openresty.pid)"
ExecStop=/bin/sh -c "/bin/kill -s TERM $(/bin/cat /var/run/openresty.pid)"
KillMode=mixed
PrivateTmp=true
Restart=on-failure
RestartSec=5s

Security settings
NoNewPrivileges=yes
PrivateDevices=yes
ProtectHome=yes
ProtectSystem=strict
ReadWritePaths=/var/log/openresty /var/run /tmp

[Install]
WantedBy=multi-user.target

Set up database and test user

Create a test PostgreSQL database and user for demonstrating the connection pool.

sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable --now postgresql

sudo dnf install -y postgresql postgresql-server postgresql-contrib
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

sudo -u postgres psql -c "CREATE DATABASE webapp;"
sudo -u postgres psql -c "CREATE USER webapp_user WITH PASSWORD 'secure_password_here';"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE webapp TO webapp_user;"
sudo -u postgres psql -d webapp -c "CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE, email VARCHAR(100), created_at TIMESTAMP DEFAULT NOW());"
sudo -u postgres psql -d webapp -c "INSERT INTO users (username, email) VALUES ('admin', 'admin@example.com'), ('user1', 'user1@example.com');"
sudo -u postgres psql -d webapp -c "GRANT ALL PRIVILEGES ON TABLE users TO webapp_user;"
sudo -u postgres psql -d webapp -c "GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO webapp_user;"

Create web directory and sample page

Set up the web directory structure with proper permissions and a sample HTML page.

sudo mkdir -p /var/www/html
sudo chown -R www-data:www-data /var/www/html
echo '


    


    
OpenResty PostgreSQL Connection Pool Demo
    
API Endpoints:
    

        
GET /api/users - List all users
        
GET /api/users/{id} - Get user by ID
        
GET /health - Health check
    

' | sudo tee /var/www/html/index.html

Enable and start services

Enable and start both PostgreSQL and OpenResty services with automatic startup on boot.

sudo systemctl daemon-reload
sudo systemctl enable openresty
sudo systemctl start openresty
sudo systemctl status openresty

Set up SSL/TLS encryption

Configure SSL certificates for secure database connections and HTTPS endpoints.

sudo mkdir -p /etc/openresty/ssl
sudo openssl req -x509 -nodes -days 365 -newkey rsa:2048 \
  -keyout /etc/openresty/ssl/server.key \
  -out /etc/openresty/ssl/server.crt \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=example.com"

server {
    listen 443 ssl http2;
    server_name example.com www.example.com;
    
    # SSL configuration
    ssl_certificate /etc/openresty/ssl/server.crt;
    ssl_certificate_key /etc/openresty/ssl/server.key;
    ssl_protocols TLSv1.2 TLSv1.3;
    ssl_ciphers ECDHE-RSA-AES256-GCM-SHA512:DHE-RSA-AES256-GCM-SHA512:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-GCM-SHA384;
    ssl_prefer_server_ciphers off;
    ssl_session_cache shared:SSL:10m;
    ssl_session_timeout 10m;
    
    # Enhanced security headers
    add_header Strict-Transport-Security "max-age=31536000; includeSubDomains; preload" always;
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-XSS-Protection "1; mode=block" always;
    add_header X-Content-Type-Options "nosniff" always;
    
    # Include the same location blocks as HTTP config
    root /var/www/html;
    index index.html index.htm;
    
    # All location blocks from webapp.conf would go here
    include /usr/local/openresty/nginx/conf/locations.conf;
}

Configure rate limiting

Implement rate limiting to protect against abuse and ensure fair resource usage.

local limit_req = require "resty.limit.req"

local _M = {}

-- Rate limiting configuration
local lim, err = limit_req.new("my_limit_req_store", 200, 100)  -- 200 req/sec, burst of 100

function _M.check_rate_limit()
    if not lim then
        ngx.log(ngx.ERR, "failed to instantiate a resty.limit.req object: ", err)
        return ngx.exit(500)
    end
    
    local key = ngx.var.binary_remote_addr
    local delay, err = lim:incoming(key, true)
    
    if not delay then
        if err == "rejected" then
            ngx.status = 429
            ngx.header.content_type = "application/json"
            ngx.say('{"error":"Too Many Requests","retry_after":60}')
            return ngx.exit(429)
        end
        
        ngx.log(ngx.ERR, "failed to limit req: ", err)
        return ngx.exit(500)
    end
    
    if delay >= 0.001 then
        local excess = err
        ngx.sleep(delay)
    end
end

return _M

Configure firewall rules

Set up firewall rules to restrict access to database and management interfaces.

sudo ufw allow 22/tcp
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp
sudo ufw --force enable

sudo firewall-cmd --permanent --add-service=ssh
sudo firewall-cmd --permanent --add-service=http
sudo firewall-cmd --permanent --add-service=https
sudo firewall-cmd --reload

Configure PostgreSQL for connection pooling

Optimize PostgreSQL settings for better connection pool performance.

# Connection settings
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

Logging
log_connections = on
log_disconnections = on
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

Performance
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100

sudo systemctl restart postgresql

Monitor connection pool metrics

Set up monitoring endpoints and logging for connection pool performance tracking.

local _M = {}

function _M.log_pool_stats()
    local stats = {
        worker_id = ngx.worker.id(),
        worker_pid = ngx.worker.pid(),
        connections_active = ngx.var.connections_active or "N/A",
        connections_reading = ngx.var.connections_reading or "N/A",
        connections_writing = ngx.var.connections_writing or "N/A",
        connections_waiting = ngx.var.connections_waiting or "N/A",
        timestamp = ngx.time()
    }
    
    ngx.log(ngx.INFO, "Connection pool stats: ", require("cjson").encode(stats))
end

-- Log stats every 60 seconds
local function schedule_stats_logging()
    ngx.timer.at(60, function()
        _M.log_pool_stats()
        schedule_stats_logging()
    end)
end

function _M.init_monitoring()
    schedule_stats_logging()
end

return _M