PostgreSQL Administration and Performance Tuning

Day 1

• The PostgreSQL project, support, community, release- and version management
• Overview architecture
• Installation, start and stop the server, connect to the client
• Client “psql”
• Indexing with B-Tree indexes
• EXPLAIN
• Transactions: Atomicity, Isolation, Locking

Day 2

• Transactions: optimistic and pessimistic “locks”
• Multiversioning
• VACUUM
• Autovacuum
• Logical backup, data export and import (pg_dump)
• physical backup, Transaction log archiving
• Point-in-time Recovery

Day 3

• Streaming Replication
• Synchrone Replication
• Hot Standby and conflict management
• Failover
• High Availability, concept of Patroni
• User management, authentification
• Remote access to the database
• Permissions

Day 4

• Configuration parameters
• Logging
• Resource management
• Parameter tuning
• Security
• Upgrade
• Statistics and monitoring
• Statement tuning
• EXPLAIN (ANALYZE, BUFFERS)
• Extended Statistics