PostgreSQL Administration Edition

336 Pages·2009·1.47 MB·German

by  Peter Eisentraut

Die fortschrittlichste Ori D
gieu
Open-Source-Datenbank n t
alsc
a h
use
g
a
b
e
PostgreSQL
Administration
O’Reilly
Peter Eisentraut & Bernd Helmle

PostgreSQL-Administration
Peter Eisentraut & Bernd Helmle
Beijing · Cambridge · Farnham · Köln · Sebastopol · Taipei · Tokyo
Die Informationen in diesem Buch wurden mit größter Sorgfalt erarbeitet. Dennoch können
Fehler nicht vollständig ausgeschlossen werden. Verlag, Autoren und Übersetzer übernehmen
keine juristische Verantwortung oder irgendeine Haftung für eventuell verbliebene Fehler und
deren Folgen.
Alle Warennamen werden ohne Gewährleistung der freien Verwendbarkeit benutzt und sind
möglicherweise eingetragene Warenzeichen. Der Verlag richtet sich im Wesentlichen nach den
Schreibweisen der Hersteller. Das Werk einschließlich aller seiner Teile ist urheberrechtlich
geschützt. Alle Rechte vorbehalten einschließlich der Vervielfältigung, Übersetzung,
Mikroverfilmung sowie Einspeicherung und Verarbeitung in elektronischen Systemen.
Kommentare und Fragen können Sie gerne an uns richten:
O’Reilly Verlag
Balthasarstr. 81
50670 Köln
Tel.: [phone]
Fax: [phone]
E-Mail: [email protected]
Copyright der deutschen Ausgabe:
© 2009 by O’Reilly Verlag GmbH & Co. KG
1. Auflage 2009
Die Darstellung von Blaurückenwaldsängern im Zusammenhang mit dem Thema
PostgreSQL ist ein Warenzeichen von O’Reilly Media, Inc.
Bibliografische Information Der Deutschen Bibliothek
Die Deutsche Bibliothek verzeichnet diese Publikation in der
Deutschen Nationalbibliografie; detaillierte bibliografische Daten
sind im Internet über http://dnb.ddb.de abrufbar.
Lektorat: Volker Bombien, Köln
Fachliche Begutachtung: Sven Riedel, München
Korrektorat: Eike Nitz, Köln
Satz: III-satz, Husby, www.drei-satz.de
Umschlaggestaltung: Michael Oreal, Köln
Produktion: Andrea Miß und Astrid Sander, Köln
Belichtung, Druck und buchbinderische Verarbeitung:
Druckerei Kösel, Krugzell; www.koeselbuch.de
ISBN 978-3-89721-777-5
Dieses Buch ist auf 100% chlorfrei gebleichtem Papier gedruckt.
Inhalt
Vorwort . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . VII
1 Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Softwareinstallation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
PostgreSQL einrichten . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Upgrades durchführen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Konfiguration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Allgemeines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Einstellungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Betriebssystemeinstellungen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Zusammenfassung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3 Wartung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
VACUUM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
ANALYZE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Das Programm vacuumdb . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Autovacuum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Kostenbasiert verzögertes Vacuum . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Reindizierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Weitere Wartungsaufgaben . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Wartungsstrategie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
4 Datensicherung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Datensicherungsstrategie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Datensicherungsmethoden für PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
| V
5 Überwachung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Was überwachen? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Wie überwachen? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Und nun? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
6 Wiederherstellung, Reparatur und Vorsorge . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Wiederherstellung und Reparatur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
Vorsorge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
7 Sicherheit, Rechteverwaltung, Authentifizierung . . . . . . . . . . . . . . . . . . . . . . . . . 153
Allgemeines über Sicherheit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Benutzerverwaltung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Zugangskontrolle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Rechteverwaltung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
Sichere Datenübertragung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
8 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Ablauf der Befehlsverarbeitung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Flaschenhälse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Indexe einsetzen . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Ausführungspläne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Partitionierung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
Befüllen der Datenbank . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239
9 Replikation und Hochverfügbarkeit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Begriffserklärung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
pgpool-II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
PgBouncer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
PL/Proxy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257
Slony-I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
WAL-Replikation mit pg_standby . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289
DRBD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Zusammenfassung . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297
10 Hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Festspeichersystem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 299
Arbeitsspeicher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305
Prozessor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Aufbau eines Serversystems für PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
Hardware-Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317
VI | Inhalt
0
Vorwort
Das fortschrittlichste Open-Source-Datenbankmanagementsystem der Welt. So lautet
weithin unangefochten seit einem Jahrzehnt der Untertitel zu PostgreSQL. Mittlerweile
ist es millionenfach im Einsatz, Teil der kritischen öffentlichen Infrastruktur des Inter-
nets und der Gesellschaft und ein zentrales Element in der Zukunft der Datenbankwelt.
Doch jeder kann Teil dieser Erfolgsgeschichte sein. PostgreSQL ist Open Source, es ist
kostenlos verfügbar und wird von einer großen, offenen Community aus Anwendern und
Entwicklern vorangetrieben. Dieses Buch möchte einen Teil dazu beitragen, dieses Soft-
ware-Produkt allen interessierten Anwendern zugänglich zu machen.
Zielgruppe
Dieses Buch richtet sich primär an Administratoren von PostgreSQL-Datenbanksyste-
men. Es soll dabei helfen, PostgreSQL-Datenbanksysteme erfolgreich stabil und perfor-
mant zu betreiben. Es wird davon ausgegangen, dass der Leser entweder schon Umgang
mit PostgreSQL hatte oder Erfahrungen mit der Administration von anderen Datenbank-
systemen hat. Vertrautheit mit SQL und Unix-Shells wird von Vorteil sein.
Die Entwicklung von Datenbankanwendungen wird in diesem Buch nicht behandelt und
fortgeschrittene Programmierkenntnisse sind auch nicht vonnöten. Allerdings wird im
Zuge der Administration eines Datenbanksystems oft die Kommunikation zwischen
Administration und Entwicklung notwendig sein. Daher können Kenntnisse in Sachen
Anwendungsentwicklung generell von Vorteil sein.
Dieses Buch soll die PostgreSQL-Dokumentation um praktische Erfahrungswerte ergän-
zen. Es kann für den PostgreSQL-Administrator im Alltag aber auch als alleinige Doku-
mentation und Referenz nützlich sein, wobei dieses Buch aber niemals den Anspruch
haben kann, den gesamten Umfang des PostgreSQL-Systems abzudecken.
| VII
Struktur dieses Buchs
Dieses Buch besteht aus zehn Kapiteln. Die Kapitel sind in der Reihenfolge ausgelegt, in
der man die entsprechenden Themen im Laufe des Lebens eines Datenbanksystems
ungefähr betrachten wird. Wer also schnell »von 0 auf 100« kommen möchte, kann die-
ses Buch von vorne bis hinten durchlesen. Jedes Kapitel soll aber auch für sich stehen
und Anwendern, die schon einen gewissen Kenntnis- und Erfahrungsstand haben, die
Möglichkeit geben, sich in bestimmten Themenbereichen weiterzubilden. Auch kann das
Buch so als tägliche Referenz verwendet werden. Es ist also auch möglich – und in vielen
Fällen wohl auch empfehlenswert –, das Buch in einer selbst gewählten Reihenfolge
durchzuarbeiten.
Kapitel 1, Installation
Das Leben jeder Software beginnt mit der Installation.
Kapitel 2, Konfiguration
Behandelt die Einstellung der Konfigurationsparameter im PostgreSQL-Server.
Kapitel 3, Wartung
Hier werden wiederkehrende Aufgaben beschrieben, die zur Wartung eines Postgre-
SQL-Servers notwendig sind.
Kapitel 4, Datensicherung
Teil der Wartungsaufgaben ist die Datensicherung, der ein eigenes Kapitel gewidmet
ist.
Kapitel 5, Überwachung
Stellt Verfahren vor, mit denen Zustand und Verhalten eines PostgreSQL-Servers
überwacht und analysiert werden können.
Kapitel 6, Wiederherstellung
Hier wird behandelt, was man tun kann, wenn irgendetwas kaputt gegangen zu sein
scheint.
Kapitel 7, Sicherheit
Die Absicherung der Daten vor unberechtigtem Zugriff ist Thema dieses Kapitels.
Kapitel 8, Performance
Behandelt, wie man SQL-Befehle schneller machen kann.
Kapitel 9, Replikation
Stellt verschiedene Lösungen vor, PostgreSQL-Datenbanken zu replizieren und zu
clustern, um bessere Verfügbarkeit oder bessere Leistung zu erreichen.
Kapitel 10, Hardware
Enthält Hinweise zu Auswahl und Einrichtung von Hardware für PostgreSQL-Sys-
teme. Von der Logik her würde die Hardware-Auswahl wohl noch vor der Installa-
tion stattfinden, aber es ist auch sinnvoll, diese Fragen erst zu betrachten, nachdem
man die Interna eines PostgreSQL-Systems gut verstanden hat.
VIII | Vorwort
In diesem Buch behandelte Versionen
Dieses Buch ist auf PostgreSQL 8.3 ausgelegt. Die aktuellste PostgreSQL-Version zum
Zeitpunkt der Drucklegung ist 8.3.4, aber alle Releases der Reihe 8.3 unterscheiden sich
– wenn überhaupt – nur geringfügig in den Benutzerschnittstellen und in der Verhaltens-
weise.
Wo es bedeutende Unterschiede gibt, wird auch kurz auf Version 8.2 und ältere Versio-
nen eingegangen. Aber gerade bei der Datenbankadministration hat sich sowohl bei den
Möglichkeiten als auch bei den Anforderungen über die letzten Hauptversionen sehr viel
getan, weswegen ältere Versionen erstens aus Platzgründen nur kurz behandelt werden
können und zweitens weniger zu empfehlen sind, wenn man die maximalen Möglichkei-
ten bei der Datenbankadministration ausnutzen möchte.
An den Stellen wo es um Betriebssystemeinstellungen und die Einbindung externer Pro-
grammpakete geht, haben wir natürlich eine Auswahl treffen müssen, die sich letztlich
daran orientiert, womit wir selbst Umgang haben und was wir weiterempfehlen wollen.
In den allermeisten Teilen ist dieses Buch aber komplett unabhängig von der Wahl des
Betriebssystems oder der Zusatzwerkzeuge.
Typografische Konventionen
In diesem Buch werden die folgenden typografischen Konventionen verwendet:
Kursivschrift
Wir für die Namen von Programmen, Befehlen, Dateien, Verzeichnissen sowie für
URLs verwendet.
Nichtproportionalschrift
Wird für SQL-Anweisungen sowie Codeteile, Codebeispiele und Systemausgaben
verwendet.
Nichtproportionalschrift kursiv
Wird in Codebeispielen für Platzhalter dort verwendet, wo Sie selbst eigene Werte
einsetzen müssen.
Dieses Symbol kennzeichnet einen Hinweis, der eine nützliche Bemerkung
zum nebenstehenden Text enthält.
Dieses Symbol kennzeichnet eine Warnung, die sich auf den nebenstehen-
den Text bezieht.
Typografische Konventionen | IX
Danksagungen
Treibende Kraft bei diesem Buch war wieder einmal unser Lektor Volker Bombien. Seine
Geduld und Ausdauer waren durch nichts zu ersetzen.
Wir danken dem Fachlektor Sven Riedel und allen Kollegen, Probelesern und Vorabkriti-
kern für ihre Hinweise.
Die credativ GmbH hat es uns ermöglicht, unser Hobby zum Beruf zu machen. Unseren
Erfahrungsschatz, den wir in diesem Buch teilen möchten, konnten wir nur so aufbauen.
Wir grüßen das Linuxhotel und alle Schulungsteilnehmer, die gewissermaßen unsere
Versuchskaninchen und Betatester beim Aufbau dieses Materials waren.
X | Vorwort