sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

Default port is 5432, one can verify server is running with sudo lsof -nPi | grep 5432.

Use the psql client to interact with the database. Switching user to postgres is needed since the identification method is initially by user only (authentication configuration found in /etc/postgresql/14/main/pg_hba.conf)

sudo -u postgres psql

Some basic connection syntax:

# List databases
postgres=# \l

# Connect to database
postgres=# \c postgres

# List tables in database
postgres=# \dt+

# Regular SQL syntax works
postgres=# SELECT * FROM {{tablename}};

# Quit
postgres=# \q

pg_dump -U postgres -F c {{databasename}} > {{backup}}.tar

Create new user:

> sudo -u postgres createuser --interactive
Enter name of role to add: openproject
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Assign user password:

> sudo -u postgres psql
postgres=# \password openproject
Enter new password for user "openproject":
Enter it again:

Create new database:

sudo -u postgres createdb openproject

or

CREATE DATABASE openproject;

Modify required permissions for logging in, see SO, by setting /etc/postgresql/[version]/main/pg_hba.conf:

local all postgres trust
local all all md5

Assign permissions:

GRANT CREATE ON DATABASE openproject TO openproject;