PostgreSQL
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. Die Datenbank 'template1' ist eine Systemdatenbank und existiert immer.
Dazu muss man zunächst auf den Systembenutzer postgres wechseln:
root@zero:~# su - postgres
Einfache Konsolenkomandos
Aufruf der psql-konsole und Verinden mit der Datenbank template1
postgres@zero:~$ psql template1 psql (9.1.8) Type "help" for help. template1=#
Listen der vorhandenen Datenbanken
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+-------------+-----------+-------------+-------------+-----------------------
davical | davical_dba | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
postgres | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 |
template0 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
xinux_intrra | postgres | SQL_ASCII | de_DE.UTF-8 | de_DE.UTF-8 |
(5 rows)
Verbinden mit der Datenbank davical
postgres=# \c davical You are now connected to database "davical" as user "postgres".
Anzeigen der Objekte der Datenbank
davical=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------------------------+----------+-------------
public | aaaaas_ticket | table | davical_dba
public | aav_id_seq | sequence | davical_dba
public | addressbook_address_adr | table | davical_dba
.......
bei längeren Listen kommt man mit q wieder raus.
Anzeigen der Tabellen der Datenbank
List of relations Schema | Name | Type | Owner --------+--------------------------------------+----------+------------- public | aaaaas_ticket | table | davical_dba public | addressbook_address_adr | table | davical_dba public | addressbook_address_email | table | davical_dba ......
Anzeigen der Infos eines Objekts
davical=# \d addressbook_address_adr
Table "public.addressbook_address_adr"
Column | Type | Modifiers
----------------+--------+-----------
dav_id | bigint | not null
type | text |
box_no | text |
unit_no | text |
street_address | text |
locality | text |
region | text |
postcode | text |
country | text |
property | text |
Foreign-key constraints:
"addressbook_address_adr_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
Anzeigen der Berechtigungen auf ein Objekt
davical=# \z addressbook_address_adr
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-------------------------+-------+---------------------------------+--------------------------
public | addressbook_address_adr | table | davical_dba=arwdDxt/davical_dba+|
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