Database Setup (Self-Managed PostgreSQL)

Deployment Options

PostgreSQL can run on the same host as Hivel services or on a dedicated database host. For production, Hivel recommends a dedicated PostgreSQL host with its own backup routine. See Backup and Restore for backup procedures.

Important: Backups are your responsibility in a self-hosted setup. Set up regular pg_dump cron jobs or your organization's standard PostgreSQL backup tooling before going to production.

---

Step 1: Install PostgreSQL

Run:

sudo apt update
sudo apt install -y postgresql postgresql-contrib
sudo systemctl enable postgresql
sudo systemctl start postgresql

Supported versions: PostgreSQL 12, 13, 14, or 15. Versions outside this range are not validated; contact your Hivel representative before using a newer major release.

Step 2: Set Superuser Password

PostgreSQL's default installation only allows local peer authentication with no password. Set a password for the postgres superuser to enable TCP connections:

sudo -u postgres psql -c "ALTER USER postgres WITH PASSWORD 
'your_strong_superuser_password';"

Replace your_strong_superuser_password with a strong password. Record this password. You'll need it for the migration phase.

Step 3: Configure PostgreSQL for Remote Connections

1. Find your PostgreSQL configuration directory: (e.g., /etc/postgresql/14/main/ on Ubuntu: replace version number with your installed version)

2. Edit postgresql.conf:

listen_addresses = '*'

1. Edit pg_hba.conf to allow connections from the host:

host    insightly    all    <HOST_PRIVATE_IP>/32    scram-sha-256

Replace <HOST_PRIVATE_IP> with the host's private IP address recorded in Server Infrastructure Setup.

Security note: This configuration uses network-level isolation as the security boundary. PostgreSQL listens on all interfaces, but access is restricted to the host's private IP via pg_hba.conf and the firewall rule below. Transport encryption is not used. See Security and Secrets for the complete security model.

1. Restart PostgreSQL to apply changes:

sudo systemctl restart postgresql

1. Configure firewall to allow port 5432 from the host only:

sudo ufw allow from <HOST_PRIVATE_IP> to any port 5432

Step 4: Create the Application Database

Run:

sudo -u postgres psql -c "CREATE DATABASE insightly;"

Verify Database connectivity

psql -h <HOST_PRIVATE_IP> -U postgres -d insightly /

Success: Connection opens without error Failure: See Database connection issues in Troubleshooting.

Record Database Credentials

Save the following for the next phase (Environment Configuration):

• Database host/IP: [your host's private IP]

• Port: 5432

• Superuser: postgres (fixed, do not change;

• Superuser password: [the password you set in Step 2]

Back to Prerequisites

Project Setup