# Install Postgres DB

The Psono server requires a postgres database with some extensions. This section will explain how to install one and prepare it for the Psono server.

# Preamble

This guide is covering the installation on Ubuntu 18.04 LTS and CentOS 7. Ubuntu 12.04+ LTS and Debian based systems should be similar if not even identical to the 18.04 Installation.

# Installation with Docker

If you have docker running, then the database is just one command away:

  1. First create the folder for your data e.g.:

    sudo mkdir -p /opt/docker/psono/postgres
    
  2. Second start the database

    docker run --name psono-database \
     -v /opt/docker/psono/postgres:/var/lib/postgresql/data \
     -e POSTGRES_USER=psono \
     -e POSTGRES_PASSWORD=password \
     -d --restart=unless-stopped \
     -p 5432:5432 postgres:13-alpine
    

    Replace password with a unique password

# Installation with Ubuntu

We will be using postgres (min version 13, yet the latest is recommended):

  1. First install postgres

    sudo apt -y install vim bash-completion wget gnupg lsb-release
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
    echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list
    sudo apt -y update
    sudo apt -y install postgresql-13 postgresql-client-13
    
  2. Now lets switch the postgres user

    sudo -iu postgres
    
  3. Create our new DB

    createdb psono
    
  4. Now switch the command prompt to postgres command prompt

    psql psono
    
  5. Followed by some nice postgres commands to create the user and grant all privileges:

    CREATE USER psono WITH PASSWORD 'password';
    GRANT ALL PRIVILEGES ON DATABASE "psono" to psono;
    GRANT CREATE ON SCHEMA public TO psono;
    

    Replace password with a unique password

  6. Install some necessary extensions:

    CREATE EXTENSION IF NOT EXISTS ltree;
    CREATE EXTENSION IF NOT EXISTS "pgcrypto";
    
  7. (optional) If you want to use this database for unit testing, you should also do:

    ALTER USER psono CREATEDB;
    
  8. To exit this shell and return to your normal user do:

    \q
    Ctrl + D
    

    Other databases are not supported because of missing ltree extension

  9. Adjust pg_hba.conf in /etc/postgresql/13/main/

    Depending on your setup you might get FATAL: Ident authentication failed for user which makes it necessary to adjust the pg_hba.conf e.g. add the following lines:

    host    psono             psono             127.0.0.1/32            md5
    host    psono             psono             ::1/128                 md5
    

    Afterwards restart postgres e.g.

    service postgresql restart
    
  10. Allow network connections

    Depending on your setup you might need postgres to listen on public interfaces, allowing other devices in the network to connect to your host. For that edit postgresql.conf in /etc/postgresql/13/main/ and add the following line:

    listen_addresses = '*'
    

    Afterwards restart postgres e.g.

    service postgresql restart
    

# Installation with CentOS

We will be using postgres (min 13, yet the latest is recommended):

  1. First install some requirements

    sudo yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    sudo yum -y update
    sudo yum -y install postgresql13-server postgresql13-contrib
    sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
    sudo systemctl start postgresql-13
    sudo systemctl enable postgresql-13
    
  2. Now lets switch the postgres user

    sudo -iu postgres
    
  3. Create our new DB

    createdb psono
    
  4. Now switch the command prompt to postgres command prompt

    psql psono
    
  5. Followed by some nice postgres commands to create the user and grant all privileges:

    CREATE USER psono WITH PASSWORD 'password';
    GRANT ALL PRIVILEGES ON DATABASE "psono" to psono;
    GRANT CREATE ON SCHEMA public TO psono;
    

    Replace password with a unique password

  6. Install some necessary extensions:

    CREATE EXTENSION IF NOT EXISTS ltree;
    CREATE EXTENSION IF NOT EXISTS "pgcrypto";
    
  7. (optional) If you want to use this database for unit testing, you should also do:

    ALTER USER psono CREATEDB;
    
  8. To exit this shell and return to your normal user do:

    \q
    Ctrl + D
    

    Other databases are not supported because of missing ltree extension

  9. Adjust pg_hba.conf in /var/lib/pgsql/13/data

    Depending on your setup you might get FATAL: Ident authentication failed for user which makes it necessary to adjust the pg_hba.conf e.g. add the following lines:

    host    psono             psono             127.0.0.1/32            md5
    host    psono             psono             ::1/128                 md5
    

    Afterwards restart postgres e.g.

    systemctl restart postgresql
    
  10. Allow network connections

    Depending on your setup you might need postgres to listen on public interfaces, allowing other devices in the network to connect to your host. For that edit postgresql.conf in /var/lib/pgsql/13/data and add the following line:

    listen_addresses = '*'
    

    Afterwards restart postgres e.g.

    systemctl restart postgresql