PostgreSQL: Unterschied zwischen den Versionen
Keine Bearbeitungszusammenfassung |
|||
| (13 dazwischenliegende Versionen von 4 Benutzern werden nicht angezeigt) | |||
| Zeile 1: | Zeile 1: | ||
{{Kerndoku|https://doc.hostsharing.net/einstieg/datenbank.html|sowie unter https://doc.hostsharing.net/referenz/datenbanken/index.html}} | |||
== Server == | == Server == | ||
Wir haben pro Host einen einzelnen PostgreSQL-Datenbankserver. Der Zugriff erfolgt über den Hostnamen localhost und den Port 5432. PostgreSQL unterstützt auch eine Verbindung über einen Socket. Dies wird bei uns für administrative Arbeiten genutzt, da hierüber per Konfiguration eine Verbindung ohne Passwortabfrage zugelassen wird. Dementsprechend steht diese Möglichkeit nicht für normale User zur Verfügung. | Wir haben pro Host einen einzelnen PostgreSQL-Datenbankserver. Der Zugriff erfolgt über den Hostnamen localhost und den Port 5432. PostgreSQL unterstützt auch eine Verbindung über einen Socket. Dies wird bei uns für administrative Arbeiten genutzt, da hierüber per Konfiguration eine Verbindung ohne Passwortabfrage zugelassen wird. Dementsprechend steht diese Möglichkeit nicht für normale User zur Verfügung. | ||
| Zeile 11: | Zeile 13: | ||
=== Paketadmin === | === Paketadmin === | ||
Der Paketadmin hat mit seinem Account sehr weitgehende administrative Rechte und sollte dementsprechend mit Umsicht genutzt werden. Er kann mit seinem Account | Der Paketadmin hat mit seinem Account sehr weitgehende administrative Rechte und sollte dementsprechend mit Umsicht genutzt werden. Er kann mit seinem Account mit Hilfe von hsadmin: | ||
* Datenbanken anlegen und löschen | * Datenbanken anlegen und löschen | ||
* Benutzer anlegen und löschen | * Datenbank-Benutzer anlegen und löschen | ||
=== | === Datenbank-Benutzer anlegen === | ||
Du | Du musst Dir vor dem Anlegen einer Datenbank mindestens einen Benutzer für PostgreSQL anlegen. Wir empfehlen für jede Anwendung eine eigene Datenbank mit jeweils eigenem Datenbank-Benutzer. Für einzelne Aufgaben kannst Du neben dem Datenbank-Administrator noch weitere Benutzer anlegen, denen Du natürlich verschiedene Rechte geben kannst, z.B. nur Rechte an bestimmten Tabellen oder zur Ausführung bestimmter Kommandos. | ||
Wir legen | Wir legen einen User an: | ||
siehe [[Datenbanken]] | siehe [[Datenbanken]] | ||
| Zeile 33: | Zeile 30: | ||
Eine neue Datenbank muss in PostgreSQL mit dem Tool [[hsadmin]], beschrieben unter [[Datenbanken]], angelegt werden. | Eine neue Datenbank muss in PostgreSQL mit dem Tool [[hsadmin]], beschrieben unter [[Datenbanken]], angelegt werden. | ||
== Der PostgreSQL | == Web-Frontend == | ||
Das Web-Frontend zur Administration ist unter https://phppgadmin.hostsharing.net/current erreichbar, es läuft die Software [http://www.phppgadmin.net phpPgAdmin]. Hier lässt sich die jeweilige Datenbank mit einem grafischen Webinterface verwalten. | |||
== Der PostgreSQL Command Line Client psql == | |||
Für die interaktive Verbindung von der [[Shell]] aus zu einer Datenbank wird bei PostgreSQL das Programm <code>psql</code> verwendet. | |||
[https://www.postgresql.org/docs/9.4/static/app-psql.html psql] ist der Command Line Client zum Zugriff auf eine Datenbank vom [[Terminal]] aus. In seiner Mächtigkeit steht psql einem GUI-Client wie [https://www.pgadmin.org/ PgAdmin3] in nichts nach. Nur die Bedienung ist etwas gewöhnungsbedürftig. | |||
Nachdem ein User und eine Datenbank angelegt wurden, kann eine erste Verbindung zu PostgreSQL aufgebaut werden: | |||
<pre><nowiki> | <pre><nowiki> | ||
xyz00@hopi$ psql --host localhost --username | xyz00@hopi$ psql --host localhost --username xyz00_benutzer --dbname xyz00_datenbank | ||
Password: passwort | Password: passwort | ||
Welcome to psql, the PostgreSQL interactive terminal. | Welcome to psql, the PostgreSQL interactive terminal. | ||
| Zeile 53: | Zeile 58: | ||
</nowiki></pre> | </nowiki></pre> | ||
Nun können wir | Nun können wir die Steuerbefehle des psql-Clients, aber auch SQL-Befehle eingeben. Zu beachten ist, dass die Zeilen bei SQL-Befehlen mit <code><nowiki>';'</nowiki></code> abgeschlossen werden müssen. | ||
Von <code>psql</code> und einigen anderen Tools werden eine Reihe von Umgebungsvariablen ausgewertet, so dass für die Anmeldung bei der Datenbank nicht immer alle Verbindungsdaten eingetippt werden müssen. Dazu gehören <code>PGDATABASE</code>, <code>PGHOST</code>, <code>PGPORT</code> und <code>PGUSER</code>. Man kann diese Variablen z.B. in seinem Shell-Profile setzen und damit die Anmeldung vereinfachen. Bei <code>/bin/bash</code> als Shell sieht das so aus: | Von <code>psql</code> und einigen anderen Tools werden eine Reihe von Umgebungsvariablen ausgewertet, so dass für die Anmeldung bei der Datenbank nicht immer alle Verbindungsdaten eingetippt werden müssen. Dazu gehören <code>PGDATABASE</code>, <code>PGHOST</code>, <code>PGPORT</code> und <code>PGUSER</code>. Man kann diese Variablen z.B. in seinem Shell-Profile setzen und damit die Anmeldung vereinfachen. Bei <code>/bin/bash</code> als Shell sieht das so aus: | ||
| Zeile 59: | Zeile 64: | ||
<pre><nowiki> | <pre><nowiki> | ||
PGHOST=localhost | PGHOST=localhost | ||
PGUSER= | PGUSER=xyz00_benutzer | ||
PGDATABASE=xyz00_datenbank | PGDATABASE=xyz00_datenbank | ||
export PGHOST PGUSER PGDATABASE | export PGHOST PGUSER PGDATABASE | ||
| Zeile 71: | Zeile 76: | ||
<pre><nowiki> | <pre><nowiki> | ||
xyz00_test=> ALTER USER | xyz00_test=> ALTER USER xyz00_benutzer WITH PASSWORD 'neuespasswort'; | ||
</nowiki></pre> | </nowiki></pre> | ||
== Backups == | |||
Für Backup und Restore werden die beiden Programme <code>pg_dump</code> und <code>pg_restore</code> verwendet. Damit kann auch der Paketadmin eigene Sicherungen anlegen und zurückspielen oder auch eine Datenbank von seiner Entwicklungsumgebung einspielen. | Für Backup und Restore werden die beiden Programme <code>pg_dump</code> und <code>pg_restore</code> verwendet. Damit kann auch der Paketadmin vom Terminal aus eigene Sicherungen anlegen und zurückspielen oder auch eine Datenbank von seiner Entwicklungsumgebung einspielen. | ||
Automatisch wird von [[HS]] von den PostgreSQL-Datenbanken einmal pro Nacht eine Sicherung mit pg_dump angefertigt, die im Backup-Verzeichnis des Paketes abgelegt wird. Die Sicherung erfolgt mit dem folgenden Kommando: | |||
<pre><nowiki> | <pre><nowiki> | ||
| Zeile 86: | Zeile 91: | ||
Der Dump wird dann mit <code>gzip</code> komprimiert und unter dem Namen der jeweiligen Datenbank unter <code>[[~/.bak]]</code> angelegt. | Der Dump wird dann mit <code>gzip</code> komprimiert und unter dem Namen der jeweiligen Datenbank unter <code>[[~/.bak]]</code> angelegt. | ||
== | == Zugriff von ausserhalb == | ||
Da der Server aus Sicherheitsgründen nur auf Connections von localhost und auf Port 5432 reagiert, ist der Zugriff von außen nur über einen SSH-Tunnel möglich. Wer z.B. von außen mit PgAdmin3 auf den Server zugreifen möchte kann das von einem Linuxsystem mit folgendem Skript erreichen (Voraussetzung ist ein funktionierender Zugriff per SSH und die installierten Pakete autossh und pgadmin3): | |||
<syntaxhighlight lang=shell> | |||
#! /bin/sh | |||
TESTCONN=$(lsof | grep -c 55432) | |||
if [ $TESTCONN -eq 0 ] | |||
then | |||
autossh -M 0 -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \ | |||
-f -N -L 55432:127.0.0.1:5432 xyz00-user@xyz00.hostsharing.net | |||
fi | |||
/usr/bin/pgadmin3 "$@" | |||
</syntaxhighlight> | |||
Dieser Skript kann z.B. als '''/usr/local/bin/pgadmin3''' gespeichert und ausführbar gemacht werden und ersetzt somit den normalen Aufruf von '''pgadmin3''' auf der Workstation des Datenbank-Admins. | |||
In pgadmin3 kann dann eine Server-Connection eingerichtet werden, die auf localhost und Port 55432 zugreift und damit über den Tunnel direkt mit dem Datenbank-Server auf Hostsharing verbindet. | |||
Durch die Verwendung von autossh kann dann auch psql vom lokalen System aus denselben Tunnel verwenden. | |||
== Verschiedenes == | == Verschiedenes == | ||
* Online-Dokumentation: Die Online-Doku gibt es unter http://www.postgresql.org/docs/manuals/. Eine Liste von Büchern (einige davon zum Download) unter http://www.postgresql.org/docs/books/. | * Online-Dokumentation: Die Online-Doku gibt es unter http://www.postgresql.org/docs/manuals/. Eine Liste von Büchern (einige davon zum Download) unter http://www.postgresql.org/docs/books/. | ||
Aktuelle Version vom 31. Mai 2024, 13:57 Uhr
Dieses Thema in der technischen Dokumentation (Kerndoku)
https://doc.hostsharing.net/einstieg/datenbank.htmlsowie unter https://doc.hostsharing.net/referenz/datenbanken/index.html
Server
Wir haben pro Host einen einzelnen PostgreSQL-Datenbankserver. Der Zugriff erfolgt über den Hostnamen localhost und den Port 5432. PostgreSQL unterstützt auch eine Verbindung über einen Socket. Dies wird bei uns für administrative Arbeiten genutzt, da hierüber per Konfiguration eine Verbindung ohne Passwortabfrage zugelassen wird. Dementsprechend steht diese Möglichkeit nicht für normale User zur Verfügung.
Keine default DB_User und DB_Datenbank
Auf dem Server werden per Default keine Accounts oder Datenbanken für ein Paket angelegt. Der Paketadmin muss dies mit dem Tool hsadmin vornehmen, da das CREATEDB- und CREATEUSER-Recht nicht an normale Useraccounts vergeben wird.Dabei ist zu beachten, dass sowohl Benutzer- als auch Datenbanknamen mit dem Paketkürzel ( xyz00_ ) anfangen müssen, also z.B. xyz00_datenbank und xyz00_datenbankuser.
Keine Beziehung
Zwischen den Accounts xyz00/xyz00-bla (Shell) und xyz00/xyz00_bla (PgSQL) besteht keine Beziehung.
PostgreSQL-User
Paketadmin
Der Paketadmin hat mit seinem Account sehr weitgehende administrative Rechte und sollte dementsprechend mit Umsicht genutzt werden. Er kann mit seinem Account mit Hilfe von hsadmin:
- Datenbanken anlegen und löschen
- Datenbank-Benutzer anlegen und löschen
Datenbank-Benutzer anlegen
Du musst Dir vor dem Anlegen einer Datenbank mindestens einen Benutzer für PostgreSQL anlegen. Wir empfehlen für jede Anwendung eine eigene Datenbank mit jeweils eigenem Datenbank-Benutzer. Für einzelne Aufgaben kannst Du neben dem Datenbank-Administrator noch weitere Benutzer anlegen, denen Du natürlich verschiedene Rechte geben kannst, z.B. nur Rechte an bestimmten Tabellen oder zur Ausführung bestimmter Kommandos.
Wir legen einen User an:
siehe Datenbanken
Datenbank anlegen
Eine neue Datenbank muss in PostgreSQL mit dem Tool hsadmin, beschrieben unter Datenbanken, angelegt werden.
Web-Frontend
Das Web-Frontend zur Administration ist unter https://phppgadmin.hostsharing.net/current erreichbar, es läuft die Software phpPgAdmin. Hier lässt sich die jeweilige Datenbank mit einem grafischen Webinterface verwalten.
Der PostgreSQL Command Line Client psql
Für die interaktive Verbindung von der Shell aus zu einer Datenbank wird bei PostgreSQL das Programm psql verwendet.
psql ist der Command Line Client zum Zugriff auf eine Datenbank vom Terminal aus. In seiner Mächtigkeit steht psql einem GUI-Client wie PgAdmin3 in nichts nach. Nur die Bedienung ist etwas gewöhnungsbedürftig.
Nachdem ein User und eine Datenbank angelegt wurden, kann eine erste Verbindung zu PostgreSQL aufgebaut werden:
xyz00@hopi$ psql --host localhost --username xyz00_benutzer --dbname xyz00_datenbank Password: passwort Welcome to psql, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DES-CBC3-SHA, bits: 168) xyz00_datenbank=>
Nun können wir die Steuerbefehle des psql-Clients, aber auch SQL-Befehle eingeben. Zu beachten ist, dass die Zeilen bei SQL-Befehlen mit ';' abgeschlossen werden müssen.
Von psql und einigen anderen Tools werden eine Reihe von Umgebungsvariablen ausgewertet, so dass für die Anmeldung bei der Datenbank nicht immer alle Verbindungsdaten eingetippt werden müssen. Dazu gehören PGDATABASE, PGHOST, PGPORT und PGUSER. Man kann diese Variablen z.B. in seinem Shell-Profile setzen und damit die Anmeldung vereinfachen. Bei /bin/bash als Shell sieht das so aus:
PGHOST=localhost PGUSER=xyz00_benutzer PGDATABASE=xyz00_datenbank export PGHOST PGUSER PGDATABASE
Benutzer- und Datenbankname sollten natürlich an das eigene Paket angepasst werden. Anschließend reicht der einfache Aufruf von psql, um eine Verbindung zu dieser Datenbank mit dem angegebenen Benutzernamen aufzubauen.
Ändern des Passworts
Nach dem Anmelden in psql als der jeweilige Benutzer kann das Passwort folgendermaßen geändert werden:
xyz00_test=> ALTER USER xyz00_benutzer WITH PASSWORD 'neuespasswort';
Backups
Für Backup und Restore werden die beiden Programme pg_dump und pg_restore verwendet. Damit kann auch der Paketadmin vom Terminal aus eigene Sicherungen anlegen und zurückspielen oder auch eine Datenbank von seiner Entwicklungsumgebung einspielen.
Automatisch wird von HS von den PostgreSQL-Datenbanken einmal pro Nacht eine Sicherung mit pg_dump angefertigt, die im Backup-Verzeichnis des Paketes abgelegt wird. Die Sicherung erfolgt mit dem folgenden Kommando:
xyz00@hopi:~$ pg_dump --blobs --format=c xyz00_datenbank
Der Dump wird dann mit gzip komprimiert und unter dem Namen der jeweiligen Datenbank unter ~/.bak angelegt.
Zugriff von ausserhalb
Da der Server aus Sicherheitsgründen nur auf Connections von localhost und auf Port 5432 reagiert, ist der Zugriff von außen nur über einen SSH-Tunnel möglich. Wer z.B. von außen mit PgAdmin3 auf den Server zugreifen möchte kann das von einem Linuxsystem mit folgendem Skript erreichen (Voraussetzung ist ein funktionierender Zugriff per SSH und die installierten Pakete autossh und pgadmin3):
#! /bin/sh
TESTCONN=$(lsof | grep -c 55432)
if [ $TESTCONN -eq 0 ]
then
autossh -M 0 -o "ServerAliveInterval 60" -o "ServerAliveCountMax 3" \
-f -N -L 55432:127.0.0.1:5432 xyz00-user@xyz00.hostsharing.net
fi
/usr/bin/pgadmin3 "$@"
Dieser Skript kann z.B. als /usr/local/bin/pgadmin3 gespeichert und ausführbar gemacht werden und ersetzt somit den normalen Aufruf von pgadmin3 auf der Workstation des Datenbank-Admins.
In pgadmin3 kann dann eine Server-Connection eingerichtet werden, die auf localhost und Port 55432 zugreift und damit über den Tunnel direkt mit dem Datenbank-Server auf Hostsharing verbindet.
Durch die Verwendung von autossh kann dann auch psql vom lokalen System aus denselben Tunnel verwenden.
Verschiedenes
- Online-Dokumentation: Die Online-Doku gibt es unter http://www.postgresql.org/docs/manuals/. Eine Liste von Büchern (einige davon zum Download) unter http://www.postgresql.org/docs/books/.