Requirements

You will need a running Postgres PostgreSQL server.

A quick web search will show you how to install one. You basically 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 web host.

...

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

...

1. Install Postgres

   1. Linux

   2. MacOS

   3. Windows

   4. Docker Compose

2. Configure Server

3. 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/

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.

1. create a new folder: database_postgres

2. create a new text file inside the folder called: docker-compose.yml

3. 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}

version: '3.5'

services:
  postgres:
    container_name: postgres_container
    image: postgres:14.4
    environment:
      POSTGRES_USER: {CHANGE HERE}
      POSTGRES_PASSWORD: {CHANGE HERE}
      PGDATA: /data/postgres
    restart: alwaysvolumes:
       - postgres:/data/postgres
    ports:
      - "5432:5432"
    environment:
 networks:
      - postgres
    restart: unless- POSTGRES_USER=postgres
stopped
 
  pgadmin:
    container_name: pgadmin_container
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: {CHANGE HERE}
     - POSTGRES_PASSWORD=passwordPGADMIN_DEFAULT_PASSWORD: {CHANGE HERE}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
       - ./postgres-data:/var/lib/postgresql/data

...

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:

docker-compose up -d

Setup Postgres Server - 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.

Setup Postgres Server - MacOS

It’s recommended to use Homebrew to install Postgres

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

brew install postgres

Setup Postgres Server - Windows

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

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/

Use pgAdmin4 to view & edit your Database.
Set an master password.

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:

1. 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
      

2. 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:

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

# 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

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

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

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

File location pg_hba.conf

Windows: C:\Program Files\PostgreSQL\14\data\pg_hba.conf
MacOS: /usr/local/var/pg_hba.conf

1. configure access to the database

   1. edit postgresql.conf
      Windows: C:\Program Files\PostgreSQL\14\data\postgresql.conf
      MacOS: /usr/local/var/postgresql.conf

   2. edit line: listen_addresses = '*'

2. allow User access

   1. edit pg_hba.conf

   2. edit block:

...

Can not connect to database

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