Next:
Contents
Contents
PostgreSQL: Schulungsunterlagen
www.postgresql.at, Hans-Juergen Schoenig
Contents
Vorwort
Motivation
Schulungen
Über PostgreSQL
Die Entwicklung von PostgreSQL
Einarbeitung von Patches
Release Cycles
Aktuelle Einsatzgebiete
PostgreSQL Architektur
Die Systemarchitektur
Der Postmaster
Backends
Background Writer
Statistics Collector
Autovacuum
WAL-Writer
Der Weg einer Abfrage
Das Frontend / Backend Protokoll
Der Parser
Das Rewrite System
Der Planner
Der Executor
Das Speichermanagement
Storage Management
Tabellen
Paging
System Tables
Installation
Die Installation auf UNIX Systemen
Installation auf Linux und BSD
RPM Packages installieren
Debian basierte Systeme
Source Code kompilieren
AIX specifics
Solaris Specifics
Wo finde ich Informationen
Sun Bugs
Regression Tests
Installation auf Windows Systemen
Nach der Installation
Datenbanken anlegen
Die 'postgres' Datenbank
Datenbanken löschen
Das Frontend
SQL
Datenstrukturen definieren
Einfache Tabellen erzeugen
Den Platzbedarf optimieren
Primärschlüssel vergeben
Grundlegende Operationen
Daten einfügen
Daten verändern
Daten löschen
DELETE
Datensätze maskieren und Security
TRUNCATE
Daten selektieren
Einfache SELECT-Statements
IN und NOT IN
BETWEEN
Die Ausgabe limitieren
Doppelte Werte filtern
NULL-Werte
Sortieren
Aggregierungen
Einfache Verknüpfungen
Outer Joins
Self Joins
Subselects
Bedingte Ausgabe
Mengenoperationen
Outputformatierung
Daten in Tabellen importieren
Datentypen und Operatoren
Datentypen
Auto Increment
Schemas
Schemas anlegen
Schemas löschen
Schemas wiederfinden
Das 'public'-Schema
Daten importieren und exportieren
Indices verwenden
Typen von Indices
B+ Bäume (btree)
R-Baum Semantik via Gist
Hash Indices (hash)
Gist-Indices
GIN-Indices
Einfache Indices definieren
Unique Indices definieren
Kombinierte Indices
Partielle Indices
Indices und Gleichzeitigkeit
Indices entfernen
BITMAP
Views
Cursor
Einfache Cursor
WITH HOLD Cursor
Binary Cursors
ORA-01000: maximum open cursors exceeded
Sequenzen
Datenstrukturen verändern
Syntax
Tabellen umbenennen
Spalten einfügen und löschen
Datentypen modifizieren
Defaultwerte und Constraints
Storage Parameter
Analytics und Windowing Functions
Einfache Windowing Functions
Eingebaute Funktionen
Redundanten SQL Code vermeiden
Beispiele
Prozentuelle Veränderungen berechnen
Windows mit unterschiedlicher Sichtbarkeit
Windows auf Windows aufbauen
WITH und WITH RECURSIVE
WITH
WITH RECURSIVE
Am Limit ...
Erweiterte Funktionalitaet
Rules und Trigger
Trigger
Rules
Rules anlegen und wieder löschen
Rules und Views
Rules vs. Trigger
Domains und Co. definieren
Vererbung und Arrays
Vererbung
Arrays
Eindimensionale Arrays
Operationen mit Arrays durchführen
ANY und ALL
Teilmengen auswählen
Mehrdimensionale Arrays
Foreign Keys und Constraints
Einfache Constraints
CHECK Constraints
NULL-Werte
Wozu Foreign Keys
Foreign Keys definieren
ON UPDATE / ON DELETE
Durchführung von Integritätschecks und Constraints modifizieren
Foreign Keys und Performance
LISTEN / NOTIFY
Transaction Management
Transaktionen in PostgreSQL
Einfache Transaktionen
Transaction Isolation Levels
Read-Committed
Serializable
MVCC und Co.
Locking Konzepte
Table Locking
Page Locking und Row Level Locking
Mulitversion Concurrency Control
Grundlegende Regeln
Einfache Beispiele mit einer schreibenden Transaktion
SELECT FOR UPDATE
SELECT FOR SHARE
Deadlocks
Explizite Locks
LOCK TABLE NOWAIT
Savepoints
Crash Recovery und Checkpointing
WAL und Checkpoints
Recovery
Temporary Tabellen und Transaktionen
Sequenzen und Transaktionen
ROLLBACK verwenden
Sichtbarkeitsregeln für Sequences
Administration
Tablespaces und Co.
Benutzerverwaltung
Benutzer und Gruppen anlegen
Benutzer und Gruppen modifizieren
Benutzer und Gruppen löschen
Die interne Speicherung
GRANT und REVOKE
Rechte auf Tabellen
Rechte auf Schemas
Rechte auf Funktionen
Rechte auf Sprachen
WITH GRANT OPTION
Netzwerk und Netzwerksecurity
pg_hba.conf - Host Based Access Control
Ident Server benutzen
SSL
Ein Zertifikat erzeugen
SSH Tunnels als Alternative
Parameter adjustieren
Filesystem Optionen
- data_directory:
- hba_file
- ident_file
- external_pid_file
Authentifizierung
- listen_addresses:
- port:
- max_connections:
- superuser_reserved_connections:
- unix_socket_directory:
- unix_socket_group:
- unix_socket_permissions:
- rendezvous_name:
- authentication_timeout:
- ssl:
- password_encryption:
- krb_server_keyfile:
- db_user_namespace:
Ressourcenverwaltung
- shared_buffers:
- work_mem
- maintenance_work_mem
- max_stack_depth
- max_fsm_pages:
- max_fsm_relations
- max_files_per_process
- preload_libraries:
- vacuum_cost_delay:
- vacuum_cost_page_hit
- vacuum_cost_page_miss
- vacuum_cost_page_dirty:
- vacuum_cost_limit
- bgwriter_delay:
- bgwriter_percent:
- bgwriter_maxpages
Write Ahead Logs
- fsync
- wal_sync_method
- wal_buffers:
- commit_delay:
- commit_siblings
- checkpoint_segments
- checkpoint_timeout
- checkpoint_warning
- archive_command:
Planner Einstellungen
- enable_hashagg
- enable_hashjoin
- enable_bitmapscan
- enable_indexscan
- enable_mergejoin
- enable_nestloop
- enable_seqscan
- enable_sort
- enable_tidscan
- effective_cache_size
- random_page_cost
- cpu_tuple_cost
- cpu_index_tuple_cost
- cpu_operator_cost
- geqo
- geqo_threshold
- geqo_effort
- geqo_pool_size
- geqo_generations
- geqo_selection_bias
- default_statistics_target
- from_collapse_limit
- join_collapse_limit
Logging
- log_destination
- redirect_stderr
- log_directory
- log_filename
- log_truncate_on_rotation
- log_rotation_age
- log_rotation_size
- syslog_facility
- syslog_ident
- client_min_messages
- log_min_messages
- log_error_verbosity
- log_min_error_statement
- log_min_duration_statement
- silent_mode
- debug_print_parse
- debug_print_rewritten
- debug_print_plan
- debug_pretty_print
- log_connections
- log_disconnections
- log_duration
- log_line_prefix
- log_statement
- log_hostname
- log_parser_stats
- log_planner_stats
- log_executor_stats
- log_statement_stats
Laufzeitparameter
- stats_start_collector
- stats_command_string
- stats_block_level
- stats_row_level
- stats_reset_on_server_start
Verbindungsparameter
- search_path
- default_tablespace
- check_function_bodies
- default_transaction_isolation
- default_transaction_read_only
- statement_timeout
- datestyle
- timezone
- australian_timezones
- extra_float_digits
- client_encoding
- lc_messages
- lc_monetary
- lc_numeric
- lc_time
- explain_pretty_print
- dynamic_library_path
Lock Management
- deadlock_timeout
- max_locks_per_transaction
Kompatibilität
- add_missing_from
- regex_flavor
- sql_inheritance
- default_with_oids
- transform_null_equals
Logverwaltung
Backup und Recovery
Daten sichern
Full-Backups ziehen
COPY vs. INSERT
Das Schema dumpen
Wie pg_dump funktioniert
Daten wiederherstellen
Fussangeln und Fallstricke
Compiled Functions
32-bit Probleme
Binär sichern
Character Sets
Point-in-Time-Recovery
Das Konzept
Point-In-Time-Recovery starten
Die Datenbank zurückrollen
recovery.conf
Datenbank Maintenance
VACUUM
Verschiedene Levels von VACUUM
VACUUM ausbremsen
VACUUM VERBOSE
System Monitoring
Aktuelle Datenbankverbindungen
I/O Analyse
Heap Access
Index Performance
Cache Hit Rates
Locks
Kernel Parameter adjustieren
Linux
FreeBSD
Solaris
MacOS X
Windows
Detailinfos
PostgreSQL auf größeren Systemen
Storagebedarf und Kapazitäsplanung
Overhead
32 vs. 64 bit
Sehr große Indices
Daten partitionieren
Fazit
Serverseitiges Programmieren
Der Function Manager
Funktionen anlegen und löschen
Einfache Funktionen erzeugen
Werte maskieren
Funktionen ersetzen
Funktionen modifizieren
Funktionen und deren interne Speicherung
Stabile und instabile Funktionen
VOLATILE
STABLE
IMMUTABLE
Vorsicht bei instabilen Funktionen
Zusammengesetzte Datentypen
Set Returning Functions
Aggregierungsfunktionen
PL/pgSQL
Programmiersprachen installieren
PL/pgSQL Programme
Kontrollstrukturen und Schleifen
IF / ELSE
WHILE
FOR
Exception Handling
Programme debuggen
PL/pgSQL Funktionen und Transaktionen
RECORDs
Mit Cursorn arbeiten
TYPE
ROWTYPE
Set-Returning Functions in PL/pgSQL
Trigger in PL/pgSQL
Trigger anlegen und löschen
Einfache Beispiele
Ausführungsreihenfolgen
Vordefinierte Variablen
Row-Level vs. Statement-Level Triggers und deren Limits
Trigger modifizieren
PL/SQL vs. PL/pgSQL
PL/Perl
PL/Perl installieren
Einfache Funktionen
Logging und Debugging
Zusammengesetzte Datentypen
Trusted vs. Untrusted
Globale Werte
Das SPI von PL/Perl aus nutzen
Set-Returning Functions
Triggers in PL/Perl
PL/Tcl
PL/Tcl einbinden
Einfache Funktionen
Datenbankzugriffe
Trigger in Tcl
PL/Python
Einfache Funktionen
Datenbankabfragen absetzen
Serverseitiges Java
SPI und Co
Einfache SPI Programme
Das PostgreSQL build system verwenden
Trigger
PostgreSQL erweitern
Datentypen implementieren
INPUT
OUTPUT
RECEIVE
SEND
ANALYZE
INTERNALLENGTH
PASSEDBYVALUE
ALIGNMENT
STORAGE
DEFAULT
ELEMENT und DELIMITER
Ein vollständiges Beispiel
Aggregates implementieren
Einfache Aggregates
Performance Tuning und Query Optimization
Grundlegendes
Klassisches Parameter Tuning
Tuning durch intelligentes Fragen
Die Big-O Notation
Daten importieren
Den Tuple Header klein halten
Indizierung
Imports und Checkpointing
Execution Plans
Einfache Plans lesen
Kosten als Entscheidungskriterium
VACUUM ANALYZE und Plans
Plans im Detail
Plan Nodes
Sequential Scans
Index Scans
Sorts
Limit
Merge Joins
Hashes
Nested Loops
Group Aggregates
Subquery Scans
Mengenoperationen
Unique
Materialize
Komplexere Plans
Die Funktionsweise des Planners
Arten der Optimierung
Rule Based Optimization
Cost Based Optimization
Genetische Optimierung
Der 'Standard' Planner
Subselects und Views
Funktionen auflösen
Konstanten berechnen
Implizite Gleichheiten
Bedingungen binden
Die eigentliche Planerzeugung
IN-Statements
Aggregierungen
Explizite Joins und Subselects beeinflussen
Genetische Optimierung
Statistische Information
Planner Hints
Basiskosten
Kostenadjustierung
Kosten händisch nachrechnen
Caching und Performance
Der LRU-Algorithmus
Der 2Q-Algorithmus
shared_buffers und effective_cache_size
Execution Plans cachen
PREPARE / EXECUTE
Probleme mit gecachten Plans
'Persistent Storage' Optimierungen
Sync Methoden
Index Organized Tables
Bitmap Indexes
Tablespaces und Performance
Storage Bereiche trennen
SQL effizienter formulieren
Datensätze zählen
MIN / MAX
Joinreihenfolgen, explizite Joins und join_collapse_limit
Der Stats-Collector
Den Stats-Collector aktivieren
pg_stat_activity
Das I/O Verhalten tracen
Clientprogrammierung
C
Datenbankverbindungen aufbauen
Einfache Befehle ausführen
Daten aus einem Cursor holen
Metadaten abfragen
Ein weiteres Beispiel
COPY verwenden
Auf BLOBs zugreifen
Das asynchrone Interface
pgeasy
C++
Datenbankverbindungen
SQL absetzen
Daten abfragen
Metadaten abfragen
COPY verwenden
ECPG
Datenbankverbindungen aufbauen und Code kompilieren
SQL ausführen und Ergebnisse abfragen
Error Handling
Cursor verwenden
Descriptoren verwenden
Der ECPG Parser
Perl
Das Pg-Modul
Die Installation
Datenbankverbindungen erzeugen
Ergebnisse abfragen
Metadaten verarbeiten
Large Objects
Verbindungen tracen
Das DBD-Modul
Die Installation
Datenbankverbindungen aufbauen
SQL absetzen
Transaction Management
Bind Variablen
Cursor
Error Handling
Connection Parameter
BLOBs
COPY verwenden
PHP
Datenbankverbindungen aufbauen
Einfache Statements ausführen
Daten auslesen
Metadaten auslesen
Cursor verwenden
COPY verwenden
Persistente Datenbankverbindungen
Tcl
Verbindungen aufbauen
SQL absetzen
Daten abfragen
Metadaten
Python
Verbindungen aufbauen
Daten einfügen
Daten abfragen
Metadaten
Schlusswort
Java
Installation
Datenbankverbindungen aufbauen
SQL absetzen
Metadaten
Daten abfragen
Transaction Isolation Level und Transactions
Prepared Statements
Fehlende Funktionen
Mono und .Net
ADO.NET und Npgsql
Datenbankverbindungen aufbauen
Einfaches SQL ausführen
Daten abfragen
Metadaten abfragen
Transaktionen
Prepared Statements
Logging
Data Sets
Andere Programmiersprachen
Geometrische Daten
Geometrische Operationen
Geometrische Datentypen
Point
Circle
Box
Line und Lseg
Indizierung
Path und Polygon
PostGIS
Distributed Databases, Replication und Failover
Lösungsansätze
Commit Protokolle
2-Phase Commit
3-Phase Commit
Asynchrone vs. Synchrone Replikation
Asynchrone Replikation
Synchrone Replikation
Problemstellung bei synchroner Replikation
Log Based vs. Query Based
Die Kemme Papers
Slony I
Grundlegendes
Slony installieren
Slony konfigurieren
Slony starten
Filesysteme spiegeln
About this document ...
Cybertec Schönig & Schönig GmbH
PostgreSQL support, training, consulting
www.postgresql-support.de
