PostgreSQL Administration and Performance Tuning

• 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

• Basic knowledge of SQL
• Basic Linux skills (e.g., using commands and a text editor)

What you will learn

• 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:
  Handling transactions and isolation (optimistic and pessimistic locking) to ensure data integrity and optimal
  performance.
• Performance tuning and monitoring:
  Using of EXPLAIN, parameter tuning and monitoring tools to optimise query performance and resource usage
• Understanding PostgreSQL architecture and processes:
  Gaining knowledge of the core background processes of PostgreSQL and how their interaction 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:
  Managing user rights, authentication methods and remote access to ensure database security.

These skills enable participants to run PostgreSQL efficiently, automate maintenance procedures, and optimize database performance and security.

Course content

Module 1: Introduction to PostgreSQL and basic administration

• 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

• 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

• 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)

Module 4: Performance tuning and security

• 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

• We will provide access to a dedicated lab environment and virtual machines running a Linux distribution.
• Upon completion of the course, participants will receive a PDF of the slide deck that was used during the training sessions.
• All scripts and configuration files present on the virtual machines during training will be available for download and use.