PostgreSQL

Aus Xinux Wiki
Zur Navigation springen Zur Suche springen

Installation

root@zero:~# apt-get install postgresql

Bei der Installation über einen Paketmanager wird automatisch ein Datenbank-Cluster erzeugt.

Prüfen, ob der Server läuft: 

root@zero:~# netstat -lntp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      5359/postgres

Verzeichnisstruktur

Konfigurationsdateien: 

/etc/postgresql/*/main/*

Datenbank(en): 

/var/lib/postgresql/*/main/

Logdatei(en): 

/var/log/postgresql/

Kommandoverzeichnis für mitgelieferte Kommandos, die nicht im Pfad liegen: 

/usr/lib/postgresql/*/bin

Starten und Stoppen

root@zero:~# /etc/init.d/postgresql* start

root@zero:~# /etc/init.d/postgresql* stop

Administration

Zugriff auf das Datenbanksystem

Bei der Installation von PostgreSQL wird ein Console-Client mitinstalliert. Da direkt nach der Installation ausser dem Superuser-Account noch keine Benutzer angelegt sind, kann man zu diesem Zeitpunkt nur als der PostgreSQL Superuser postgres auf das System zugreifen.

Dazu muss man zunächst auf den Systembenutzer postgres wechseln: 

root@zero:~# su - postgres

Danach kann man sich über die PostgreSql Console mit dem Datenbanksystem verbinden: 

postgres@zero:~$ psql template1

Die hier angebene Datenbank 'template1' ist eine Systemdatenbank und existiert immer.

Einfache Konsolenkomandos

Auswahl von Kommandos in der Console:

Kommando Beschreibung
\? Zeigt eine Liste von console-spezifischen Kommandos an.
\h Zeigt eine Liste der SQL-Anweisungen an.
\h <SQL-Anweisung> Zeigt eine Hilfe zur angegebenen SQL-Anweisung an
\l Zeigt eine Liste der vorhandene Datenbanken an.
\c <Datenbankname> Verbindet mit der angegebenen Datenbank.
\d Gibt eine Liste von Datenbankobjekten aus.
\dt Gibt eine Liste der Tabellen aus.
\d <Tabellenname> Zeigt die Struktur der angegebenen Tabelle.
\z Zeigt eine Übersicht der Datenbankobjekte mit Zugriffsrechten an.
\z <Tabellenname> Zeigt die Berechtigungen auf eine Tabelle an.
\i <Dateiname> Führt die in der datei einthaltenen SQL-Anweisungen aus.
\q Verlassen der Console

Beim Absetzen einer SQL-Anweisung in der Console wird dieses mit einem Semikolon abgeschlossen.

Anlegen und Löschen von Datenbanken

1. In der psql Console: 

template1=# CREATE DATABASE dummy_database;

template1=# DROP DATABASE dummy_database;

2. Über mitgelieferte Scripte: 

postgres@zero:~$ createdb test

postgres@zero:~$ dropdb test

Anlegen und Löschen von Tabellen

dummy_database=#CREATE TABLE namen (id INTEGER, vorname VARCHAR(25),nachname VARCHAR(40));

Da Tabellendefinitionen recht komplex sein können, werden diese meist in einer Datei gespeichert, die dann an psql übergeben wird. 

dummy_database=#DROP TABLE namen;

Primässchlüssel

Bei der Erstellung der Tabelle: 

dummy_database=# CREATE TABLE namen (id INTEGER, vorname VARCHAR(25),nachname VARCHAR(40),PRIMARY KEY(id));

SERIAL Datentyp

Beim SERIAL Datentyp handelt es sich um einen Autoinkrement-Wert, d.h. ein Wert, der automatisch vom Datenbanksystem erhöht wird. 

dummy_database=#CREATE TABLE namen (id SERIAL, vorname VARCHAR(25),nachname VARCHAR(40));

Anlegen und Löschen von Benutzern

1. In der psql Console: 

dummy_database=# CREATE USER tina WITH PASSWORD 'geheim';

dummy_database=# DROP USER tina;

2. Über mitgelieferte Scripte: 

postgres@zero:~$ createuser -P tina

postgres@zero:~$ dropuser tina

Zugriffsrechte auf Datenbanken / Tabellen

Innerhalb des Datenbanksystems

dummy_database=# GRANT ALL ON namen TO tina;

Das Schlüsselwort ALL steht für alle Rechte.

Schlüsselwort Bedeutung
SELECT Erlaubt SELECT Anweisungen auf dem Objekt.
INSERT Erlaubt INSERT Anweisungen auf dem Objekt.
UPDATE Erlaubt UPDATE Anweisungen auf dem Objekt.
DELETE Erlaubt DELETE Anweisungen auf dem Objekt.
REFERENCES Wird für die Erstellung eines Fremdschlüssels benötigt. Auf beiden betroffenen Tabellen muss dieses Recht gesetzt sein.
CONNECT Benutzer darf sich mit der Datenbank verbinden. Wird zusätzlich zu den in der Konfigurationsdatei pg_hba.conf vorhandenen Einstellungen ausgewertet.

Bei eventuell schon vorhandenen Rechten, gelten die hier angegebenen Rechte zusätzlich. 

dummy_database=# \z namen
              Zugriffsrechte für Datenbank »dummy_database«
 Schema | Name  |   Typ   |                 Zugriffsrechte                  
--------+-------+---------+-------------------------------------------------
 public | namen | Tabelle | {postgres=arwdxt/postgres,tina=arwdxt/postgres}
(1 Zeile)

dummy_database=# REVOKE ALL ON namen FROM tina;

Über Konfigurationsdateien

/etc/postgresql/8.3/main/pg_ident.conf

In dieser Datei können Zuordnungen von Systembenutzern zu PostgreSQL-Benutzern definiert werden. Diese können dann mit einem Namen in der Datei pg_hba.conf verwendet werden. Die Authentifizierung über ident funktioniert nur, wenn auf dem Server ein ident-Server läuft. 

/etc/postgresql/8.3/main/pg_hba.conf

In dieser Datei wird festgelegt, welche User sich von wo mit welcher Datenbank verbinden können.

Die Syntax lautet: 

TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

TYPE bezeichnet dabei den Verbindungstyp (UNIX Sockets oder über TCP/IP).

DATABASE gibt die Datenbank an (Datenbankname oder Schlüsselwort 'all').

USER ist der Benutzer, der sich mit der Datenbank verbindet (Benutzername oder Schlüsselwort 'all').

CIDR-ADDRESS gibt den Host bzw. die Hosts an, von denen aus zugegriffen werden darf.

Beispiel: 

192.168.254.27/32 - nur dieser Host
192.168.254.0/24 - kleines Netzwerk
10.6.0.0/16 - großes Netzwerk

METHOD kennzeichnet die Methode, die zum Anmelden benutzt wird.

trust Die Verbindung wird bedingungslos erlaubt.
reject Verbindung wird zurückgewiesen.
md5 Md5-verschlüsseltes Passwort wird zum Verbinden benötigt.
crypt Ein crypt()-verschlüsseltes Passwort wird zum Verbinden benötigt.
password Ein unverschlüsseltes Passwort wird zum Verbinden benötigt.
ident Die pg_ident.conf wird zum Verbinden benutzt.

Werden die Dateien pg_hba.conf und / oder pg_ident.conf geändert, muss PostgreSQL neu gestartet werden, damit die Änderungen wirksam werden.


Konfiguration

Hauptkonfigurationsdatei von PostgreSQL: 

/etc/postgresql/8.3/main/postgresql.conf

Ausgewählte Parameter:

Name Beschreibung
data_directory PostgreSQL Datenverzeichnis
hba_file Name und Pfad zur Konfigurationsdatei für Zugriffsrechte
ident_file Name und Pfad zur ident Konfigurationsdatei
listen_addresses IP-Adresse, an der der Server auf Verbindungen lauscht.
port Der Port, an dem gelauscht wird.
max_connections maximale Anzahl der gleichzeitigen Verbindungen.
shared_buffers Hauptspeicher, den die Datenbank für Shared Memory Puffer benutzt. Empfohlener Wert: 5%-25% RAM (Achtung: eventuell muss der kernel Parameter kernel.shmmax angepasst werden)
work_mem Hauptspeicher, der für interne Sortieroperationen benutzt wird.

Bei vielen Optionen ist nach einer Änderung ein Neustart des Servers erforderlich.


Anpassen kernel.shmmax 

sysctl kernel.shmmax = <WERT>  (bei Reboot wieder auf ursprünglichem Wert.)

oder:

Anpassen der Datei /etc/sysctl.conf: 

kernle.shmmax = <WERT>

einfügen

Anlegen eines neuen Datenbank-Clusters

Anlegen eines Verzeichnisses für den neuen DB-Cluster und setzen der Rechte: 

root@zero:~# mkdir /var/test_cluster
root@zero:~# chown postgres /var/test_cluster

Neuen Cluster mit allen benötigten Strukturen erzeugen: 

postgres@zero:~$ /usr/lib/postgresql/8.3/bin/initdb -D /var/test_cluster/

Starten des neuen DB-Clusters 

/usr/lib/postgresql/8.3/bin/postgres -D /var/test_cluster

Backup

komplette Datenbank: 

postgres@zero:~$ pg_dump -f dummy_database.dmp dummy_database

Einzelne Tabelle: 

postgres@zero:~$ pg_dump -t namen -f namen.dmp dummy_database

Nur Struktur einer Tabelle: 

postgres@zero:~$ pg_dump -s -t namen -f namen.schema dummy_database

Nur Daten einer Tabelle: 

postgres@zero:~$ pg_dump -a -t namen -f namen.data dummy_database
Abgerufen von „http://wiki.ixheim.de/index.php?title=PostgreSQL&oldid=3328“