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

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

sudo mkdir -p /opt/docker/psono/postgres

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):

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

Now lets switch the postgres user
```
sudo -iu postgres
```
Create our new DB
```
createdb psono
```
Now switch the command prompt to postgres command prompt
```
psql psono
```

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

Install some necessary extensions:

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

(optional) If you want to use this database for unit testing, you should also do:
```
ALTER USER psono CREATEDB;
```
To exit this shell and return to your normal user do:
```
\q
Ctrl + D
```
Other databases are not supported because of missing ltree extension
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
```
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):

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

Now lets switch the postgres user
```
sudo -iu postgres
```
Create our new DB
```
createdb psono
```
Now switch the command prompt to postgres command prompt
```
psql psono
```

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

Install some necessary extensions:

CREATE EXTENSION IF NOT EXISTS ltree;
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

(optional) If you want to use this database for unit testing, you should also do:
```
ALTER USER psono CREATEDB;
```
To exit this shell and return to your normal user do:
```
\q
Ctrl + D
```
Other databases are not supported because of missing ltree extension
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
```
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
```

← 0. Installation Preparation 2a. Install Psono CE →