Vagrant — PostgreSQL 15 on OEL 8

# -*- mode: ruby -*-
Vagrant.configure("2") do |config|
  config.vm.box      = "generic/oracle8"
  config.vm.hostname = "postgres15"

  config.vm.network "private_network", ip: "192.168.56.40"
  config.vm.network "forwarded_port",  guest: 5432, host: 15432

  config.vm.provider "virtualbox" do |vb|
    vb.name   = "PostgreSQL15-OEL8"
    vb.memory = 2048
    vb.cpus   = 2
  end

  config.vm.provider "parallels" do |prl|
    prl.name   = "PostgreSQL15-OEL8"
    prl.memory = 2048
    prl.cpus   = 2
    prl.update_guest_tools = false
  end

  config.vm.provision "shell", path: "scripts/install_postgres.sh"
end

#!/bin/bash
# scripts/install_postgres.sh
set -e

PG_VERSION="15"
PG_DATA="/var/lib/pgsql/${PG_VERSION}/data"
PG_PASS="Postgres@123!"

echo "=== Installing PostgreSQL ${PG_VERSION} on OEL 8 ==="

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

# Disable built-in PostgreSQL module
dnf module disable -y postgresql

# Install PostgreSQL
dnf install -y postgresql${PG_VERSION}-server postgresql${PG_VERSION}-contrib

# Initialize database cluster
/usr/pgsql-${PG_VERSION}/bin/postgresql-${PG_VERSION}-setup initdb

# Configure PostgreSQL to allow remote connections
cat > ${PG_DATA}/postgresql.conf << EOF
listen_addresses = '*'
port = 5432
max_connections = 200
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
min_wal_size = 1GB
max_wal_size = 4GB
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_rotation_age = 1d
log_min_duration_statement = 1000
log_line_prefix = '%m [%p] '
log_timezone = 'UTC'
datestyle = 'iso, mdy'
timezone = 'UTC'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'

# Replication settings (standby-ready)
wal_level = replica
max_wal_senders = 10
wal_keep_size = 64
hot_standby = on
EOF

# Configure pg_hba.conf for remote access
cat > ${PG_DATA}/pg_hba.conf << EOF
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
host    replication     replicator      0.0.0.0/0               md5
EOF

# Start and enable PostgreSQL
systemctl enable --now postgresql-${PG_VERSION}

# Set postgres user password and create lab objects
sudo -u postgres psql << EOF
ALTER USER postgres PASSWORD '${PG_PASS}';

-- Create replication user
CREATE USER replicator REPLICATION LOGIN PASSWORD 'Repl@123!';

-- Create lab database
CREATE DATABASE labdb;
\c labdb

-- Create sample schema
CREATE TABLE employees (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    dept      VARCHAR(50),
    salary    NUMERIC(10,2),
    hire_date DATE DEFAULT CURRENT_DATE
);

INSERT INTO employees (name, dept, salary) VALUES
    ('John Smith',    'Engineering', 75000),
    ('Jane Doe',      'Marketing',   65000),
    ('Bob Johnson',   'Engineering', 80000),
    ('Alice Brown',   'HR',          60000);

-- Create index
CREATE INDEX idx_employees_dept ON employees(dept);

\q
EOF

echo "=== PostgreSQL ${PG_VERSION} Ready ==="
echo ""
echo "Connection:"
echo "  Host: 192.168.56.40 or 127.0.0.1:15432"
echo "  User: postgres"
echo "  Pass: ${PG_PASS}"
echo "  DB:   labdb"
echo ""
echo "  psql -U postgres -h 127.0.0.1 -p 15432 -d labdb"

# Start VM
vagrant up

# From host (via port forward)
psql -U postgres -h 127.0.0.1 -p 15432 -d labdb

# Or via private IP
psql -U postgres -h 192.168.56.40 -p 5432 -d labdb

# Inside VM
vagrant ssh
sudo -u postgres psql -d labdb

# Test queries
SELECT * FROM employees;
SELECT dept, COUNT(*), AVG(salary) FROM employees GROUP BY dept;
SELECT version();
SHOW server_version;

# Multi-machine replication setup
# In a separate Vagrantfile with primary + standby:

# On Primary (192.168.56.40):
# Already configured with wal_level=replica and replicator user

# On Standby (192.168.56.41) — after PostgreSQL installed:
sudo -u postgres bash << EOF
# Stop PostgreSQL on standby
systemctl stop postgresql-15

# Clear data directory
rm -rf /var/lib/pgsql/15/data/*

# Base backup from primary
pg_basebackup -h 192.168.56.40 -U replicator -p 5432   -D /var/lib/pgsql/15/data   -P -Xs -R

# Start standby
systemctl start postgresql-15
EOF

# Verify replication on primary
sudo -u postgres psql -c "SELECT client_addr, state, sent_lsn FROM pg_stat_replication;"