- Understanding the PostgreSQL architecture and community
- Mastering basic administration, transaction management and backup strategies
- Database Replication and its applications
- Efficient troubleshooting and tuning for optimal performance
Target audience
- Database administrators
- developers
- IT specialists
Prerequisites and additional requirements
The participant should:
- know basic SQL
- have basic Linux skills (basic commands, ability to use a text editor)
What this course will give you
Skills & knowledge
- PostgreSQL setup and operation: Installation, configuration and daily administration of PostgreSQL databases.
- Backup and restore for secure operation: Implementation of logical and physical backups as well as point-in-time recovery for data restoration.
- Efficient transaction and lock management: Management of transactions and isolation (optimistic and pessimistic locking) to ensure data integrity and optimal performance.
- Performance tuning and monitoring of the database: Use of EXPLAIN, parameter tuning and monitoring tools to optimise query performance and resource usage
- Understanding of the PostgreSQL architecture and processes: Knowledge of the basic background processes of PostgreSQL and how they interact to manage connections, storage and data.
- Maintenance and optimisation: Application of VACUUM and Autovacuum to optimise database performance and disk space management.
- Security and access management: Manage user rights, authentication methods and remote access to ensure database security.
Program content
Module 1: Introduction to PostgreSQL and basic administration
This module provides participants with a comprehensive overview of the PostgreSQL project and familiarises them with the basic functions and administration tasks.
- The PostgreSQL project, community and version management
- PostgreSQL architecture overview
- Installation, starting and stopping the server
- Connection to the ‘psql’ client
- Introduction to indexing (B-tree indexes)
- First steps with transactions: Atomicity, isolation and locking
Module 2: Transaction management and database maintenance
This module deepens the understanding of transactions and shows how databases are optimised and maintained.
- Deepening transaction management: Optimistic and pessimistic locking mechanisms
- Multiversion Concurrency Control (MVCC)
- VACUUM and Autovacuum: Database maintenance and fragmentation
- Backup strategies: Logical backups, data export and import with pg_dump
- Physical backups and transaction log archiving
- Point-in-Time Recovery (PITR)
Module 3: High availability and replication
Here participants learn how to make PostgreSQL highly available and fail-safe.
- Streaming replication and synchronous replication
- Hot standby and conflict management
- Failover mechanisms for reliability
- Concept and use of Patroni for high availability solutions
- User administration and authentication
- Remote access and access rights (permissions)
This module deals with the fine-tuning and security of PostgreSQL for productive use.
- Configuration parameters and logging for optimised performance
- Resource and parameter tuning
- Security concepts and user rights
- Upgrade strategies
- Database monitoring: statistics and monitoring
- Query optimisation with EXPLAIN (ANALYZE, BUFFERS) and extended statistics
Course materials and environment
- Participants will get access to a dedicated lab environment with dedicated virtual machines running some Linux distribution.
- Upon completion of the course, participants will receive a PDF of the slide deck that was used during the training sessions
- Participants can download and use all scripts and configuration files present on their dedicated virtual machines during training
- Upon completion of the course, participants will receive a complimentry copy of the most recent edition of Hans-Jürgens Schönig’s book Mastering PostgreSQL
