CYBERTEC PostgreSQL Logo

PostgreSQL Administration and Performance Tuning

LEVEL: Intermediate
This course offers you a comprehensive introduction to the use of PostgreSQL. You will learn the basic and advanced concepts of PostgreSQL, from installation and architecture to high-availability setups and performance tuning. The training is aimed at database administrators, developers and IT specialists who want to deepen their knowledge and operate PostgreSQL securely in productive environments.
Book Now

Course Topics

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

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

Course INFO

Level Intermediate

Knowledge needed.

Language

English

Location

Online

1. 

Date

March 17, 2025 -
March 21, 2025
Duration: 5 Days

Time

Start 9:00 am
End 2:00 pm
Time Zone: CEST (UTC +2)

2. 

Date

September 9, 2025 -
September 12, 2025
Duration: 5 Days

Time

Start 9:00 am
End 2:00 pm
Time Zone: CET (UTC +1)
Make a Booking Request
CYBERTEC Logo white
Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram