Postgresql

Hardware requirements

CPU

At least 2 cores.

Memory

At least 2GB of RAM.

Disk

The recommended size is at least 1GB of disk.

Installation

To install version 15 follow the following steps:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-15
sudo systemctl enable postgresql

Allow local oka user to be authenticated postgres-side:

sudo sed -i -E "s|host    all(.*)ident|host    all\1md5|g" /etc/postgresql/<VERSION>/main/pg_hba.conf

Different versions are included depending on the selected distribution, please check the following link to see how to install one that fits OKA’s requirements as it might not be the default version:

# https://www.postgresql.org/download/linux/redhat/

Note

Following commands can be done using either yum or dnf

Available version for current distribution can be listed using the following command:

# Check which versions are available
dnf module list postgresql

If default enabled version is not the one you require do:

dnf module enable postgresql:<REQUIRED_VERSION>

Then install the package:

dnf install postgresql-server

Initialize postgres:

sudo postgresql-setup --initdb

Finally, allow local oka user to be authenticated postgres-side:

# Replace 'ident' by 'md5' in /var/lib/pgsql/<VERSION>/data/pg_hba.conf lines 86 and 88
sudo vim /var/lib/pgsql/<VERSION>/data/pg_hba.conf
# or
sed -i -E "s|host    all(.*)ident|host    all\1md5|g" /var/lib/pgsql/<VERSION>/data/pg_hba.conf

Version 15 included in distribution and can be installed with the following command:

sudo dnf -y  install postgresql15 postgresql15-server

Initialize postgres:

sudo postgresql-setup --initdb

Allow local oka user to be authenticated postgres-side:

sudo sed -i -E "s|host    all(.*)ident|host    all\1md5|g" /var/lib/pgsql/data/pg_hba.conf

Warning

The pg_hba.conf file is usually located under either /etc/postgresql/<VERSION>/main/ or /var/lib/pgsql/<VERSION>/data/ depending on the linux distribution you are using. However, in some cases, the <VERSION> folder might not exists. The file can then directly be found under /etc/postgresql/main/ or /var/lib/pgsql/data/. Here, <VERSION> represents the specific PostgreSQL version that you installed (e.g., 14, 15…).

HowTo

Start the database service:

sudo systemctl enable postgresql
sudo systemctl start postgresql

Note

The systemctl service can sometimes be name differently, such as postgresql-15. Check it with systemctl list-units | grep postgres.

Setting up

An oka user (protected by the okapwd password - use a strong password here) and an oka_db database must be created:

sudo -i -u postgres
    psql
        CREATE USER oka WITH PASSWORD 'okapwd';
        ALTER ROLE oka SET client_encoding TO 'utf8';
        ALTER ROLE oka SET timezone TO 'UTC';
        ALTER ROLE oka SET default_transaction_isolation TO 'read committed';
        CREATE DATABASE oka_db;
        GRANT ALL PRIVILEGES ON DATABASE oka_db TO oka;
        ctrl+d
    ctrl+d
sudo service postgresql restart

Warning

Starting from version 15, postgresql changed the default behavior of its public schema. Therefore, as Django requires access to this particular schema, you will need to give access to the database to the user you defined for OKA. This can be done by additionally applying the following commands for example:

GRANT ALL ON DATABASE oka_db TO oka;
ALTER DATABASE oka_db OWNER TO oka;