Vorwort
Zielgruppe
Struktur dieses Buchs
In diesem Buch behandelte Versionen
Neues in der dritten Auflage
Typografische Konventionen
Danksagungen
1. Installation
Softwareinstallation
Versionierung
Paketinstallation
Debian und Ubuntu
Red Hat
SUSE
Quellcode bauen
Contrib
PostgreSQL einrichten
Datenverzeichnis initialisieren
Datenverzeichnis bestimmen
Benutzerkonto einrichten
Datenverzeichnis initialisieren
Server starten
Server starten mit Programm postgres
Server starten mit dem Programm pg_ctl
Server mit Init-Skript starten
Server anhalten
Server per Signal anhalten
Server anhalten mit dem Programm pg_ctl
Server anhalten mit Init-Skript
Server neu starten oder neu laden
Nächste Schritte
Upgrades durchführen
Kleine und große Upgrades
Upgrade mit pg_dumpall
Upgrade mit pg_upgrade
Upgrade durch Replikation
2. Konfiguration
Allgemeines
Die Datei postgresql.conf
Syntax
include
Änderungen laden
Kommandozeile
PGOPTIONS
SET, RESET und SHOW
Einstellungen für Datenbanken und Rollen
Präzedenz
Einstellungen
Verbindungskontrolle
listen_addresses
port
max_connections
superuser_reserved_connections
ssl
Speicherverwaltung
shared_buffers
temp_buffers
work_mem
maintenance_work_mem
Wartung: Vacuum und Autovacuum
autovacuum
autovacuum_max_workers
autovacuum_naptime
Scale Factor und Threshold
Transaktionslog
fsync
wal_buffers
synchronous_commit
wal_writer_delay
checkpoint_segments
checkpoint_timeout
checkpoint_warning
checkpoint_completion_target
full_page_writes
wal_level
archive_mode
archive_command
archive_timeout
hot_standby
max_wal_senders
wal_sender_delay
max_standby_archive_delay
max_standby_streaming_delay
wal_keep_segments
vacuum_defer_cleanup_age
wal_receiver_status_interval
hot_standby_feedback
Planereinstellungen
Plantypen
enable_seqscan
enable_indexscan
enable_indexonlyscan
enable_bitmapscan
enable_nestloop
enable_hashjoin
enable_mergejoin
enable_hashagg
enable_sort
enable_tidscan
enable_material
Kostenparameter
seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
effective_cache_size
Andere Planereinstellungen
default_statistics_target
cursor_tuple_fraction
Logging
Wohin soll geloggt werden?
log_destination
CSV-Log
logging_collector
log_directory
log_filename
log_rotation_age
log_rotation_size
log_truncate_on_rotation
syslog_facility
syslog_ident
Wann soll geloggt werden?
client_min_messages
log_min_messages
log_autovacuum_min_duration
log_error_verbosity
log_min_error_statement
log_min_duration_statement
Was soll geloggt werden?
log_checkpoints
log_connections
log_disconnections
log_duration
log_hostname
log_line_prefix
log_lock_waits
log_statement
log_temp_files
log_timezone
Statistiken
track_activities
track_activity_query_size
track_counts
track_functions
track_io_timing
update_process_title
stats_temp_directory
Lokalisierung
client_encoding
datestyle
intervalstyle
lc_collate
lc_ctype
lc_messages
lc_monetary
lc_numeric
server_encoding
Diverses
bytea_output
custom_variable_classes
effective_io_concurrency
search_path
server_version
server_version_num
shared_preload_libraries
local_preload_libraries
statement_timeout
timezone
Betriebssystemeinstellungen
Shared Memory
Memory Overcommit
Zusammenfassung
3. Wartung
VACUUM
Multiversion Concurrency Control
Der VACUUM-Befehl
Einfaches VACUUM
VACUUM FULL
Einfrieren
Die Free Space Map
Die Visibility Map
Überwachung von VACUUM
ANALYZE
Das Programm vacuumdb
Autovacuum
Konfiguration
autovacuum
log_autovacuum_min_duration
autovacuum_max_workers
autovacuum_naptime
autovacuum_freeze_max_age , vacuum_freeze_min_age
vacuum_freeze_table_age
Scale Factor und Threshold
Überwachung von Autovacuum
Tabellenspezifische Einstellungen für Autovacuum
Kostenbasiert verzögertes Vacuum
Konfiguration
vacuum_cost_delay
vacuum_cost_limit
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
Reindizierung
Weitere Wartungsaufgaben
Wartungsstrategie
4. Datensicherung
Datensicherungsstrategie
Allgemeines über Sicherheit
Risiken
Überlegungen zur Datensicherung
Wohin sichern?
Wie oft sichern?
Was sichern?
Wie wiederherstellen?
Was kostet das?
Datensicherungsmethoden für PostgreSQL
RAID
Replikation
Dateisystemsicherung
Dateisystem sichern
Snapshots
Wiederherstellung
Dumps
Dumps ausführen
Datensicherung auf andere Rechner
Automatisierung
Sicherungsstände rotieren
Wiederherstellung
Keine inkrementelle Sicherung
Andere Ausgabeformate
WAL-Archivierung und Point-in-Time-Recovery
Konzepte
Archivierung konfigurieren
Archivierungsintervalle
Basissicherungen
Basissicherung von Hand
Organisation der Sicherung
Bereinigung der Sicherung
Wiederherstellung
Point-in-Time-Recovery
Zeitleisten
Einschätzung
Hot Standby
5. Überwachung
Was überwachen?
Datenbankaktivität
Sperren
Logdateien
Betriebssystem
Datensicherung
Wie überwachen?
Unix-Werkzeuge
ps
top
ptop
iotop
vmstat
iostat
Statistiktabellen
Aktivität
Datenbanken
Tupelstatistiken
Background Writer
Blockstatistiken
Replikationsstatistiken
Statistiken zurücksetzen
Sperren
Informationen über Objektgrößen
pg_stat_statements
Grafische Administrationsprogramme
Überwachungswerkzeuge
Nagios
Munin
Sysstat
pgFouine
pgBadger
Und nun?
6. Wiederherstellung, Reparatur und Vorsorge
Wiederherstellung und Reparatur
Softwarefehler und Abstürze
Clientanwendungsabstürze
Datenbankserverabstürze
Betriebssystemabstürze
Hardwareausfälle
Stromausfall
Festplattenausfall
Speicherfehler
Bedienfehler und versehentliches Löschen
Versehentliches Löschen (DELETE)
Datei gelöscht
Tabelle gelöscht
Index gelöscht
Datenbank gelöscht
Korrupte Dateien
Server startet nicht
Write-Ahead-Log defekt
Index defekt
Tabelle defekt
Vorsorge
7. Sicherheit, Rechteverwaltung, Authentifizierung
Allgemeines über Sicherheit
Benutzerverwaltung
Benutzer, Gruppen, Rollen
Benutzer anlegen
Rollenattribute
Login
Superuser
Attribut zum Erzeugen von Datenbanken
Attribut zum Erzeugen von Rollen
Replikation
Passwörter
Passwortgültigkeit
Verbindungslimits
Rollen ändern
Gruppenrollen anlegen und verwalten
Rollen anzeigen
Rollen löschen
Benutzer und Rollen in der Praxis
Sichere Datenübertragung
Sichere Datenübertragung mit SSL
Sichere Datenübertragung mit Tunneln
Zugangskontrolle
Die Datei pg_hba.conf
Typ
Datenbankname
Benutzername
Adresse
Methode
Authentifizierungsmethoden
Trust
Reject
Passwortauthentifizierung
Die Datei .pgpass
Ident-basierte Authentifizierung
Peer-Authentifizierung
Authentifizierung mit Kerberos, GSSAPI und SSPI
Authentifizierung mit PAM
Authentifizierung mit LDAP
Authentifizierung mit RADIUS
Clientauthentifizierung mit SSL
Authentifizierungsprobleme
Zugangskontrolle in der Praxis
Rechteverwaltung
Privilegien gewähren und entziehen
Eigentümerrechte
Privilegtypen
Privilegien für Tabellen und Sichten
Privilegien für Sequenzen
Privilegien für Funktionen
Privilegien für Schemas
Privilegien für Datenbanken
Privilegien für Fremddaten-Wrapper
Privilegien für Fremdserver
Privilegien für Sprachen
Privilegien für Large Objects
Privilegien für Tablespaces
Privilegien für Typen
Vorgabeprivilegien
Grant-Optionen
Privilegien anzeigen
Rechteverwaltung in der Praxis
8. Performance-Tuning
Ablauf der Befehlsverarbeitung
Empfang über Netzwerk
Parser
Rewriter
Planer/Optimizer
Executor
Ergebnis über Netzwerk
Flaschenhälse
CPU
RAM
Festplattendurchsatz
Festplattenlatenz
Festplattenrotation
Netzwerkverbindung
Indexe einsetzen
Einführung
Indextypen
Mehrspaltige Indexe und Indexkombination
Indexe über Ausdrücke
Unique Indexe
Partielle Indexe
Operatorklassen
Indizierung von Mustersuchen
Indexe und Fremdschlüssel
HOT Updates
Nebenläufiges Bauen von Indexen
Optimierung von CREATE INDEX
Ausführungspläne
Planknoten
Pläne ansehen und analysieren
EXPLAIN und EXPLAIN ANALYZE
Pläne mit Bitmap Index Scan
Pläne und LIMIT
Pläne mit Indexscan
Pläne mit Index-Only-Scan
Pläne mit Sortieren
Pläne auswählen und vergleichen
Join-Pläne
auto_explain
Statistiken und Kostenparameter
Statistiken für den Planer
ANALYZE
Kostenparameter
seq_page_cost
random_page_cost
cpu_tuple_cost
cpu_index_tuple_cost
cpu_operator_cost
effective_cache_size
Ungeloggte Tabellen
Partitionierung
Tabellen partitionieren
Constraint Exclusion
Partitionierte Tabellen beschreiben
Einschätzung
Befüllen der Datenbank
Transaktionen
COPY statt INSERT
Indexe , Fremdschlüssel , Reihenfolge
Serverkonfiguration
Checkpoints
Speicher
Archivierung
Fsync
Überblick
Nach dem Laden
9. Replikation und Hochverfügbarkeit
Begriffserklärung
Connection Pooling
Clustering
Shared Storage
Shared Nothing
Sharding
Replikation
Master/Slave-Replikation
Multimaster-Replikation
Standby-Systeme
Hot Standby
Planung
Konfiguration
Failover
Verwalten von WAL-Archiven
Einschränkungen
Zusammenfassung
Streaming Replication
Planung
Konfiguration
Asynchrone Replikation
Synchrone Replikation
Kaskadierende Replikation und Änderungen der Timeline
Überwachung
Zusammenfassung
WAL-Replikation mit pg_standby
Konfiguration
Failover mit pg_standby
Slony-I
Konzeption
Bevorzugte Anwendungsgebiete
Installation
Auswahl der Slony-I-Version
Debian
Quelltextinstallation
Die Kommandosprache slonik
slonik-Präambel
CLUSTER NAME
NODE ADMIN CONNINFO
Initialisieren eines Clusters
INIT CLUSTER
Verwalten und Konfigurieren von Clustern
CLONE PREPARE, CLONE FINISH
CREATE SET
DROP NODE
DROP PATH
EXECUTE SCRIPT
FAILOVER
LOCK SET, UNLOCK SET
MERGE SET
MOVE SET
RESTART NODE
SET ADD TABLE
SET ADD SEQUENCE
SET DROP TABLE
SET DROP SEQUENCE
STORE NODE
STORE PATH
SUBSCRIBE SET
UNINSTALL NODE
UNSUBSCRIBE SET
UPDATE FUNCTIONS
WAIT FOR EVENT
Der erste Slony-I-Cluster
Das Datenbankschema
Einrichten der Authentifizierung
Einrichten des Datenbankschemas
Definition des Clusters
Initialisieren des Clusters
Starten der Replikationsserver
Erstellen der Sets
Abonnieren der Sets
Überwachung und Wartung
Lagtime überwachen
slon-Prozesse überwachen
Speicherverbrauch überwachen
Optimierung
Subscriber mit hoher oder anwachsender Lagtime
Hohe I/O-Last auf Origin
slon-Prozesse und Instabilitäten
Zusammenfassung
pgpool-II
Installation
Konfiguration
pgpool und Slony
pgpool und Streaming Replication
PgBouncer
Installation
Pool-Modi
Konfiguration
Starten
Überwachung und Wartung
PgBouncer und Skalierung mit vielen Datenbankverbindungen
PL/Proxy
Installation
Konfiguration
plproxy.get_cluster_version
plproxy.get_cluster_config
plproxy.get_cluster_partitions
Beispiel
Zusammenfassung
DRBD
Installation
Konfiguration
Integration mit Pacemaker
Grundlegende Konfiguration
Einrichten der Cluster Ressourcen
Konfiguration von Ressourcen
Wartung
STONITH - Shoot The Other Node In The Head
Zusammenfassung
10. Hardware
Arbeitsspeicher
Prozessor
Festspeichersystem
Anforderungen an das Festspeichersystem
Größe des Festspeichersystems
Anbindung des Festspeichersystems
Geschwindigkeit und Redundanz
RAID 0
RAID 1
RAID 5
RAID 6
RAID 10
RAID 15
Redundanz in NAS- oder SAN-Systemen
Datensicherheit bei Festplattenlaufwerken und RAID-Controllern
Solid State Drives
Aufbau eines Serversystems für PostgreSQL
Tablespaces
Einrichtung von Tablespaces auf dedizierten Laufwerken
Verwendung von Tablespaces
Verschieben zwischen Tablespaces
Tablespace für temporäre Dateien
Einrichtung eines dedizierten WAL-Laufwerks
Hardwaretests
Leistungsmessung mit dd
Leistungsmessung mit bonnie++
Leistungsmessung mit pgbench
Index
Kolophon
Impressum
Das fortschrittlichste Open-Source-Datenbankmanagementsystem der Welt, so lautet weithin unangefochten seit über einem Jahrzehnt der Untertitel zu PostgreSQL. Mittlerweile ist es millionenfach im Einsatz, als Teil der kritischen öffentlichen Infrastruktur des Internets und der Gesellschaft und als 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 von Anwendern und Entwicklern vorangetrieben. Dieses Buch möchte seinen Teil dazu beitragen, dieses Software-Produkt allen interessierten Anwendern zugänglich zu machen.
Dieses Buch richtet sich primär an Administratoren von PostgreSQL-Datenbanksystemen. Es soll dabei helfen, PostgreSQL-Datenbanksysteme erfolgreich, stabil und performant zu betreiben. Es wird davon ausgegangen, dass der Leser entweder schon Umgang mit PostgreSQL hatte oder über Erfahrungen mit der Administration von anderen Datenbanksystemen verfügt. 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 sind Kenntnisse in Sachen Anwendungsentwicklung generell von Vorteil.
Dieses Buch soll die PostgreSQL-Dokumentation um praktische Erfahrungswerte ergänzen. Es kann aber dem PostgreSQL-Administrator im Alltag auch schon für sich genommen als eigenständige Referenz nützlich sein, wobei dieses Buch aber niemals den Anspruch haben kann, den gesamten Umfang des PostgreSQL-Systems abzudecken.
Dieses Buch besteht aus zehn Kapiteln. Die Kapitel sind so ausgelegt, dass sie der Reihenfolge entsprechen, in der man sich mit den entsprechenden Themen im Laufe des Lebens eines Datenbanksystems ungefähr befassen wird. Wer also schnell »von 0 auf 100« kommen möchte, kann dieses 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. Auf diese Weise kann das Buch außerdem 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
Das Leben jeder Software beginnt mit der Installation.
Kapitel 2
Hier werden die Einstellungen der Konfigurationsparameter im PostgreSQL-Server erläutert.
Kapitel 3
Hier werden wiederkehrende Aufgaben beschrieben, die zur Wartung eines PostgreSQL-Servers notwendig sind.
Kapitel 4
Teil der Wartungsaufgaben ist die Datensicherung, der ein eigenes Kapitel gewidmet ist.
Kapitel 5
Hier werden Verfahren vorgestellt, mit denen Zustand und Verhalten eines PostgreSQL-Servers überwacht und analysiert werden können.
Kapitel 6
Hier wird beschrieben, was man tun kann, wenn irgendetwas beschädigt worden zu sein scheint.
Kapitel 7
Die Absicherung der Daten vor unberechtigtem Zugriff ist Thema dieses Kapitels.
Kapitel 8
Hier wird erläutert, wie man SQL-Befehle schneller machen kann.
Kapitel 9
Hier werden verschiedene Lösungen vorgestellt, um PostgreSQL-Datenbanken zu replizieren und zu clustern, um bessere Verfügbarkeit oder bessere Leistung zu erzielen.
Kapitel 10
Enthält Hinweise zu Auswahl und Einrichtung von Hardware für PostgreSQL-Systeme. Von der Logik her würde die Hardware-Auswahl wohl noch vor der Installation stattfinden, aber es ist auch sinnvoll, sich diesen Fragen erst dann zu widmen, wenn man die Interna eines PostgreSQL-Systems gut verstanden hat.
Dieses Buch behandelt hauptsächlich PostgreSQL 9.2 und 9.1. Die aktuellste PostgreSQL-Version zum Zeitpunkt der Drucklegung ist 9.2.2, aber alle Releases der Reihe 9.2 unterscheiden sich – wenn überhaupt – nur geringfügig bezüglich der Benutzerschnittstellen und der Verhaltensweise.
Wo es bedeutende Unterschiede gibt, wird auch kurz auf Version 9.0 und ältere Versionen eingegangen. Aber gerade bei der Datenbankadministration hat sich sowohl hinsichtlich der Möglichkeiten als auch bezüglich der Anforderungen über die letzten Jahre hinweg 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öglichkeiten bei der Datenbankadministration ausnutzen möchte.
An den Stellen, an denen es um Betriebssystemeinstellungen und die Einbindung externer Programmpakete geht, haben wir natürlich eine Auswahl treffen müssen, die sich letztlich daran orientiert, womit wir selbst arbeiten und was wir weiterempfehlen wollen. Die allermeisten Teile dieses Buches gelten aber völlig unabhängig von der Wahl des Betriebssystems oder der Zusatzwerkzeuge.
In der dritten Auflage wurden alle Kapitel dieses Buches überarbeitet und an neuere Software-Versionen und Hardware-Entwicklungen angepasst sowie um zusätzliche Erfahrungswerte erweitert. Wichtige Neuerungen gibt es insbesondere in den Bereichen Replikation, Überwachung sowie Performance-Optimierung.
In diesem Buch werden die folgenden typografischen Konventionen verwendet:
Kursivschrift
Wird 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 verwendet, für die eigene Werte eingesetzt werden müssen.
Dieses Symbol kennzeichnet einen Hinweis, der eine nützliche Anmerkung zum nebenstehenden Text enthält.
Dieses Symbol kennzeichnet eine Warnung, die sich auf den nebenstehenden Text bezieht.
Treibende Kraft bei diesem Buch war wieder einmal unser Lektor Volker Bombien. Seine Geduld und Ausdauer waren unbezahlbar.
Wir danken dem Fachlektor Sven Riedel und allen Kollegen, Probelesern und Vorabkritikern 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.