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/
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}
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:
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
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
remove the # before the line:
listen_addresses = '*'
This allows access from all IP addresses.save the file
allow access from remote workstation
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
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.
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
# 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