PostgreSQL Database Administration and Performance Training Course

Course overview

PostgreSQL administrators are expected to keep business-critical databases available, recoverable and responsive while application workloads, data volumes and user demands increase. This course addresses the practical decisions behind that responsibility: configuring a secure cluster, controlling access, managing transactions and vacuum activity, planning backups, diagnosing slow queries, and preventing routine operational issues from becoming outages. It is designed for professionals who need to operate PostgreSQL with confidence rather than rely on default settings or reactive troubleshooting.

Across five days, participants work through PostgreSQL architecture, installation and configuration, database objects, roles, authentication, storage, WAL, MVCC, autovacuum, statistics, backup and recovery, replication, monitoring and performance tuning. They use psql and pgAdmin 4 to administer instances, pgBackRest to execute backup and restore scenarios, and PostgreSQL diagnostic views and EXPLAIN ANALYZE to identify query bottlenecks. Participants learn how to choose indexes, interpret execution plans, tune selected server parameters, manage database growth, and establish repeatable maintenance routines.

Instructor demonstrations are followed by guided administration labs on a realistic PostgreSQL environment. Participants investigate failed jobs, excessive table bloat, slow reporting queries, permission issues and recovery requirements using operational evidence rather than guesswork. By the end of the course, each participant produces a PostgreSQL administration and performance runbook containing a baseline configuration, access model, backup-and-recovery procedure, monitoring checks, maintenance schedule and prioritised tuning recommendations.

The course suits database administrators, infrastructure professionals, data engineers and technically capable application support staff who administer, support or take operational responsibility for PostgreSQL databases. Managers gain staff able to translate database metrics and incidents into defensible operational actions.

Course objectives

By the end of this course, participants will be able to:

• Configure a PostgreSQL instance using postgresql.conf, pg_hba.conf and role-based access controls
• Administer databases, schemas, roles, extensions and connection privileges with psql and pgAdmin 4
• Interpret MVCC, WAL, checkpoints and autovacuum behaviour when diagnosing transaction and storage issues
• Execute pgBackRest full, differential and incremental backups and validate a point-in-time recovery procedure
• Implement streaming replication and assess replication lag, failover considerations and replica health
• Analyse query plans with EXPLAIN ANALYZE and pg_stat_statements to isolate costly SQL operations
• Tune indexes, table statistics and selected PostgreSQL memory and planner parameters for defined workloads
• Produce a PostgreSQL operations runbook covering monitoring, maintenance, recovery and performance actions

Benefits of attending

Target competencies

Who should attend

• Database Administrators — who need to operate, secure, recover and tune PostgreSQL production environments
• Systems Administrators — who support Linux-hosted database services and need PostgreSQL-specific operational methods
• Data Engineers — who manage analytical or pipeline workloads that depend on reliable PostgreSQL performance
• Application Support Engineers — who investigate database-related incidents, query failures and service degradation
• Backend Developers — who need to write and diagnose SQL with an informed understanding of PostgreSQL execution behaviour
• Technical Team Leads — who set database operating practices and need to assess performance and recovery risks

Requirements and prerequisites

Participants should be comfortable using a command line, navigating files and directories, and working with basic SQL statements such as SELECT, INSERT, UPDATE, DELETE and CREATE TABLE. Familiarity with relational concepts including tables, primary keys, indexes, joins, users and permissions is assumed. Experience administering another database platform or Linux services is useful but not essential. This is a foundation-to-intermediate course, so prior PostgreSQL administration experience is not required. Participants do not need prior knowledge of replication, backup tooling, query-plan analysis, cloud platforms or advanced SQL optimisation.

Training methodology

The course combines short instructor-led explanations with daily PostgreSQL administration labs conducted in a controlled server environment. Participants use psql, pgAdmin 4 and pgBackRest to configure access, inspect system catalogues, run backups, restore data, review replication status and investigate slow workloads. Case exercises present realistic symptoms such as table bloat, long-running queries, failed authentication and recovery requests. Small-group review sessions compare operational decisions, and the final day converts lab findings into an individual PostgreSQL operations runbook and action plan.

Course outline

Day 1: PostgreSQL architecture, installation and secure access

• PostgreSQL process architecture, clusters, databases and data directories
• Installing and initialising a PostgreSQL cluster
• Using psql meta-commands and pgAdmin 4 administration features
• Database, schema, table and extension administration
• Role creation, privilege grants and default privilege management
• Client authentication rules in pg_hba.conf
• Core configuration settings in postgresql.conf and reload procedures

Workshop: Participants configure a new database environment with application, reporting and administrator roles, then produce a tested access-control matrix.

Day 2: Transactions, storage and routine maintenance

• MVCC row versioning and transaction visibility
• Transaction isolation levels and lock behaviour
• Write-ahead logging, checkpoints and crash recovery fundamentals
• Autovacuum thresholds, workers and cost settings
• VACUUM, VACUUM FULL, ANALYZE and table bloat decisions
• Planner statistics, histogram estimates and statistics targets
• Tablespaces, disk growth and database capacity monitoring

Workshop: Participants investigate a database with blocked sessions and table bloat, then create a maintenance plan with vacuum, analyse and capacity actions.

Day 3: Backup, recovery and high availability

• Recovery objectives, backup retention and restore validation
• Physical backups and WAL archiving concepts
• pgBackRest repository configuration and backup types
• Full, differential and incremental pgBackRest backup execution
• Point-in-time recovery using restore targets and archived WAL
• Streaming replication, replication slots and synchronous commit
• Replication lag monitoring, failover risks and replica verification

Workshop: Participants back up a PostgreSQL cluster with pgBackRest and restore a damaged database to a specified recovery point, documenting the recovery sequence.

Day 4: Query diagnosis and performance tuning

• Performance baselining with PostgreSQL activity and database statistics views
• Session, lock and wait-event analysis using pg_stat_activity
• Workload profiling with the pg_stat_statements extension
• EXPLAIN and EXPLAIN ANALYZE execution-plan interpretation
• Index selection with B-tree, GIN, GiST and partial indexes
• Join methods, sort operations and cardinality-estimation issues
• Memory, planner and connection settings for workload-specific tuning

Workshop: Participants diagnose a slow reporting workload, compare execution plans, implement index and statistics changes, and record measured performance improvements.

Day 5: Operational control and administration runbook

• Database health checks and operational service-level indicators
• Connection management, pooling considerations and session limits
• Logging configuration, log analysis and audit evidence
• Secure administration practices and privilege review
• Patch planning, extension management and controlled configuration change
• Incident triage for availability, performance and data-recovery events
• PostgreSQL runbook structure, escalation paths and maintenance calendars

Workshop: Participants complete and peer-review a PostgreSQL operations runbook containing configuration baselines, recovery steps, monitoring checks and a prioritised improvement backlog.

Tools & standards covered

PostgreSQL, psql, pgAdmin 4, pgBackRest

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions