Grundlagen der PostgreSQL Administration - Helwig-HMV

Grundlagen der PostgreSQL Administration
Jens Wilke
Vortrag bei der BELUG
16.03.2011
Grundlagen der PostgreSQL Administration
Der Vortrag behandelt die Installation und
Konfiguration von PostgreSQL, dem fortschrittlichsten Open Source Datenbanksystem.
Es wird auf die wichtigsten Konfigurationsparameter
eingeangen, Wartungs- und Datensicherungsstrategien erläutert und auf die wesentlichen Unterschiede zu MySQL eingegangen.
Bei Interesse können auch Hochverfügbarkeitsstrategien, insbesondere die neuen Features Streaming
Replication und Hot Standby erörtert werden.
Grundlagen der PostgreSQL Administration
●
Basics
–
ACID Compliant
●
–
–
–
–
–
–
Atomarität, Konsistenz, Isolation, Dauerhaftigkeit
SQL Standardkonform ANSI-SQL:2008
Stabilität und Codequalität haben höchste Priorität
Write Ahead Log (pg_xlog) zur Wiederherstellung und
zur Replikation sowie für Hot Backup
Query Planner/Optimizer sind ausgereift
Multi-Version Concurrency Control (MVCC)
BSD-artige Lizenz
Grundlagen der PostgreSQL Administration
●
Installation
–
Sourcen
●
–
Regression tests
Pakete
●
Debian PostgreSQL Infrastruktur
–
●
●
pg_(create|ctl|ls|drop)cluster
RH vor 9.0 nicht multi Server fähig
Konfiguration
–
Configs im data_directory oder unter /etc/postgres/...
●
●
postgresql.conf
pg_hba.conf
Grundlagen der PostgreSQL Administration
●
Konfiguration
–
Parameter
●
●
●
●
●
shared_buffers, default viel zu niedrig
checkpoint_(segments|completion_target|timeout)
logging (log_disconnections, log_checkpoints,log_duration)
autovacuum
Traps/Fallen
–
–
–
fsync=off (full_page_writes, synchronous_commit)
ssl=off
trust, ident, password in pg_hba.conf
Grundlagen der PostgreSQL Administration
●
Aufbau und Struktur der Datenbanken
–
Cluster: Instanz, „einzelner“ Serverprozess
●
●
–
Ein Cluster enthält mehrere Datenbanken
●
●
–
wird mit initdb erzeugt (bzw. pg_createcluster)
Rollen und Tablespaces sind global im Cluster
Eine DB wird mit CREATE DATABASE erzeugt
default: postgres, template0, template1
Eine Datenbank enthält mehrere Schemata
●
●
default: public, pg_catalog, information_schema, pg_toast
Ein Schema wird mit CREATE SCHEMA erzeugt
Grundlagen der PostgreSQL Administration
●
Tools
–
Psql
●
Kommandozeilenclient
–
●
Backslash-Befehle
–
–
–
●
Hilfe: \?
psql -E
\x
SQL-Befehle
–
–
~/.psqlrc
Hilfe: \h
PgAdmin
●
GUI
Grundlagen der PostgreSQL Administration
●
MVCC
–
Snapshots
●
Transaction Isolation Level
–
–
–
–
Read committed, default sieht eigene Änderungen
Serializable, arbeitet mit einem Snapshot von Beginn der Transaktion
Delete, Update ,xmin, xmax
Autovacuum
●
●
●
●
●
●
autovacuum_vacuum_threshold
autovacuum_vacuum_scale_factor
autovacuum_analyze_threshold
autovacuum_analyze_scale_factor
autovacuum_vacuum_cost_limit
autovacuum_max_workers
Grundlagen der PostgreSQL Administration
●
Tuning
–
Query Tuning
Ausführungspläne ermitteln mit
●
●
–
Fehlende Indexe erzeugen
●
–
explain
explain analyze
partielle Indexe
Partitionierung
●
TRUNCATE statt DELETE
spart beim delete und vermeidet Vacuum
Grundlagen der PostgreSQL Administration
●
Upgrade
–
–
minor version (nur die letzte Stelle ändert sich)
upgrade (8.4.3 nach 8.4.7) – nur restart erforderlich
major version upgrade (8.4.7 nach 9.0.3) – pg_upgrade
oder dump/restore erforderlich
Grundlagen der PostgreSQL Administration
●
Backup
–
Dump
●
pg_dump
–
–
●
●
pg_dumpall für globals (Rollen und Tablespaces)
pg_restore
–
–
–
ASCII
Custom (Binary)
● Partielle Wiederherstellung möglich, mehrere CPUs nutzbar
Für custom dumps
Zum Einspielen oder um ASCII- Dump erzeugen
WAL-Archivierung
●
●
●
Basis Sicherung + WAL Archiv
schneller, Point in Time Recovery (PITR) möglich
config: archive_mode, archive_command
Grundlagen der PostgreSQL Administration
●
Replikation
–
Intern
●
WAL based
–
–
●
Hot Standby
–
–
WAL shipping
Streaming Replicaton ab 9.0, asynchron, ab 9.1 auch synchron
Ab 9.0 lesende Zugriffe auf den Slave
Tools
●
Slony, trigger based
+ selektiv
+ slave ausserhalb der replizierten Objekte schreibbar
+ Replikation zwischen verschiedenen major Versions möglich
- Schreiblast auf dem Master verdoppelt
- aufwendige Konfiguration
Grundlagen der PostgreSQL Administration
●
Monitoring
–
–
–
–
–
●
pg_stats_user_(tables|...)
pg_statio_...
pgstatspack
Nagios check_postgres
pgfouine, php-Script zur Logfileauswertung
Objektgößen
–
–
–
pg_database_size
pg_relation_size
pg_total_relation_size
select pg_size_pretty(pg_database_size('postgres'));
Grundlagen der PostgreSQL Administration
●
Community
–
Mailinglisten, IRC, News
http://www.postgresql.org/community/
–
Dokumentation
http://www.postgresql.org/docs/
–
Planet
http://planet.postgresql.org/
–
Entwicklung
http://commitfest.postgresql.org/