[[Vorlage(Getestet, focal)]] 

{{{#!vorlage Wissen
[:Pakete_installieren:Installation von Programmen] 
[:Terminal:Ein Terminal öffnen] 
[:Editor:Einen Editor öffnen]
[:mit Root-Rechten arbeiten:]
}}}

[[Inhaltsverzeichnis(2)]] 

[[Bild(./postgreSQL_logo.png, 64, align=left)]] 
[https://www.postgresql.org/ PostgreSQL] {en} nennt sich selbst "The world's most advanced open source database". PostgreSQL ist für Linux, aber auch für alle anderen, gängigen Betriebssysteme wie MacOS, Windows und BSD verfügbar.

PostgreSQL setzt den SQL-Standard 2008 sehr umfassend um. Neben den gängigen Datentypen kann die Datenbank auch nativ mit XML umgehen und ab Version 9.2 auch mit Daten im [wikipedia:JavaScript_Object_Notation:JSON-Format:].

= Installation =
== Datenbankserver aus den offiziellen Quellen ==
PostgreSQL wird durch folgendes Paket installiert [1]: 

{{{#!vorlage Paketinstallation
postgresql
}}}

Dieses Paket installiert die neueste verfügbare Version von PostgreSQL aus den offiziell unterstützten Paketquellen (''main'').

Nach der Installation ist der Postgres-Dienst automatisch aktiviert und kann über die üblichen [:systemd/systemctl/#Kommandos-zum-Steuern-von-Units:Systemctl-Kommandos] gesteuert werden.

== Datenbankserver aus den PostgreSQL-Repositories ==
Das Repository von PostgreSQL wird gut gepflegt und die aktuelle stabile Version kann damit installiert werden, bei Bedarf aber auch ältere Versionen.

{{{#!vorlage Befehl
# Lege die Datei für das Repository an.
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Importiere den Schlüssel, um das Repository zu signieren
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# Update den Paket-Cache
sudo apt-get update
# Installiere die aktuelle Version von PostgresQL
sudo apt-get -y install postgresql
}}}

Optional wird eine ältere Version installiert, indem man die genaue Version angibt, z.B. Version 10:

{{{#!vorlage Befehl
sudo apt-get -y install postgresql-10}}} 

[[Vorlage(Fremd, Quelle)]]

= Konfiguration =
== Netzwerkzugriff ermöglichen ==
[[Anker(netzwerk)]]
PostgreSQL akzeptiert nach der Installation keine Verbindungen von anderen Computern. Um sich über das Netzwerk auf PostgreSQL anmelden zu können, muss in der Datei '''/etc/postgresql/PostgreSQL-Version/main/postgresql.conf''' das "Listen" (Horchen) auf allen Netzwerkkarten erlaubt werden[3].

{{{listen_addresses = '*'}}}

{{{#!vorlage Warnung
Man sollte prüfen, ob dies wirklich erforderlich ist! Denn in der Regel sollte es reichen, wenn PostgreSQL auf der Netzwerkschnittstelle lauscht, auf der auch die Datenbankanwendungen auf diese zugreifen. Insbesondere ohne SSL-Verschlüsselung kann sonst viel Unfug mit der Datenbank angestellt werden!
}}}

== Authentifizierung ==
[[Anker(authentifizierung)]]
In der Datei '''/etc/postgresql/PostgreSQL-Version/main/pg_hba.conf''' erfolgt die Konfiguration, wer welche Datenbank verwenden darf und welche Methoden (Verschlüsselung, Passwort) dafür erforderlich sind [3]. Mit einer '#' beginnende Zeilen sind Kommentare und werden von PostgreSQL nicht beachtet!

Eine gültige Zeile in dieser Datei hat folgendes Format:

{{{
Typ Datenbank Benutzer Adressen Methode
}}}

Folgende Werte sind für das Feld `Typ` möglich:
 * `local` wird für Unix Domain Sockets (nur lokal möglich) verwendet
 * `host` wird für unverschlüsselte TCP/IP-Verbindungen verwendet
 * `hostssl` wird für SSL verschlüsselte TCP/IP-Verbindungen verwendet

Das Feld `Datenbank` sollte entweder auf `all` (Zugriff auf alle Datenbanken) stehen oder den Namen einer vorhandenen Datenbank enthalten. Beim Feld `Benutzer` verhält es sich entsprechend, auch hier steht `all` für alle möglichen Benutzer.

Das Feld `Adressen` darf bei dem Typ `local` nicht gesetzt werden, bei allen anderen Varianten ist es aber zwingend notwendig. In der Regel wird man hier eine [wikipedia:CIDR:]-konforme Adresse eintragen, hier ein paar Beispiele:
  * `192.168.1.0/24` würde alle Adressen aus dem Subnet 192.168.1.0 erlauben
  * `127.0.0.1/32` würde nur Verbindungen von localhost (127.0.0.1) erlauben
  * `0.0.0.0/0` würde Verbindungen von überall erlauben

Das Feld `Methode` sollte in der Regel auf `scram-sha-256` stehen, andere Möglichkeiten sind der [https://www.postgresql.org/docs/current/static/auth-methods.html PostgreSQL-Dokumentation] {en} erklärt.
Um scram-sha-256 zu verwenden, muss sichergestellt sein, dass in der Datei '''/etc/postgresql/PostgreSQL-Version/main/postgresql.conf''' dieser Algorithmus bei password_encryption eingestellt ist.

Eine gültige Zeile, welche dem Benutzer `max` Zugriff auf die Datenbank `buchhaltung` nur über SSL verschlüsselte Verbindungen aus dem Subnet 10.0.0.0/8 erlaubt, würde demnach so aussehen:

{{{
hostssl buchhaltung max 10.0.0.0/8 scram-sha-256
}}}

Nach Änderungen an der PostgreSQL-Konfiguration muss der Server [#steuerung neu geladen] werden.

= Administration =
== Datenbankbenutzer anlegen oder löschen ==
Damit man sich (mit der Standardauthentifizierung) an der Datenbank anmelden kann, ist es nicht ratsam, den Datenbanksuperuser (`postgres`) zu verwenden. Es ist daher ratsam, einen extra Benutzer hierfür anzulegen, dies ist mit folgendem Befehl möglich:

{{{#!vorlage befehl
sudo -u postgres createuser -P -d NUTZERNAME
}}}

{{{#!vorlage Hinweis
Das `-P` als Schalter für `createuser` ist erforderlich, da PostgreSQL sonst nicht nach einem Passwort für den neuen Benutzer fragen würde.

Das `-d` als Schalter für `createuser` ist erforderlich, wenn der Benutzer Datenbanken anlegen können soll.

Siehe auch in der offiziellen [https://www.postgresql.org/docs/current/static/app-createuser.html postgreSQL-Dokumentation] {en}.
}}}

Das Löschen eines Datenbankbenutzers geht mit nachfolgendem Kommando:

{{{#!vorlage Befehl
sudo -u postgres dropuser NUTZERNAME
}}}

== Datenbanken anlegen oder löschen ==
Für jede Anwendung, die man benötigt, ist in der Regel eine eigene Datenbank erforderlich. Diese kann in einem Terminal [2] wie folgt angelegt werden:

{{{#!vorlage Befehl
sudo -u postgres createdb -O NUTZERNAME DATENBANK
}}}

Dieser Befehl würde die Datenbank `DATENBANK` mit dem Eigentümer `NUTZERNAME` anlegen. Als Vorlage für die Datenbank wird die Datenbank Template1 verwendet.

Eine Datenbank kann mit folgendem Befehl wieder gelöscht werden:

{{{#!vorlage Befehl
sudo -u postgres dropdb DATENBANK
}}}
== Psql ==

Nach der Installation des Postgres-Servers kann man sich als Benutzer `postgres` per `psql` in PostgreSQL einloggen:
{{{#!vorlage Befehl
sudo su - postgres -c 'psql'
}}}

Dann können übliche SQL-Kommandos oder auch psql-spezifische Kommandos ausgeführt werden. Bei den SQL-Befehlen selbst muss man nicht zwischen Groß- und Kleinschreibung unterscheiden, es ist aber Standard, diese vollständig in Großbuchstaben zu schreiben. Ein Befehl in psql wird normalerweise mit einem Semikolon aufgeschlossen. Dieses ist jedoch nicht immer notwendig.

Im folgenden einige Beispiele.

=== Datenbank erstellen ===

Erstelle die Datenbank testdb

{{{#!code sql
CREATE DATABASE testdb;
}}}

=== Datenbank wechseln ===

Wechsele in die Datenbank tesdb (psql spezifisch)
{{{#!code sql
\c testdb
}}}           

=== Tabelle erzeugen ===     

Erzeuge die Tabelle `personen`  mit den Spalten `user_id`, `nachname`, `vorname` und `email`. Hierbei muss dann die `email` durch das `UNIQUE` eindeutig in der Spalte sein. Fall diese schon existiert, bricht das Kommando mit einer Fehlermeldung ab. Ebenso ist der Typ `PRIMARY KEY` eindeutig und hierüber wird auch ein Index aufgebaut, d.h. darüber kann sehr schnell in der Datenbank gesucht und zugegriffen werden. 

{{{#!code sql
CREATE TABLE personen ( user_id serial PRIMARY KEY, nachname VARCHAR(200), vorname VARCHAR(200), email VARCHAR(200) UNIQUE NOT NULL);
}}}

=== Tabellen auflisten ===

Liste alle Tabellen und Schemata auf (psql spezifisch):
{{{#!code sql
\dt
}}}

=== Datenbanken auflisten ===
Liste alle Datenbanken auf (psql spezifisch):

{{{#!code sql
\l
}}}

=== Zeile in Tabelle einfügen ===
Füge eine neue Zeile in die Tabelle ein
{{{#!code sql
INSERT INTO personen VALUES (1, 'Bar', 'Foo', 'Foo.Bar@ubu.de');
}}}

=== Tabelleninhalt anzeigen ===

Zeige alle Zeilen der Tabelle an
{{{#!code sql
SELECT * FROM personen;
}}}

== Datenbankclient ==
Soll ein Rechner lediglich auf die Datenbank zugreifen, genügt es, den Datenbank-Client zu installieren [1]:

{{{#!vorlage Paketinstallation
postgresql-client
}}}

== phpPgAdmin ==
[[Bild(./phppgadmin.png, 64, left)]]
[[Bild(./phppgadmin2.png, 200, right)]]

[sourceforge2:phppgadmin:phpPgAdmin] {en} ist eine freie PHP-Applikation zur Administration von PostgreSQL-Datenbanken ähnlich wie [:MySQL/Werkzeuge#phpMyAdmin:phpMyAdmin] für MySQL. Die Administration erfolgt über die Web-Oberfläche eines Browsers. Voraussetzung für die Verwendung von phpPgAdmin ist, dass ein [:Apache:Webserver] mit [:PHP:] auf demselben System installiert und aktiv ist.

Für phpPgAdmin muss das folgende Paket installiert [1] werden:

{{{#!vorlage Paketinstallation
phppgadmin, universe
}}}

Nach der Installation des Pakets müssen noch die folgenden Befehle nacheinander ausgeführt werden:

{{{#!vorlage Befehl
a2enconf phppgadmin
service apache2 reload
}}}

Im Browser ist unter der Adresse `http://localhost/phppgadmin` nun phppgadmin verfügbar.

== DBeaver ==
[[Bild("DBeaver logo.png", 72, left)]] 

[https://dbeaver.io DBeaver] {en} ist ein Open-Source Client, mit dem man auf diverse Datenbanksysteme zugreifen kann, auch auf PostgresQL.

DBeaver kann folgendermaßen installiert werden:

{{{#!vorlage Befehl
# Füge den Schlüssel der DBeaver Paketquelle hinzu
wget -O - https://dbeaver.io/debs/dbeaver.gpg.key | sudo apt-key add -
# Füge die Paketquelle hinzu
echo "deb https://dbeaver.io/debs/dbeaver-ce /" | sudo tee /etc/apt/sources.list.d/dbeaver.list
# Installiere das Paket
sudo apt update && sudo apt install dbeaver-ce
}}}

[[Vorlage(Fremd, Quelle)]]

Nach dem ersten Start von DBeaver verneint man die Frage nach dem Anlegen einer Beispiel-Datenbank. Danach Wählt man PostgresQL aus der Auswahl und bestätigt das Herunterladen der entsprechenden Treiberdateien.

Danach trägt man den Hostnamen, die Datenbank, den Benutzernamen und das Passwort für die Verbindung ein und klickt auf fertigstellen.

= Datensicherung =
{{{#!vorlage Hinweis
Es gilt zu beachten, dass eine Datensicherung __immer__ geprüft wird, dies gilt unabhängig von der eingesetzten Datenbank! Auch sollten die Programme, die zu Datensicherung verwendet werden, immer in einer Version sein, die gleich oder neuer als die der installierten Datenbank ist.

Insbesondere ein "downgrade", also das Wiederherstellen einer Dateisicherung auf einem Datenbankserver in einer kleineren Version wird nicht empfohlen!
}}}

== Datensicherung erstellen ==
Um seine Daten zu sichern oder auf einen anderen Rechner zu kopieren ist eine Datensicherung erforderlich. Auch beim Upgrade von auf ein anderes Major-Release notwendig, also z.B. von 11 auf 13.

Eine einzelne Datenbank (`datenbank`) kann mit dem Befehl `pg_dump` gesichert werden, dabei werden (bis auf wenige Ausnahmen, die nur in Sonderfällen benötigt werden) alle Daten gesichert.

{{{#!vorlage Befehl
sudo -u postgres pg_dump --format=custom --dbname=datenbank --file=datenbank.dump
}}}
{{{#!vorlage Hinweis 
Die Sicherung per pg_dump kann beschleunigt werden, indem man die Option -j verwendet. Dabei werden bei der Sicherung mehr Kerne verwendet, jedoch nur einer pro Tabelle bzw. Partition.
}}}

Um alle im System vorhandenen Datenbanken (inkl. Systemtabellen, Benutzer, Zugriffsrechte, usw …) zu sichern, dient der Befehl `pg_dumpall`, Beispiel:

{{{#!vorlage Befehl
sudo -u postgres pg_dumpall > alle-datenbanken.dump
}}}

== Datensicherung wiederherstellen ==
Eine einzelne Datenbank aus der Datensicherung '''datenbank.dump''' kann in die Datenbank mit dem Namen `datenbank` wie folgt wiederhergestellt werden:

{{{#!vorlage Befehl
sudo -u postgres pg_restore -d datenbank datenbank.dump
}}}

Um eine vollständige Datensicherung aller Datenbanken wiederherzustellen, die sich in der Datei '''alle-datenbanken.dump''' befindet, ist folgender Befehl notwendig:

{{{#!vorlage Befehl
sudo -u postgres psql -f alle-datenbanken.dump postgres
}}}

= Links =
 * [https://www.postgresql.org/ Offizielle PostgreSQL-Webseite] {en} 
 * [https://www.postgresql.org/community/ Offizielle Community Seite] {en} 
 * [ubuntu_doc:lts/serverguide/postgresql.html:Offizielle Ubuntu Dokumentation] {en} 
 * [wikipedia:PostgreSQL:PostgreSQL auf Wikipedia]
 * [https://pgadmin.org/ Offizielle pgAdmin-Webseite] {en}

# tag: Server, Datenbank, SQL