Overview

Provision a dedicated / seperate PostgreSQL server for Alphie before running your alphie.installer, on your controller.

Fill Out below, to populate the lines with the correct information. Then copy and paste into the terminal, on you DataBase Server.

AlmaLinux / Rocky Linux / RHEL

# Update & install

sudo dnf -y update
sudo dnf -y install postgresql-server postgresql-contrib

# Initialize database cluster (creates PGDATA at /var/lib/pgsql/data)

sudo /usr/bin/postgresql-setup --initdb

# Enable & start service

sudo systemctl enable --now postgresql

# Create role & database (interactive via psql)

sudo -iu postgres psql <<'SQL'
CREATE ROLE $ALPHIE_USER LOGIN PASSWORD '$ALPHIE_PASS' CREATEDB CREATEROLE;
CREATE DATABASE $ALPHIE_DB OWNER $ALPHIE_USER;
SQL

# Verify database exists & owner

sudo -iu postgres psql -c \
"SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner FROM pg_database WHERE datname = 'ALPHIE_DB';"

Network access & config

# Allow the Alphie controller to connect (pg_hba.conf)

echo "host    $ALPHIE_DB    $ALPHIE_USER    $CONTROLLER_IP/32    md5" \
 | sudo tee -a /var/lib/pgsql/data/pg_hba.conf

# Listen only on the DB server IP and set port (postgresql.conf)

sudo sed -i -E "s|^[[:space:]]*#?[[:space:]]*listen_addresses[[:space:]]*=.*|listen_addresses = 'DB_SERVER_IP'|" /var/lib/pgsql/data/postgresql.conf
sudo sed -i -E "s|^[[:space:]]*#?[[:space:]]*port[[:space:]]*=.*|port = DB_PORT|" /var/lib/pgsql/data/postgresql.conf

sudo systemctl restart postgresql

Firewall (firewalld)

sudo firewall-cmd --add-rich-rule="rule family=ipv4 source address=$CONTROLLER_IP/32 port port=$DB_PORT protocol=tcp accept" --permanent
sudo firewall-cmd --reload

Connectivity tests

# On the DB server

ss -lntp | grep $DB_PORT

# From the Alphie controller

PGPASSWORD='$ALPHIE_PASS' psql -h $DB_SERVER_IP -p $DB_PORT -U $ALPHIE_USER -d $ALPHIE_DB \
  -c "select current_user, inet_client_addr();"

Debian / Ubuntu

# Update & install

sudo apt-get update
sudo apt-get -y install postgresql postgresql-contrib

# Ensure service is enabled & running

sudo systemctl enable --now postgresql

# Create role & database (interactive via psql)

sudo -iu postgres psql <<'SQL'
CREATE ROLE $ALPHIE_USER LOGIN PASSWORD $ALPHIE_PASS CREATEDB CREATEROLE;
CREATE DATABASE $ALPHIE_DB OWNER $ALPHIE_USER;
SQL

# Verify database exists & owner

sudo -iu postgres psql -c \
"SELECT datname, pg_catalog.pg_get_userbyid(datdba) AS owner FROM pg_database WHERE datname = '$ALPHIE_DB';"

Network access & config

Debian/Ubuntu config lives under /etc/postgresql/<version>/main/. Adjust the version path.

# Determine version directory (e.g., 15)

PGVER="$(psql -V | awk '{print $3}' | cut -d. -f1)"
CONF_DIR="/etc/postgresql/${PGVER}/main"

# Allow the Alphie controller to connect (pg_hba.conf)

echo "host    $ALPHIE_DB    $ALPHIE_USER    $CONTROLLER_IP/32    md5" \
 | sudo tee -a "${CONF_DIR}/pg_hba.conf"

# Listen only on the DB server IP and set port (postgresql.conf)

sudo sed -i -E "s|^[[:space:]]*#?[[:space:]]*listen_addresses[[:space:]]*=.*|listen_addresses = 'DB_SERVER_IP'|" /var/lib/pgsql/data/postgresql.conf
sudo sed -i -E "s|^[[:space:]]*#?[[:space:]]*port[[:space:]]*=.*|port = DB_PORT|" /var/lib/pgsql/data/postgresql.conf

sudo systemctl restart postgresql

Firewall (UFW)

# Allow only the Alphie controller IP to reach 5432/TCP

sudo ufw allow from $CONTROLLER_IP to any port $DB_PORT proto tcp
sudo ufw reload

Connectivity tests

# On the DB server

ss -lntp | grep $DB_PORT

# From the Alphie controller

PGPASSWORD='$ALPHIE_PASS' psql -h $DB_SERVER_IP -p $DB_PORT -U $ALPHIE_USER -d ALPHIE_DB \
  -c "select current_user, inet_client_addr();"

Next steps (Alphie installer)

On your Alphie controller host, point the installer to this database:

DB_HOST="DB_SERVER_IP"
DB_PORT="DB_PORT"
DB_NAME="ALPHIE_DB"
DB_USER="ALPHIE_USER"
DB_PASSWORD="ALPHIE_PASS"

Keep your password secret. Combine host firewalls with network rules (security groups/VPC) for best results.