Datenbankserver aus den offiziellen Quellen¶

PostgreSQL wird durch folgendes Paket installiert ^[1]:

• postgresql

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 [signed-by=/etc/apt/keyrings/postgres.gpg] 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
curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo -H gpg --dearmor -o /etc/apt/keyrings/postgres.gpg
# 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 

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

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

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 

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.

1

CREATE DATABASE testdb;

1

\c testdb

1

CREATE TABLE personen ( user_id serial PRIMARY KEY, nachname VARCHAR(200), vorname VARCHAR(200), email VARCHAR(200) UNIQUE NOT NULL);

1

\dt

1

\l

1

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

1

SELECT * FROM personen;

Datenbankclient¶

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

• postgresql-client

Befehl zum Installieren der Pakete:

sudo apt-get install postgresql-client 

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

phpPgAdmin¶

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:

• phppgadmin (universe)

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 🇬🇧 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
curl https://dbeaver.io/debs/dbeaver.gpg.key | sudo -H gpg --dearmor -o /etc/apt/keyrings/dbeaver.gpg
# Füge die Paketquelle hinzu
echo "deb [signed-by=/etc/apt/keyrings/dbeaver.gpg] 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 

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

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