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

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

sudo apt-get install postgresql

Allow local oka user to be authenticated postgres-side:

# Replace 'ident' by 'md5' in /etc/postgresql/13/main/pg_hba.conf lines 86 and 88

sudo vim /etc/postgresql/<VERSION>/main/pg_hba.conf
# or
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/

Default distribution version can be installed using the following command:

yum install postgresql-server
# or
dnf install postgresql-server

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

Included versions in this distribution can be found and installed using the following commands:

# Check which versions are available
sudo  amazon-linux-extras | grep postgre

# Install selected version of PGSQL
sudo amazon-linux-extras install -y postgresql<VERSION>
sudo yum install -y postgresql-server postgresql-contrib

# Optionally initialize the database and enable automatic start:
sudo /usr/bin/postgresql-setup --initdb --unit postgresql

Allow local oka user to be authenticated postgres-side:

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

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

dnf -y  install postgresql15

Allow local oka user to be authenticated postgres-side:

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

HowTo

Start the database service:

sudo systemctl enable postgresql
sudo systemctl restart postgresql

Note

The systemctl service can sometimes be name differently, such as postgresql-12. 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;