PostgreSQL

Dieser Artikel wurde für die folgenden Ubuntu-Versionen getestet:


Du möchtest den Artikel für eine weitere Ubuntu-Version testen? Mitarbeit im Wiki ist immer willkommen! Dazu sind die Hinweise zum Testen von Artikeln zu beachten.

Zum Verständnis dieses Artikels sind folgende Seiten hilfreich:

  1. Installation von Programmen

  2. Ein Terminal öffnen

  3. Einen Editor öffnen

  4. mit Root-Rechten arbeiten

Inhaltsverzeichnis
  1. Installation
    1. Datenbankserver aus den offiziellen Quel...
    2. Datenbankserver aus den PostgreSQL-Repos...
  2. Konfiguration
    1. Netzwerkzugriff ermöglichen
    2. Authentifizierung
  3. Administration
    1. Datenbankbenutzer anlegen oder löschen
    2. Datenbanken anlegen oder löschen
    3. Psql
    4. Datenbankclient
    5. phpPgAdmin
    6. DBeaver
  4. Datensicherung
    1. Datensicherung erstellen
    2. Datensicherung wiederherstellen
  5. Links

./postgreSQL_logo.png PostgreSQL 🇬🇧 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 JSON-Format:.

Installation

Datenbankserver aus den offiziellen Quellen

PostgreSQL wird durch folgendes Paket installiert [1]:

Befehl zum Installieren der Pakete:

sudo apt-get install postgresql 

Oder mit apturl installieren, Link: apt://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 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.

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

sudo apt-get -y install postgresql-10 

Hinweis!

Zusätzliche Fremdquellen können das System gefährden.

Konfiguration

Netzwerkzugriff ermöglichen

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 = '*'

Achtung!

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

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:

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 CIDR-konforme Adresse eintragen, hier ein paar Beispiele:

Das Feld Methode sollte in der Regel auf scram-sha-256 stehen, andere Möglichkeiten sind der PostgreSQL-Dokumentation 🇬🇧 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 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:

sudo -u postgres createuser -P -d NUTZERNAME 

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 postgreSQL-Dokumentation 🇬🇧.

Das Löschen eines Datenbankbenutzers geht mit nachfolgendem Kommando:

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:

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:

sudo -u postgres dropdb DATENBANK 

Psql

Nach der Installation des Postgres-Servers kann man sich als Benutzer postgres per psql in PostgreSQL einloggen:

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

1
CREATE DATABASE testdb;

Datenbank wechseln

Wechsele in die Datenbank tesdb (psql spezifisch)

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

1
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):

1
\dt

Datenbanken auflisten

Liste alle Datenbanken auf (psql spezifisch):

1
\l

Zeile in Tabelle einfügen

Füge eine neue Zeile in die Tabelle ein

1
INSERT INTO personen VALUES (1, 'Bar', 'Foo', 'Foo.Bar@ubu.de');

Tabelleninhalt anzeigen

Zeige alle Zeilen der Tabelle an

1
SELECT * FROM personen;

Datenbankclient

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

Befehl zum Installieren der Pakete:

sudo apt-get install postgresql-client 

Oder mit apturl installieren, Link: apt://postgresql-client

phpPgAdmin

./phppgadmin.png ./phppgadmin2.png

phpPgAdmin 🇬🇧 ist eine freie PHP-Applikation zur Administration von PostgreSQL-Datenbanken ähnlich wie phpMyAdmin für MySQL. Die Administration erfolgt über die Web-Oberfläche eines Browsers. Voraussetzung für die Verwendung von phpPgAdmin ist, dass ein Webserver mit PHP auf demselben System installiert und aktiv ist.

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

Befehl zum Installieren der Pakete:

sudo apt-get install phppgadmin 

Oder mit apturl installieren, Link: apt://phppgadmin

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

a2enconf phppgadmin
service apache2 reload 

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

DBeaver

DBeaver logo.png

DBeaver 🇬🇧 ist ein Open-Source Client, mit dem man auf diverse Datenbanksysteme zugreifen kann, auch auf PostgresQL.

DBeaver kann folgendermaßen installiert werden:

# 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 

Hinweis!

Zusätzliche Fremdquellen können das System gefährden.

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

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.

sudo -u postgres pg_dump --format=custom --dbname=datenbank --file=datenbank.dump 

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:

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:

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:

sudo -u postgres psql -f alle-datenbanken.dump postgres