PostgreSQL Administration and Performance Tuning

• Understanding the PostgreSQL architecture and community
• Mastering basic administration, transaction management and backup strategies
• Setting up highly available and scalable database solutions
• Efficient troubleshooting and tuning for optimal performance

Target audience

• Database administrators
• developers
• IT specialists

Course's learning outcomes

Skills

Participant shall know and understand:

• Experienced in setting up and operating PostgreSQL:

  • Installation, configuration and daily administration of PostgreSQL databases.

• Application of backup and restore concepts 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 locks to ensure data integrity and optimal performance.

• Setting up high availability and replication:

  • Implementation of streaming replication and failover strategies for fail-safe PostgreSQL environments.

• Performance tuning and monitoring of the database:

  • Use of EXPLAIN, parameter tuning and monitoring tools to optimise query performance and resource usage

Knowledge

Participant shall be able to:

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

• Ability to perform efficient database administration:

  • Installation, configuration and operation of PostgreSQL instances, including starting, stopping and managing servers.

• Transaction management and locking:

  • Secure handling of transactions, optimism and pessimism locking concepts (locks) and isolation of data changes.

• Backup and recovery strategies:

  • Planning and execution of logical and physical backups, including point-in-time recovery, to secure data assets.

• Maintenance and optimisation:

  • Application of VACUUM and Autovacuum to optimise database performance and avoid storage fragmentation.

• Replication and high availability:

  • Setting up replication and failover solutions for high availability, including streaming replication and use of Patroni.

• Performance tuning and monitoring:

  • Analysis and optimisation of queries through EXPLAIN and fine-tuning of configuration for optimal resource utilisation.

• Security and access management:

  • Manage user rights, authentication methods and remote access to ensure database security.

These capabilities enable participants to run PostgreSQL efficiently, automate maintenance procedures and optimise database performance and 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)

Module 4: Performance tuning and security

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 dedicated lab environment, with dedicated virtual machines
• Participants will receive upon completion of the course a slide decs in PDF form used during traiing sessions
• Participants can download and use all scripts and configuration files present on their dedicated virtual machines during training
• Participants will receive upon completion of the course complimentry copu of most recent edition of Hans-Jürgens Schönig’s book Mastering PostgreSQL