PostgreSQL

Willemers Informatik-Ecke

Weitere Informationen zu PostgreSQL finden Sie im Buch Linux-Server für Einsteiger.

Initialisierungsarbeiten

Unter Debian wird der PostgreSQL-Server mit dem folgenden Befehl installiert:
apt-get install postgresql
Automatisch wird ein Benutzer namens postgres angelegt, der für den Start des Server-Prozesses verwendet wird. Auch die Administrationstätigkeiten erfolgen über diesen Benutzer. Da dieser kein Passwort hat, erreicht man ihn über den root-Zugang des Rechners
su - (oder bei Ubuntu sudo -s)
su - postgres
Nun kann der Kommandozeileninterpreter von PostgreSQL aufgerufen werden.
psql
Durch die Tastenkombination Strg-D oder \q kann der Interpreter wieder verlassen werden.

Arbeiten mit PostgreSQL

Innerhalb des Interpreters können SQL-Kommandos abgesetzt werden. Der SQL-Standard braucht hier nicht noch einmal erläutert zu werden, da er an
anderer Stelle bereits ausgeführt ist.

\h für Hilfe über SQL-Anweisungen
\? für Hilfe über interne Anweisungen
\g oder Semikolon, um eine Anfrage auszuführen
\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

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)
Nach \d kann der Name einer Tabelle angegeben werden, um die Struktur der Tabelle anzuzeigen.
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

Sie k&puml;nnen eine Tabelle mit Daten aus einer Textdate füllen. Der Befehl dazu lautet:

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

Transaktionen

Transaktionen können mit BEGIN; eingeleitet werden. Dann erfolgen ein oder mehrere Datenbankbefehle. Mit dem Befehl COMMIT; werden die Befehle bestätigt. Dann werden alle Aktionen durchgeführt. Sollten einer der Befehle fehlschlagen führt ein ROLLBACK; dazu, dass keiner der Befehle ausgeführt wird und der Zustand wiederhergestellt ist, der zum Zeitpunkt des BEGIN; vorlag.

Datensicherung

Mit dem Befehl pg_dump wird eine Datenbank gesichert. Als Parameter wird der Name der Datenbank erwartet. Die Ausgabe erfolgt auf dem Standardausgabekanal und muss umgeleitet werden.

pg_dump mydb > dbsicher

\end{gpListing*} Die Umleitung ermöglicht eine flexible Umleitung des Datenstroms durch Filterwerkzeuge auf beliebige Datenträger. Bei der R\"ucksicherung muss zunächst die Datenbank angelegt werden. Dann kann die Datensicherung mit pqsql eingelesen werden.

createdb mydb
psql mydb < dbsicher

\end{gpListing*} Der Befehl pg_dumpall legt eine komplette Datensicherung der gesamten Datenbankinstallation an.

pg_dumpall > pgsicherung

\end{gpListing*} Die Rücksicherung einer solchen Vollsicherung erfolgt auch per psql.

psql -f pgsicherung postgres

Anlegen eines Benutzers mit eigener Datenbank

su - psql
psql
create user dbuser password 'geheim' login;
create database dbname;
grant all on database dbname to dbuser;
Der Benutzer kann sich nun selbst einloggen und die Tabellen in der Datenbank dbname anlegen.
psql -h localhost -d dbname -U dbuser -W
Passwort für Benutzer dbuser: 
psql (9.1.14)
Welche Benutzer sich einloggen können, wird in der Datei /etc/postgresql/9.1/main/pg_hba.conf definiert. Die folgende Zeile sorgt dafür, dass sich lokale Benutzer per Passwort anmelden können.
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5
Nach Änderungen der pg_hba.conf muss der PostgreSQL-Dienst neu gestartet werden.

Zugriffskonfiguration

Die Konfigurationsdateien von PostgreSQL befinden sich bei Debian im Verzeichnis /etc/postgresql/Versionsnummer/main.

postgresql.conf

Hier wird unter anderem die Netzwerkzugriff eingestellt. Der Parameter listen_addresses ermöglicht den Zugriff über das Netzwerk, wenn ein Stern eingetragen ist. Feinere Zugriffe regelt die Datei pg_hba.conf (siehe unten).
#listen_addresses = 'localhost'
listen_addresses = '*'
port = 5432
PostgreSQL arbeitet standardmäßig auf Port 5432. Ist dieser belegt, auch durch eine alte PostgreSQL-Version, verwendet PostgreSQL den nächsten Port, also beispielsweise 5433.

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     
...
In der Datei /etc/postgresql/9.1/main/pg_hba.conf werden die Zugriffsrechte eingestellt. Neben dem bisher erlaubten localhost sollen nun alle Rechner im Netzwerk 192.168.109.0 ZUgriff erhalten.
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 
Im nächsten Schritt kann ein Anmelden per psql getestet werden.
psql -d dbname -U dbuser -h 192.168.109.142
Durch die Option -h wird eine TCP/IP-Verbindung auch auf dem lokalen Rechner hergestellt. Sollte dies funktionieren, wird auch ein Zugriff von außen klappen, sofern nicht eine Firewall dazwischen sitzt.

pg_hba.conf

Die Datei pg_hba.conf verwaltet, welche Clients auf die PostgreSQL-Datenbank zugreifen dürfen. Sie k&puml;nnen einschränken, dass nur lokale Benutzer auf bestimmten Datenbanken arbeiten dürfen, falls diese beispielsweise ausschließlich für den PHP-Zugriff gedacht ist. Sie k&puml;nnen bestimmte Benutzer auf bestimmte Client-Rechner beschränken. Die Standardkonfiguration erlaubt alle Zugriffe.

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

Grafisches Tools

Das Paket pgadmin3 installiert eine grafische Oberfläche zur Verwaltung von PostgreSQL-Servern.

Homepage (C) Copyright 2001,2010,2015 Arnold Willemer