PostgreSQL Datenbank Administration

PostgreSQL ist ein freies und objektrelationales Datenbankmanagementsystem (ORDBMS). PostgreSQL ist für Linux, MacOS, Windows und BSD verfügbar und darf auch im kommerziellen Umfeld kostenfrei genutzt werden. Das ist auch einer der Gründe, warum sich PostgreSQL für viele Datenbank Anwender mehr und mehr zur Alternative zu Oracle-Datenbanken entwickelt. Neben grundlegenden administrativen Aufgaben beschäftigen wir uns mit Performance Tuning, Monitoring, Replication und anderen wichtigen Themen. 

Das Training wird auf Rocky Linux 10.x abgehalten. Die Unterlagen beinhalten aber auch alle Unterschiede und Kommandos um alle Übungen auf SUSE oder Debian durchzuführen zu können.

Grundlagen PostgreSQL

• Entstehungsgeschichte von PostgreSQL
• Release Zyklen
• Datenbank Limits

Installation von PostgreSQL

• PostgreSQL auf Linux installieren (Rocky, SUSE, Debian)
• PostgreSQL auf Windows installieren (Theorie)
• PostgreSQL Umgebungsvariablen (PGHOST, PGPORT, PGDATABASE, PGUSER, PGDATA,..)
• Verwaltungswerkzeuge im Überblick
• Erstellen von Datenbank Instanzen
• Anpassen von Kernel Parametern

Beispiel Datenbanken

• Importieren von Beispieldatenbanken via pg_restore, psql und pgAdmin

Die Architektur eines PostgreSQL Clusters

• Postmaster Prozess
• Hintergrundprozesse (checkpointer, writer, wal writer, archiver, stats collector, bgworker)
• Verbindungsprozess des Clients
• Memory Strukturen (Shared Memory, WAL Buffer)
• Multiversion Concurrency Control Modell und VACUUM
• Datenbank Objekte gemäß SQL-Standard
• Datenbank Objekte im PostgreSQL (Cluster, Schema, Datenbanken, Sichten, Funktionen, Tabellen, Tablespaces, ...)
• Parameter in der postgres.conf Konfigurationsdatei

pgAdmin

• pgAdmin Installation auf Rocky, SUSE und Debian
• Desktop vs. Server Mode
• SELinux Einstellungen anpassen für pgAdmin
• PostgreSQL Server und Datenbanken administrieren
• Kommandozeilenwerkzeug psql im Detail
• Verwalten des Clusters mit psql internen Kommandos 
• PostgreSQL Cluster Einstellungen im Detail
• Gebietsschema und Zeichensatz Problematik
• Write ahead log (WAL) und die WAL Archivierung
• Auslagern von DB-Objekten in einen eigenen Tablespace 
• Log Einstellungen und Audit
• PostgreSQL Extension Verwaltung
• SELinux Einstellungen anpassen
• Viele praktische Beispiele

VACUUM und ANALYZE

• Arbeitsweise von PostgreSQL beim löschen von Datensätzen
• Free Space Map: *_fsm
• Visibility Map: *_vm
• Standard VACUUM vs. VACUUM FULL
• VACUUM Einstellungen in der postgresql.conf
• Erstellen von Statistiken mit ANALYSE
• Autovacuum und ANALYZE
• Wartunsaufgaben mit VACUUM, ANALYZE und vacuumdb durchführen
• Maintenance in pgAdmin

Transaktionen und konkurrierende Zugriffe

• Autocommit, Commit, Rollback und Savepoint
• SELECT FOR UPDATE
• SELECT FOR UPDATE NOWAIT
• Beispiel 1: Savepoint und Rollback
• Beispiel 2: NOWAIT
• Beispiel 3: Deadlock

WRITE AHEAD LOG (WAL)

• Aufbau der WAL Segmente
• Einrichten der WAL Archivierung
• WAL-Einstellungen in der postgresql.conf (wal_level, fsync, synchronous_commit, wal_buffers, archive_mode, archive_timeout, archive_command,...)

PostgreSQL Sicherung und Wiederherstellung

• Online Sicherung mit Point-in-time-Recovery
• Offline Sicherung auf Dateisystemebene
• SQL Dump
• pg_dump, pg_restore, pg_basebackups, pgbackrest
• Diverse Übungen zum Thema Backup und Restore (komplett, Schema, Daten, ...)

PostgreSQL Sicherheit

• TCP und UNIX Socket Verbindung
• Host based Authentication (pg_hba.conf)
• Rollen und Privilegien
• Authentifizierung und Zugangskontrolle
• Privilegien auf Datenbank Objekten (Schema, Tabellen, ...)
• Rechteverwaltung
• Auditing und Monitoring
• Verschlüsselte Client-Server Verbindungen via SSL

PostgreSQL Upgrade

• Minor vs. Major Update
• Notwendige Überlegungen und Schritte beim PostgreSQL Update
• Upgrade Variante: pg_dumpall (allgemein)
• Upgrade Variante: pg_upgrade (allgemein)
• Upgrade Variante: postgresql-setup --upgrade (Rocky spezifisch)

PostgreSQL Replikation zwischen Clustern

• Streaming Replication
• Konfiguration und Aktivierung
• Kaskadenförmige Replikation
• Einstellungen in der postgresql.conf
• Einrichten einer Master -> Hot Standby Replikation
• Wechseln zwischen Asynchroner und Synchroner Replikation
• SQL-Statements um die Replikation überwachen
• Verstehen des Transaction Logs
• Failover und Switchover durchführen

Performance Tuning

• Linux OS-Tuning Einstellungen für PostgreSQL (SWAP, Huge Pages, Barrier, IPC, ulimit, OOM,...)
• Wichtige Tuning Parameter in der postgres.conf im Detail
• Optimieren von PostgreSQL Memory Parametern
• Viele praktische Beispiele und Erfahrungen aus der Praxis

alle Teilnehmer*innen mit Administrationserfahrungen im SQL Bereich

SQL - Grundkenntnisse