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.
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.
Es gibt zwei Hauptkonfigurationsdateien: postgresql.conf und
pg_hba.conf. Die erstere ist die eigentliche Konfigurationsdatei,
in der zweiten konfiguriert man Zugriffsbeschränkungen.
Etliche Aktionen kann man wahlweise über externe Programme oder
über SQL-Kommandos durchführen, beispielsweise das Anlegen neuer
Datenbankbenutzer.
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.
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
SET einstellen.
Die Konfigurationsdatei heißt postgresql.conf. Hier können
viele Optionen auf bestimmte Werte gesetzt werden. In jeder Zeile
der Datei kann eine Option stehen, die das Format
option = wert
hat (genau genommen kann das = weggelassen werden). Zeilen, die
mit # beginnen, sind Kommentare.
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
_cost.
Mit den Optionen debug_level, log_connections und
log_timestamp kann die Protokollierung beeinflusst werden. Soll
diese durch syslog erfolgen, kann man dies mit den Optionen syslog,
syslog_facility und syslog_ident einstellen.
Die Optionen deadlock_timeout und
default_transaction_isolation beeinflussen das Transaktionslocking.
Die Option password_encryption gibt an, ob
Passwörter im Klartext oder verschlüsselt gespeichert werden
sollen. Mit 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.
postgresql.conf |
#Beispieldatei postgresql.conf fuer SelfLinux [c] <steffen@dett.de>
#
#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
|
Der Bedarf an Shared Memory wird durch die Kombination der Anzahl von
PostgreSQL Instanzen (max_connections) und der geteilten Speicherpuffer
(shared_buffers) bestimmt. Erhöht man diese Parameter, kann es sein, dass
sich PostgreSQL beschwert, es sei zu wenig Shared Memory
vorhanden.
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:
root@linux #
echo 134217728 >/proc/sys/kernel/shmall
root@linux #
echo 134217728 >/proc/sys/kernel/shmmax
|
Diese Kommandos muss man natürlich so ablegen, dass sie beim
Systemstart vor dem Start von PostgreSQL ausgeführt
werden.
Ist das Programm sysctl installiert, kann man alternativ folgendes in die
Datei /etc/sysctl.conf eintragen:
/etc/sysctl.conf |
kernel.shmall = 134217728
kernel.shmmax = 134217728
|
Durch Ausführen von
werden dann die Einträge der Datei /etc/sysctl.conf übernommen.
|
Über die Datei 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.
Eine sehr schöne Funktion ist auch das Usermapping. Es kann
eingestellt werden, dass bestimmte Benutzer von bestimmten
Maschinen aus nur auf ein bestimmtes Benutzerkonto zugreifen
können (zum Beispiel, www-run des Webservers bekommt den Benutzer
wwwro, dieser darf dann nur lesen). Diese Funktion steht leider
nur zur Verfügung, wenn ident verwendet wird, eine
Authentifizierung, von der man leider abraten sollte, da sie nur
Sinn macht, wenn man den Administratoren dieser Server vertraut.
Aus Performanzgründen wird diese Datei bei neueren PostgreSQL Versionen
nur noch einmalig beim Start und nicht mehr bei jedem
Verbindungsaufbau geladen.
Um PostgreSQL Änderungen an dieser Datei mitzuteilen, kann man als
Benutzer root auch folgenden Befehl eingeben, statt das DBMS komplett
neu zu starten:
root@linux #
su -l postgres -s /bin/sh -c "/usr/bin/pg_ctl reload -D $PGDATA -s"
|
Hat man das Programmpaket der Distribution installiert, kann man auch
einfach:
root@linux #
/etc/init.d/postgresql reload
|
eingeben.
Jede Zeile ist eine Regel. Eine Regel besteht aus mehreren
Teilen, die durch Leerzeichen getrennt sind. Der erste Teil gibt
dabei den Regeltyp an.
Der wichtigste Regeltyp host gilt für Netzwerkadressen. Er hat
das Format:
host Datenbankname IP-Adresse Netzmaske Authentifizierung
Daneben gibt es beispielsweise noch den Typ local, der für
Verbindungen über Unix-Domain-Sockets verwendet wird.
Auf dedizierten Datenbankservern, also Servern, die nur die
Datenbank fahren und vor allem keine lokalen Benutzerkonten
besitzen, verwendet man hier auch oft die Authentifizierung trust,
also Anmeldung ohne Passwort, da es hier nur root gibt, und der
darf eh alles. Plant man cron jobs, so ist hier trust
angebracht, da cron natürlich keine Passwörter eingibt. Dies ist
jedoch problematisch, wenn es Benutzerkonten auf dem System gibt.
Mögliche Werte für Authentifizierung:
trust
Keine Authentifizierung, der Benutzername wird akzeptiert (evtl.
Passwort gilt als korrekt).
password
Klartext-Passwort Authentifizierung. Optional kann eine
Passwortdatei angegeben werden.
crypt
Verhält sich wie password, über das Netzwerk werden jedoch
die Passwörter verschlüsselt übertragen
md5
Neuere Versionen bieten MD5 Passwörter an. Diese Option benutzt
einen anderen und besseren Algorithmus zur Verschlüsselung als crypt.
ident
Der Ident-Daemon wird gefragt. Es ist möglich, über eine Datei
pg_ident.conf ein Benutzernamen-Mapping durchzuführen.
reject
Die Verbindung wird in jedem Fall abgelehnt.
Eine Beispielkonfiguration:
pg_hba.conf |
# 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
|
|
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.
Verwendet man ein Distributionspaket, so kann die Datenbank
vermutlich sofort gestartet werden oder läuft sogar bereits.
Das DBMS-Backend von PostgreSQL heißt postmaster. Dieses nimmt
Verbindungsanfragen an, startet für jede Verbindung einen eigenen
postgres Prozess, der die eigentliche Arbeit erledigt, und koordiniert
die Kommunikation zwischen den einzelnen postgres Instanzen.
Hat man selbst kompiliert, so startet man beispielsweise mit
root@linux #
su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres
|
oder als Postgres-Systembenutzer mit:
root@linux #
postgres$ pg_ctl start -D /usr/local/pgsql/data -l serverlog
|
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
root@linux #
rcpostgres start # SuSE
|
oder
root@linux #
service postgresql start # RedHat
|
oder
root@linux #
/etc/init.d/postgres* start # generisch
|
das DBMS.
Details finden sich sicherlich im Handbuch.
Sollte dies der erste Start nach dem Update sein, ist dies
vermutlich ein guter Zeitpunkt, um das Backup wieder
einzuspielen:
root@linux #
psql -d template1 -f backup.sql
|
Das Herunterfahren der Datenbank erledigt man analog zum
Starten:
root@linux #
su -c 'pg_ctl stop'
|
oder einem Distributionskommando wie zum Beispiel
root@linux #
rcpostgres stop
|
Man kann auch Signale verwenden. Das Signal SIGKILL sollte hier
unter allem Umständen vermieden werden, da in diesem Fall die
Datenbank nicht geschlossen wird - Datenverluste sind fast
unvermeidlich.
Das Signal SIGTERM veranlasst PostgreSQL, so lange zu warten, bis
alle Clients ihre Verbindungen beendet haben und ist somit die
schonendste Methode. Das Signal SIGINT beendet alle
Clientverbindungen, und fährt die Datenbank sofort sauber
herunter. Letzlich kann man noch 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.
Ein Beispielaufruf:
root@linux #
killall -INT postmaster
|
|
An dieser Stelle wird nur der Vollständigkeit halber psql
genannt. An späterer Stelle wird genauer darauf eingegangen.
psql ist das 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.
psql erfordert als Parameter den Namen der Datenbank, zu der
verbunden werden soll. Es gibt meistens mindestens die Datenbank
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 postgres zu
der Datenbank template1 auf localhost verbinden, kann man
schreiben:
root@linux #
psql -h localhost -U postgres template1
Password: Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit
template1=#
|
Unten sieht man das Prompt (das den Datenbanknamen beinhaltet).
Hier kann man SQL Kommandos eingeben, beispielsweise:
template1=# SELECT version(); version --------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row)
|
Hier läuft also die Version 7.0.2 (welches mal ein Update vertragen
könnte). psql kennt zusätzlich zu den SQL Kommandos sogenannte
interne Kommandos. Diese beginnen mit einem \ (backslash).
Diese lassen sich mit \? auflisten.
Mit \h kann man auf umfangreiche Hilfe zurückgreifen, eine SQL
Referenz. Mit \q beendet man das Programm.
|
Zunächst sollte man natürlich seine Datenbank planen. Vielleicht
erstellt man ein paar Entity-Relationship-Diagramme (ER Modelle).
Diese kann man dann in eine Normalform übertragen (die Tabellen
normieren), bis man in etwa die 3. Normalform erreicht hat.
Dann überlegt man sich Wertebereiche, Gültigkeiten und Abhängigkeiten.
Aus den Standard-Use-Cases kann man oft recht einfach die
erforderlichen Berechtigungen und zu erzeugenden Views ableiten.
Hat man das erledigt, kann man beginnen, die Datenbank zu
erzeugen und die Tabellen anzulegen. Oft schreibt und testet man
Konsistenzprüfungsfunktionen wie 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).
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
Kompatiblitätsmodus-Views und ähnliche Workarounds benötigt, da
die Anwendungen selten alle auf einen Schlag angepasst werden
können.
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 pg_dump das
Schema in ein Skript schreiben, und dieses kommentieren:
root@linux #
pg_dump --schema-only database -f schema.sql
|
Das ist bei kleinen Projekten oder in frühen Stadien oft eine
nützliche Hilfe.
|
Es gibt zwei Möglichkeiten, neue Datenbanken zu erzeugen. Man
kann das Programm createdb verwenden. Dieses verwendet das SQL
Kommando CREATE DATEBASE, um eine neue Datenbank zu erzeugen.
createdb versteht etliche Optionen, die sehr ähnlich zu denen von
psql sind. Man kann auch psql verwenden, und dann mit dem SQL
Kommando CREATE DATEBASE eine Datenbank erzeugen. Als einzigen
geforderten Parameter gibt man den Namen der zu erzeugenden
Datenbank an. Beispiel:
template1=# CREATE DATABASE test; CREATE DATABASE
|
Datenbanken kann man mit DROP DATABASE löschen. Achtung, diese
Kommandos sind eigentlich kein SQL Kommandos (kein Abfragekommando,
also nicht query language), sondern ein sogenannte
Strukturkommandos. Diese lassen sich nicht in Transaktionen
ausführen und damit insbesondere nicht rückgängig machen!
Versucht man ein DROP DATABASE test; in einer Transaktion, so
wird das Kommando ignoriert.
|
Analog zu Datenbanken kann man Benutzer über das Programm createuser
oder über psql anlegen. Das SQL (Struktur-) Kommando heißt CREATE
USER. Hier gibt es eine Vielzahl von Parametern; beispielsweise,
ob der Benutzer Datenbanken anlegen darf (CREATEDB) oder nicht
(NOCREATEDB), ob der Benutzer weitere Benutzer anlegen darf
(CREATEUSER) oder nicht (NOCREATEUSER), welches Passwort er
bekommt (PASSWORD geheim), in welchen Gruppen er ist (IN GROUP
gruppe1, gruppe2, ...) und wie lange er gültig ist (VALID UNTIL
Zeitstempel).
Dieses Kommando legt einen Benutzer steffen mit einem sehr
schlechten Passwort an:
template1=# CREATE USER steffen WITH PASSWORD '123' NOCREATEDB NOCREATEUSER; CREATE USER
|
Auch hier gibt es ein DROP USER.
Änderungen werden über das Kommando ALTER USER
durchgeführt:
template1=# ALTER USER steffen PASSWORD 'geheim'; ALTER USER
|
Gruppen werden mit CREATE GROUP erzeugt. Man kann die Benutzer
aufzählen, die Mitglied werden sollen (USER benutzer1, benutzer2,
...). Es gibt auch DROP GROUP, um Gruppen zu löschen.
Alle hier genannten Kommandos sind Strukturkommandos und
unterliegen nicht (ganz) den Transaktionsregeln. Ein Rollback auf
ein DROP USER funktioniert nicht (vollständig).
Zum Hinzufügen bzw. Entfernen von Benutzern zu Gruppen stehen die
Kommandos
template1=# ALTER GROUP gruppe1 ADD USER steffen, elvira template1=# ALTER GROUP gruppe1 DROP USER elvira
|
zur Verfügung.
Für den Datenbank Superuser oder Administrator gelten
Sonderregeln, die im Abschnitt Privilegien kurz erklärt werden.
|
Privilegien sind Zugriffsrechte. PostgreSQL unterstützt hier
verschiedene Arten:
SELECT |
das Leserecht |
INSERT |
darf neue Datensätze einfügen |
UPDATE |
darf Datensätze ändern und Sequenzen verwenden |
DELETE |
darf Datensätze löschen |
RULE |
darf Regeln für Tabellen erzeugen (eine
PostgreSQL Erweiterung)
|
REFERENCES |
darf einen Schlüssel dieser Tabelle als Fremdschlüssel
verwenden
|
TRIGGER |
darf Trigger an der Tabelle erzeugen
|
CREATE |
darf Objekte in Datenbank (ab 7.3 auch Schemata)
anlegen
|
TEMPORARY |
darf temporäre Tabelle in Datenbank anlegen |
EXECUTE |
darf Funktion ausführen |
USAGE |
darf Sprache (z.B. PL/pgSQL) oder Objekte in Schema (ab 7.3)
benutzen
|
ALL |
darf alles |
In SQL werden Privilegien über GRANT erlaubt und mit REVOKE
entzogen. Das Grant-Kommando ist SQL92 konform. Um sicherzugehen,
dass nicht bereits andere Rechte gesetzt sind, führt man vor einem
GRANT manchmal auch ein REVOKE aus, um alle Rechte erstmal zu
löschen.
Der Benutzer wwwro darf statistics nur lesen:
template1=# REVOKE ALL ON statistics FROM wwwro; template1=# GRANT SELECT ON statistics TO wwwro;
|
Die Gruppe stats darf alles auf dieser Tabelle:
template1=# GRANT ALL ON statistics TO GROUP stats;
|
Der Eigentümer hat automatisch immer alle Bereichtigungen. Den
Eigentümer kann man über beispielsweise mit:
template1=# ALTER TABLE statistics OWNER TO steffen;
|
einstellen.
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 su vergleichen. Das geschieht wie folgt:
template1=# SET SESSION AUTHORIZATION 'steffen';
|
Einige Aktionen können nur vom Superuser durchgeführt werden,
beispielsweise das Installieren neuer Sprachen mit
Systemberechtigungen.
|
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
VACUUM. Dieses gibt nicht mehr benutzten
Speicher frei. Es gibt auch ein Programm vacuumdb, das man als
cronjob einrichten kann.
Eine Option von VACUUM ist 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 INSERTs oder UPDATEs durchführen. Eine reine Analyse
belastet den Rechner auch weniger und kann daher auch stündlich
durchgeführt werden.
Hierzu kann z.B. folgendes Skript dienen, welches vom Benutzer postgres
ausgeführt werden muss:
analyzedbs |
#!/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
|
root@linux #
su -l postgres -c analyzedbs
|
Hat man keine besonderen Anforderungen, führt man die ANALYZE zusammen
mit VACUUM aus. Ein Beispielaufruf:
root@linux #
vacuumdb --all --analyze --full --username=postgres
|
Seit Version 7.2 werden Tabellen während VACUUM nicht mehr komplett
gesperrt, man kann dieses aber durch die Angabe von --full erzwingen
und so eine bessere Kompression des Datenbestandes erreichen.
Verwendet man einen cron-Job, so sollte in pg_hba.conf für Typ
local die Authentifizierung trust verwenden, da cron keine
Passwörter eingibt.
Man kann beispielsweise in die Datei /etc/cron.d/postgresql eintragen:
/etc/cron.d/postgresql |
0 0 * * * postgres vacuumdb --all --full --analyze
5 * * * * postgres /usr/local/bin/analyzedbs
|
Hier muss man beachten, dass cron nicht die /etc/profile auswertet,
und damit vacuumdb nicht unbedingt im Pfad liegt. Hier sollte man
lieber absolute Pfade angeben.
|
Ä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 lib Verzeichnis, also
beispielsweise /usr/local/lib.
Hier gibt es ein Programm createlang. Dieses verwendet das SQL
Kommando CREATE LANGUAGE, um die Sprache zu installieren, führt
jedoch zusätzlich etliche Prüfungen durch, und wird daher
empfohlen.
Um Beispielsweise die Sprache PL/pgSQL auf einem SuSE 7.0 System
zu installieren, genügt folgendes Kommando:
root@linux #
createlang --username=postgres --pglib=/usr/lib/pgsql/ plpgsql
|
Der Pfad /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.
Es gibt zwei Möglichkeiten, Sprachen zu installieren: trusted und
untrusted. Da die wörtlichen Übersetzungen nicht weiterhelfen,
folgt eine Erklärung. Eine untrusted Sprache darf mehr, als eine
trusted Sprache. Von einer trusted Sprache wird erwartet, dass
über diese keine normalerweise verbotenen Aktionen durchgeführt
werden können. PL/pgSQL ist ein Beispiel.
PL/Perl kann auch im 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 untrusted Sprachen nur vom
Datenbank Superuser installiert werden. Die Funktionen in solchen
Spachen müssen selbst für Sicherheit sorgen.
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 noch nicht andere PL/Perl
Funktionen aufrufen).
|
Es gibt mehrere Arten, Backups anzufertigen. Es gibt die Programme
pg_dump und 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 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).
Es wird als Parameter der Datenbankname erwartet. Die Ausgabe
(den 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.
Wichtige Optionen sind:
--file=datei, -f datei
|
Ausgabe in Datei |
--inserts |
INSERT im Dump verwenden
|
--attribute-inserts |
INSERT mit Attributen verwenden
|
--host servername
|
Zu diesem Server verbinden |
--quotes |
Viele Bezeichner quotieren |
--schema-only |
Nur die Struktur, nicht die Daten |
--table tabelle
|
Nur diese Tabelle tabelle |
--no-acl |
Berechtigungen auslassen |
Möchte man die Datenbank wwwdb sichern, so schreibt man:
root@linux #
pg_dump wwwdb -f backup.sql
|
Leider werden so keine 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.
|
Dieses Programm ruft 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:
root@linux #
pg_dumpall -f backup.sql
|
Erfreulicherweise ist auf Grund des später erklärten MVCC die
Datenbank während des Backups vollständig verwendbar
(möglicherweise gibt es einige spezielle Einschränkungen, ein
DROP DATABASE zum Löschen einer Datenbank wird wohl nicht
funktionieren).
|
Den Aufruf von 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 cron als
Unix-Benutzer postgres starten kann:
backup.sh |
#!/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
|
|
Eine Datenbank aus einem mit
pg_dumpall erstellten File
wiederherzustellen, ist sehr einfach. Man muss dafür sorgen, dass
die Datenbank template1 vorhanden ist und das DBMS läuft. Dann
übergibt man die Backupdatei einfach dem psql Interpreter als
SQL-Programm:
root@linux #
psql -d template1 -f backup.sql
|
In seltenen Fällen kann man die Backupdatei auch mit einem Editor
öffnen, und nur Teile daraus in 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).
Es gibt noch ein weiteres Programm, 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:
root@linux #
$ pg_restore -d template1 backup.sql
|
|
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.
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.
Vor dem Wiederherstellen muss die Datenbank natürlich ebenfalls
heruntergefahren werden.
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 Dump gezogen
werden.
|
Leider ist das Backup mit pg_dump nicht perfekt. pg_dump wertet
nicht aus, ob Tabellen Funktionen benutzen. Es geht davon aus,
das Funktionen Tabellen verwenden (man beachte die Reihenfolge!).
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 pg_restore
diese Funktion zuerst wiederherstellen) und dann das Backup
einspielen. Das gibt zwar eine Warnung, da die Funktion schon
existiert, funktioniert aber.
Hat man zirkuläre Abhängigkeiten, so wird es etwas komplizierter,
hier hilft nur Handarbeit. Solche Situationen sind meistens
jedoch Fehler und unerwünscht.
pg_dump sichert auch keine 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.
|
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.
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.
Angenommen, man startet die neue Datenbank auf Port 5433. Dann
kann man mit folgender Kette den gesamten Datenbestand kopieren:
root@linux #
pg_dumpall -p 5432 | psql -p 5433
|
|
|
Ein Transaktionsprotokoll darf keinesfalls mit Protokolldateien mit
Textmeldungen verwechselt werden. In einem Transaktionsprotokoll stehen
Änderungen von Daten. Wird eine Transaktion committed, also
erfolgreich beendet, so werden diese in ein Protokoll eingetragen und
erst bei Gelegenheit in die 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).
Write ahead logging (WAL) ist ein - wenn nicht das -
Standardverfahren für Transaktionsprotokolle und wird von PostgreSQL
verwendet.
|
Stellt PostgreSQL (genauer gesagt, das 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.
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 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.
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.
|
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.
Portiert man ein System von anderen Datenbanken auf PostgreSQL,
so ist je nach Art und Komplexität des Systems etliches an Arbeit
zu erwarten.
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
CREATE FUNCTION Teil von SQL99, allerdings sind die Sprachen,
in denen die Funktionen geschrieben sind, nicht standardisiert.
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
Trigger, Regeln und Stored Procedures neu implementiert werden
müssen.
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 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.
Das Migrations-Projektteam sollte über Testsysteme mit beiden
Datenbanken verfügen und Spezialisten für alle beteiligten
Systeme besitzen.
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.
In den sogenannten 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 psql gelesen werden können.
In der Praxis sind allerdings einige Änderungen zu erwarten. So
kann es beispielsweise sein, das man Probleme mit der Quotierung
bekommt (mySQL verwendet beispielsweise backticks, um
Systembezeichner zu quoten, was bei anderen Datenbanken zu
Syntaxfehlern führt). Des weiteren ist PostgreSQL bei
Zeichenketten case-sensitiv, dass heißt, die Groß/Kleinschreibung
wird grundsätzlich unterschieden. Bei Tabellen und Spaltennamen
ist PostgreSQL nicht 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
|| wird in PostgreSQL so verwendet, wie in ANSI (mySQL kennt
hier einen ANSI-Modus, der jedoch vermutlich selten verwendet
wird). Man muss daher die || in OR und die && in AND
ändern; "||" ist der Konkatenierungsoperator (wie in mySQL's
ANSI-Modus).
Der Umfang von SQL ist bei mySQL kleiner, dafür gibt es etliche,
nicht standardkonforme Erweiterungen. mySQL verwendet # als
Kommentarzeichen. ANSI schreibt -- vor.
Steigt man auf PostgreSQL um, sollte man daran denken, die nun
zur Verfügung stehenden Funktionen auch sinnvoll zu nutzen,
beispielsweise Trigger und Views. Die Arbeit mit Transaktionen
kann verbessert werden, da jetzt die ISO Transaktionslevel read
commited und serializable zur Verfügung stehen.
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.
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 || Operators als
Vorteil preisen, oder die Möglichkeit von stored procedures in
mySQL nennen (die man dann in C schreiben muss, und als root zur
Datenbank dazu linken muss). Eine andere Seite suggerierte es fast
als Vorteil, keine Fremdschlüsselbedingungen zu prüfen.
|
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.
Es gibt in den Tech Docs von PostgreSQL Informationen hierzu.
Hier findet man Hilfen für die Migration von MS-SQL Server,
Oracle und anderen zu PostgreSQL.
Oracle-Erfahrene werden sich freuen, mit PL/pgSQL eine zu PL/SQL
ähnliche Sprache zu finden.
|
|
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.
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.
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.
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).
Rechenleistung ist bei vielen Anwendungen weniger ein Thema. Das
Unix-Programm 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.
|
|