\documentclass[a4paper,11pt]{article} \usepackage{ngerman} \usepackage[latin1]{inputenc} \setlength\parskip{\medskipamount} \setlength\parindent{0pt} \begin{document} % PostgreSQL % Copyright Steffen Dettmer % Lizenz: GFDL % % $Name$ % $Revision$ % $Source: /cvsroot/selflinux/tutorial/test/gpghandbook/concepts,v $ % SelfLinux-0.7.2 % % Diese Datei ist Teil von SelfLinux http://www.selflinux.de % %%% $Id$ \title{PostgreSQL} \author{Steffen Dettmer} %\url{mailto:steffen@dett.de} \maketitle %\ref{../index.tex} %\ref{anwendungen.tex} Anwendungs-Software %\ref{datenbank1.tex} Datenbanken \ref{PostgreSQL} \par{Layout} Matthias Hagedorn %\url{mailto:herbert-kw@t-online.de} \par{Lizenz} GFDL \tableofcontents{} \section{Einleitung} \label{d71e51} \par Datenbanken Managementsysteme helfen, komplexe Aufgaben einfach zu lösen, da etliche Teilaufgaben von diesen erledigt werden. \par PostgreSQL ist ein relationales Datenbank Managementsystem (RDBMS). Es ist OpenSource und verfügt über Leistungsmerkmale, die es für den Einsatz in Produktionsumgebungen qualifizieren. PostgreSQL gilt wohl als das zuverlässigste und fortschrittlichste OpenSource DBMS. \subsection{Über dieses Dokument} \label{d71e70} \par Dieses Dokument kann nur einen kleinen Überblick über PostgreSQL geben. Viele Themen werden nur angerissen oder überhaupt nicht erwähnt. Da PostgreSQL sehr komplex ist, und ständig weiterentwickelt wird, kann es ebenfalls sein, dass etliche der hier genannte Informationen nicht mehr aktuell sind. Trotz aller Sorgfalt kann es dennoch sein, dass einige der Informationen aus diesem Dokument fehlerhaft sind. \par Es ist versucht worden, dieses Dokument so zu schreiben, dass man nur wenig Datenbankvorwissen benötigt. Grundlegende Linux-Administrationskenntnisse werden benötigt. So sollte bekannt sein, wie man Softwarepakete im Allgemeinen installiert. \par Dieser Text beschäftigt sich hauptsächlich mit den Besonderheiten von PostgreSQL. Um effektiv mit einem Datenbanksystem arbeiten zu können, sollten allgemeine Kenntnisse von relationalen Datenbanken vorhanden sein (Relationen, Normalformen, Datenmodelle, SQL usw.). \par Hier wird auch nicht SQL erklärt. Interessierte finden in einer PostgreSQL Referenz oder in einem SQL Buch Informationen. Es wird jedoch versucht, auf PostgreSQL Eigenheiten einzugehen. Das SQL ist nah am Standard SQL92 und realisiert SQL99 teilweise. \par Dank gebührt Mirko Zeibig für die fachliche Kontrolle und Korrektur, insbesondere vielen Anpassungen und Aktualisierungen für die Version 7.3. Der Autor Steffen Dettmer freut sich natürlich über Kommentare und Anregungen zu diesem Text. \subsection{Datenbanken} \label{d71e114} \par Datenbanken sind nicht einfach nur {\bf Daten in Tabellen}. Vielmehr bestehen sie neben den eigentlichen Daten aus Zugriffsbedingungen und -berechtigungen, Benutzern, Regeln, Funktionen und Weiterem. Sie dienen dazu, umfangreiche Datenmengen zu speichern und wiederzugewinnen und dies mehreren Anwendungen gleichzeitig zu ermöglichen. \par Es geht hier also nicht einfach nur um Datenspeicher, sondern auch um das Knüpfen von Regeln an Daten. So kann man beispielsweise sicherstellen, dass nur bestimmte Benutzer diese Daten lesen oder ändern können. Man kann sicherstellen, dass die Daten Konsistenzbedingungen genügen; beispielsweise, dass gespeicherte Adressen entweder gar keine oder eine fünfstellige Postleitzahl haben, die nur aus Ziffern besteht. \par Datenbanken werden von sogenannten Datenbank Mangementsystemen (DBMS) verwaltet. Nur das DBMS selbst kann direkt auf die Datenbanken zugreifen. Anwendungen greifen immer über das DBMS auf diese zu. Das DBMS prüft und kontrolliert dabei und führt komplexe Operationen im Auftrag von Anwendungen aus. \par Solche Systeme werden eingesetzt, um Datenredundanz zu verringern. In der Regel wird jedes Datum nur einmal in der Datenbank gespeichert. Verschiedene Repräsentationen werden vom DBMS bei Bedarf bereitgestellt, ohne diese Daten etwa zu kopieren. \par Wie bereits angedeutet, werden sie eingesetzt, um die Datenintegrität (Konsistenz) zu gewährleisten. Bei Veränderungen werden beispielsweise notwendige Folgeänderungen automatisch durchgeführt, das Eintragen von inkonsistenten Daten wird verhindert. Des weiteren werden die Daten vor unberechtigtem Zugriff geschützt. Auch sind die Daten unabhängiger von den Anwendungsprogrammen; es spielt keine Rolle, wie diese tatsächlich gespeichert werden. Wird hier etwas geändert, so müssen die Anwendungen nicht alle angepasst werden. \subsection{Schnittstellen und Clients} \label{d71e140} \par Schnittstellen zur Datenbank sind immer Schnittstellen zum Datenbank Managementsystem. Da dies immer so ist, spricht man auch einfach von {\bf Datenbankschnittstellen}. \par Anwendungen kommunizieren über Schnittstellen mit dem DBMS. Diese Schnittstellen sind fast immer in mehrere Ebenen zu unterteilen. Die meisten Schnittstellen sind netzwerkfähig, üblicherweise verwenden DBMS TCP/IP. Darüber setzen Anwendungen Befehle oder Kommandos in Datenbanksprachen ab. Die sicherlich bekannteste Datenbanksprache ist SQL (structured query language). Natürlich verfügt jede Datenbank über eine eigene Schnittstelle, dass heißt, der genaue Aufbau der Kommandos und vor allem die Übertragung über das Netzwerk unterscheiden sich erheblich - selbst bei der Verwendung von SQL. \par DBMS verfügen über Dienstprogramme, die über solche Schnittstellen mit der Datenbank kommunizieren. Diese Dienstprogramme sind für bestimmte Aufgaben unumgänglich, beispielsweise für die erste Einrichtung. Oft bieten diese Zusatzfunktionen, die man für die Administration benötigt, beispielsweise das Anlegen von Benutzern. Zusätzlich bieten die meisten DBMS solche Funktionen auch über SQL an: die Kommandos sind so definiert, dass sie den SQL Standards genügen bzw. diesen nicht widersprechen. Man spricht hier von SQL-Erweiterungen. Die meisten Datenbanken verfügen über etliche Erweiterungen, die man nach Möglichkeit jedoch sparsam einsetzen sollte, um sich spätere Migrationsprobleme zu ersparen. \par Damit Anwendungen nicht so DBMS abhängig sind, gibt es eine weitere Ebene der Schnittstellen, die Programmier- oder Applikationsebene. Das ist in der Regel eine Reihe von Funktionen, die aus einer Programmiersprache aufgerufen werden können. Die wohl bekanntesten Schnittstellen sind hier ODBC und JDBC. ODBC, OpenDataBaseConnectivity, beschreibt, wie Programme mit DBMS kommunizieren können. ODBC selbst ist in etwa eine Funktionsbibliothek, die SQL im Prinzip voraussetzt. ODBC regelt aber auch den Verbindungsaufbau zu einem DBMS, Benutzerauthentifizierung und anderes. JDBC ist das Standard-Verfahren, Java-Anwendungen mit DBMS Unterstützung zu versehen. \par Auch wenn in der Theorie die Schnittstellen ODBC und JDBC suggerieren, dass man das DBMS problemlos transparent wechseln kann, ist das in der Praxis selten so. In der Praxis sind diese Standards nämlich selten vollständig implementiert, und es gibt etliche Punkte, wo sie nicht eindeutig sind. Gerade über ODBC ist es zudem äußerst umständlich, wirklich datenbankunabhängig zu arbeiten, so dass sich meistens einige ärgerliche Abhängigkeiten einschleichen. \par Beispiele für Anwendungen beziehungsweise Clients sind Datenbank Frontends. Die wohl beliebtesten für PostgreSQL sind {\bf psql} (eine textbasierter, interaktiver SQL Interpreter), {\bf pgaccess} (ein graphisches Frontend, mit dem man gut Tabellen, Views, Reports und vieles mehr anlegen und bearbeiten kann) und {\bf phpPgAdmin}, einem sehr flexiblen Webfrontend, dass man sich unbedingt anschauen sollte, wenn man Webfrontends mag. \subsection{PostgreSQL} \label{d71e202} \par PostgreSQL ist ein relationales DBMS (RDBMS). Als Hauptsprache findet SQL Verwendung (natürlich ebenfalls mit etlichen Erweiterungen). Wie viele andere Datenbanken unterstützt auch PostgreSQL nicht den vollständigen SQL Standard. Die Funktionen von PostgreSQL sind aber sehr umfangreich, so dass man in der Praxis selten Beispiele findet, wo sich eine Anforderung nicht realisieren läßt. \par PostgreSQL ist ein Nachfolger von {\bf INGRES} und {\bf POSTGRES}, ist jedoch in vielen Punkten stark erweitert und verbessert worden. Die Entwickler unternehmen große Anstrengungen, um möglichst standardkonform zu sein. Das DBMS ist in weiten Teilen SQL92 kompatibel und unterstützt einiges aus SQL99. Es ist unter einer BSD-Style Lizenz verfügbar. \par Auch wenn bekannt ist, dass es mit großen Datenmengen umgehen kann (es sind Installationen mit mehr als 60 GB Datenbasis bekannt), eignet es sich wohl weniger für Enterprise-Class Anwendungen. Hier sollten professionelle Datenbanken wie IBM DB2 erwogen werden. \par Bei einfachen Datenbankanwendungen, beispielsweise CGI basierten Webanwendungen, die ein paar Adressen speichern, wird von vielen ein einfacheres, schnelleres DBMS vorgezogen: {\bf mySQL}. {\bf mySQL} gilt als schnell installiert und einfach bedienbar. \par Natürlich ist PostgreSQL auch in kleinen Systemen eine sehr gute Wahl, und da kleine Systeme zum Wachsen neigen, ergibt sich hier schnell ein weiterer Vorteil. PostgreSQL wartet dafür auch mit etlichen {\bf high-end} Datenbankfunktionen auf. \subsection{Verfügbarkeit} \label{d71e272} \par PostgreSQL, JDBC und ODBC sind für verschiedene Plattformen verfügbar, beispielsweise für Linux, BSD und Windows. PostgreSQL kann über unixODBC und Perl::DBI verwendet werden, als Programmierplattformen sind C/C++, Java, PHP, Perl, TCL und viele andere bekannt. \par Auch Microsoft Windows Benutzer können Vorteile ziehen, so kann MS Access beispielsweise problemlos über ODBC auf das DBMS zugreifen. Damit kann man die Datenbank gut in die MS Office Anwendungen einbetten. \subsection{Vorteile und Funktionen} \label{d71e292} \par Die ISO Transaktionsmodelle {\bf read committed} und {\bf serializable} werden unterstützt. Umfangreiche Möglichkeiten für Benutzerberechtigungen und Datenbankregeln stehen zur Verfügung. JDBC und ODBC Treiber sind für verschiedene Plattformen verfügbar, beispielsweise für Linux, BSD und Windows. Die Software wird von einer stabilen, erfahrenen und weltweit arbeitenden Gruppe gepflegt und weiterentwickelt. \par PostgreSQL gilt nach 16 jähriger Entwicklungszeit als sehr stabil und zuverlässig. ''Advocacy'' schreibt hierzu: Im Gegensatz zu Benutzern vieler kommerzieller Datenbanksysteme ist es bei Unternehmen, die PostgreSQL einsetzen der Normalfall, dass das Datenbanksystem noch kein einziges Mal abgestürzt ist. Auch nicht bei jahrelangem Einsatz und großem Datenaufkommen. Es läuft einfach. \par Wie bereits erwähnt, versuchen die Entwickler, nah an den Standards zu arbeiten. Fast alle von SQL92 und SQL99 spezifizierten Datentypen werden unterstützt, eigene Datentypen können erzeugt werden. Fremdschlüssel, Trigger und Views sind verfügbar. Alle von SQL99 spezifizierten {\bf joins} sind implementiert. Internationale Zeichentabellen, Unicode und locale gehören ebenso dazu, wie Unterstützung von Unterabfragen (sub queries), GROUP BY, UNION, INTERSECT, LIMIT, LIKE und vollständige POSIX konforme reguläre Ausdrücke, verschiedene Indexverfahren. Das DBMS ist an weiten Teilen erweiterbar. Transaktionen werden unterstützt (ISO {\bf read commited} und {\bf serializable}), umfangreiche Sicherheitskonzepte sind realisierbar (Benutzer, SSL/TLS, Algorithmen). Mehrere CPUs können verwendet werden, eben so {\bf virtuelle hosts}. \par Es gibt viele Erfolgsgeschichten über PostgreSQL; im Internet finden sich viele Projekte und Firmen, die erfolgreich aufwendige Systeme mit dieser Datenbank realisiert haben. \subsection{Grenzen} \label{d71e355} \par PostgreSQL verfügt wohl über keine praxisrelevanten Grenzen mehr. Plant man eine Anwendung, die möglicherweise in die Nähe der im Folgenden angegebenen Werte kommt, sollte man ernsthaft den Einsatz von IBM DB/2 oder anderen Enterprise Class Systemen erwägen. %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} Maximale Datenbankgröße \end{minipage} & \begin{minipage}{60mm} unbegrenzt (60 GB Datenbanken existieren) \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Tabellengröße \end{minipage} & \begin{minipage}{60mm} 64 TB (65536 GB) auf allen Plattformen \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Größe einer Zeile \end{minipage} & \begin{minipage}{60mm} unbegrenzt \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Größe eines Feldes \end{minipage} & \begin{minipage}{60mm} 1 GB \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Anzahl von Zeilen in einer Tabelle \end{minipage} & \begin{minipage}{60mm} unbegrenzt \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Anzahl von Spalten in einer Tabelle \end{minipage} & \begin{minipage}{60mm} 1600 \end{minipage} \\ \hline \begin{minipage}{60mm} Maximale Anzahl von Indizes einer Tabelle \end{minipage} & \begin{minipage}{60mm} unbegrenzt \end{minipage} \\ \hline \end{tabular} \section{Installation} \label{d71e443} \par Die Installation gliedert sich im Wesentlichen in zwei Komplexe. Zum Einen muss zunächst natürlich die Software selbst installiert werden. Zum Anderen müssen einige Dinge eingestellt und eine erste Datenbank muss erzeugt werden. \par Die meisten Linuxdistributionen sollten PostgreSQL als Softwarepakete anbieten. Auf Grund der Größe sind es oft sogar mehrere. Mit der Installation solcher Pakete wird in der Regel auch die Grundkonfiguration durchgeführt, so dass PostgreSQL sofort nach dem Installieren gestartet und benutzt werden kann. \subsection{Installation der Software} \label{d71e462} \par Verfügt die verwendete Distribution über Softwarepakete, so sollten diesem im Allgemeinen vorgezogen werden und mit den DistributionsProgramme installiert werden. Dies spart mindestens viel Arbeit und Zeit. Installiert man beispielsweise die Pakete von SuSE, so kann man die Datenbank sofort nach der Installation starten. \par Es ist natürlich auch möglich, PostgreSQL als Quellpaket über http://www.postgresql.org/ downzuladen und es selbst zu kompilieren. Dies ist insbesondere dann notwendig, wenn man ganz bestimmte Einstellungen benötigt, beispielsweise Unterstützung für bestimmte Zeichensätze. Der Rest dieses Kapitels beschäftigt sich mit diesem Verfahren und kann ausgelassen werden, wenn ein Distributionspaket verfügbar ist. \par PostgreSQL verwendet {\bf ./configure} und {\bf make} zum Übersetzen und verhält sich damit sehr ähnlich zu GNU Software - jedoch sind einige zusätzliche Schritte nach dem Installieren notwendig. Die Installation unter Windows/Cygwin ist nicht Thema dieses Dokumentes, hier wird ausschließlich auf Linux eingegangen. \subsubsection{Vorbedingungen} \label{d71e493} \par Um die Datenbanksoftware selbst übersetzen zu können, müssen etliche Programme verfügbar sein. Eine halbwegs aktuelle Linuxdistribution vorausgesetzt, sind diese aber entweder bereits installiert oder als Softwarepakete verfügbar. \par Neben {\bf GNU-make} ist natürlich ein C Compiler erforderlich. Der {\bf GCC} ist hier gut geeignet. PostgreSQL stellt also keine hohen oder speziellen Anforderungen an das System. \subsubsection{Auspacken und Vorbereiten} \label{d71e516} \par Über http://www.postgresql.org/ besorgt man sich ein Paket der Software. Im Beispiel wird die Version 7.2.1 verwendet. Die Schritten sollten bei neueren (und älteren) Versionen analog sein. \par Die erhaltenen Quellen packt man zunächst aus: \begin{tt} \begin{scriptsize} root@linux / \# tar xzf postgresql-7.2.1.tar.gz \end{scriptsize} \end{tt} \linebreak \par Führt man ein {\bf update} durch, so sollte man unbedingt spätestens jetzt die Datenbank in eine Datei sichern. Das kann man mit dem PostgreSQL Programm {\bf pg\_dump} oder {\bf pg\_dumpall} erledigen: \begin{tt} \begin{scriptsize} root@linux / \# pg\_dumpall \verb+>+ backup.sql \end{scriptsize} \end{tt} \linebreak \par Genaueres findet sich im Abschnitt {\bf Backup}. Anschließend vergewissert man sich, dass das Backup erfolgreich war und stoppt die (alte) Datenbank. Das Datenverzeichnis der alten Datenbank sollte aus Sicherheitsgründen umbenannt werden: \begin{tt} \begin{scriptsize} root@linux / \# mv /usr/local/pgsql /usr/local/pgsql.old \end{scriptsize} \end{tt} \linebreak \par Dieser Pfad ist bei Distributionen in der Regel anders; SuSE und RedHat verwenden beispielsweise {\bf /var/lib/pgsql/data}. \par Nun führt man in dem Verzeichnis, das durch das tar Kommando entstanden ist, {\bf configure} aus. Dabei kann man etliche Optionen angeben. Neben die üblichen GNU Optionen wie beispielsweise {\bf --prefix}, gibt es auch viele PostgreSQL spezifische Optionen. \par Ist ein produktiver Einsatz geplant, so sollte die Dokumentation zu Rate gezogen werden, und ausführliche Tests gefahren werden. \par Einige wichtige Optionen: %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} {\bf --enable-locale} \end{minipage} & \begin{minipage}{60mm} Aktiviert locale-Unterstützung. Dies kostet etwas Performanz, ist aber im nicht-englischsprachigem Raum sehr sinnvoll \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --enable-multibyte} \end{minipage} & \begin{minipage}{60mm} Aktiviert Multibyte Unterstüzung (unter anderem Unicode). Java und TCL erwarten beispielsweise Multibyte. Diese Option sollte daher nach Möglichkeit gesetzt werden. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --enable-nls} \end{minipage} & \begin{minipage}{60mm} Aktiviert Sprachunterstützungen, um Meldungen in Landessprache geben zu können. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --with-CXX} \linebreak {\bf --with-perl} \linebreak {\bf --with-python} \linebreak {\bf --with-tcl} \linebreak {\bf --with-java} \end{minipage} & \begin{minipage}{60mm} C++, Perl, Python, TCL beziehungsweise Java (JDBC) Bibliotheken erzeugen. Für Java wird das Programm ''ant'' benötigt. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --enable-odbc} \linebreak {\bf --with-iodbc} \linebreak {\bf --with-unixodbc} \end{minipage} & \begin{minipage}{60mm} Erzeugt ODBC Treiber. Es kann unabhängig vom DriverManger erzeugt werden (weder --with-iodbc noch --with-unixodbc), für die Verwendung mit iODBC oder unixODBC, nicht jedoch für beide. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --with-openssl} \end{minipage} & \begin{minipage}{60mm} OpenSSL SSL/TLS Unterstützung aktivieren \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --with-pam} \end{minipage} & \begin{minipage}{60mm} PAM Unterstützung aktivieren \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --enable-syslog} \end{minipage} & \begin{minipage}{60mm} Syslog Unterstützung aktivieren (kann dann bei Bedarf konfiguriert werden) \end{minipage} \\ \hline \end{tabular} \par Ein Aufruf könnte also wie folgt aussehen: \begin{tt} \begin{scriptsize} root@linux / \# ./configure --enable-unicode-conversion --enable-multibyte=UNICODE $\backslash$ --with-CXX --with-perl --with-python --with-tcl --with-java $\backslash$ --enable-odbc --with-unixodbc $\backslash$ --with-pam --enable-syslog $\backslash$ --enable-locale \end{scriptsize} \end{tt} \linebreak \par Die Auswahl der Parameter ist kompliziert und hängt von vielen Faktoren ab, daher sollte auf Distributionspakete zurückgegriffen werden, soweit möglich. \subsubsection{Übersetzen und Installieren} \label{d71e735} \par Nach dem {\bf configure} übersetzt man wie gewohnt mit: \begin{tt} \begin{scriptsize} root@linux / \# make \end{scriptsize} \end{tt} \linebreak \par Optional kann man Regressionstests durchführen: \begin{tt} \begin{scriptsize} root@linux / \# make check \end{scriptsize} \end{tt} \linebreak \par Die eigentliche Installation wird mit \begin{tt} \begin{scriptsize} root@linux / \# make install \end{scriptsize} \end{tt} \linebreak \par durchgeführt. Dies macht man in der Regel als {\bf root}. \par Je nach Configure-Optionen installiert PostgreSQL Bibliotheken beispielsweise in {\bf /usr/local/pgsql/lib}. Dieser Pfad sollte dann in {\bf /etc/ld.so.conf} eingetragen werden (das Ausführen von {\bf ldconfig} ist danach notwendig). Gegebenenfalls fügt man den Pfad zu den Binärprogrammen zum Pfad hinzu, beispielsweise in dem man {\bf /usr/local/pgsql/bin} zum {\bf PATH} in {\bf /etc/profile} hinzufügt. \subsection{Grundkonfiguration} \label{d71e804} \par Bevor PostgreSQL gestartet werden kann, müssen noch einige Einstellungen durchgeführt werden. Verwendet man Distributionspakete, so können diese Schritte in der Regel entfallen. \subsubsection{Datenbank Systembenutzer} \label{d71e817} \par Das DBMS benötigt einen Systembenutzer. Darüber hinaus können natürlich viele Datenbankbenutzer existieren. Beide Benutzerarten dürfen keinesfalls verwechselt werden. Der Systembenutzer ist der Benutzer, dem später die Datenbankdateien gehören. Diesem Benutzer ist wohl nie eine Person assoziiert (im Gegensatz zu den Datenbankbenutzern). \par Ein gutes Beispiel für einen Systembenutzernamen ist {\bf postgres}, wie er beispielsweise von SuSE und RedHat verwendet wird ({\bf root} ist in keinem Fall geeignet). Den Benutzer kann man einfach erzeugen: \begin{tt} \begin{scriptsize} root@linux / \# useradd postgres \end{scriptsize} \end{tt} \linebreak \par und so sperren, dass man sich nicht einloggen kann: \begin{tt} \begin{scriptsize} root@linux / \# passwd -l postgres \end{scriptsize} \end{tt} \linebreak \par Nun kann nur {\bf root} über das {\bf su} Kommando auf den {\bf postgres} Benutzer zugreifen. \subsubsection{Erzeugen einer initialen Datenbank} \label{d71e870} \par Um PostgreSQL starten zu können, muss eine Datenbankgrundstruktur vorhanden sein. Hierbei handelt es sich im Wesentlichen um eine komplizierte Verzeichnisstruktur, die mit dem Programm {\bf initdb} erzeugt werden sollte. Dieses Programm sollte auf jeden Fall mit dem oben genannten Benutzer durchgeführt werden. Ist das Datenverzeichnis beispielsweise {\bf /usr/local/pgsql/data}, bietet sich folgende Kommandokette (begonnen als {\bf root}!) an: \begin{tt} \begin{scriptsize} root@linux / \# mkdir /usr/local/pgsql/data \end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} root@linux / \# chown postgres /usr/local/pgsql/data \end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} root@linux / \# su - postgres \end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} root@linux postgres/ \# initdb --pwprompt -D /usr/local/pgsql/data \end{scriptsize} \end{tt} \linebreak \par Dieses Kommando verwendet die gerade eingestellten {\bf locale} als Sortierfolge in Indizes. Diese kann später nicht mehr einfach geändert werden. Ein Nachteil bei der Verwendung von {\bf locale} ist, dass der {\bf LIKE} Operator und reguläre Ausdrücke diese Indizes nicht verwenden können - und dadurch langsam werden. Möchte man lieber keine (oder andere) {\bf locale} für diesen Fall, setzt man vor {\bf initdb} die Variable {\bf LC\_COLLATE}. Beispielsweise kann man die locale auf den Standard {\bf C} setzen: \begin{tt} \begin{scriptsize} root@linux postgres/ \# LC\_COLLATE=''C'' initdb --pwprompt -D /usr/local/pgsql/data \end{scriptsize} \end{tt} \linebreak \par Möchte man dies später ändern, so muss ein Komplettbackup gemacht werden, die Datenbank heruntergefahren, {\bf initdb} erneut ausgeführt und nach dem Start muss das Backup wieder eingespielt werden. Dies ist ja nichts überraschendes; die {\bf sort order} kann man eben nicht nachträglich einstellen, dass ist wohl bei allen DBMS so. \par Durch {\bf initdb} wird eine erste Datenbank erzeugt. Diese heißt {\bf template1}. Wie der Name schon andeutet, wird diese als Vorlage beim Erzeugen neuer Datenbanken verwendet; daher sollte man mit dieser Datenbank nicht arbeiten (da dies zukünftige neue Datenbanken beeinflussen würde). Im Beispielaufruf wird auch gleich ein Passwort für den Datenbankadministrator gesetzt. Setzt man kein Passwort, so gibt es kein gültiges (das heißt, man kann sich nicht als Administrator verbinden, wenn ein Passwort gefordert ist). Auf dieses Verhalten sollte man sich jedoch nicht verlassen, und das System lieber korrekt konfigurieren. \section{Administration} \label{d71e959} \par Der Abschnitt Administration wendet sich an Datenbank Administratoren und beschreibt Aufgaben wie Einrichtung und Backup. Benutzer, die eine von Anderen administrierte Datenbank verwenden, können diesen Abschnitt daher auslassen. \par Dieses Kapitel setzt voraus, dass PostgreSQL bereits installiert ist. Hat man ein Distributionspaket verwendet, so ist vermutlich wenig bis gar nichts an Konfiguration notwendig, wenn man keine besonderen Einstellungen benötigt. \par Es gibt zwei Hauptkonfigurationsdateien: {\bf postgresql.conf} und {\bf pg\_hba.conf}. Die erstere ist die eigentliche Konfigurationsdatei, in der zweiten konfiguriert man Zugriffsbeschränkungen. \par Etliche Aktionen kann man wahlweise über externe Programme oder über SQL-Kommandos durchführen, beispielsweise das Anlegen neuer Datenbankbenutzer. \subsection{Konfiguration} \label{d71e987} \par Dieser Abschnitt richtet sich an fortgeschrittene PostgreSQL Administratoren. Für kleinere Systeme (weniger als 100.000 Datensätze) sind die Voreinstellungen sicherlich ausreichend. In solchen Fällen diesen Abschnitt einfach auslassen. \par Die hier genannten Optionen können auch über Kommandozeilenparameter gesetzt werden. Man sollte natürlich darauf achten, keine widersprüchlichen Optionswerte einzustellen. Einige Optionen kann man auf zur Laufzeit über das SQL Kommando {\bf SET} einstellen. \par Die Konfigurationsdatei heißt {\bf postgresql.conf}. Hier können viele Optionen auf bestimmte Werte gesetzt werden. In jeder Zeile der Datei kann eine Option stehen, die das Format {\bf option = wert} \par hat (genau genommen kann das {\bf =} weggelassen werden). Zeilen, die mit {\bf \#} beginnen, sind Kommentare. \par Zunächst gibt es eine Reihe von Optionen, die das Verhalten des Planers beeinflussen. Hier kann man die relativen Kosten für bestimmte Operationen einstellen. Diese Optionen enden mit {\bf \_cost}. \par Mit den Optionen {\bf debug\_level}, {\bf log\_connections} und {\bf log\_timestamp} kann die Protokollierung beeinflusst werden. Soll diese durch {\bf syslog} erfolgen, kann man dies mit den Optionen {\bf syslog}, {\bf syslog\_facility} und {\bf syslog\_ident} einstellen. \par Die Optionen {\bf deadlock\_timeout} und {\bf default\_transaction\_isolation} beeinflussen das Transaktionslocking. Die Option {\bf password\_encryption} gibt an, ob Passwörter im Klartext oder verschlüsselt gespeichert werden sollen. Mit {\bf fsync} kann gefordert werden, dass die Daten wirklich auf Festplatte geschrieben werden, wenn sie geändert wurden. Dies kostet zwar Performanz, sollte aber aus Sicherheitsgründen aktiviert werden, sonst kann es bei Abstürzen zu Problemen und Datenverlusten kommen. \begin{tabular}{|l|} \hline \begin{tt} postgresql.conf\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} #Beispieldatei postgresql.conf fuer SelfLinux [c] # #Diese Datei zeigt, wie man PostgreSQL fuer groessere Server # einstellen koennte. # Verbindungsoptionen #Verbindungsparameter: TCP akzeptieren tcpip_socket = true # kein SSL verwenden ssl = false #Anzahl gleichzeitiger Verbindungen max_connections = 64 #TCP Port #port = 5432 #hostname_lookup = false #show_source_port = false #Parameter fuer Unix Domain Sockets (alternativ oder zusäztlich zu TCP) #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 #virtual_host = '' # Groesse des Shared Memories. # #2.2er Kernel erlauben erstmal nur 32 MB, jedoch kann das # ohne Reboot erhoeht werden, beispielsweise auf 128 MB: # #$ echo 134217728 >/proc/sys/kernel/shmall #$ echo 134217728 >/proc/sys/kernel/shmmax shared_buffers = 128 # 2*max_connections, min 16 max_fsm_relations = 500 # min 10, Voreinstellung 100, in pages max_fsm_pages = 50000 # min 1000, Voreinstellung 10000, in pages max_locks_per_transaction = 64 # min 10, Voreinstellung 64 wal_buffers = 8 # min 4 #Weitere Speichergroessen in KB: sort_mem = 1024 # min 32, Voreinstellung 512 vacuum_mem = 8192 # min 1024, Voreinstellung 8192 # # Write-ahead log (WAL) # #wal_files = 0 # range 0-64 #wal_sync_method = fsync #wal_debug = 0 # range 0-16 #commit_delay = 0 # range 0-100000 #commit_siblings = 5 # range 1-1000 #checkpoint_segments = 3 # in logfile segments (16MB each), min 1 #checkpoint_timeout = 300 # in seconds, range 30-3600 fsync = true # # Optimizer Optionen # #enable_seqscan = true #enable_indexscan = true #enable_tidscan = true #enable_sort = true #enable_nestloop = true #enable_mergejoin = true #enable_hashjoin = true #Key Set Query Optimizer: viele AND, ORs duerfen # in UNIONs optimiert werden. Achtung, Resultat kann abweichen # (wegen DISTINCT). # Diese Option macht eventuell Sinn, wenn hauptsaechlich ueber # MS Access gearbeitet wird. Handoptimierung sollte natuerlich # immer vorgezogen werden! ksqo = false #effective_cache_size = 1000 # Voreinstellung in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 # # Genetic Query Optimizer Optionen # #geqo = true #geqo_selection_bias = 2.0 # range 1.5-2.0 #geqo_threshold = 11 #geqo_pool_size = 0 # Voreinstellung basiert auf Anzahl der # Tabellen der Abfrage; 128-1024 #geqo_effort = 1 #geqo_generations = 0 #geqo_random_seed = -1 # -1 --> auto # # Logging und Debuganzeigen # #silent_mode = false #log_connections = false #log_timestamp = false #log_pid = false #debug_level = 0 # 0-16 #debug_print_query = false #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # #(nur, wenn entsprechend uebersetzt!) #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # # Statistiken # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_query_stats = false #show_btree_build_stats = false # # Zugriffsstatistiken # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false # # Lock Behandlung # #trace_notify = false #(nur, wenn mit LOCK_DEBUG uebersetzt) #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Allgemeines # #dynamic_library_path = '$libdir' #australian_timezones = false #authentication_timeout = 60 # min 1, max 600 #deadlock_timeout = 1000 #default_transaction_isolation = 'read committed' #max_expr_depth = 10000 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = false #sql_inheritance = true #transform_null_equals = false \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Der Bedarf an Shared Memory wird durch die Kombination der Anzahl von PostgreSQL Instanzen ({\bf max\_connections}) und der geteilten Speicherpuffer ({\bf shared\_buffers}) bestimmt. Erhöht man diese Parameter, kann es sein, dass sich PostgreSQL beschwert, es sei zu wenig Shared Memory vorhanden. \par Wie auch im Kommentar zu lesen, helfen folgende Kommandos, die maximale Größe des Shared Memory in Linux 2.2.x auf beispielsweise 128 MB zu erhöhen: \begin{tt} \begin{scriptsize} root@linux / \# echo 134217728 \verb+>+/proc/sys/kernel/shmall \end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} root@linux / \# echo 134217728 \verb+>+/proc/sys/kernel/shmmax \end{scriptsize} \end{tt} \linebreak \par Diese Kommandos muss man natürlich so ablegen, dass sie beim Systemstart vor dem Start von PostgreSQL ausgeführt werden. \par Ist das Programm {\bf sysctl} installiert, kann man alternativ folgendes in die Datei {\bf /etc/sysctl.conf} eintragen: \begin{tabular}{|l|} \hline \begin{tt} /etc/sysctl.conf\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} kernel.shmall = 134217728 kernel.shmmax = 134217728 \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Durch Ausführen von \begin{tt} \begin{scriptsize} root@linux / \# sysctl -p \end{scriptsize} \end{tt} \linebreak \par werden dann die Einträge der Datei {\bf /etc/sysctl.conf} übernommen. \subsection{Authentifizierung} \label{d71e1152} \par Über die Datei {\bf pg\_hba.conf} (hba: host based access, hostbasierter Zugriff) kann eingestellt werden, von welchen Systemen aus welche Authentifizierung durchgeführt werden muss. So läßt sich beispielsweise einstellen, dass Verbindungen vom Webserver nur auf eine bestimmte Datenbank erfolgen dürfen. \par Eine sehr schöne Funktion ist auch das {\bf Usermapping}. Es kann eingestellt werden, dass bestimmte Benutzer von bestimmten Maschinen aus nur auf ein bestimmtes Benutzerkonto zugreifen können (zum Beispiel, {\bf www-run} des Webservers bekommt den Benutzer {\bf wwwro}, dieser darf dann nur lesen). Diese Funktion steht leider nur zur Verfügung, wenn {\bf ident} verwendet wird, eine Authentifizierung, von der man leider abraten sollte, da sie nur Sinn macht, wenn man den Administratoren dieser Server vertraut. \par Aus Performanzgründen wird diese Datei bei neueren PostgreSQL Versionen nur noch einmalig beim Start und nicht mehr bei jedem Verbindungsaufbau geladen. \par Um PostgreSQL Änderungen an dieser Datei mitzuteilen, kann man als Benutzer {\bf root} auch folgenden Befehl eingeben, statt das DBMS komplett neu zu starten: \begin{tt} \begin{scriptsize} root@linux / \# su -l postgres -s /bin/sh -c ''/usr/bin/pg\_ctl reload -D \$PGDATA -s'' \end{scriptsize} \end{tt} \linebreak \par Hat man das Programmpaket der Distribution installiert, kann man auch einfach: \begin{tt} \begin{scriptsize} root@linux / \# /etc/init.d/postgresql reload \end{scriptsize} \end{tt} \linebreak \par eingeben. \par Jede Zeile ist eine Regel. Eine Regel besteht aus mehreren Teilen, die durch Leerzeichen getrennt sind. Der erste Teil gibt dabei den Regeltyp an. \par Der wichtigste Regeltyp {\bf host} gilt für Netzwerkadressen. Er hat das Format: {\bf host Datenbankname IP-Adresse Netzmaske Authentifizierung} \par Daneben gibt es beispielsweise noch den Typ {\bf local}, der für Verbindungen über Unix-Domain-Sockets verwendet wird. \par Auf dedizierten Datenbankservern, also Servern, die nur die Datenbank fahren und vor allem keine lokalen Benutzerkonten besitzen, verwendet man hier auch oft die Authentifizierung {\bf trust}, also Anmeldung ohne Passwort, da es hier nur {\bf root} gibt, und der darf eh alles. Plant man {\bf cron jobs}, so ist hier {\bf trust} angebracht, da {\bf cron} natürlich keine Passwörter eingibt. Dies ist jedoch problematisch, wenn es Benutzerkonten auf dem System gibt. \par Mögliche Werte für {\bf Authentifizierung}: \par {\bf trust} Keine Authentifizierung, der Benutzername wird akzeptiert (evtl. Passwort gilt als korrekt). \par {\bf password} Klartext-Passwort Authentifizierung. Optional kann eine Passwortdatei angegeben werden. \par {\bf crypt} Verhält sich wie password, über das Netzwerk werden jedoch die Passwörter verschlüsselt übertragen \par {\bf md5} Neuere Versionen bieten MD5 Passwörter an. Diese Option benutzt einen anderen und besseren Algorithmus zur Verschlüsselung als crypt. \par {\bf ident} Der Ident-Daemon wird gefragt. Es ist möglich, über eine Datei {\bf pg\_ident.conf} ein Benutzernamen-Mapping durchzuführen. \par {\bf reject} Die Verbindung wird in jedem Fall abgelehnt. \par Eine Beispielkonfiguration: \begin{tabular}{|l|} \hline \begin{tt} pg\_hba.conf\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} # TYPE DATENBANK IP-ADRESSE NETZMASKE TYP # #Uber Unix-Domain-Sockets darf mit Klartextpasswort verbunden werden # Auf dedizierten Datenbankservern verwendet man hier auch oft # trust, siehe Text local all password #Von localhost darf mit Klartextpasswort verbunden werden host all 127.0.0.1 255.255.255.255 password #192.168.1.3 ist ein Webserver und darf nur auf wwwdb host wwwdb 192.168.1.3 255.255.255.255 crypt #192.168.1.1 ist ein Router und darf gar nichts host all 192.168.1.1 255.255.255.255 reject #Der Admin sitzt auf 192.168.1.4 host all 192.168.1.4 255.255.255.255 md5 #Die Infodatenbank ist für das ganze Netz erlaubt (außer 1.1, siehe oben) host info 192.168.1.0 255.255.255.0 crypt #Die Auftragsabteilung 192.168.2.x fuettert die wwwdb host wwwdb 192.168.2.0 255.255.255.0 crypt \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsection{Starten und Stoppen} \label{d71e1336} \par Nach der Grundkonfiguration kann man die Datenbank starten. Wie man das macht, hängt davon ab, ob man ein Distributionspaket verwendet, oder selbst kompiliert hat. \par Verwendet man ein Distributionspaket, so kann die Datenbank vermutlich sofort gestartet werden oder läuft sogar bereits. \par Das DBMS-Backend von PostgreSQL heißt {\bf postmaster}. Dieses nimmt Verbindungsanfragen an, startet für jede Verbindung einen eigenen {\bf postgres} Prozess, der die eigentliche Arbeit erledigt, und koordiniert die Kommunikation zwischen den einzelnen {\bf postgres} Instanzen. \par Hat man selbst kompiliert, so startet man beispielsweise mit \begin{tt} \begin{scriptsize} root@linux / \# su -c 'pg\_ctl start -D /usr/local/pgsql/data -l serverlog' postgres \end{scriptsize} \end{tt} \linebreak \par oder als Postgres-Systembenutzer mit: \begin{tt} \begin{scriptsize} root@linux / \# postgres\$ pg\_ctl start -D /usr/local/pgsql/data -l serverlog \end{scriptsize} \end{tt} \linebreak \par In der Regel schreibt man sich ein Skript, dass beim Booten ausgeführt wird. Distributionen installieren in der Regel so ein Skript bereits. Dann startet man beispielsweise über \begin{tt} \begin{scriptsize} root@linux / \# rcpostgres start \# SuSE \end{scriptsize} \end{tt} \linebreak \par oder \begin{tt} \begin{scriptsize} root@linux / \# service postgresql start \# RedHat \end{scriptsize} \end{tt} \linebreak \par oder \begin{tt} \begin{scriptsize} root@linux / \# /etc/init.d/postgres* start \# generisch \end{scriptsize} \end{tt} \linebreak \par das DBMS. \par Details finden sich sicherlich im Handbuch. \par Sollte dies der erste Start nach dem Update sein, ist dies vermutlich ein guter Zeitpunkt, um das Backup wieder einzuspielen: \begin{tt} \begin{scriptsize} root@linux / \# psql -d template1 -f backup.sql \end{scriptsize} \end{tt} \linebreak \par Das Herunterfahren der Datenbank erledigt man analog zum Starten: \begin{tt} \begin{scriptsize} root@linux / \# su -c 'pg\_ctl stop' \end{scriptsize} \end{tt} \linebreak \par oder einem Distributionskommando wie zum Beispiel \begin{tt} \begin{scriptsize} root@linux / \# rcpostgres stop \end{scriptsize} \end{tt} \linebreak \par Man kann auch Signale verwenden. Das Signal {\bf SIGKILL} sollte hier unter allem Umständen vermieden werden, da in diesem Fall die Datenbank nicht geschlossen wird - Datenverluste sind fast unvermeidlich. \par Das Signal SIGTERM veranlasst PostgreSQL, so lange zu warten, bis alle Clients ihre Verbindungen beendet haben und ist somit die schonendste Methode. Das Signal {\bf SIGINT} beendet alle Clientverbindungen, und fährt die Datenbank sofort sauber herunter. Letzlich kann man noch {\bf SIGQUIT} verwenden, was die Datenbank sofort beendet, ohne sie sauber herunterzufahren. Dieses Signal sollte daher nicht verwendet werden. Mit einer automatischen Reparatur ist beim Starten anschließend zu rechnen. \par Ein Beispielaufruf: \begin{tt} \begin{scriptsize} root@linux / \# killall -INT postmaster \end{scriptsize} \end{tt} \linebreak \subsection{Mit der Datenbank arbeiten} \label{d71e1470} \par An dieser Stelle wird nur der Vollständigkeit halber {\bf psql} genannt. An späterer Stelle wird genauer darauf eingegangen. \par {\bf psql} ist das {\bf Interaktive Terminal}, eine Art Shell für die Datenbank. Hier kann man SQL Kommandos absetzen. So kann man Datenbanken anlegen, füllen, benutzen und administrieren. \par {\bf psql} erfordert als Parameter den Namen der Datenbank, zu der verbunden werden soll. Es gibt meistens mindestens die Datenbank {\bf template1}. Über Optionen kann man angeben, auf welchen Server die Datenbank läuft und welcher Benutzername verwendet werden soll. Möchte man beispielsweise als Administrator {\bf postgres} zu der Datenbank {\bf template1} auf {\bf localhost} verbinden, kann man schreiben: \begin{tt} \begin{scriptsize} root@linux / \# psql -h localhost -U postgres template1 \end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} Password: \linebreak Welcome to psql, the PostgreSQL interactive terminal. \linebreak \linebreak Type:\verb+ +$\backslash$copyright for distribution terms \linebreak \verb+ +\verb+ +\verb+ + $\backslash$h for help with SQL commands \linebreak \verb+ +\verb+ +\verb+ + $\backslash$? for help on internal slash commands \linebreak \verb+ +\verb+ +\verb+ + $\backslash$g or terminate with semicolon to execute query \linebreak \verb+ +\verb+ +\verb+ + $\backslash$q to quit \linebreak \linebreak template1=\#\end{scriptsize} \end{tt} \linebreak \par Unten sieht man das Prompt (das den Datenbanknamen beinhaltet). Hier kann man SQL Kommandos eingeben, beispielsweise: \begin{tt} \begin{scriptsize} template1=\# SELECT version(); \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +version \linebreak --------------------------------------------------------------- \linebreak PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Hier läuft also die Version 7.0.2 (welches mal ein Update vertragen könnte). {\bf psql} kennt zusätzlich zu den SQL Kommandos sogenannte {\bf interne} Kommandos. Diese beginnen mit einem {\bf $\backslash$} (backslash). Diese lassen sich mit {\bf $\backslash$?} auflisten. Mit {\bf $\backslash$h} kann man auf umfangreiche Hilfe zurückgreifen, eine SQL Referenz. Mit {\bf $\backslash$q} beendet man das Programm. \subsection{Datenbanken planen} \label{d71e1554} \par Zunächst sollte man natürlich seine Datenbank planen. Vielleicht erstellt man ein paar {\bf Entity-Relationship-Diagramme} (ER Modelle). Diese kann man dann in eine Normalform übertragen (die Tabellen normieren), bis man in etwa die {\bf 3. Normalform} erreicht hat. \par Dann überlegt man sich Wertebereiche, Gültigkeiten und Abhängigkeiten. Aus den {\bf Standard-Use-Cases} kann man oft recht einfach die erforderlichen Berechtigungen und zu erzeugenden Views ableiten. \par Hat man das erledigt, kann man beginnen, die Datenbank zu erzeugen und die Tabellen anzulegen. Oft schreibt und testet man Konsistenzprüfungsfunktionen wie {\bf Trigger} vor dem Anlegen der Tabellen. Auch die Dokumentation sollte man nicht vergessen. Im Internet findet man Hilfen zur Datenbankplanung (die Planung ist ja nicht PostgreSQL spezifisch). \par Nun sollte man Testdaten erzeugen. Diese sollten vom Umfang her fünf- bis zehnmal mächtiger als die zu erwartenden Daten sein, wenn möglich. Nun testet man das System und optimiert gegebenenfalls. In einem frühen Stadium ist die Optimierung oft noch einfach - später wird es dann kompliziert, weil man oft {\bf Kompatiblitätsmodus-Views} und ähnliche Workarounds benötigt, da die Anwendungen selten alle auf einen Schlag angepasst werden können. \par Wenn man die Datenbank entwickelt und nicht ständig Skripte nachpflegen möchte, kann man nach dem Erzeugen des Schemas (also der Tabellen und was so dazugehört) mit dem Programm {\bf pg\_dump} das Schema in ein Skript schreiben, und dieses kommentieren: \begin{tt} \begin{scriptsize} root@linux / \# pg\_dump --schema-only database -f schema.sql \end{scriptsize} \end{tt} \linebreak \par Das ist bei kleinen Projekten oder in frühen Stadien oft eine nützliche Hilfe. \subsection{Datenbanken erzeugen und löschen} \label{d71e1607} \par Es gibt zwei Möglichkeiten, neue Datenbanken zu erzeugen. Man kann das Programm {\bf createdb} verwenden. Dieses verwendet das SQL Kommando {\bf CREATE DATEBASE}, um eine neue Datenbank zu erzeugen. \par {\bf createdb} versteht etliche Optionen, die sehr ähnlich zu denen von {\bf psql} sind. Man kann auch {\bf psql} verwenden, und dann mit dem SQL Kommando {\bf CREATE DATEBASE} eine Datenbank erzeugen. Als einzigen geforderten Parameter gibt man den Namen der zu erzeugenden Datenbank an. Beispiel: \begin{tt} \begin{scriptsize} template1=\# CREATE DATABASE test; \linebreak CREATE DATABASE\end{scriptsize} \end{tt} \linebreak \par Datenbanken kann man mit {\bf DROP DATABASE} löschen. Achtung, diese Kommandos sind eigentlich kein SQL Kommandos (kein Abfragekommando, also nicht {\bf query language}), sondern ein sogenannte Strukturkommandos. Diese lassen sich nicht in Transaktionen ausführen und damit insbesondere nicht rückgängig machen! \par Versucht man ein {\bf DROP DATABASE test;} in einer Transaktion, so wird das Kommando ignoriert. \subsection{Benutzer und Gruppen} \label{d71e1662} \par Analog zu Datenbanken kann man Benutzer über das Programm {\bf createuser} oder über {\bf psql} anlegen. Das SQL (Struktur-) Kommando heißt {\bf CREATE USER}. Hier gibt es eine Vielzahl von Parametern; beispielsweise, ob der Benutzer Datenbanken anlegen darf ({\bf CREATEDB}) oder nicht ({\bf NOCREATEDB}), ob der Benutzer weitere Benutzer anlegen darf ({\bf CREATEUSER}) oder nicht ({\bf NOCREATEUSER}), welches Passwort er bekommt ({\bf PASSWORD} {\bf geheim}), in welchen Gruppen er ist ({\bf IN GROUP} gruppe1, gruppe2, ...) und wie lange er gültig ist ({\bf VALID UNTIL} {\bf Zeitstempel}). \par Dieses Kommando legt einen Benutzer {\bf steffen} mit einem sehr schlechten Passwort an: \begin{tt} \begin{scriptsize} template1=\# CREATE USER steffen WITH PASSWORD '123' NOCREATEDB NOCREATEUSER; \linebreak CREATE USER\end{scriptsize} \end{tt} \linebreak \par Auch hier gibt es ein {\bf DROP USER}. \par Änderungen werden über das Kommando {\bf ALTER USER} durchgeführt: \begin{tt} \begin{scriptsize} template1=\# ALTER USER steffen PASSWORD 'geheim'; \linebreak ALTER USER\end{scriptsize} \end{tt} \linebreak \par Gruppen werden mit {\bf CREATE GROUP} erzeugt. Man kann die Benutzer aufzählen, die Mitglied werden sollen ({\bf USER} benutzer1, benutzer2, ...). Es gibt auch {\bf DROP GROUP}, um Gruppen zu löschen. \par Alle hier genannten Kommandos sind Strukturkommandos und unterliegen nicht (ganz) den Transaktionsregeln. Ein Rollback auf ein {\bf DROP USER} funktioniert nicht (vollständig). \par Zum Hinzufügen bzw. Entfernen von Benutzern zu Gruppen stehen die Kommandos \begin{tt} \begin{scriptsize} template1=\# ALTER GROUP gruppe1 ADD USER steffen, elvira \linebreak template1=\# ALTER GROUP gruppe1 DROP USER elvira\end{scriptsize} \end{tt} \linebreak \par zur Verfügung. \par Für den Datenbank Superuser oder Administrator gelten Sonderregeln, die im Abschnitt Privilegien kurz erklärt werden. \subsection{Privilegien} \label{d71e1775} \par Privilegien sind Zugriffsrechte. PostgreSQL unterstützt hier verschiedene Arten: %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} {\bf SELECT} \end{minipage} & \begin{minipage}{60mm} das Leserecht \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf INSERT} \end{minipage} & \begin{minipage}{60mm} darf neue Datensätze einfügen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf UPDATE} \end{minipage} & \begin{minipage}{60mm} darf Datensätze ändern und Sequenzen verwenden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf DELETE} \end{minipage} & \begin{minipage}{60mm} darf Datensätze löschen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf RULE} \end{minipage} & \begin{minipage}{60mm} darf Regeln für Tabellen erzeugen (eine PostgreSQL Erweiterung) \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf REFERENCES} \end{minipage} & \begin{minipage}{60mm} darf einen Schlüssel dieser Tabelle als Fremdschlüssel verwenden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf TRIGGER} \end{minipage} & \begin{minipage}{60mm} darf {\bf Trigger} an der Tabelle erzeugen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf CREATE} \end{minipage} & \begin{minipage}{60mm} darf Objekte in Datenbank (ab 7.3 auch Schemata) anlegen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf TEMPORARY} \end{minipage} & \begin{minipage}{60mm} darf temporäre Tabelle in Datenbank anlegen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf EXECUTE} \end{minipage} & \begin{minipage}{60mm} darf Funktion ausführen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf USAGE} \end{minipage} & \begin{minipage}{60mm} darf Sprache (z.B. PL/pgSQL) oder Objekte in Schema (ab 7.3) benutzen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf ALL} \end{minipage} & \begin{minipage}{60mm} darf alles \end{minipage} \\ \hline \end{tabular} \par In SQL werden Privilegien über {\bf GRANT} erlaubt und mit {\bf REVOKE} entzogen. Das {\bf Grant}-Kommando ist SQL92 konform. Um sicherzugehen, dass nicht bereits andere Rechte gesetzt sind, führt man vor einem {\bf GRANT} manchmal auch ein {\bf REVOKE} aus, um alle Rechte erstmal zu löschen. \par Der Benutzer wwwro darf statistics nur lesen: \begin{tt} \begin{scriptsize} template1=\# REVOKE ALL ON statistics FROM wwwro; \linebreak template1=\# GRANT SELECT ON statistics TO wwwro;\end{scriptsize} \end{tt} \linebreak \par Die Gruppe stats darf alles auf dieser Tabelle: \begin{tt} \begin{scriptsize} template1=\# GRANT ALL ON statistics TO GROUP stats;\end{scriptsize} \end{tt} \linebreak \par Der Eigentümer hat automatisch immer alle Bereichtigungen. Den Eigentümer kann man über beispielsweise mit: \begin{tt} \begin{scriptsize} template1=\# ALTER TABLE statistics OWNER TO steffen;\end{scriptsize} \end{tt} \linebreak \par einstellen. \par Es gibt einen besonderen Benutzer, den Superuser oder Administrator. Dieser darf jegliche Aktion immer durchführen; die Privilegien werden nicht ausgewertet. Der Superuser darf auch über ein Kommando jede andere Identität einstellen, das ist bei Tests sehr sinnvoll. Dieses Kommando läßt sich in etwa mit dem Unix-Kommando {\bf su} vergleichen. Das geschieht wie folgt: \begin{tt} \begin{scriptsize} template1=\# SET SESSION AUTHORIZATION 'steffen';\end{scriptsize} \end{tt} \linebreak \par Einige Aktionen können nur vom Superuser durchgeführt werden, beispielsweise das Installieren neuer Sprachen mit Systemberechtigungen. \subsection{Vacuum} \label{d71e1988} \par Gelöschte bzw. geänderte Datensätze sind lediglich als solche markiert und noch auf der Festplatte vorhanden. Darum ist es erforderlich, regelmäßig den Speicher aufzuräumen. Dies sollte man machen, wenn die Datenbank gerade wenig zu tun hat, beispielsweise nachts. Hierzu dient das SQL Kommando {\bf VACUUM}. Dieses gibt nicht mehr benutzten Speicher frei. Es gibt auch ein Programm {\bf vacuumdb}, das man als {\bf cronjob} einrichten kann. \par Eine Option von {\bf VACUUM} ist {\bf ANALYZE}, die die Statistiktabellen für den Planer (Optimizer) aktualisiert.Da die Geschwindigkeit, mit der PostgreSQL agiert, entscheidend von diesen Daten abhängt, sollte man die Analyse häufiger, auf jeden Fall aber nach einer größeren Anzahl von {\bf INSERTs} oder {\bf UPDATEs} durchführen. Eine reine Analyse belastet den Rechner auch weniger und kann daher auch stündlich durchgeführt werden. \linebreak Hierzu kann z.B. folgendes Skript dienen, welches vom Benutzer postgres ausgeführt werden muss: \begin{tabular}{|l|} \hline \begin{tt} analyzedbs\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} #!/bin/bash # analyzedbs (c) 2003 by SelfLinux.de # analysiert PostgreSQL-Datenbanken ohne Vacuum # PSQL=/usr/bin/psql dbs=`$PSQL -U postgres -q -t -A -d template1 \ -c 'SELECT datname FROM pg_database WHERE datallowconn'` for db in $dbs ; do $PSQL -q -c "SET autocommit TO 'on'; ANALYZE" -d $db done \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par \begin{tt} \begin{scriptsize} root@linux / \# su -l postgres -c analyzedbs \end{scriptsize} \end{tt} \linebreak \par Hat man keine besonderen Anforderungen, führt man die {\bf ANALYZE} zusammen mit {\bf VACUUM} aus. Ein Beispielaufruf: \begin{tt} \begin{scriptsize} root@linux / \# vacuumdb --all --analyze --full --username=postgres \end{scriptsize} \end{tt} \linebreak \par Seit Version 7.2 werden Tabellen während {\bf VACUUM} nicht mehr komplett gesperrt, man kann dieses aber durch die Angabe von {\bf --full} erzwingen und so eine bessere Kompression des Datenbestandes erreichen. \par Verwendet man einen {\bf cron-Job}, so sollte in {\bf pg\_hba.conf} für Typ {\bf local} die Authentifizierung {\bf trust} verwenden, da {\bf cron} keine Passwörter eingibt. \par Man kann beispielsweise in die Datei {\bf /etc/cron.d/postgresql} eintragen: \begin{tabular}{|l|} \hline \begin{tt} /etc/cron.d/postgresql\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} 0 0 * * * postgres vacuumdb --all --full --analyze 5 * * * * postgres /usr/local/bin/analyzedbs \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Hier muss man beachten, dass {\bf cron} nicht die {\bf /etc/profile} auswertet, und damit {\bf vacuumdb} nicht unbedingt im Pfad liegt. Hier sollte man lieber absolute Pfade angeben. \subsection{Sprachen installieren} \label{d71e2123} \par Ähnlich zu Datenbanken und Benutzern kann man Sprachen, genauer gesagt, prozedurale Sprachen, in die Datenbank installieren. Etliche Sprachen sind Lieferumfang von PostgreSQL. Diese Sprachen liegen als Systembibliotheken im {\bf lib} Verzeichnis, also beispielsweise {\bf /usr/local/lib}. \par Hier gibt es ein Programm {\bf createlang}. Dieses verwendet das SQL Kommando {\bf CREATE LANGUAGE}, um die Sprache zu installieren, führt jedoch zusätzlich etliche Prüfungen durch, und wird daher empfohlen. \par Um Beispielsweise die Sprache PL/pgSQL auf einem SuSE 7.0 System zu installieren, genügt folgendes Kommando: \begin{tt} \begin{scriptsize} root@linux / \# createlang --username=postgres --pglib=/usr/lib/pgsql/ plpgsql \end{scriptsize} \end{tt} \linebreak \par Der Pfad {\bf /usr/lib/pgsql/} muss angepasst werden. Neben PL/pgSQL sind auch noch PL/TCL (TCL für PostgreSQL) und PL/Perl (Perl-Sprache) sehr beliebt und mit PostgreSQL verfügbar. \par Es gibt zwei Möglichkeiten, Sprachen zu installieren: {\bf trusted} und {\bf untrusted}. Da die wörtlichen Übersetzungen nicht weiterhelfen, folgt eine Erklärung. Eine {\bf untrusted} Sprache darf mehr, als eine {\bf trusted} Sprache. Von einer {\bf trusted} Sprache wird erwartet, dass über diese keine normalerweise verbotenen Aktionen durchgeführt werden können. PL/pgSQL ist ein Beispiel. \par PL/Perl kann auch im {\bf untrusted} Modus installiert werden (wird dann oft plperlu genannt). Dann kann der komplette Sprachumfang von Perl verwendet werden. So kann z.B. eine Perlfunktion erstellt werden, die eine Mail verschickt. Dies gibt dem Benutzer damit automatisch die Berechtigungen des Unix-PostgreSQL Benutzers postgres. Daher können {\bf untrusted} Sprachen nur vom Datenbank Superuser installiert werden. Die Funktionen in solchen Spachen müssen selbst für Sicherheit sorgen. \par Die PostgreSQL Sprachen (wie PL/Perl) haben natürlich Einschränkungen zu den normalen Versionen (wie Perl). Dies sind zum einen Sicherheitseinschränkungen von trusted Modus Sprachen, und zum anderen Dinge, die aus technischen Gründen nicht gehen (in PL/Perl kann man beispielsweise {\bf noch} nicht andere PL/Perl Funktionen aufrufen). \subsection{Backup} \label{d71e2245} \par Es gibt mehrere Arten, Backups anzufertigen. Es gibt die Programme {\bf pg\_dump} und {\bf pg\_dumpall}. Das erste schreibt eine Datenbank in eine Datei, das zweite sichert alle Datenbanken. Beide kennen eine Vielzahl von Parametern. So kann man sich beispielsweise eine Folge von {\bf INSERT} Kommandos erzeugen lassen, was hilfreich ist, wenn man dieses Backup auch in anderen Datenbanken verwenden möchte, die nicht PostgreSQL basiert sind (oder wenn man zu anderen DBMS wechseln möchte/muss). \subsubsection{pg\_dump} \label{d71e2267} \par Es wird als Parameter der Datenbankname erwartet. Die Ausgabe (den {\bf Dump}) schickt man in eine Datei. Man kann über Optionen einstellen, wie der Dump aussehen soll, ob nur bestimmte Tabellen ausgelesen werden sollen oder alle und vieles mehr. \par Wichtige Optionen sind: %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} {\bf --file}=datei, {\bf -f} datei \end{minipage} & \begin{minipage}{60mm} Ausgabe in Datei \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --inserts} \end{minipage} & \begin{minipage}{60mm} {\bf INSERT} im Dump verwenden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --attribute-inserts} \end{minipage} & \begin{minipage}{60mm} {\bf INSERT} mit Attributen verwenden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --host} servername \end{minipage} & \begin{minipage}{60mm} Zu diesem Server verbinden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --quotes} \end{minipage} & \begin{minipage}{60mm} Viele Bezeichner quotieren \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --schema-only} \end{minipage} & \begin{minipage}{60mm} Nur die Struktur, nicht die Daten \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --table} tabelle \end{minipage} & \begin{minipage}{60mm} Nur diese Tabelle tabelle \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf --no-acl} \end{minipage} & \begin{minipage}{60mm} Berechtigungen auslassen \end{minipage} \\ \hline \end{tabular} \par Möchte man die Datenbank wwwdb sichern, so schreibt man: \begin{tt} \begin{scriptsize} root@linux / \# pg\_dump wwwdb -f backup.sql \end{scriptsize} \end{tt} \linebreak \par Leider werden so keine {\bf large objects} (große Objecte, ein Datentyp) gesichert. Hier eröffnet ein Blick in die PostgreSQL Dokumentation mehrere Lösungsmöglichkeiten, die den Rahmen an dieser Stelle sprengen. \subsubsection{pg\_dumpall} \label{d71e2390} \par Dieses Programm ruft {\bf pg\_dump} für alle Datenbanken auf, und wird daher meistens für Backups verwendet. Es ist die empfohlene Art. Ein Backup kann man beispielsweise mit folgendem Kommando durchführen: \begin{tt} \begin{scriptsize} root@linux / \# pg\_dumpall -f backup.sql \end{scriptsize} \end{tt} \linebreak \par Erfreulicherweise ist auf Grund des später erklärten {\bf MVCC} die Datenbank während des Backups vollständig verwendbar (möglicherweise gibt es einige spezielle Einschränkungen, ein {\bf DROP DATABASE} zum Löschen einer Datenbank wird wohl nicht funktionieren). \subsubsection{Automatisiert} \label{d71e2419} \par Den Aufruf von {\bf pg\_dumpall} zu automatisieren, fällt nicht schwer. Wenn man noch den Wochentag in den Dateinamen aufnimmt, wird das Backup nur wöchentlich überschrieben. Falls man sich mal vertippt hat, kann es sehr hilfreich sein, etwas ältere Backups zu haben. Ein ganz einfaches Skript, dass man täglich über {\bf cron} als Unix-Benutzer postgres starten kann: \begin{tabular}{|l|} \hline \begin{tt} backup.sh\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} #!/usr/bin/bash #muss als postgres gestartet werden #Sicherheitshalber standard locale export LC_ALL=C #Wohin mit den Backups cd /home/postgres/db_backups/ #Ergibt "Sun", "Mon" usw. DAY=`date +%a` #Alternativ: JJJJ-MM-TT #DAY=`+%Y-%m-%d` #Man kann alte Backups automatisch löschen, um Platz zu sparen, # beispielsweise alles löschen, was älter als 14 Tag ist: #find /home/postgres/db_backups/ \ # -iname 'dump_all-*.sql' -mtime +14 \ # | xargs --no-run-if-empty rm -f #Die Pfade müssen natürlich angepasst werden. /usr/bin/pg_dumpall > dump_all-$DAY.sql #Damit man nicht noch einen extra cron job für Vacuum machen muss: /usr/bin/vacuumdb --all --analyze \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsubsection{Wiederherstellung} \label{d71e2449} \par Eine Datenbank aus einem mit {\bf pg\_dumpall} erstellten File wiederherzustellen, ist sehr einfach. Man muss dafür sorgen, dass die Datenbank {\bf template1} vorhanden ist und das DBMS läuft. Dann übergibt man die Backupdatei einfach dem {\bf psql} Interpreter als SQL-Programm: \begin{tt} \begin{scriptsize} root@linux / \# psql -d template1 -f backup.sql \end{scriptsize} \end{tt} \linebreak \par In seltenen Fällen kann man die Backupdatei auch mit einem Editor öffnen, und nur Teile daraus in {\bf psql} eingeben (um Teile wiederherzustellen, beispielsweise). Auch kann man sich so Skripte erstellen, die beispielsweise neue Datenbanken anlegen (wenn man die Testdatenbank für ein Frontend erzeugt hat). \par Es gibt noch ein weiteres Programm, {\bf pg\_restore}, welche speziell für diesen Zweck entwickelt wurde. Dieses verfügt über einige Zusatzfunktionen, beispielsweise können so nur Teile wiederhergestellt werden. Man kann so einzelne Tabellen oder Funktionen wiederherstellen lassen. Ein Beispielaufruf: \begin{tt} \begin{scriptsize} root@linux / \# \$ pg\_restore -d template1 backup.sql \end{scriptsize} \end{tt} \linebreak \subsubsection{Dateisystem} \label{d71e2493} \par Natürlich kann man auch das Verzeichnis sichern, in dem PostgreSQL seine Daten aufbewahrt. Dazu muss die Datenbank unbedingt sauber heruntergefahren werden. Dann kann man das Verzeichnis einfach mit tar oder ähnlichem sichern. Verwendet man LVM, kann man die Datenbank stoppen, einen Snapshoot ziehen und die Datenbank wieder starten. Man sichert dann den Snapshoot, und die Datenbank muss nur kurz heruntergefahren werden. \par Dies hat den großen Nachteil, dass man unbedingt absolut genau die gleiche Version benötigt, um mit dem Backup etwas anfangen zu können. Diese ist insbesondere bei alten Bändern schwierig (welche Version hatte man damals eigentlich?). Ein weiterer Nachteil ist, dass man nicht nur einzelne Tabellen bzw. Datenbanken rücksichern kann (es geht wirklich nicht, da die commit logs auch benötigt werden!) oder anderes. \par Vor dem Wiederherstellen muss die Datenbank natürlich ebenfalls heruntergefahren werden. \par Ein Backup über pg\_dumpall ist in den meisten Fällen günstiger und sollte vorgezogen werden. Selbst wenn man über das Dateisystem sichert, sollte hin- und wieder ein {\bf Dump} gezogen werden. \subsubsection{Grenzen} \label{d71e2517} \par Leider ist das Backup mit {\bf pg\_dump} nicht perfekt. {\bf pg\_dump} wertet nicht aus, ob Tabellen Funktionen benutzen. Es geht davon aus, das Funktionen Tabellen verwenden (man beachte die Reihenfolge!). \par Daher kommt es zu Problemen, wenn man Funktionen als Voreinstellung von Tabellenspalten verwendet. In solchen Fällen kann man die Funktion einfach per Hand anlegen (die Backupdatei mit einem Editor öffnen, Funktion übertragen, oder mit {\bf pg\_restore} diese Funktion zuerst wiederherstellen) und dann das Backup einspielen. Das gibt zwar eine Warnung, da die Funktion schon existiert, funktioniert aber. \par Hat man zirkuläre Abhängigkeiten, so wird es etwas komplizierter, hier hilft nur Handarbeit. Solche Situationen sind meistens jedoch Fehler und unerwünscht. \par {\bf pg\_dump} sichert auch keine {\bf large objects} (große Objekte, ein Datentyp), wenn keine besonderen Optionen verwendet werden. Hier eröffnet ein Blick in die PostgreSQL Dokumentation mehrere Lösungsmöglichkeiten, die den Rahmen an dieser Stelle sprengen. \subsubsection{Update von älteren Versionen} \label{d71e2555} \par Vor einem Update sollte mit pg\_dumpall ein Backup erstellt werden. Diese kann man dann in die neue Version wiederherstellen. Eine Konvertierung der Daten-Dateien ist leider nicht vorgesehen. \par Man kann auch die alte und neue Datenbankversion parallel laufen lassen, und dann die Daten einfach über das Netzwerk kopieren. Die neue Datenbank muss dazu natürlich ein eigenes Datenverzeichnis verwenden. \par Angenommen, man startet die neue Datenbank auf Port 5433. Dann kann man mit folgender Kette den gesamten Datenbestand kopieren: \begin{tt} \begin{scriptsize} root@linux / \# pg\_dumpall -p 5432 | psql -p 5433 \end{scriptsize} \end{tt} \linebreak \subsection{Transaktionsprotokolle} \label{d71e2579} \par Ein Transaktionsprotokoll darf keinesfalls mit Protokolldateien mit Textmeldungen verwechselt werden. In einem Transaktionsprotokoll stehen Änderungen von Daten. Wird eine Transaktion {\bf committed}, also erfolgreich beendet, so werden diese in ein Protokoll eingetragen und erst bei Gelegenheit in die {\bf normalen} Dateien gespeichert. Das ist ein performantes Vorgehen, was auch bei Abstürzen funktioniert: in solchen Fällen wird das Log durchgearbeitet, und die noch nicht überspielten Änderungen werden durchgeführt (siehe auch Abschnitt Datenbankreparatur). \linebreak {\bf Write ahead logging} (WAL) ist ein - wenn nicht das - Standardverfahren für Transaktionsprotokolle und wird von PostgreSQL verwendet. \subsection{Datenbankreparatur} \label{d71e2607} \par Stellt PostgreSQL (genauer gesagt, das {\bf postmaster} Programm) beim Start fest, dass die Datenbank nicht sauber heruntergefahren wurde, wird automatisch eine Reparatur begonnen. Hier wird im Wesentlichen das WAL (write ahead log) durchgearbeitet. Dieses Verhalten ist ähnlich dem Journal, über das moderne Filesysteme wird ext3 und Reiser-FS verfügen (diese Technik kommt aus dem Datenbankbereich, aber durch Diskussionen ist die Funktion bei Dateisystemen inzwischen scheinbar fast bekannter). PostgreSQL hat also kein separates Standard-Reparatur-Programm, sondern erledigt diese Aufgaben automatisch beim Start. \par Unter ganz seltenen Umständen kann es jedoch sein, dass dieser Mechanismus nicht funktioniert. Diese können entstehen, wenn Arbeitsspeicher defekt ist (und einzelne Bits {\bf umkippen}), ein Sicherungsband geknittert wurde, und so kleine Teile fehlen oder fehlerhaft sind und möglicherweise auch durch ganz ungünstige Stromausfälle. Dann kann es vorkommen, dass die automatische Reparatur abbricht, und die Datenbank gar nicht startet. \par Selbst in solchen Fällen kann man oft noch viel retten, jedoch muss man dazu unangenehme Sachen machen, beispielsweise das WAL zurücksetzen. Hat man ein solches Problem, sucht man am besten in Mailinglisten Hilfe, denn hier muss man sehr vorsichtig sein, um nicht noch mehr zu zerstören. \subsection{Migration} \label{d71e2636} \par Migriert man von anderen DBMS, so erstellt man sich in der Regel eine SQL Kommandodatei mit einem Backup, und bearbeitet diese per Hand oder mit Skripten so, dass sie von den anderen DBMS gelesen werden kann. \par Portiert man ein System von anderen Datenbanken auf PostgreSQL, so ist je nach Art und Komplexität des Systems etliches an Arbeit zu erwarten. \par Grundsätzlich kann man davon ausgehen, Daten relativ unproblematisch übernehmen zu können. Tabellen sind oft auch gut handhabbar. Dann wird es aber leider schnell schwierig. Stored Procedures beziehungsweise Datenbankfunktionen müssen in der Regel neu geschrieben werden. Erschwerend kommt hinzu, dass PostgreSQL keine Stored Procedures, sondern nur Funktionen kennt, die jedoch die Flexiblität von ersteren haben. Zwar ist {\bf CREATE FUNCTION} Teil von SQL99, allerdings sind die Sprachen, in denen die Funktionen geschrieben sind, nicht standardisiert. \par Systeme, die viel in der Datenbank machen, sind natürlich aufwendiger in der Portierung. Da man die Konsistenz grundsätzlich in der Datenbank regeln sollte, muss damit gerechnet werden, dass alle {\bf Trigger}, Regeln und Stored Procedures neu implementiert werden müssen. \par Der Aufwand für die Anwendungen selbst hängt maßgeblich davon ab, wie nahe diese dem Standard sind. Selbst wenn diese Anwendungen gut standardkonform sind, kann natürlich immer noch nicht mit {\bf Plug'n'Play} gerechnet werden. Bei Anwendungen, die von Fremdfirmen geschrieben wurden, sollte nach Möglichkeit unbedingt Unterstützung durch diese Firmen verfügbar sein. \par Das Migrations-Projektteam sollte über Testsysteme mit beiden Datenbanken verfügen und Spezialisten für alle beteiligten Systeme besitzen. \subsubsection{Umstieg von mySQL} \label{d71e2679} \par mySQL ist eine sehr verbreitete OpenSource Datenbank. Da zu vermuten ist, dass viele bereits mit mySQL Erfahrungen haben, ist diesen Umsteigern hier ein eigenes Kapitel gewidmet. \par In den sogenannten {\bf Tech Docs} von PostgreSQL finden sich Informationen, wie man von mySQL zu PostgreSQL migriert. Es gibt Skripte, die mySQL SQL-Kommandodateien zu weiten Teilen automatisch so umwandeln, dass sie von {\bf psql} gelesen werden können. \par In der Praxis sind allerdings einige Änderungen zu erwarten. So kann es beispielsweise sein, das man Probleme mit der Quotierung bekommt (mySQL verwendet beispielsweise {\bf backticks}, um Systembezeichner zu quoten, was bei anderen Datenbanken zu Syntaxfehlern führt). Des weiteren ist PostgreSQL bei Zeichenketten {\bf case-sensitiv}, dass heißt, die Groß/Kleinschreibung wird grundsätzlich unterschieden. Bei Tabellen und Spaltennamen ist PostgreSQL nicht {\bf case-sensitiv}, es sei denn, man erzwingt dies durch die Verwendung von doppelten Anführungszeichen. Bei mySQL hängt dies von der verwendeten Plattform ab. Der Operator {\bf ||} wird in PostgreSQL so verwendet, wie in ANSI (mySQL kennt hier einen ANSI-Modus, der jedoch vermutlich selten verwendet wird). Man muss daher die {\bf ||} in {\bf OR} und die {\bf \&\&} in {\bf AND} ändern; ''{\bf ||}'' ist der Konkatenierungsoperator (wie in mySQL's ANSI-Modus). \par Der Umfang von SQL ist bei mySQL kleiner, dafür gibt es etliche, nicht standardkonforme Erweiterungen. mySQL verwendet {\bf \#} als Kommentarzeichen. ANSI schreibt {\bf --} vor. \par Steigt man auf PostgreSQL um, sollte man daran denken, die nun zur Verfügung stehenden Funktionen auch sinnvoll zu nutzen, beispielsweise {\bf Trigger} und {\bf Views}. Die Arbeit mit Transaktionen kann verbessert werden, da jetzt die ISO Transaktionslevel {\bf read commited} und {\bf serializable} zur Verfügung stehen. \par Ein entscheidendes Detail ist die Verwendung von Fremdschlüsseln. mySQL unterstützt diese zwar syntaktisch, jedoch ohne Funktion. Daher ist zu erwarten, dass Daten nicht einfach übernommen werden können, da vermutlich viele Fremdschlüsselintegritäten verletzt sind. \par Betrachtet man Vergleiche zwischen den beiden DBMS, so muss man diese sehr vorsichtig bewerten. So gibt es beispielsweise Seiten, die die nicht standardkonforme Verwendung des {\bf ||} Operators als Vorteil preisen, oder die Möglichkeit von stored procedures in mySQL nennen (die man dann in {\bf C} schreiben muss, und als {\bf root} zur Datenbank dazu linken muss). Eine andere Seite suggerierte es fast als Vorteil, keine Fremdschlüsselbedingungen zu prüfen. \subsubsection{Umstieg von anderen Systemen} \label{d71e2822} \par Je nach Standard-Konformität zu SQL ist es mehr oder weniger aufwendig, das DBMS zu wechseln. Natürlich spielt auch eine große Rolle, wie viele Spezialfunktionen man verwendet, und wie anspruchsvoll die Anwendungen sind. \par Es gibt in den {\bf Tech Docs} von PostgreSQL Informationen hierzu. Hier findet man Hilfen für die Migration von MS-SQL Server, Oracle und anderen zu PostgreSQL. \par Oracle-Erfahrene werden sich freuen, mit PL/pgSQL eine zu PL/SQL ähnliche Sprache zu finden. \subsection{Hardware} \label{d71e2858} \par Der Vollständigkeit halber ein paar Worte zur Hardware. PostgreSQL fühlt sich auf handelsüblichen PCs mit i386 Architektur wohl. Eine kleinere, gut geplante Datenbank mit weniger als einer Millionen Datensätzen läuft auf einem PC mit vielleicht 1Ghz, 256 MB RAM und normalen Platten wohl zügig genug. \par Je nach zu erwartender Last, Größe und Effizienz steigt der Hardwarebedarf schnell an. Abschätzungen lassen sich hier nur schwer treffen, zu groß ist beispielsweise der Unterschied, ob Indizes effizient arbeiten, oder nicht. Bei Datenbanken spielt oft die Geschwindigkeit der Festplatten eine große Rolle. SCSI Platten haben oft eine geringere Zugriffszeit und unterstützen Tagged Command Queuing - gerade Datenbanken profitieren von diesen Eigenschaften. \par Ist man der Meinung, die Festplatten sind zu langsam, so kann man den Einsatz von RAID, beispielsweise RAID0+1, erwägen. Je nach Konfiguration kann man gleichzeitig auch eine erhöhte Ausfallsicherheit erreichen. Deshalb ist RAID0+1 beliebt: Man stript über einen Mirror (das ist etwas ausfallsicherer, als über Stripes zu spiegeln, da in letzerem Fall der zweite Plattenausfall weniger wahrscheinlich tötlich ist. Aufmalen!). Ein RAID0+1 mit insgesamt vier Platten erreicht (in der Theorie) die doppelte Schreib- und sogar die dreifache (der Faktor drei ist hier ein Praxiswert) Lesegeschwindigkeit, bietet in jedem Fall Schutz vor einem Plattenausfall und ermöglicht es, 50% der Plattenkapazität zu nutzen - oft ein guter Kompromiss. In solchen Konfigurationen sind SCSI RAID Controller sinnvoll, jedoch stoßen die preiswerteren Controller schnell an Performanzgrenzen (dann bremst der Controller die Platten aus). Hier sollte man sich vor dem Kauf informieren. \par Je nach Art der Daten kann auch eine Verdopplung des Hauptspeichers viel Performanz bringen. Hier muss man die im Abschnitt Konfiguration beschriebenen Änderungen durchführen und etwas mit den Werten spielen, bis man günstige Kombinationen gefunden hat. Hat man viel Speicher, so kann es sogar Sinn machen, PostgreSQL mehr als 50% zu geben (auf dedizierten Systemen natürlich). \par Rechenleistung ist bei vielen Anwendungen weniger ein Thema. Das Unix-Programm {\bf top} hilft einem bei der Analyse. Sollte sich herausstellen, dass man eine sehr rechenintensive Datenbank hat, oder hat man einfach genügend Hauptspeicher, um die Plattenaktivität in den Griff zu bekommen, hilft vielleicht eine weitere CPU. \section{Benutzung} \label{d71e2894} \par Dieser Abschnitt ersetzt keine SQL Referenz und kein PostgreSQL Handbuch. Es wird nur exemplarisch auf einige Details eingegangen. Dabei stehen PostgreSQL-spezifische Eigenschaften im Vordergrund. \subsection{psql} \label{d71e2910} \par Der bereits kurz erwähnt interaktive Kommandointerpreter ist sicherlich das wichtigste Programm. \subsubsection{Kommandozeilenoptionen} \label{d71e2920} \par psql versteht etliche Optionen: %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} {\bf -d} Datenbank \end{minipage} & \begin{minipage}{60mm} Zu dieser Datenbank verbinden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -h} Servername \end{minipage} & \begin{minipage}{60mm} Über TCP/IP zu diesem Server verbinden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -p} Port \end{minipage} & \begin{minipage}{60mm} Diesen Port verwenden (Voreinstellung 5432) \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -U} Benutzer \end{minipage} & \begin{minipage}{60mm} Als Benutzer anmelden \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -c} Kommando \end{minipage} & \begin{minipage}{60mm} Dieses Kommando ausführen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -f} Datei \end{minipage} & \begin{minipage}{60mm} Diese SQL Datei ausführen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -o} Datei \end{minipage} & \begin{minipage}{60mm} Ausgaben die Datei schreiben \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -s} \end{minipage} & \begin{minipage}{60mm} Einzelschrittmodus: jedes SQL Kommando bestätigen \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf -E} \end{minipage} & \begin{minipage}{60mm} zeigt das ausgeführte SQL-Kommando bei internen Befehlen (z.B. $\backslash$d) an. \end{minipage} \\ \hline \end{tabular} \par Nach den Optionen gibt man eine Datenbank an, sofern man nicht {\bf -d} verwendet. Dahinter kann man noch einen Benutzernamen schreiben, sofern man nicht {\bf -U} verwendet. \par Um als Superuser postgres zur Datenbank test zu verbinden, schreibt man also beispielsweise: \begin{tt} \begin{scriptsize} root@linux / \# psql -U postgres -d test \end{scriptsize} \end{tt} \linebreak \par Je nach Einstellung der Authentifizierung wird nun nach einen Passwort gefragt. Es erscheint das Datenbankprompt. \par Hat man PostgreSQL mit der readline-Unterstützung übersetzt, kann man ebenso wie in der Bash die Tabulator-Taste drücken, um Befehle und Objekte zu erweitern \subsubsection{Interaktion} \label{d71e3062} \par Am Prompt kann man SQL Befehle eingeben: \begin{tt} \begin{scriptsize} test=\# CREATE TEMPORARY TABLE temp \linebreak test-\# ( feld1 int UNIQUE NOT NULL, \linebreak test(\# feld2 varchar(100000) DEFAULT NULL ); \linebreak NOTICE:\verb+ +CREATE TABLE / UNIQUE will create implicit index \linebreak 'temp\_feld1\_key' for table 'temp' \linebreak CREATE\end{scriptsize} \end{tt} \linebreak \par Man sieht, das SQL Kommandos mit Semikolon abgeschlossen werden und dann automatisch ausgeführt werden. Das Prompt zeigt an, ob man in einer Klammer ist, eine kleine Hilfe. Das Beispielkommando hat nun eine einfach Testtabelle erzeugt. Diese kann man nun mit Daten füllen: \begin{tt} \begin{scriptsize} test=\# INSERT INTO TEMP (feld1, feld2) VALUES (1234, 'hallo'); \linebreak INSERT 1532564 1\end{scriptsize} \end{tt} \linebreak \par Die Ausgabe enthält eine merkwürdige Nummer. Das ist der {\bf OID}, der object identifier. Diese sollte man nicht weiter beachten (es handelt sich um eine Art automatisches Indexfeld, ist aber höchst unportabel, und wird nur intern benötigt). \par Über {\bf psql} kann man auch in Transaktionen arbeiten: \par Die Tabelle enthält einen Datensatz: \begin{tt} \begin{scriptsize} test=\# SELECT count(*) FROM temp; \linebreak count \linebreak ------- \linebreak \verb+ +\verb+ + 1 \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Transkation beginnen: \begin{tt} \begin{scriptsize} test=\# BEGIN; \linebreak BEGIN\end{scriptsize} \end{tt} \linebreak \par Tabelle temp leermachen (alles löschen): \begin{tt} \begin{scriptsize} test=\# DELETE FROM temp; \linebreak DELETE 1\end{scriptsize} \end{tt} \linebreak \par Die Tabelle ist jetzt auch Sicht der Transaktion leer: \begin{tt} \begin{scriptsize} test=\# SELECT count(*) FROM temp; \linebreak count \linebreak ------- \linebreak \verb+ +\verb+ + 0 \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Transaktion abbrechen: \begin{tt} \begin{scriptsize} test=\# ROLLBACK; \linebreak ROLLBACK\end{scriptsize} \end{tt} \linebreak \par Es ist nichts geändert worden: \begin{tt} \begin{scriptsize} test=\# SELECT * FROM temp; \linebreak feld1 | feld2 \linebreak ------+------- \linebreak 1234\verb+ +| hallo \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Die Temporäre Tabelle verfällt automatisch, wenn man die Verbindung schließt. \subsubsection{Interne Kommandos} \label{d71e3157} \par {\bf psql} verfügt über eine Reihe sogenannter interner Kommandos. Diese beginnen mit einem {\bf $\backslash$} (Backslash). Einige der wichtigesten internen Kommandos sind: %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} {\bf $\backslash$?} \end{minipage} & \begin{minipage}{60mm} kurze Hilfe zu allen Backslash Kommandos \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$d} Objekt \end{minipage} & \begin{minipage}{60mm} Objekt beschreiben. Ist Objekt beispielsweise eine Tabelle, so werden die Spalten und Typen angezeigt. Auch definierte Indizes werden aufgelistet. Wird Objekt nicht angegeben, werden alle Tabellen aufgelistet, die existieren (außer natürlich temporäre Tabellen). \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$d}Kürzel \end{minipage} & \begin{minipage}{60mm} Liste die zu Kürzel passenden Objekte: Tabellen ({\bf t}), Indizes ({\bf i}), Sequenzen ({\bf s}), Views ({\bf v}), Privilegien ({\bf p}), Systemtabellen ({\bf S}), große Objekte ({\bf l}), Aggregatfunktionen ({\bf a}), Kommentare ({\bf d}; Objektname muss folgen), Funktionen ({\bf f}), Operatoren ({\bf o}) und Datentypen ({\bf T}). \linebreak Durch ein Leerzeichen kann man noch ein Objekt angeben. {\bf $\backslash$dp temp} zeigt beispielsweise die Privilegien für die Tabelle temp an (was nur funktioniert, wenn es keine temporäre Tabelle ist). \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$e} Datei \end{minipage} & \begin{minipage}{60mm} Öffnet das letzte Kommando oder Datei im Editor. Hilfreich, um lange Kommandos wie {\bf CREATE TABLE} zu bearbeiten und zu speichern. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$l} \end{minipage} & \begin{minipage}{60mm} Listet alle Datenbanken auf. \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$q} \end{minipage} & \begin{minipage}{60mm} Beendet {\bf psql} \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$x} \end{minipage} & \begin{minipage}{60mm} Erweiterte Ausgabe \end{minipage} \\ \hline \begin{minipage}{60mm} {\bf $\backslash$H} \end{minipage} & \begin{minipage}{60mm} HTML Ausgabe \end{minipage} \\ \hline \begin{minipage}{60mm} $\backslash$c Datenbank \linebreak $\backslash$c - Benutzer \end{minipage} & \begin{minipage}{60mm} Verbindet zu einer neuen Datenbank oder zur aktuellen mit einem neuen Benutzer. Dies ist in etwa mit dem {\bf USE} vergleichbar, das andere DBMS verwenden. \end{minipage} \\ \hline \end{tabular} \par Es folgt ein Beispiel für das Ausgabeformat. Zunächst soll die Ausgabe der oben erwähnten Testtabelle nicht feld1 und feld2 beinhalten, sondern Nummer und Textfeld. Wenn man diese Bezeichner {\bf case-sensitiv} haben möchte (Tabellen- und Feldnamen sind sonst case-insensitiv, das heißt, Groß-/Kleinschreibung wird nicht beachtet), muss man diese quoten: \begin{tt} \begin{scriptsize} test=\# SELECT feld1 AS ''Nummer'', feld2 AS ''Textfeld'' FROM temp; \linebreak Nummer | Textfeld \linebreak --------+---------- \linebreak \verb+ + 1234 | hallo \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Nach {\bf $\backslash$x} sieht die Ausgabe so aus: \begin{tt} \begin{scriptsize} test=\# SELECT feld1 AS ''Nummer'', feld2 AS ''Textfeld'' FROM temp; \linebreak -[ RECORD 1 ]--- \linebreak Nummer\verb+ + | 1234 \linebreak Textfeld | hallo\end{scriptsize} \end{tt} \linebreak \par Dies macht bei großen Tabellen Sinn, wenn nicht mehr alle Spalten nebeneinander auf den Bildschirm passen. \subsubsection{Verwendung} \label{d71e3347} \par Neben der interaktiven Verwendung kann man {\bf psql} dazu verwenden, SQL Skripte auszuführen, beispielsweise Skripte, die Datenbankschemata erzeugen. Man kann {\bf psql} sogar dazu verwenden, Shell-Skripte mit rudimentärer Datenbankfunktionalität zu versehen; hier ist die Verwendung von {\bf Perl::DBI} oder anderen Methoden jedoch oft einfacher und sauberer. \subsection{pgaccess} \label{d71e3368} \par {\bf pgaccess} ist eine graphisches Frontend, mit dem etliche Standardaufgaben erledigt werden können. Das Anlegen von Tabellen beispielsweise macht sich mit diesem Frontend wesentlich besser, also mit {\bf psql} . \par Über das Menü kann man zu einer Datenbank verbinden. Im folgenden Dialog können Server- und Datenbankname sowie ein Benutzerkonto angegeben werden. \par Im Hauptfenster kann man rechts die anzuzeigende Objekte wählen. Hier kann man beispielsweise zwischen Tabellen, Views und Sequenzen auswählen. Im linken Teil werden dann die entsprechenden Objekte aufgelistet und können ausgewählt werden. \par Nach einem Doppelklick auf eine Tabelle bekommt man ein Fenster, in dem der Inhalt dargestellt wird und geändert werden kann. Klickt man eine Tabelle nur einmal an, so kann man weitere Funktionen anwenden, beispielsweise {\bf Design}. Hier öffnet sich ein Fenster, in dem man komfortabel Indizes hinzufügen kann oder neue Felder anhängen kann. \par Seit Version 7.3 ist pgaccess nicht mehr Bestandteil der PostgreSQL- Distribution und muss separat von http://www.pgaccess.org besorgt werden. \subsection{RedHat Database Admin} \label{d71e3406} \par RedHat vertreibt eine eigene Version von PostgreSQL. Diese entspricht ungefähr der Version 7.2.3 und ist unter http://www.redhat.com/software/database/ erhältlich. RedHat stellt alle Änderungen am DBMS und auch sein graphisches Administrationsfrontend unter die GPL. Dieses läuft auch mit einer {\bf konventionellen} PostgreSQL Installation und ist unter http://sources.redhat.com/rhdb/ zu finden. Es ist {\bf hübscher} als {\bf pgaccess} und bietet im Bereich der Verwaltung mehr Optionen als dieses, kann dafür aber nicht zur Definition von TCL-Formularen herangezogen werden. \par Die neuen Funktionen der Version 7.3 (Schemata) werden allerdings noch nicht unterstützt. \subsection{phpPgAdmin} \label{d71e3447} \par Dies ist ein Webfrontend und setzt einen Webbrowser voraus. Dieses Frontend verfügt über sehr viele nützliche Funktionen. Tabellendaten können als HTML Tabelle betrachtet und editiert werden, beliebige Abfragen können erstellt und ausgeführt werden. \par Tabellen selbst können einfach und komfortabel bearbeitet werden, so können neue Felder hinzugefügt oder gelöscht werden. Weiterhin stehen Kopier- und Dumpfunktionen bereit. Auch Berechtigungen können komfortabel verwaltet werden. Die zur Verfügung stehenden Optionen sind sinnvoll in Auswahlfeldern aufgelistet. Bei Bedarf ist es auch möglich, eigene SQL Kommandos einzugeben und ausführen zu lassen. \par Eine weitere schöne Funktion ist die Verlinkung zu jeweils passenden Seiten der PostgreSQL Dokumentation. \par Wer Webfrontends mag, wird dieses Frontend wohl lieben. Es lohnt sich allemal, sich dieses zu installieren. Natürlich muss unbedingt darauf geachtet werden, den Zugang zu diesem Frontend zu schützen, da der Zugriff auf das Frontend Zugriff auf die Datenbank gestattet - und zwar als Superuser! \subsection{Transaktionen} \label{d71e3470} \par Dieser Abschnitt geht kurz auf Transaktionen ein. Transaktionen sind notwendig, um Änderungen atomar, dass heißt, ganz oder gar nicht, durchführen zu können. \par Im Folgenden wird oft der englische Ausdruck lock verwendet. Wörtlich übersetzt bedeutet er in etwa {\bf sperren}. Hier ist gemeint, ein Objekt so zu benutzen, dass es niemand anders gleichzeitig benutzen kann. Lock wird später noch genauer erklärt. \subsubsection{Einführung} \label{d71e3486} \par Das klassische Beispiel für den Bedarf ist das Buchungsbeispiel. Angenommen, es existieren zwei Kontotabellen. Möchte man nun eine Buchung gegen diese beiden Tabellen machen, muss in jede Tabelle ein neuer Datensatz angelegt werden. Dazu muss man zwei {\bf INSERT INTO SQL} Kommandos ausführen lassen. \par Nun könnte es ja passieren, dass eines der beiden Kommandos klappt, das andere jedoch nicht. In diesem Fall würden die Konten nicht mehr stimmen, da die Summen nicht mehr passen. Man hätte inkonsistente Daten und ein Problem. \par Daher fasst man beide Kommandos zu einer Transaktion zusammen. Eine Transaktion klappt entweder ganz, oder gar nicht. Geht also eines der SQL Kommandos schief, so hat auch das andere automatisch keinen Effekt (es wird gegebenenfalls {\bf rückgängig gemacht}). \par Transaktionen sind für andere erst sichtbar, wenn sie abgeschlossen wurden. Das bedeutet im Beispiel, dass nach dem Ausführen der ersten Kommandos ein anderer Client diese Änderung überhaupt nicht sieht. Erst wenn das andere Kommando erfolgreich war und die Transaktion beendet wurde, werden die Änderungen sichtbar. Somit stimmen die Summen zu jedem Zeitpunkt. \par Wenn innerhalb einer Transaktion Daten gelesen werden, und von einer anderen Transaktion in dieser Zeit geändert werden, so wird die Transaktion automatisch abgebrochen. Auch hier kann es nicht passieren, dass Daten versehentlich zurückgeschrieben werden, die inzwischen an anderer Stelle geändert wurden. \subsubsection{Multiversion Concurrency Control} \label{d71e3515} \par Implementiert wird ein sogenanntes {\bf Multiversion Concurrency Control} (MVCC). Das bedeutet, das Abfragen einer Transaktion die Daten so sehen, wie sie zu einem bestimmten Zeitpunkt waren, unabhängig davon, ob sie inzwischen von einer anderen Transaktion geändert wurden. Dies verhindert, dass eine Transaktion einen Teil Daten vor und einen anderen nach einer nebenläufig abgeschlossenen Transaktion lesen kann und verhindert so inkonsistentes Lesen: die Transaktionen werden von einander isoliert. Der Hauptunterschied zu {\bf Lock} Verfahren ist, dass MVCC Locks für das Lesen nicht mit Locks für das Schreiben in Konflikt stehen. Somit blockiert das Schreiben nie das Lesen und das Lesen nie das Schreiben. \par Eine wichtige Einschränkung gibt es: Transaktionen können in PostgreSQL nicht geschachtelt werden (es gibt also keine {\bf Untertransaktionen}). \subsubsection{Transaktionslevel} \label{d71e3541} \par PostgreSQL unterstützt zwei Transaktionslevel: {\bf read committed} und {\bf serializable}. Verwendet eine Transaktion {\bf read committed}, so kann es vorkommen, dass sie Daten erneut liest, aber andere Daten erhält als beim ersten Lesen (nicht-wiederholbares Lesen, non-repeatble reads). Auch sogenanntes Phantom-Lesen (phantom reads) kann vorkommen. Vom Phantom-Lesen spricht man, wenn sich in einer Transaktion die Ergebnissätze von Suchbedingungen ändern können. Sogenanntes {\bf schmutziges Lesen} (dirty reads), also das Lesen von Änderungen aus anderen, nicht abgeschlossenen Transaktionen kann jedoch nicht auftreten. Dieser Transaktionslevel ist die Voreinstellung. Er ist einfach anzuwenden, schnell und für die meisten Anwendungen ausreichend. \par Verwendet eine Transaktion {\bf serializable}, können diese beiden unerwünschten Effekte nicht auftreten. Man benutzt diesen Level für Anwendungen, die komplexe Abfragen und Änderungen durchführen. \subsubsection{Anwendung} \label{d71e3573} \par Transaktionen werden durch das SQL Kommando {\bf BEGIN} eingeleitet. Dies ist nicht standardkonform; ANSI fordert, das immer implizit eine Transaktion begonnen wird. PostgreSQL bietet jedoch wie viele andere DBMS auch eine sogenanntes {\bf auto commit} Funktion an, dies ist auch das Standardverhalten. Jedes SQL Kommando wird dann so aufgefasst, als wäre es eine einzelne Transaktion (es wird also sozusagen ein implizites {\bf COMMIT} nach jedem SQL Kommando ausgeführt). Möchte man nun eine aus mehreren Anweisungen bestehende Transaktion beginnen, schreibt man einfach {\bf BEGIN} als erstes Kommando. Dies passt auch gut zum eingebettetem SQL, da die SQL Kommandos dadurch in einen schicken {\bf BEGIN} - {\bf END} Block eingeschlossen sind. \par Grundsätzlich gibt es zwei Möglichkeiten, eine Transaktion zu beenden. Eine Anwendung kann eine Transaktion selbst abbrechen. Hierzu dient das Kommando {\bf ROLLBACK}. Keine der Änderungen der Transaktion wird ausgeführt. Eine Anwendung kann die Transaktion auch positiv beenden. Dazu wird {\bf END} oder {\bf COMMIT} verwendet. Die Transaktion wird genau dann durchgeführt, wenn sie fehlerfrei war. In diesem Fall werden alle Änderungen (oder die eine komplexe Transaktionsänderung) übernommen (sichtbar). Trat in der Transaktion ein Fehler auf, so gibt es natürlich keine Möglichkeit, sie doch noch positiv zu beenden, da dies zu Inkonsistenzen führen würde. In solchen Fällen kann die Anwendung (je nach Art des Fehlers) die Transaktion wiederholen. Dies ist natürlich nicht sinnvoll, wenn beispielsweise ein Tabelle fehlt. Dann wird auch die Wiederholung fehlschlagen. \par So ist also sichergestellt, dass Transaktionen nur vollständig (und vollständig erfolgreich), oder überhaupt nicht durchgeführt werden. \par Hat man mit {\bf BEGIN} eine Transaktion begonnen, so ist zunächst die Datenbankvoreinstellung des Transaktionslevel ({\bf read committed}) aktiv. Solange die Transaktion noch nicht begonnen wurde, kann der Transaktionslevel noch geändert werden. Dazu wird das SQL Kommando {\bf SET TRANSACTION ISOLATION LEVEL} verwendet. Als Parameter wird {\bf READ COMMITTED} oder {\bf SERIALIZABLE} angegeben. Damit ist der Transaktionslevel eingestellt. Ein Client kann auch einen eigene Voreinstellung setzen, wenn beispielsweise Transaktionen grundsätzlich {\bf serializable} sein sollen. Das SQL Kommando lautet {\bf SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL} und erwartet die gleichen Parameter wie das vorherige Kommando. \subsubsection{Mögliche Effekte} \label{d71e3644} \par Verwendet man Transaktionen, so kann es natürlich vorkommen, dass eine Transaktion vom DBMS beendet wird, weil eine andere Transaktion Daten geändert hat; insbesondere, wenn {\bf serializable} verwendet wird. In solchen Fällen wird die Transaktion in der Regel einfach von vorn beginnend vollständig wiederholt (die Anwendung führt diese also erneut aus). \par Derartige Effekte minimiert man oft, in dem man Datensätze, von denen man schon weiß, dass man sie ändern muss, schon mal für selbiges vormerkt. Dies geschieht mit dem SQL Kommando {\bf SELECT FOR UPDATE}. Nun weiß das DBMS, dass diese Datensätze {\bf der Transaktion gehören}. Möchte eine andere Transaktion hier auch Daten ändern, so wartet diese automatisch, bis die erste Transaktion beendet wurde (also bestätigt oder abgebrochen). Dann erst wird die Aktion ausgeführt. Mit dem SQL Kommando {\bf LOCK TABLE} können auch komplette Tabellen gesperrt werden. Verwendet man diese Mechanismen sorgfältig, vereinfacht sich die Handhabung; spätere Transaktionsabbrüche treten nicht auf, da die Daten ja bereits verwendet werden. \par Es kann passieren, dass sich Transaktionen gegenseitig ausschließen. Würde beispielsweise Transaktion A die Tabelle A sperren und Transaktion B Tabelle B und anschließend Tabelle A sperrt, kommt es zu einer solchen Situation, wenn Transaktion A auch versucht, Tabelle B zu sperren. Transaktion B kann ja Tabelle A nicht sperren, weil diese schon von Transaktion A bereits gesperrt ist und blockiert, bis Transaktion A beendet wurde. Transaktion A wiederum wartet auf Transaktion B, um Tabelle B sperren zu können. Man spricht von einem Deadlock - beide Transaktionen haben sich gegenseitig blockiert. \par PostgreSQL erkennt solche Fälle automatisch. Eine der beiden Transaktionen wird mit einem entsprechendem Deadlock-Fehler abgebrochen, woraufhin die andere durchgeführt werden kann. Auch hier wiederholt die Anwendung einfach die Transaktion. Da nun keine andere mehr läuft, wird es diesmal klappen. \par Bei der Arbeit mit komplexen Transaktionen muss man damit rechnen, dass eine Transaktion durch solche oder ähnliche Gründe abgebrochen wird. In der Software ist also vorzusehen, Transaktionen wiederholen zu können. Da im Falle eines Transaktionsabbruches ja überhaupt keine Daten geändert werden, geht das unproblematisch. Man beginnt einfach von vorn. \subsubsection{Sperren für Tabellen} \label{d71e3682} \par Sperre oder Lock bedeutet, dass der Inhaber oder Eigentümer dessen davor geschützt ist, dass jemand anders eine Sperre erzeugt, der dieser widerspricht. Es gibt verschiedene Arten von Sperren. Lese-Locks beispielsweise schließen sich nicht gegenseitig aus (es können ja problemlos mehrere Transaktionen die gleichen Daten lesen), jedoch schließt ein Lese-Lock einen Schreib-Lock aus. Schreib-Locks schließen sich und Lese-Locks aus. Letztere nennt man daher auch {\bf exklusiv}, keine anderere Sperre kann neben einem Schreib-Lock ausgeführt sein. \par Die folgende Aufstellung ist unvollständig. %table \begin{tabular}{|l|l|} \hline \begin{minipage}{60mm} AccessShareLock \end{minipage} & \begin{minipage}{60mm} (lesender Zugriff) Lese-Lock, der automatisch auf Tabellen gesetzt wird, aus denen gelesen wurden. Schließt AccessExclusiveLock aus. \end{minipage} \\ \hline \begin{minipage}{60mm} RowShareLock \end{minipage} & \begin{minipage}{60mm} (lesender Zugriff auf Zeilen) Wird durch {\bf SELECT FOR UPDATE} und {\bf LOCK TABLE IN ROW SHARE MODE} gesetzt. Schließt ExclusiveLock und AccessExclusiveLock Modi aus. \end{minipage} \\ \hline \begin{minipage}{60mm} RowExclusiveLock \end{minipage} & \begin{minipage}{60mm} (exklusiver Zugriff auf Zeilen) Wird durch {\bf UPDATE}, {\bf DELETE}, {\bf INSERT} und {\bf LOCK TABLE IN ROW EXCLUSIVE MODE} gesetzt. Schließt ShareLock, ShareRowExclusiveLock, ExclusiveLock und AccessExclusiveLock Modi aus. \end{minipage} \\ \hline \begin{minipage}{60mm} AccessExclusiveLock \end{minipage} & \begin{minipage}{60mm} (exklusiver Zugriff) Gesetzt durch {\bf ALTER TABLE}, {\bf DROP TABLE}, {\bf VACUUM FULL} und {\bf LOCK TABLE}. Schließt alle Modi aus. Selbst {\bf SELECT} in anderen Transaktionen blockiert in diesem Fall. \end{minipage} \\ \hline \end{tabular} \subsubsection{Sperren für Datensätze} \label{d71e3773} \par Datensätze werden mit {\bf SELECT FOR UPDATE} gesperrt. Dies schließt Änderungen an genau diesen Datensätzen aus. Wie bereits angedeutet, schließt dies kein Lesen aus (Schreiben blockiert kein Lesen). \subsubsection{Transaktionsbeispiel} \label{d71e3787} \par Wie bereits gesagt, werden Transaktionen bei Fehlern automatisch abgebrochen. Alle Kommandos werden ignoriert: \par Transaktion beginnen: \begin{tt} \begin{scriptsize} test=\# BEGIN; \linebreak BEGIN\end{scriptsize} \end{tt} \linebreak \par Es Kommando geht schief, zum Beispiel weil syntaktisch falsch: \begin{tt} \begin{scriptsize} test=\# SYNTAX ERROR; \linebreak ERROR:\verb+ +parser: parse error at or near ''SYNTAX''\end{scriptsize} \end{tt} \linebreak \par Die Transaktion ist abgebrochen worden. Alle Kommandos werden ab jetzt ignoriert: \begin{tt} \begin{scriptsize} test=\# DELETE FROM temp; \linebreak NOTICE:\verb+ +current transaction is aborted, queries ignored until \linebreak end of transaction block \linebreak *ABORT STATE*\end{scriptsize} \end{tt} \linebreak \par Selbst wenn man versucht, die Transaktion positiv zu beenden, wird nichts geändert (die Transaktion wird also trozdem abgebrochen): \begin{tt} \begin{scriptsize} test=\# COMMIT; \linebreak COMMIT\end{scriptsize} \end{tt} \linebreak \par Die Antwort {\bf COMMIT} heißt nicht, dass wirklich etwas committed wurde. Hier wurde ja ein {\bf Rollback} durchgeführt. Dieses Verhalten ist bei Skripts sehr nützlich. Die Kommandos schreibt man einfach ein einen {\bf BEGIN;} - {\bf END;} Block ({\bf End} ist das gleiche wie {\bf Commit}). Bei einem Fehler wird keine Änderung ausgeführt - die Datenbank sieht genauso aus, wie vorher. Man kann das Skript korrigieren und erneut ausführen. \par An dieser Stelle sei noch einmal daran erinnert, dass Strukturkommandos (wie {\bf CREATE} und {\bf DROP}) nicht den den Transaktionsregeln unterliegen. \subsubsection{Arbeiten mit Bedingungen} \label{d71e3864} \par Es ist möglich, Bedingungen ({\bf CONSTRAINTS}) an Tabellen zu definieren. Beispielsweise könnte man fordern, dass die Summe über alle Felder einer Tabelle null sein muss. Möchte man nun zu einem Datensatz drei addieren, muss man also von einem anderen drei abziehen. Doch kurz dazwischen ist die Bedingung ja verletzt, denn die Summe ist ja dann nicht mehr null, sondern drei! \par Bedingungen können daher in Transaktionen aufgeschoben werden ({\bf DEFERRED}). Das bedeutet, sie werden erst am Ende der Transaktion geprüft. Eine Bedingung kann dies aber auch verhindern. Bedingungen können so definiert werden, dass sie immer sofort geprüft werden. Bedingungen können aber auch so definiert werden, dass die Prüfung per Voreinstellung aufgeschoben wird, oder das die Bedingung explizit aufgeschoben werden kann. \par Um Bedingungen aufzuschieben, die sofort geprüft werden sollen, aber auch aufgeschoben werden dürfen, verwendet man das SQL Kommando {\bf SET CONSTRAINTS ALL DEFERRED}. Anstatt ALL kann man auch den Namen der Bedingung angeben (das wird auch oft gemacht). Anstatt {\bf DEFERRED} kann auch {\bf IMMEDIATE} eingestellt werden. Damit hat das den Gegenteiligen Effekt. Bedingungen, die per Voreinstellung aufgeschoben werden, werden dennoch sofort ausgeführt. \par Schiebt man also eine Prüfung auf, so wird diese am (bisher positiven) Ende der Transaktion durchgeführt. Stellt sich nun heraus, dass die Bedingung verletzt ist, wird die Transaktion abgebrochen (und die Bedingung bleibt dadurch erfüllt). \subsection{Variablen und Zeitzonen} \label{d71e3900} \par Es gibt einige Variablen, die das Verhalten des DBMS (für den entsprechenden Clienten) beeinflussen. Über Variablen wird beispielsweise gesteuert, wie Datumsangaben aussehen. Dies ist nicht standard konform (mit Ausnahme von {\bf TIME ZONE}, hier wurde der Standard erweitert). \par Variablen werden mit {\bf SET} gesetzt und mit {\bf SHOW} abgefragt. Mit {\bf SET} wird eine Variable auf einen Wert gesetzt. Zwischen der Variable und dem Wert steht {\bf TO} (oder ein Gleichheitszeichen). \par Hier werden nur zwei wichtige Variablen erwähnt. Die Variable {\bf DATESTYLE} setzt die Form der Datumsrepräsentation. Mögliche Werte sind German, ISO und andere. \par Auch die Zeitzone kann man setzen. Hier verwendet man {\bf SET TIME ZONE}. ANSI erlaubt als Parameter nur eine Zahl, beispielsweise {\bf SET TIME ZONE 2}. Dies ist natürlich ungünstig, da die Sommer- und Winterzeit Unterscheidung von der Anwendung getroffen werden muss (Ist Berlin nun gerade -1 oder -2? Das hängt vom Datum ab!). PostgreSQL erlaubt jedoch auch {\bf SET TIME ZONE 'Europe/Berlin'}. \par An einem Beispiel wird gezeigt, wie man die aktuelle Uhrzeit mit Datum in Californien (Zeitzone PST8PDT) im ISO Format (amerikanische Notation) und in Berlin (Zone CET, Central European Time, deutsche Notation) ausgeben lassen kann. \begin{tt} \begin{scriptsize} test=\# SET TIME ZONE 'PST8PDT'; SET DATESTYLE TO ISO; SELECT now(); \linebreak SET VARIABLE \linebreak SET VARIABLE \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +now \linebreak ------------------------------- \linebreak 2003-01-02 11:30:17.698728-08 \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \begin{tt} \begin{scriptsize} test=\# SET TIME ZONE 'CET'; SET DATESTYLE TO German; SELECT now(); \linebreak SET VARIABLE \linebreak SET VARIABLE \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +now \linebreak -------------------------------- \linebreak 02.01.2003 20:32:46.387261 CET \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \subsection{Datentypen} \label{d71e3969} \par PostgreSQL unterstützt unter anderem die SQL92 Datentypen. Ingesammt werden viele Typen unterstützt und eigene können definiert werden. Beispiele sind int (Ganzzahlen), double precision (8 Byte Fließkomma), serial (Autoinkrementeller int), varchar (variable lange Zeichenketten), bytea (Binäre Zeichenkette, wie ANSI BLOB), timestamp (Datum und Uhrzeit), boolean (Wahrheitswert) und viele andere. \par Typ-Umwandlungen werden durchgeführt, in dem man den Zieltyp durch zwei Doppelpunkte {\bf ::} getrennt an den Typ anfügt: {\bf '123'::int}. \linebreak Dies konvertiert die Zeichenkette 123 in einen Ganzzahltyp mit dem Wert einhundertdreiundzwanzig. \subsection{Operatoren} \label{d71e3997} \par Neben den normalen Operatoren ({\bf OR}, {\bf AND}, {\bf +}, {\bf -}, {\bf *}, {\bf ||} usw.) gibt viele weitere, beispielsweise Quadratwurzel {\bf (|/)}, {\bf LIKE} und {\bf ILIKE} (Patternmatching wie bei {\bf LIKE}, aber case-insensitiv) auch reguläres Patternmatching ({\bf \~{}}, {\bf \~{}*} und andere). Die Operatoren verhalten sich je nach Datentyp korrekt. Addiert man mit dem Operator {\bf +} beispielsweise ein timestamp und ein intervall (also {\bf now() + intervall '2 hours'}), kommt das erwartete Ergebnis heraus. \subsection{Vordefinierte FunktioneN} \label{d71e4050} \par PostgreSQL stellt viele Funktionen bereit. Viele mathematische Funktionen sind verfügbar ({\bf sin()}, {\bf cos()},{\bf abs()}, {\bf random()} usw). Daneben gibt es viele Zeichenkettenfunktionen ({\bf lower()}, {\bf substring()}, {\bf initcap()}, {\bf translate()}, {\bf encode()}, um nur einige zu nennen). Auch die Zeit- und Datumsfunktionen sind sehr interessant und leistungsfähig. Beispielsweise gibt es {\bf current\_timestamp} (oder auch kurz {\bf now}, eine klassische PostgreSQL-Erweiterung), extract (liefert Datumsteile, {\bf SELECT EXTRACT(MINUTE FROM TIMESTAMP '2001-02-16 20:38:40'}); führt also zu 38) und age (berechnet die Differenz zwischen zwei Zeitstempeln). \subsection{Datenbanken} \label{d71e4103} \par Das Erzeugen und Planen von Datenbanken findet sich im Abschnitt Administration. \subsection{Tabellen} \label{d71e4117} \par Wie in jedem anderen RDBMS werden natürlich auch Tabellen unterstützt. Diese werden mit {\bf CREATE TABLE} erzeugt. Dieses Kommando ist gut ANSI konform. Es gibt temporäre Tabellen, die automatisch gelöscht werden. Tabellen und Spalten können Bedingungen besitzen, das sind beispielsweise Funktionen, die es verhindern können, sinnlose Daten einzutragen (2 stellige Postleitzahlen beispielsweise). Wie bereits in Arbeiten mit Bedingungen genannt, können die Prüfungen gegebenenfalls auf das Transaktionsende verschoben werden. \par Fremdschlüssel sind Sonderformen von Bedingungen und werden auch unterstützt. Hiermit kann man gewährleisten, dass in eine Tabellenspalte nur solche Werte eingetragen werden können, die bereits in der Spalte einer anderen Tabelle definiert sind. Hat man z.B. eine Tabelle mit Herstellern und eine mit Teilen, in welcher der Hersteller vermerkt wird, kann sichergestellt werden, dass kein ungültiger Hersteller in letzterer eingetragen wird). Bei Fremdschlüsseln kann beispielsweise eine Aktion angegeben werden, die ausgeführt werden soll, falls der Fremdschlüssel gelöscht wird: {\bf NO ACTION, RESTRICT} (dann ist das ein Fehler), {\bf CASCADE} (die den Schlüssel referenzierenden Datensätze auch automatisch löschen, Vorsicht, dass können dann evtl. eine ganze Menge sein!), {\bf SET NULL} (Wert auf {\bf NULL} setzen), {\bf SET DEFAULT} (auf Voreinstellung setzen). \begin{tabular}{|l|} \hline \begin{tt} Beispiele ähnlich denen aus der PostgreSQL Dokumentation\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Eine Tabelle mit Primärschlüssel und einfachem Aufbau CREATE TABLE films ( code CHARACTER(5) CONSTRAINT films_pkey PRIMARY KEY, title CHARACTER VARYING(40) NOT NULL, distributors_id DECIMAL(3) NOT NULL, date_prod DATE, kind CHAR(10), len INTERVAL HOUR TO MINUTE ); -- Beispieldatensatz INSERT INTO films (code, title, distributors_id) VALUES ('FilmA', 'Der Film A', 123); -- Eine Tabelle mit einem Autoinkrement und einer einfachen Bedingung CREATE TABLE distributors ( id DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'), name VARCHAR(40) NOT NULL CHECK (name <> '') ); -- Ein Tabelle mit Bedingung (distributors_id muss größer als 100 sein, der Name -- darf nicht leer sein, sonst gibt es einen Fehler -- Das Feld modtime wird automatisch auf "jetzt" gesetzt, wenn ein -- Datensatz eingefügt wird. CREATE OR REPLACE TABLE distributors ( id DECIMAL(3) UNIQUE, name VARCHAR(40), modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP CONSTRAINT cst_valid_distributors_id CHECK (id > 100 AND name <> '') ); -- Datensatz einfügen: INSERT INTO distributors (id, name) VALUES (123, 'Name'); -- Nochmal geht schief, weil id eindeutig sein muss -- Das geht auch schief: -- INSERT INTO distributors (id, name) VALUES (001, 'Name'); -- denn: "ExecAppend: rejected due to CHECK constraint cst_valid_distributors_id" -- (muss ja > 100 sein) -- Eine Tabelle mit Fremdschlüssel und benannten Bedingungen. -- "varchar" heißt einfach: kann beliebig lang werden (also fast, -- bei ca 1000 MB ist Ende) CREATE TABLE lager ( id SERIAL PRIMARY KEY, films_code CHARACTER(5), distributors_id DECIMAL(3), info VARCHAR DEFAULT NULL, CONSTRAINT fk_lager_distributors_id FOREIGN KEY (distributors_id) REFERENCES distributors(id) ON DELETE RESTRICT, CONSTRAINT fk_lager_films_code FOREIGN KEY (films_code) REFERENCES films(code) ON DELETE RESTRICT DEFERRABLE ); -- Datensatz einfügen INSERT INTO lager (id, films_code, info) VALUES (123, 'FilmA', 'hallo'); -- Das geht schief: -- INSERT INTO lager (id, films_code, info) VALUES (124, 'FilmA', 'hallo'); -- denn: "fk_lager_distributors_id referential integrity violation -- - key referenced from lager not found in distributors" -- Das geht auch schief: -- DELETE FROM distributors; -- denn: "fk_lager_distributors_id referential integrity violation -- - key in distributors still referenced from lager"\end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Tabellen können mit dem Kommando {\bf ALTER TABLE} geändert werden. Diese Kommando hat viele Formen. \par Einige Beispiele: \begin{tabular}{|l|} \hline \begin{tt} Beispiele: ALTER TABLE\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Eine Spalte anfügen: ALTER TABLE lager ADD COLUMN plz VARCHAR(8); -- Eine Spalte ändern: ALTER TABLE lager ALTER COLUMN plz SET DEFAULT 'unsortiert'; -- Eine Spalte umbennen: ALTER TABLE lager RENAME COLUMN plz TO zipcode; -- Bedingung hinzufügen (PLZ muss fünfstellig sein) ALTER TABLE lager ADD CONSTRAINT cst_zipchk CHECK (char_length(zipcode) = 5); -- Bedingung entfernen ALTER TABLE lager DROP CONSTRAINT cst_zipchk RESTRICT; -- Tabelle umbennnen ALTER TABLE lager RENAME TO lagermitplz; ALTER TABLE lagermitplz RENAME TO lager; -- Eigentümer ändern ALTER TABLE lager OWNER TO steffen; \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Ab Version 7.3 wird endlich auch SQL 92 {\bf ALTER TABLE DROP COLUMN} unterstützt. Gibt es einen Index, eine Bedingung oder einen Fremdschlüssel der die zu löschende Spalte referenziert, muss die Option {\bf CASCADE} mit angegeben werden. \par Für ältere Versionen hat sich folgende Vorgehensweise bewährt: Man muss die Tabelle neu erzeugen. Diese Funktion wird übrigens von {\bf phpPgAdmin} unterstützt (das heißt, es gibt einen DROP Knopf, der im Prinzip das tut). Im Folgenden wird ein Workaround gezeigt. Es werden hier gleich noch ein paar weitere Kommandos demonstriert. \begin{tabular}{|l|} \hline \begin{tt} Beispiele\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Workaround für fehlendes: -- ALTER TABLE lager DROP COLUMN zipcode; -- Daten in Temp-Tabelle: BEGIN; -- Tabelle exklusiv schützen: LOCK TABLE lager IN ACCESS EXCLUSIVE MODE; -- LOCK TABLE lager; macht das gleiche (Voreinstellung ist ACCESS EXCLUSIVE) CREATE TEMPORARY TABLE temp AS SELECT id, films_code, distributors_id, info FROM lager; -- lager Tabelle neu erstellen DROP TABLE lager; CREATE TABLE lager ( id SERIAL PRIMARY KEY, films_code CHARACTER(5), distributors_id DECIMAL(3), info VARCHAR DEFAULT NULL, CONSTRAINT fk_lager_distributors_id FOREIGN KEY (distributors_id) REFERENCES distributors(id) ON DELETE RESTRICT, CONSTRAINT fk_lager_films_code FOREIGN KEY (films_code) REFERENCES films(code) ON DELETE RESTRICT DEFERRABLE ); -- Achtung, die Berechtigungen und Bedingungen der Tabelle müssen -- noch gesetzt werden! -- neue Tabelle füllen INSERT INTO lager SELECT * FROM temp; -- vielleicht noch prüfen -- SELECT * FROM lager LIMIT 100; DROP TABLE temp; -- nicht unbedingt notwendig, passiert sonst bei Ende der -- Sitzung automatisch -- Transaktion abschließen END; \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Füllt man (beispielsweise neue) Tabellen mit sehr vielen Daten, so ist {\bf INSERT} langsam. Die schnellste Möglichkeit ist das Füllen über {\bf COPY}. Bei sehr vielen Datensätzen spart es auch Zeit, die {\bf Indizes} zu löschen und anschließend neu zu erzeugen. Traut man den Daten, weil diese beispielsweise aus einem Backup kommen, so bringt es auch oft sehr viel Zeitersparnis, wenn man die {\bf Trigger} und Bedingungen löscht und nach dem Füllen wieder neu anlegt. \par Eine Erweiterung ist die Möglichkeit {\bf CREATE TABLE AS}, die eine Tabelle aus einer {\bf SELECT}-Abfrage erzeugt. Das ist äquivalent zu einer {\bf INSERT INTO} Erweiterung, mit der auch Tabellen erzeugt werden können (beides ist nicht Standard-SQL). Um standardkonform zu sein, muss man zunächst ein {\bf CREATE TABLE} machen und diese dann über {\bf INSERT ... SELECT} füllen. \subsection{Views} \label{d71e4250} \par Views sehen aus Sicht der Anwendung aus wie Tabellen. Manchmal werden sie sogar als {\bf virtuelle Tabellen} bezeichnet. Es sind Sichten auf Tabellen. Eine View stellt eine Abfrage (ein {\bf SELECT} Kommando) dar. Diese Abfrage kann beispielsweise nur einige der Spalten einer Tabelle enthalten. Die Abfrage kann auch über einen {\bf join} mehrere Tabellen verbinden und so Werte aus verschiedenen Tabellen anzeigen. \par Ein großer Vorteil von Views ergibt sich, wenn man sich an die Privilegien erinnert. Über Views kann man es erreichen, dass nur bestimmte Felder sichtbar sind. In diesem Fall definiert man einen View über die erlaubten Felder und gibt dem entsprechenden Benutzer Rechte auf den View - nicht aber auf die Tabelle. \par Momentan können Views so erstmal nur zum Lesen von Daten, nicht jedoch zum Ändern benutzt werden. Möchte man Daten auch ändern können, so verwendet man eine PostgreSQL Erweiterung, eine Regel. Im später später folgenden Abschnitt zu Regeln wird dies exemplarisch erklärt. \subsection{Cursors} \label{d71e4279} \par Das Cursorkonzept stammt aus eingebettetem SQL (ESQL). Eingebettet heißt, dass man SQL Anweisungen {\bf direkt} in Programmquelltexte einbettet (diese Programmiertechnik wurde inzwischen durch Standards wie ODBC weitgehend abgelöst; ESQL wird jedoch auch heute noch verwendet und auch von PostgreSQL unterstützt). In PostgreSQL stehen Cursors unabhängig von der Verwendung von ESQL zur Verfügung. Man kann sie beispielsweise auch über {\bf psql} interaktiv verwenden. \par Einem aktiven Cursor ist eine Menge von Datensätzen assoziiert, die über eine Abfrage, also über ein {\bf SELECT} Kommando, ausgewählt wurden. Man kann nun einzelne Datensätze oder Teilmengen der Datensatzmenge über den Cursor holen. Der Cursor merkt sich dabei die Position. Holt man beispielsweise dreimal einen Datensatz aus einem Cursor, so erhält man automatisch die ersten drei Datensätze. Der Cursor zählt sozusagen mit, was auch den Namen erklärt. Eine Besonderheit ist, dass man über Cursors (in PostgreSQL, das gilt nicht generell) auch rückwärts gehen kann, also dass man Datensätze mehrfach holen kann. \par Cursors funktionieren in PostgreSQL nur in Transaktionen. Um einen Cursor zu verwenden, muss dieser zunächst deklariert werden. Man kann sich vorstellen, dass man einer Abfrage einen (temporären) Namen gibt. Dann kann man Datensätze (die Ergebnisse der Abfrage) holen. Man kann den Cursor auch verschieben, beispielsweise, um Datensätze auszulassen oder erneut zu verarbeiten. Wird der Cursor nicht mehr benötigt, so wird er mit {\bf CLOSE} geschlossen. \par Besonderheiten in PostgreSQL sind, dass aus einem Cursor nicht über absolute Positionen gelesen werden kann und das Cursordaten nicht geändert werden können (es gibt kein {\bf DECLARE FOR UPDATE}). Ein Cursor ist also immer {\bf READ ONLY}. Durch die Transaktionsforderung ist er auch immer {\bf INSENSITIVE}, auch wenn dies nicht explizit mit angeben wurde. Auch {\bf SCROLL} ist nicht notwendig, da ein Cursor immer {\bf SCROLL} kann. Es muss auch kein {\bf OPEN} auf einen Cursor gemacht werden. \par Ein einfaches Beispiel folgt. \begin{tabular}{|l|} \hline \begin{tt} cursor.sql\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Die Tabelle sieht so aus: test=> SELECT code, title FROM films WHERE distributors_id = 124; code | title -------+------------------ MM-dt | Mädchen, Mädchen IJ1 | Indiana Jones 1 IJ2 | Indiana Jones 2 IJ3 | Indiana Jones 3 (4 rows) -- Transaktion starten test=> BEGIN; BEGIN -- Einen Cursor für Indiana Jones deklarieren. test=> DECLARE ijfilme INSENSITIVE CURSOR FOR test-> SELECT code, title FROM films test-> WHERE code LIKE 'IJ%' test-> ORDER BY code test-> FOR READ ONLY; DECLARE -- Ersten Datensatz holen test=> FETCH NEXT FROM ijfilme; code | title -------+----------------- IJ1 | Indiana Jones 1 (1 row) -- Zweiten Datensatz holen (1 ist wie NEXT) test=> FETCH 1 FROM ijfilme; code | title -------+----------------- IJ2 | Indiana Jones 2 (1 row) -- Einen Datensatz zurückgehen: test=> FETCH -1 FROM ijfilme; code | title -------+----------------- IJ1 | Indiana Jones 1 (1 row) -- Die nächsten zwei Datensätze holen: test=> FETCH 2 FROM ijfilme; code | title -------+----------------- IJ2 | Indiana Jones 2 IJ3 | Indiana Jones 3 (2 rows) -- Hier ist Ende: test=> FETCH 1 FROM ijfilme; code | title ------+------- (0 rows) -- weit Zurückspringen (an den Anfang) test=> MOVE -100 FROM ijfilme; MOVE 3 -- wieder am Anfang test=> FETCH 1 FROM ijfilme; code | title -------+----------------- IJ1 | Indiana Jones 1 (1 row) -- Rest holen test=> FETCH ALL FROM ijfilme; code | title -------+----------------- IJ2 | Indiana Jones 2 IJ3 | Indiana Jones 3 (2 rows) -- Den letzen nochmal (wie -1) test=> FETCH PRIOR FROM ijfilme; code | title -------+----------------- IJ3 | Indiana Jones 3 (1 row) -- Cursor schließen test=> CLOSE ijfilme; CLOSE -- Transaktion abbrechen test=> ROLLBACK; ROLLBACK\end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsection{Indizes} \label{d71e4359} \par Ein Index dient dazu, Datensätze mit bestimmten Eigenschaften schnell zu finden. Hat man beispielsweise eine Tabelle films wie im Beispiel {\bf Tabellen} und sucht den Film mit dem code FilmA, so müsste ja die gesamte Tabelle durchsucht werden (und dazu vor allem von Festplatte geladen werden), dann müsste jeder code geprüft werden, ob er denn dem gesuchten entspricht. \par Hier verwendet man einen Index. Ein Index gilt für eine bestimmte Tabellenspalte, also beispielsweise für code. Er kann aber auch aus mehreren zusammengesetzten Spalten bestehen. Ein Index ist eine effiziente Speicherung aller code Werte und einem Verweis auf die Stelle, an der der zugehörige Datensatz gespeichert ist. Wie genau die Speicherung funktioniert, hängt vom Typ des Index ab. Es gibt beispielsweise HashIndizes und binäre Bäume. \par Sucht man nun FilmA, so wird nur der Index geladen, der ja viel kleiner ist, als die ganze Tabelle. Es wird an der entsprechenden Stelle nachgesehen (bei einem Hash geht das bei einer Gleichoperation mit einem Zugriff), dann direkt die richtige Stelle (oder die richtigen Stellen) der Tabelle geladen. Das ist dann wesentlich schneller. \par Indizes sind aber nicht immer günstig. Hat man beispielsweise viele Datensätze, beispielsweise alle, so muss eh sehr viel von der Tabelle geladen werden. Hier bremst es nur, zusätzlich den Index zu laden (der Abfrageplaner würde in solchen Fällen den Index aber automatisch nicht verwenden, weil er das auch weiß, mehr dazu später). Das gleiche Verhalten kann man auch bei kleinen Tabellen erwarten (wenn man beispielsweise 100 aus 1000 Datensätzen liest, ist ein Index oft nicht günstig und wird nicht verwendet). Ein Index verlangsamt auch Änderungen, da nicht nur die Tabelle, sondern auch der Index aktualisiert werden muss. \par Ein Index kann auch Eindeutigkeit ({\bf UNIQUE}) fordern. Genauer gesagt, wird Eindeutigkeit in Tabellen garantiert, in dem ein {\bf UNIQUE} Index angelegt wird. Dies sollte man aber lieber durch ein sauberes {\bf ALTER TABLE ... ADD CONSTRAINT} erledigen. Das dann ein Index verwendet wird, ist ein Implementierungsdetail von PostgreSQL. \par Die bereits kurz erwähnten Speichertypen von Indizes sind: BTREE (Lehman-Yao B-Baum), RTREE (R-Baum mit Guttman's ''quadratic split'' Algorithmus), HASH (Litwin's lineares hashen) und GIST (Generalized Index Search Trees, verallgemeinerter Index Suchbaum). \par BTREE kann bei den Operationen \verb+<+, \verb+<+=, =, \verb+>+=, \verb+>+ verwendet werden. RTREE bei den Operationen \verb+<+\verb+<+, \&\verb+<+, \&\verb+>+, \verb+>+\verb+>+, @, \~{}=, \&\& und ein HASH bei =. \par Indexes kann man per Hand erzeugen. Dazu gibt es das nicht-standard SQL Kommando {\bf CREATE INDEX}. Zum Löschen gibt es analog {\bf DROP INDEX}. Ein Index auch hat immer einen Namen. Meistens setzt man diesen aus Tabellen- und Feldnamen zusammen. Ein Beispiel für einen Index test1\_id\_idx über die Spalte id der Tabelle test1: \par {\bf CREATE UNIQUE INDEX test1\_id\_idx ON test1 USING BTREE (id);} \par Es ist sogar möglich, Indizes für Funktionsergebnisse zu definieren. Verwendet man beispielsweise oft: \par {\bf SELECT * FROM test1 WHERE lower(col1) = 'value';} \par so hilft einem ein Index über col1 hier ja nichts. Man kann aber einen Index für lower(col1) erzeugen, der dann wieder verwendet wird: \par {\bf CREATE INDEX test1\_lower\_col1\_idx ON test1 (lower(col1));} \par Indizes können auch nur über Teile gelegt werden, in dem man eine {\bf WHERE} Bedingung hinzufügt. So kann man beispielsweise sehr häufige Werte ausklammern und von Geschwindigkeitsvorteilen bei seltenen Werten profitieren (bei häufigen Werten werden Indizes oft gar nicht verwendet, weil langsam). Eine genaue Diskussion würde diesen Rahmen hier jedoch sprengen. \par Eine Erweiterung ist die Möglichkeit, Indizes neu zu erstellen. Oft kann man diese einfach löschen und neu anlegen, was den Vorteil hat, dass nur die zu lesenden Datensätze gelockt werden. Hat man jedoch kaputte Indizes, kann man diese mit {\bf REINDEX} neu erstellen lassen. Dies wird nur durchgeführt, wenn der Index als kaputt bekannt ist, oder man {\bf FORCE} mit angibt. \par Es gibt drei Varianten des Kommandos: {\bf REINDEX INDEX} (erzeugt den folgenden Index neu), {\bf REINDEX TABLE} (erzeugt für die folgend genannte Tabelle alle Indizes neu) und {\bf REINDEX DATABASE} (erzeugt für die folgend genannte Datenbank alle Indizes neu). \par {\bf REINDEX DATABASE my\_database FORCE;} \par Hilft bei Problemen also (was in der Praxis jedoch im Prinzip NIE benötigt wird; aber wenn, dann hilft das). \subsection{Funktionen} \label{d71e4474} \par Man kann sich eigene Funktionen definieren. Hierzu stehen neben SQL noch weitere Sprachen bereit. SQL ist in manchen Punkten beschränkt oder umständlich. Hier hilft einem eine Sprache wie PL/pgSQL oder PL/Perl weiter. \par So kann man sich Funktionen schreiben, die beispielsweise komplizierte Bedingungen prüfen können (vielleicht {\bf Quersumme der ID muss gerade sein}). Funktionen kann man auch direkt aufrufen. In PostgreSQL ruft man selbst definierte Funktionen genauso auf, die eingebaute: einfach über {\bf SELECT}. Eine Funktion hallo mit zwei Parametern könnte man beispielsweise aufrufen: \par {\bf SELECT hallo(123, 'hallo parameter');} \par Eine Erweiterung von PostgreSQL ist die Möglichkeit, Aggregatfunktionen selbst zu definieren ({\bf CREATE AGGREGATE}). Aggregatfunktionen sind Funktionen wie min oder max, die beispielsweise in Gruppierten {\bf SELECT} Anweisungen verwendet werden. \subsection{Trigger} \label{d71e4521} \par {\bf Trigger} sind relativ SQL99 konform (es gibt einfach zu umgehende Ausnahmen). {\bf Trigger} können nicht auf einzelne Spalten angewendet werden. Über einen {\bf Trigger} kann man vor oder nach den Ereignissen {\bf INSERT}, {\bf DELETE} oder {\bf UPDATE} auf eine Tabelle eine Funkion aufrufen (man sagt, der {\bf Trigger} feuert bei einem Ereignis). Diese Funktion kann dann die Daten prüfen, ändern oder sonst was unternehmen. PostgreSQL bietet erweitert dazu auch Regeln (Rules). \par Über {\bf Trigger} kann man, wie auch mit Bedingungen, Konsistenzbedingungen realisieren. Werden beispielsweise Schlüssel geändert, so kann man über einen {\bf Trigger} vielleicht abhängige Datensätze entsprechend anpassen. \subsection{Regeln (Rules)} \label{d71e4568} \par Regeln sind eine PostgreSQL Erweiterung. Ähnlich wie {\bf Trigger} reagieren sie auf ein Ereignis {\bf SELECT}, {\bf INSERT}, {\bf DELETE} oder {\bf UPDATE} auf eine Tabelle. Optional kann noch eine Bedingung angegeben werden, die ebenfalls erfüllt sein muss, damit die Regel greift. Die Regel definiert dann, ob gar nicht passieren soll ({\bf NOTHING}), ob zusätzlich oder ob anstatt ({\bf INSTEAD}) des eigentlichen Kommandos ein anderes ausgeführt werden soll. \par Über Regeln kann man, wie auch mit Bedingungen, Konsistenzbedingungen realisieren. \par Regeln werden bei PostgreSQL oft in Verbindung mit Views verwendet. Dr.Ruud postete eine beispielhafte {\bf Regelschablone}: \begin{tabular}{|l|} \hline \begin{tt} rules-template.sql\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} CREATE VIEW AS SELECT * FROM ; CREATE RULE _ins AS ON INSERT TO DO INSTEAD INSERT INTO ( , , ... , ) VALUES ( new., new., ... , new. ); CREATE RULE _upd AS ON UPDATE TO DO INSTEAD UPDATE SET = new., = new., ... = new. WHERE = old.; CREATE RULE _del AS ON DELETE TO DO INSTEAD DELETE FROM WHERE = old.; \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsection{Sequenzen} \label{d71e4631} \par Eine Sequenz ist eine Zählfunktion, die hauptsächlich bei Autoinkrementfeldern angewendet werden. Bei jedem Aufruf liefert eine Sequenz einen größeren Wert (dies funktioniert natürlich auch vollständig in Transaktionen). Man kann auch den letzten Wert abfragen, den man in der Transaktion erhalten hat und so herausfinden, welchen Wert der letzte Datensatz im Autoinkrementfeld erhalten hat. \par Sequenzen werden beim Feldern vom Typ {\bf serial} automatisch erzeugt. Der Name wird automatisch bestimmt. Es kommt zu einem Fehler beim Anlegen der Tabelle, wenn der Name bereits vergeben ist. Man kann ein Sequenz auch für mehrere verschiedene Felder verwenden, und so tabellenübergreifend eindeutige Werte erzeugen. Daher werden automatisch erzeugte Sequenzen nicht automatisch mit dem Löschen von Tabellen gelöscht. \par Man kann Sequenzen auch explizit über {\bf CREATE SEQUENCE} erzeugen. Die Funktionen {\bf nextval('seq')} und {\bf currval('seq')} liefern den nächsten bzw. aktuellen (zuletzt gelieferte nextval) zurück. Mit {\bf setval('seq', 1234)} kann man den Wert eine Sequenz direkt setzen. \par Dies braucht man beispielsweise, wenn man IDs hat, die von der Sequenz noch gar nicht erzeugt wurden, weil jemand einen Wert bei {\bf INSERT} direkt angegeben hat. In solchen Fällen erreicht die Sequenz irgendwann diesen Wert (oder den ersten dieser Werte), daraufhin klappt das {\bf INSERT} nicht, weil die ID sicherlich eindeutig sein muss, die Sequenz wird auch nicht erhöht (Transaktionsabbruch) und man kommt nicht weiter. Hier hilft es, die Sequenz auf den höchsten verwendeten Wert zu setzen. Hat man eine Tabelle lager mit einem Autoinkrementfeld id, so heißt die automatisch erzeugte Sequenz lager\_id\_seq. Um diese anzupassen, kann man einfach schreiben: \begin{tt} \begin{scriptsize} test=\# SELECT setval('lager\_id\_seq', (SELECT max(id) FROM lager) ); \linebreak setval \linebreak -------- \linebreak \verb+ +\verb+ +\verb+ +3 \linebreak (1 row)\end{scriptsize} \end{tt} \linebreak \par Danach funktioniert das Autoinkrementfeld wieder. Vor solchen Phänomenen kann man sich schützen, wenn man Regeln verwendet, die ein direktes Setzen solcher Felder verhindern. \subsection{Sprachen} \label{d71e4681} \par Neben SQL unterstützt PostgreSQL weitere Datenbanksprachen. Arbeitet man mit SQL, so kann man bestimmte Dinge teils nur schwierig formulieren. \par SQL ist eine sehr mächtige Sprache, wenn man sie beherrscht. Im Gegensatz zu prozeduralen Sprachen beschreibt man jedoch keine Algorithmen. Möchte man beispielsweise alle Werte des Feldes gehalt einer Tabelle mitarbeiter um 10 Prozent erhöhen, würde man prozedural formulieren: {\bf gehe jeden Datensatz durch, und für jeden Wert setze Wert gleich Wert mal 1.1}. In SQL schreibt man das jedoch einfach so hin: \par {\bf UPDATE mitarbeiter SET gehalt = gehalt * 1.1;} \par Man beschreibt also in etwa Änderungen. Zusätzlich kann man hier natürlich auch Bedingungen angeben ({\bf nur, wenn gehalt kleiner als 5000 ist} beispielsweise). Diesen grundlegenden Unterschied muss man unbedingt verstehen, wenn man mit SQL arbeitet. In der Praxis sieht man manchmal Skripte, die die Datensätze einer Tabelle einzeln durchgehen, einen Test machen, und eine Änderung schreiben. So etwas macht man in der Regel einfach mit einem passendem SQL Kommando; das hat noch den angenehmen Nebeneffekt, viel schneller zu sein. \par In PostgreSQL kann man auch Unterabfragen verwenden: \begin{tabular}{|l|} \hline \begin{tt} Beispiel: Unterabfrage\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} UPDATE mitarbeiter SET gehalt = gehalt + (SELECT bonus FROM bonustabelle WHERE art = 'weihnachtsgeld'); \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \par Mit derartigen Konstrukten kann man Operationen durchführen, die in prozeduralen Sprachen nur sehr umständlich gemacht werden können. \subsubsection{PL/pgSQL} \label{d71e4732} \par Die Sprache PL/pgSQL ist im {\bf Lieferumfang} von PostgreSQL. Sie ähnelt PL/SQL von Oracle. Diese Sprache ist beliebt, um {\bf Trigger}funktionen zu implementieren. In PL/pgSQL sind Kontrollstrukturen verfügbar (beispielsweise Schleifen). Diese Sprache ist an SQL angelehnt und daher sehr leicht erlernbar und einfach zu benutzen. \par Neben Zuweisungen, der Möglichkeit dynamische SQL Kommandos auszuführen und Bedingungen auszuwerten, stehen mehrere Schleifen zur Verfügung. Mit {\bf FOR} kann gezählt oder über Datensätze iteriert werden, auch mit {\bf LOOP} und {\bf WHILE} kann man Schleifen bilden. Bedingungen sind flexibel ({\bf IF-THEN-ELSIF-ELSE}). Ein Blick in die Dokumentation ist sicherlich interessant, {\bf PL/pgSQL} sollte zum Handwerkszeug eines Datenbankbenutzers gehören. \par Als Beispiel folgt eine {\bf Trigger}funktion. Da die gesamte Funktion in einfache Anführungszeichen eingeschlossen ist, müssen innerhalb der Funktion alle einfachen Anführungszeichen durch zwei aufeinanderfolgende ersetzt werden (leider etwas unübersichtlich). \begin{tabular}{|l|} \hline \begin{tt} Beispiel: triggerbeispiel.sql\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Eine Beispieltabelle für Angestellte CREATE TABLE emp ( empname text, -- Name salary integer, -- Gehalt last_date timestamp, -- Letztes Datum last_user text -- Letzter Benutzer ); -- Der Trigger. -- Es ist eine Funktion, die einen Datensatz zurückliefert. CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS ' -- Dieses Begin kommt von PL/pgSQL. Es startet keine -- neue Transaktion! BEGIN -- Prüfen, ob empname und salary (Gehalt) angegeben wurde IF NEW.empname ISNULL THEN -- RAISE erzeugt einen Fehler (EXCEPTION) -- Die Transaktion wird dadruch abgebrochen. RAISE EXCEPTION ''empname darf nicht NULL sein''; END IF; IF NEW.salary ISNULL THEN -- anstelle des % steht dann der Name RAISE EXCEPTION ''% ohne Gehalt?!'', NEW.empname; END IF; -- Wer arbeitet für uns und muss dafür bezahlen? IF NEW.salary < 0 THEN RAISE EXCEPTION ''% mit negativen Gehalt?!'', NEW.empname; END IF; -- Es wird das letze Änderungsdatum und der Änderungsbenutzer gesetzt. -- Selbst wenn bei INSERT oder UPDATE last_user angegeben wird, so -- wird dennoch immer current_user verwendet. Es ist also -- nicht mehr möglich, einen falschen Eintrag zu erzeugen. NEW.last_user := current_user; -- now sollte hier besser als Funktion und besser gegen -- den standardkonformen Namen current_timestamp ersetzt werden: -- NEW.last_date := current_timestamp; NEW.last_date := ''now''; -- Den (geänderten) Datensatz zurückliefern (wird dann eingetragen) RETURN NEW; END; -- Das END kommt - wie auch BEGIN - von PL/pgSQL und beeinflußt -- die aktive Transaktion nicht ' LANGUAGE 'plpgsql'; -- Diese Funktion als Trigger setzen. Danach wird sie bei INSERT -- oder UPDATE automatisch gestartet. CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsubsection{PL/Perl} \label{d71e4803} \par PL/Perl kann auf zwei Arten installiert werden (siehe Abschnitt Sprachen im administrativen Teil). Im trusted Modus kann die Sprache gefahrlos benutzt werden, darf jedoch nicht alles. So dürfen zum Beispiel keine externen Module geladen werden. Im untrusted Modus geht das. Man kann so beispielsweise Mails verschicken. Da hierdurch jeder, der eine Funktion schreiben und starten darf, die Unix-Rechte des Unix-Benutzers postgres (oder unter welchem Benutzer das DBMS läuft) erhält, muss man hier vorsichtig und sorgfältig arbeiten. \par Die Verwendung von PL/Perl ist sehr intuitiv. {\bf NULL} Werte werden in Perl als {\bf undef} dargestellt. Parameter werden wie gewohnt über {\bf \$\_} erreicht. Zusammengesetzte Datentypen werden als Referenzen auf Hashes übergeben, was eine sehr komfortable Handhabung erlaubt. \par Fehler werden durch Aufruf der Funktion elog gemeldet. elog verhält sich analog zu {\bf RAISE}. \par Leider gibt es (noch?) einige Einschränkungen bei der Verwendung. So kann PL/Perl leider nicht dazu verwendet werden, {\bf Trigger}funktionen zu schreiben. Es ist aber möglich, einen {\bf Trigger} in PL/pgSQL zu schreiben, und hier einfach eine PL/Perl Funktion aufruft. \begin{tabular}{|l|} \hline \begin{tt} Beispiel: plperl.sql\end{tt} \\ \hline \begin{minipage}{130mm} \begin{scriptsize} \begin{verbatim} -- Eine Funktion, die den größeren Wert zurückliefert. -- Ist eine der Werte NULL, so wird der andere zurückgeben. -- Sind beide NULL, ergibt die Funktion auch NULL CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS ' my ($a,$b) = @_; if (! defined $a) { if (! defined $b) { return undef; } return $b; } if (! defined $b) { return $a; } if ($a > $b) { return $a; } return $b; ' LANGUAGE plperl; -- Ein Beispiel mit einen zusammengesetzten Datentyp (hier employee) CREATE TABLE employee ( name text, basesalary integer, bonus integer ); -- Als Parameter kommt ein employee, also z.B. ein Datensatz aus -- dieser Tabelle CREATE FUNCTION empcomp(employee) RETURNS integer AS ' my ($emp) = @_; return $emp->{''basesalary''} + $emp->{''bonus''}; ' LANGUAGE plperl; \end{verbatim} \end{scriptsize} \end{minipage} \\ \hline \end{tabular} \subsection{Notifikationen (Benachrichtigungen)} \label{d71e4871} \par Eine PostgreSQL Erweiterung erlaubt es, das mehrere Clienten sich synchronisieren. Dazu kann ein Client über {\bf LISTEN} ein Objekt beobachten. Ruft ein anderer Client {\bf NOTIFY} auf diesem Objekt auf, so wird ersterer (und alle anderen {\bf LISTENer}) benachrichtigt. Hat er kein Interesse mehr an Notifikationen, ruft er ein {\bf UNLISTEN} auf das Objekt auf. {\bf LISTEN} ist nicht blockierend; die Notifikation erfolgt asynchron. \par Dies wird wohl selten verwendet und ist nicht portabel. Oft kann man ähnliches Verhalten auch über Datensatz-Locks über Tabellen erreichen. \subsection{Statistiken für den Planer} \label{d71e4903} \par Wie im Abschnitt Optimierung mit EXPLAIN noch genauer erklärt wird, wird eine Abfrage vom Planer in Abfragepläne umgewandelt. Um sich für den richtigen (also den schnellsten) Abfrageplan entscheiden zu können, muss beispielsweise geschätzt werden, wie viele Daten von Festplatte gelesen werden müssen. Das hängt von der Tabellengröße ab. \par Die Tabellengrößen werden von PostgreSQL in einer speziellen Tabelle {\bf pg\_class} gespeichert. Meistens werden jedoch nicht alle Datensätze benötigt, sondern nur ein Teil. Dieser wird oft über eine Bedingung definiert. Dadurch wird es schwierig zu schätzen, wie viele Daten geladen werden müssen, da man dazu ja wissen muss, wie oft die Bedingung erfüllt ist. \par Um diese Abschätzung durchführen zu können, werden Statistiktabellen geführt, beispielsweise {\bf pg\_stats}. In diesen Tabellen werden statistische Informationen über Tabelleninhalte gespeichert, beispielsweise die häufigsten Werte, die Anzahl der {\bf NULL} Werte, die Anzahl der verwendeten Werte (es kann ja Tabellen mit 1000 Einträgen geben, die nur 4 verschiedene Werte verwenden) und andere. Mit diesen Informationen errechnet der Planer seine Abschätzungen. \par Die Statistiken werden natürlich nicht ständig aktualisiert, das wäre ja sehr bremsend (um statistische Korrelation zu berechnen, muss ja in jedem Fall jeder Datensatz gelesen werden). Statt dessen werden die Statistiken durch das SQL Kommando {\bf ANALYZE} oder {\bf VACUUM ANALYZE} aktualisiert, dass man demzufolge regelmäßig (zum Beispiels nachts und nach großen Änderungen) ausführen sollte. \subsection{Optimierung mit ''EXPLAIN''} \label{d71e4944} \par Natürlich ist es immer interessant, Abfragen auf Geschwindigkeit zu optimieren. Bei langsamen Abfragen ist es interessant, den Grund zu kennen. Vielleicht fehlt ja nur ein Index oder sitzt ungünstig? \par Verarbeitet PostgreSQL eine Abfrage, so wird vom DBMS ein Abfrageplan erstellt. Dies wird durch den sogenannten Planer erledigt. Dieser legt fest, in welcher Reihenfolge die Daten organisiert werden und ob (und welche) Indizes verwendet werden. Dazu prüft er die verschiedenen Möglichkeiten auf Effizienz. Er erstellt also erstmal viel Pläne und wählt dann den Plan aus, der die geringsten Kosten hat, also am schnellsten geht. \par Es gibt das {\bf EXPLAIN} Kommando, das den Abfrageplan für die Abfrage anzeigt (eine PostgreSQL Erweiterung). Man erhält die geschätzten Kosten, bis mit der Ausgabe begonnen werden könnte, und die gesamten Kosten. Als Einheit wird in etwa {\bf Festplattenzugriffe} verwendet. Die anderen beiden Zahlen sind die geschätzte Anzahl an Datensätzen (etwas richtiger ist hier der Begriff Tupel), die zurückgegeben werden, und die geschätzte Größe eines Datensatzes. \par Eine Abfrage besteht aus mehreren Teilen. Die Kosten jedes Teiles schließen immer die aller nach unten folgenden Teile ein. \par Ein paar Beispiele dazu. \begin{tt} \begin{scriptsize} regression=\# EXPLAIN SELECT * FROM tenk1; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Seq Scan on tenk1\verb+ +(cost=0.00..333.00 rows=10000 width=148)\end{scriptsize} \end{tt} \linebreak \par Man sieht: Es ist ein vollständiges durchgehen der Tabellen tenk1 notwendig (Seq Scan heißt sequentiell). Mit der Ausgabe kann sofort begonnen werden, nach 333 Zugriffen ist sie nach 10000 Datensätzen beendet. Die 333 Zugriffe entstehen hier übrigens durch 233 Diskzugriffe und 10000 * cpu\_tuple\_cost (Voreinstellung ist 0.01), also 233 + 100 == 333. \begin{tt} \begin{scriptsize} regression=\# EXPLAIN SELECT * FROM tenk1 WHERE unique1 \verb+<+ 1000; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Seq Scan on tenk1\verb+ +(cost=0.00..358.00 rows=1007 width=148)\end{scriptsize} \end{tt} \linebreak \par Man sieht, das immer noch die gesamte Tabelle gelesen werden muss. Es werden weniger Datensätze erwartet (natürlich ist der Wert nur geschätzt und nicht wirklich aussagekräftig). Die Kosten sind durch die zusätzlich benötigte Vergleichszeit etwas gestiegen. Es wird immer noch kein Index verwendet, weil er sich nicht lohnt. \par Oft liefert Abfragen jedoch nicht solche Mengen an Daten: \begin{tt} \begin{scriptsize} regression=\# EXPLAIN SELECT * FROM tenk1 WHERE unique1 \verb+<+ 50; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Index Scan using tenk1\_unique1 on tenk1\verb+ +(cost=0.00..181.09 rows=49 width=148)\end{scriptsize} \end{tt} \linebreak \par Hier ist die Bedingung so, dass nur noch 49 Datensätze erwartet werden. Daher entscheidet der Planer, den Index zu verwenden. Da hier nur 50 Datensätze erwartet werden, ist die Verwendung eines Index billiger, obwohl jeder einzelne Datensatz langsamer geladen wird (Festplatten lesen Folgedaten schneller). \begin{tt} \begin{scriptsize} regression=\# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 \verb+<+ 50 \linebreak regression-\# AND t1.unique2 = t2.unique2; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Nested Loop\verb+ +(cost=0.00..330.41 rows=49 width=296) \linebreak \verb+ +-\verb+>+\verb+ +Index Scan using tenk1\_unique1 on tenk1 t1 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..181.09 rows=49 width=148) \linebreak \verb+ +-\verb+>+\verb+ +Index Scan using tenk2\_unique2 on tenk2 t2 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..3.01 rows=1 width=148)\end{scriptsize} \end{tt} \linebreak \par In diesem etwas komplizierteren Beispiel wird zusätzlich eine zweite Tabelle benutzt, die über einen {\bf Join} verbunden ist. Man sieht, das der Planer einen {\bf Indexscan} ausgewählt hat. Durch den {\bf Join} entsteht ein {\bf Loop} mit zwei Teilen. Zunächst wird die Tabelle tenk1 über den Index durchgearbeitet. Die Kosten sind natürlich die gleichen im Beispiel davor (gleiche Bedingung:{\bf WHERE unique1 \verb+<+ 50}). Mit dem Wert unique2, der aus tenk1 gelesen wurde (genauer gesagt, sind das ja insgesamt 49 Werte!), wird nun ein passender Eintrag in tenk2 gesucht. Der Planer erwartet genau einen Treffer und verwendet daher wieder einen Index (der glücklicherweise auch verfügbar ist). Diese Teilabfrage gilt für einen konstanten Wert unique2 (je einen der insgesamt 49). Damit ist der Zugriff vergleichsweise billig (3). \par Die zweite Teilabfrage wird nun für jeden der 49 Werte durchgeführt. Die Kosten sind also 49 * 3 == 147. Dazu kommen die 181 des vorherigen Teils (der die 49 Werte überhaupt erstmal lieferte), macht zusammen 147 + 181 == 328. Dazu kommt noch etwas Rechenzeit für den {\bf Join} (hier ca. 2). Macht dann zusammen 330. \par 330 sind auch die Kosten, die der Planer für den Loop ausgerechnet hat. Es 49 Datensätze (es wird ja erwartet, das jeweils ein Datensatz passt), nur das die etwas größer sind, also vorher (sind ja durch einen {\bf Join} verbunden). \par Der Planer hat sich entschieden, einen {\bf nested-loop join} (etwa: {\bf geschachtelte Schleife}) zu verwenden. Man kann über Variablen den Planer beeinflussen. In der Praxis bringt das so gut wie nie Vorteile. Beispielsweise kann man dem Planer sagen, dass er {\bf nested-loop join} nicht verwenden soll: \begin{tt} \begin{scriptsize} regression=\# set enable\_nestloop = off; \linebreak SET VARIABLE \linebreak \linebreak ***layout: achtung, zwei Zeilen Prompt! \linebreak regression=\# EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 \verb+<+ 50 \linebreak regression-\# AND t1.unique2 = t2.unique2; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Hash Join\verb+ +(cost=181.22..564.83 rows=49 width=296) \linebreak \verb+ +-\verb+>+\verb+ +Seq Scan on tenk2 t2 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..333.00 rows=10000 width=148) \linebreak \verb+ +-\verb+>+\verb+ +Hash\verb+ +(cost=181.09..181.09 rows=49 width=148) \linebreak \verb+ +\verb+ +\verb+ +\verb+ +-\verb+>+\verb+ +Index Scan using tenk1\_unique1 on tenk1 t1 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..181.09 rows=49 width=148)\end{scriptsize} \end{tt} \linebreak \par Der Planer kommt nun mit einem anderen Plan. Zunächst werden wieder die 49 Datensätze aus tenk1 mit Indexunterstützung geladen. Die Werte werden nun aber erst alle geladen, und in einem Hash gespeichert. Man sieht das gut an den Anfangskosten für den Hash: sie entsprechen den Gesamtkosten für den Indexscan (da der Hash anschließend gebaut wird, und sehr schnell fertig ist). \par Anschließend wird die Tabelle tenk2 sequentiell durchsucht, ob irgendwo der Wert unique2 aus der Tabelle zu einem der im Hash gespeicherten Werte passt. Dies muss ja nun für alle 10.000 Datensätze gemacht werden ({\bf nestloop} ist ja {\bf verboten}). \par Sobald mit dem Scan über tenk2 begonnen wurde, sind die ersten Treffer zu erwarten. Die Anfangskosten des Joins entsprechen also den Kosten, die anfallen, bis mit tenk2 begonnen werden kann (vorher kommt ja keine Ausgabe), also den Gesamtkosten des ersten Indexscans. Dazu kommen die 333 für den sequentiellen Scan über tenk2, macht 514.09. Die restlichen 50 gehen für Rechenleistung drauf; schließlich muss mit jedem der 10000 Datensätze eine Test auf den vorher gespeicherten Hash gemacht werden. Die erwarten Kosten sind wesentlich höher als vorhin, daher hat der Planer vorhin auch einen {\bf nestloop} verwendet. \par Weitere Variablen, die bestimmte Pläne vermeiden, sind: {\bf ENABLE\_HASHJOIN}, {\bf ENABLE\_INDEXSCAN}, {\bf ENABLE\_MERGEJOIN}, {\bf ENABLE\_SEQSCAN} {\bf ENABLE\_SORT} und {\bf ENABLE\_TIDSCAN}. Auch diese können auf {\bf off} gesetzt werden, um anzuzeigen, dass sie zu vermeiden sind. Wie bereits gesagt, lassen sich nur schwer Fälle konstruieren, wo das was bringt. \par {\bf EXPLAIN} kann auch um {\bf ANALYZE} erweitert werden. Dann wird die Abfrage tatsächlich ausgeführt, und auch die wirklichen Werte werden ausgegeben. \begin{tt} \begin{scriptsize} regression=\# EXPLAIN ANALYZE \linebreak regression-\# SELECT * FROM tenk1 t1, tenk2 t2 \linebreak regression-\# WHERE t1.unique1 \verb+<+ 50 AND t1.unique2 = t2.unique2; \linebreak NOTICE:\verb+ +QUERY PLAN: \linebreak \linebreak Nested Loop\verb+ +(cost=0.00..330.41 rows=49 width=296) \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (actual time=1.31..28.90 rows=50 loops=1) \linebreak \verb+ +-\verb+>+\verb+ +Index Scan using tenk1\_unique1 on tenk1 t1 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..181.09 rows=49 width=148) \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (actual time=0.69..8.84 rows=50 loops=1) \linebreak \verb+ +-\verb+>+\verb+ +Index Scan using tenk2\_unique2 on tenk2 t2 \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (cost=0.00..3.01 rows=1 width=148) \linebreak \verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ +\verb+ + (actual time=0.28..0.31 rows=1 loops=50) \linebreak Total runtime: 30.67 msec\end{scriptsize} \end{tt} \linebreak \par Hier wird die tatsächlich benötigte Zeit in Millisekunden angezeigt. Man erkennt auch, dass der Planer sich gering verschätzt hat, anstatt 49 Datensätzen sind es 50. Es läßt sich abschätzen, das ein {\bf Festplattenzugriff} (die Einheit von EXPLAIN) hier in etwa 10 Millisekunden dauert. \par Dies mag als Einführung ausreichen. Das Verstehen dieser Ausgaben erfordert Übung. Man kann so erkennen, ob und wann Indexe verwendet werden, ob sie günstig sind, oder vielleicht gar nicht benötigt sind. Dann sollte man sie löschen, dass spart Zeit bei Aktualisierungen. \section{Ausblick} \label{d71e5141} \par Es gibt etliches an Dokumentation, die mit PostgreSQL mitgeliefert wird. \par Die PostgreSQL Homepage isthttp://www.postgresql.org/. Hier finden sich viele Informationen und sehr viel (englischsprachige) Dokumentation. Natürlich ist auch eine {\bf SQL Referenz} vorhanden. \ref{inhalt.tex} \end{document}