Database - Postgres

Requirements

You will need a running PostgreSQL server.

You need a dedicated server running where everybody has access to over the network. It’s also possible to host the database in the Cloud or at you webhost. A web search will show you how to install one or see operating system depended installation instructions below.

Creating a new library

When you create a new library the software will automatically create the new database for you.

All you have to provide is the login information (user & password), the database server (server name or IP address) and the database name - how you want to call the new database (Example: das_element). The software will setup all the tables for you. Nothing that you have to do.

Optional: You can use a SSL certificate.

Setup Postgres Server

Install Postgres
Configure Server
Create User

Install Postgres

On a machine that’s accessible by everybody on the network (e.g. Virtual Machine) install the Postgres Software.

Depending on you operating system follow the installation instructions (Step 1) below. Make sure to configure the server correctly (Step 2). The last step (Step 3) is to create a User for the actual library.

Install Postgres - Linux

Installation guide for Debian: https://linuxize.com/post/how-to-install-postgresql-on-debian-10/
Make sure to setup the postgresql.conf and pg_hba.conf correctly. See below.

Install Postgres - MacOS

It’s recommended to use Homebrew to install Postgres

https://www.sqlshack.com/setting-up-a-postgresql-database-on-mac/

CODE

brew install postgres

Make sure to setup the postgresql.conf and pg_hba.conf correctly. See below.

Install Postgres - Windows

Download the installer: https://www.postgresql.org/download/windows/
Installation Guide: https://www.enterprisedb.com/docs/supported-open-source/postgresql/installer/02_installing_postgresql_with_the_graphical_installation_wizard/01_invoking_the_graphical_installer/

Install Postgres - Docker Compose

A easy way to create a new database server is to use Docker Compose
Please make sure to install Docker and Docker Compose first.

create a new folder: database_postgres
create a new text file inside the folder called: docker-compose.yml
add this code snipped into the file …

This example script will create Docker Compose for Postgres & pgAdmin
Please make sure to update marked: {CHANGE HERE}

YAML

version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres
    environment:
      POSTGRES_USER: {CHANGE HERE}
      POSTGRES_PASSWORD: {CHANGE HERE}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped
 
  pgadmin:
    container_name: pgadmin_container
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: {CHANGE HERE}
      PGADMIN_DEFAULT_PASSWORD: {CHANGE HERE}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
       - pgadmin:/root/.pgadmin
    ports:
      - "6060:80"
    networks:
      - postgres
    restart: unless-stopped

networks:
  postgres:
    driver: bridge

volumes:
    postgres:
    pgadmin:

Run this command to start the database:

CODE

docker-compose up -d

After installation you can access pgAdmin via port 6060 to manage the database.
In a web browser navigate to: http://my-server:6060

Configure Postgres Server

After the installation of the Postgres you need to configure the server to allow access from a remote workstation. There are two files that need changes:

configure database to enable networking
1. edit the file: postgresql.conf
  Linux: /etc/postgresql/{version}/main/postgresql.conf
  MacOS: /usr/local/var/postgresql.conf
  Windows: C:\Program Files\PostgreSQL\{version}\data\postgresql.conf
2. remove the # before the line: listen_addresses = '*'
  This allows access from all IP addresses.
3. save the file
allow access from remote workstation
1. edit the file: pg_hba.conf
  Linux: /etc/postgresql/{version}/main/pg_hba.conf
  MacOS: /usr/local/var/pg_hba.conf
  Windows: C:\Program Files\PostgreSQL\{version}\data\pg_hba.conf
2. add this line to the block IPV4:

CODE

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# IPv4 local connections
host    all             all             0.0.0.0/0               trust

Now restart the Postgres service to activate the changes:

CODE

# Linux
sudo service postgresql.service restart

# MacOS (if instaleld with homebrew)
sudo brew services restart postgresql

# Windows
1) press Windows key + R to open the 'RUN' dialog
2) type 'services.msc' hit enter
3) find the 'postgresql' service
4) right click -> restart

Edit Firewall

Allow access to the database server, otherwise the connection gets blocked and Users can not connect to the database.

The default Port for Postgres is 5432

CODE

# Windows
Control Panel > System and Security > Windows Firewall > Allow an app through Windows Firewall

Create Database User

Create a User that can read/write the library information to the database.

The default password for the 'postgres' user should also be 'postgres'.

psql

If you are familiar with the command line you can use psql to create a new user.

The User needs permission to:

login
create databases

CODE

# postgres is the default postgres database
psql --port 5433 postgres

# create user that is allowed to create new databases and read/write information
CREATE ROLE dbuser LOGIN password 'password';
ALTER USER dbuser CREATEDB;

pgAdmin4

If you prefer a graphical user interface, use the tool pgAdmin4 to view & edit your Database. You can install the software on any workstation to access the Database server. When you start the application for the first time you have to set an master password - it’s only for your local workstation to access pgAdmin4.

Dashboard → Add New Server → input information (Host name/address, Username, Password)

Create a Database User to allow access the database.

Set the Name (General tab) and the password (Definition-tab).
The User needs permission (Privileges-tab) to:

login
create databases

Troubleshooting

no pg_hba.conf entry for host

Edit pg_hba.conf and allow Users access the database.

Can not connect to database

Check if the firewall is blocking the connection. By default Postgres is running on Port 5432