• Initialisierungsarbeiten
• Anlegen einer Datenbank für einen Benutzer
• Arbeiten mit PostgreSQL
• Datensicherung
• Zugriffskonfiguration

Initialisierungsarbeiten

apt-get install postgresql

su - (oder bei Ubuntu sudo -s)
su - postgres

psql

Anlegen eines Benutzers mit eigener Datenbank

su - postgres
psql
psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
postgres=# create user dbuser password 'geheim' login;
CREATE ROLE
postgres=# create database dbname;
CREATE DATABASE
postgres=# grant all on database dbname to dbuser;
GRANT
postgres=# quit

postgres ~$ psql -h localhost -d dbname -U dbuser -W
Passwort für Benutzer dbuser: 
psql (9.1.14)

$ psql -U dbuser dbname -W
Password: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "dbuser"

local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     password

Nach Änderungen der pg_hba.conf muss der PostgreSQL-Dienst neu gestartet werden.

# systemctl restart postgresql

Aus der Linux-Shell Benutzer und Datenbank anlegen

postgres@host:~$ createuser benutzername -P
Enter password for new role:
Enter it again:
postgres@host:~$ createdb --owner=benutzername datenbankname
postgres@host:~$ 

Arbeiten mit PostgreSQL

Beispiel: Anlegen dreier Tabellen

Informationen über Backslash

\h für Hilfe über SQL-Anweisungen \? für Hilfe über interne Anweisungen \g oder Semikolon, um eine Anfrage auszuführen \l zeigt die Datenbanken an. \dt zeigt alle Tabellen der Datenbank an \d Tabelle zeigt die Struktur der Tabelle an \q um zu beenden

Die SQL-Befehle werden mit einem Semikolon abgeschlossen. Zwei Minuszeichen sind das Kommentarzeichen. Der Rest der Zeile wird vom Interpreter ignoriert.

Informationen anzeigen

Der Befehl \du liefert eine Liste aller am System verwendeten Rollen bzw. Benutzer.

Mit \d werden alle Tabellen der Datenbank angezeigt:

dbname=> \d
           List of relations
 Schema |    Name     | Type  | Owner  
--------+-------------+-------+--------
 public | kunde       | table | dbuser
 public | lager       | table | dbuser
 public | lieferanten | table | dbuser
 public | verkauf     | table | dbuser
 public | ware        | table | dbuser
(5 rows)

dbname=> \d kunde
      Table "public.kunde"
  Column   |  Type   | Modifiers 
-----------+---------+-----------
 kundennr  | integer | not null
 name      | text    | 
 anschrift | text    | 
Indexes:
    "kunde_pkey" PRIMARY KEY, btree (kundennr)
Referenced by:
    TABLE "verkauf" CONSTRAINT "verkauf_kunde_fkey" FOREIGN KEY (kunde) REFERENCES kunde(kundennr)

Tabelle mit Daten füllen

COPY mytable FROM '/home/user/tabelleninhalt.txt';

Transaktionen

Datensicherung

Daneben kann auch eine Vollsicherung erfolgen. Diese umfasst alle Datenbanken und Datenbankbenutzer.

Einzelne Datenbanken sichern

$ sudo -s
# su - postres
$ pg_dump mydb > dbsicher

pg_dump -h localhost -U dbuser dbname > dbname.sql

Rücksicherung

$ sudo -s
# su - postres
$

postgres $ createuser dbuser -P
Enter password for new role: 
Enter it again: 
postgres $ createdb --owner=dbuser dbname

postgres $ psql -h localhost -U dbuser dbname < dbname.sql

Komplettsicherung

pg_dumpall > vollsicherung.sql

Die Rücksicherung einer solchen Vollsicherung erfolgt auch per psql.

psql -f vollsicherung.sql postgres

pg_restore --user benutzer --host hostname -c -d datenbank pgsicherung

Zugriffskonfiguration

postgresql.conf

#listen_addresses = 'localhost'
listen_addresses = '*'
port = 5432

Nach einem Neustart der Datenbank kann mit netstat -an geprüft werden, ob der Port freigegeben wurde.

netstat -an
...
tcp        0      0 192.168.109.0:5432      0.0.0.0:*               LISTEN     
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
...

local   all             postgres                                peer
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             192.168.109.0/24        md5 

psql -d dbname -U dbuser -h 192.168.109.142

pg_hba.conf

# Database administrative login by UNIX sockets local all postgres ident # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5

Die Spalten bedeuten im Einzelnen:

• local/host: Unterscheidet, ob der Zugriff lokal oder über das Netzwerk geht.
• Datenbank: Hier kann eine einzelne Datenbank angegeben werden oder all für alle Datenbanken.
• Benuter: Hier können einzelne Benutzer angegeben werden oder all für alle Benutzer.
• IP-Adresse: (bei local fehlt diese Spalte) Es können einzelne Hosts oder Netze spezifiziert werden.
• Authentifizierung: Hier steht, wie sich Benutzer ausweisen.
  • ident: Benutzeranmeldung des Betriebssystems
  • peer:
  • md5: MD5-verschlüsseltes Passwort
  • trust: Blindes Vertrauen
  • reject: Diese Verbindungen werden abgelehnt

Administration per Browser

http://localhost/phppgadmin

Soll der Zugriff auch von anderen Computern erlaubt sein, muss die Konfigurationsdatei von phppgadmin im Apache geändert werden.

# vi /etc/apache2/conf-available/phppgadmin.conf

# Only allow connections from localhost:
#Require local
Require all granted