Skip to main content

Test 2

PostgreSQL Setup for AVIX Server

This document guides IT administrators through setting up a dedicated PostgreSQL database instance for use with AVIX Server. 

Overview & Prerequisites

Before configuring AVIX Server, the database engine must be installed, configured for network connectivity, and populated with the target database schema/owner. 

Parameter Configuration Property in repositories.xml Example Value Host Address serverHost

localhost or 192.168.1.50

 

Port portNumber

5432

 

Database Name databaseName

avix1

 

Database User databaseUser

postgres (or dedicated avix_user) 

Password databasePassword

<your-secure-password>

 

SSL Connection useSSL

false or true

1. Engine Installation

Windows

    Download the official PostgreSQL Interactive Installer from EDB (see https://www.postgresql.org/download/windows/  and https://www.enterprisedb.com/downloads/postgres-postgresql-downloads )

    Run the executable and accept default paths

      image.png

      Select PostgreSQL Server, pgAdmin 4, and Command Line Tools.

        image.png

        Specify a master password for the postgres superuser during setup.

          image.png

          Take note of your created password!

          Specify the port of the Postgres server:

            image.png

            Linux (Ubuntu / Debian)

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

            Linux (RHEL / Rocky / Fedora)

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

            2. Network & Authentication Configuration

            By default, PostgreSQL only accepts connections on localhost. Perform these steps if AVIX Server runs on a separate machine:

            Edit postgresql.conf

              Windows Location: C:\Program Files\PostgreSQL\<version>\data\postgresql.conf

              Linux Location: /etc/postgresql/<version>/main/postgresql.conf (Debian/Ubuntu) or /var/lib/pgsql/data/postgresql.conf (RHEL)

              Set the listen address and verify the port:

              Ini, TOML 
              listen_addresses = '*'
port = 5432

              Edit pg_hba.conf

              In the same data directory, add an authorization rule for the AVIX Server host IP:

              Plaintext 
              # TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             <AVIX_SERVER_IP>/32     scram-sha-256

              Restart Service & Firewall

                Windows: Restart postgresql-x64-<version> via services.msc and allow TCP port 5432 in Windows Defender Firewall.

                Linux:

                Bash 
                sudo systemctl restart postgresql

# Firewall (Ubuntu UFW)
sudo ufw allow 5432/tcp

# Firewall (RHEL firewalld)
sudo firewall-cmd --add-port=5432/tcp --permanent && sudo firewall-cmd --reload

                3. Database & User Creation

                Log into psql using the postgres administrator account:

                Bash 
                # Linux
sudo -i -u postgres psql

# Windows (SQL Shell / CMD)
psql -U postgres

                Execute the following SQL commands to prepare the environment:

                SQL 
                -- 1. Create dedicated user (optional if using default 'postgres')
CREATE USER postgres WITH PASSWORD 'postgres';

-- 2. Create the target database
CREATE DATABASE avix1 OWNER postgres;

-- 3. Grant full privileges to the database
GRANT ALL PRIVILEGES ON DATABASE avix1 TO postgres;

                4. Verification & AVIX repositories.xml Integration

                Connection Verification

                Test connection from the machine hosting AVIX Server prior to service launch:

                Bash 
                psql -h <serverHost> -p 5432 -U postgres -d avix1

                XML Configuration

                Update repositories.xml on the AVIX Server host to reflect the database parameters: 

                XML 
                <repositoryConfig>
    <repository name="repo1" 
                type="se.solme.avix.server.cdo.postgresql"
                serverHost="localhost"
                portNumber="5432"
                databaseName="avix1"
                databaseUser="postgres"
                databasePassword="postgres"
                useSSL="false"/>
</repositoryConfig>
```[cite: 1]

---

> **Note:** For production deployments, change default passwords and set `useSSL="true"`[cite: 1] along with configured TLS certificates on the PostgreSQL host.