> For the complete documentation index, see [llms.txt](https://docs.hivel.ai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.hivel.ai/self-managed-hivel-deployment/on-premises-generic/hivel-on-premises-deployment-guide-generic/database-setup-self-managed-postgresql.md).

# 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](https://docs.hivel.ai/self-managed-hivel-deployment/on-premises/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 = '*'
```

3. 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](https://docs.hivel.ai/self-managed-hivel-deployment/on-premises/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](https://docs.hivel.ai/self-managed-hivel-deployment/on-premises/security-and-secrets) for the complete security model.

4. **Restart PostgreSQL to apply changes:**

```
sudo systemctl restart postgresql
```

5. 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

{% code expandable="true" %}

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

{% endcode %}

**Success:** Connection opens without error\
**Failure:** See Database connection issues in [Troubleshooting](/self-managed-hivel-deployment/on-premises-generic/hivel-on-premises-deployment-guide-generic/troubleshooting.md).

#### 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]

<br>

{% columns %}
{% column %} <a href="/self-managed-hivel-deployment/on-premises-generic/hivel-on-premises-deployment-guide-generic/prerequisites.md" class="button primary" data-icon="backward">Back to Prerequisites</a>
{% endcolumn %}

{% column %}

{% endcolumn %}

{% column %} <a href="/self-managed-hivel-deployment/on-premises-generic/hivel-on-premises-deployment-guide-generic/project-setup.md" class="button primary" data-icon="forward">Project Setup</a>
{% endcolumn %}
{% endcolumns %}