Postgres

Willemers Informatik-Ecke

Initialisierungsarbeiten

Unter Debian wird der PostgreSQL-Server mit dem folgenden Befehl installiert:
apt-get install postgresql
Automatisch wird ein Bentuzer 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 -
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;
Damit sich der Benutzer einloggen kann, muss in der Datei /etc/postgresql/9.1/main/pg_hba.conf eine neue Zeile für die Zugriffe eingetragen werden:
local   all             postgres                                peer
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     md5
Danach muss der PostgresSQL-Dienst neu gestartet werden. Dann sollte ein Einloggen funktionieren:
psql -d dbname -U dbuser
Passwort für Benutzer dbuser: 
psql (9.1.14)

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


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