Database Admin · Online training

PostgreSQL DBA Training

Installing and configuring PostgreSQL, roles and security, backup and point-in-time recovery, vacuum and maintenance, query tuning and replication.

Level: Beginner to IntermediateDelivery: Online8 modules

Who should attend

Aspiring database administrators, support engineers, developers who manage PostgreSQL databases and system administrators who look after PostgreSQL servers.

Prerequisites

Basic SQL (SELECT, INSERT, UPDATE, DELETE) and basic comfort with the Linux command line. The course starts PostgreSQL administration from first principles.

Learning outcomes

  • Install, configure and secure a PostgreSQL server for a practice environment.
  • Create and manage databases, roles and privileges, and configure client authentication.
  • Plan and carry out logical and physical backups, and perform point-in-time recovery using write-ahead log archives.
  • Explain MVCC, vacuum and autovacuum, and keep tables and indexes healthy.
  • Find slow queries, read execution plans and improve them with indexes and configuration changes.
  • Set up streaming replication and describe logical replication, failover and connection pooling options.

Detailed curriculum

8 modules
01Introduction to PostgreSQL and its architecture
  • The role of a database administrator and typical DBA responsibilities
  • PostgreSQL features, versions and release cycle
  • Process architecture: postmaster, backend and background processes
  • Shared buffers, WAL, checkpoints and the storage layout on disk
  • Clusters, databases, schemas, tablespaces and the system catalogs
02Installation, configuration and the lab environment
  • Preparing a Linux lab machine or container
  • Installing PostgreSQL from packages and initialising a cluster
  • Starting, stopping and reloading the server
  • postgresql.conf, ALTER SYSTEM and the key memory and connection settings
  • Working with psql, pgAdmin and remote connections
03Databases, schemas, data types and objects
  • Creating databases, schemas and tablespaces
  • Data types, constraints and choosing them for storage and accuracy
  • Tables, indexes, sequences and views from an administrator's view
  • Extensions and how to manage them
  • Altering objects safely and managing schema changes
04Roles, privileges and security
  • Roles, login roles and group roles
  • Granting and revoking privileges, and default privileges
  • Client authentication with pg_hba.conf
  • Password policies, encrypted connections and TLS basics
  • Row-level security overview and a hardening checklist
05Backup, recovery and point-in-time recovery
  • Logical backups with pg_dump, pg_dumpall and pg_restore
  • Physical backups with pg_basebackup
  • Write-ahead log archiving and continuous archiving
  • Point-in-time recovery step by step
  • Backup scheduling, verification and a written recovery plan
06MVCC, vacuum and routine maintenance
  • Multiversion concurrency control and transaction IDs
  • Dead tuples, table bloat and index bloat
  • VACUUM, VACUUM FULL, ANALYZE and autovacuum tuning
  • REINDEX, statistics and disk space management
  • Routine maintenance checklist and scheduling
07Query performance tuning and monitoring
  • Reading EXPLAIN and EXPLAIN ANALYZE output
  • Index types: B-tree, GIN, GiST and BRIN, and choosing between them
  • The pg_stat views and the pg_stat_statements extension
  • Logging slow queries, locks and blocking sessions
  • Common performance anti-patterns and configuration tuning
08Replication, high availability and operations
  • Streaming replication: primary and standby
  • Monitoring replication and replication lag
  • Logical replication overview and typical uses
  • Switchover, failover and tools that help manage them
  • Connection pooling, upgrades with pg_upgrade and DBA runbooks

How you will practise

Learn through instructor-led sessions using realistic, live applications. Then apply each concept through guided assignments on a separate hands-on practice application, with feedback to help you build practical skills and confidence.

Practical exercises and labs

  • Install PostgreSQL on a Linux lab machine and adjust key configuration settings.
  • Create roles and privileges for an application and configure pg_hba.conf, then test access.
  • Take logical and physical backups, delete data on purpose and recover it, including a point-in-time recovery from archived WAL files.
  • Observe table bloat, run vacuum and tune autovacuum settings for a busy table.
  • Build a primary and standby streaming replication pair and practise a controlled switchover.

Capstone project

Prepare and document a small production-style PostgreSQL environment for a sample application: installation notes, configuration choices, role and access design, a tested backup and recovery plan, replication, a monitoring checklist and a tuning report with before-and-after evidence.

Tools and technologies

PostgreSQL (version to be confirmed for each batch), psql, pgAdmin, pg_dump, pg_restore, pg_basebackup, the pg_stat views and the pg_stat_statements extension, on a Linux virtual machine or container. Third-party tools such as connection poolers are shown as examples where relevant.

Assessment approach

  • Module-wise knowledge checks or short practical tasks.
  • Hands-on exercise review using an agreed checklist or rubric.
  • Final project demonstration and explanation of design decisions, test evidence or outputs, as applicable.
  • Completion criteria, attendance requirements and any certificate conditions must be separately approved and published by TrainingRite.

Schedule and fees

Upcoming batch schedules are shared by our TrainingRite team. Please submit an enquiry and our team will contact you by email or phone with the latest schedule. TrainingRite does not publish course fees or discounts on the website. Our counselling team will share the applicable course fee and other details with you by email or phone after you submit an enquiry.

Frequently asked questions

Who should join this course?

Aspiring database administrators, support engineers, developers who manage PostgreSQL databases and system administrators who look after PostgreSQL servers.

Do I need prior experience?

Basic SQL (SELECT, INSERT, UPDATE, DELETE) and basic comfort with the Linux command line. The course starts PostgreSQL administration from first principles.

Is the training online?

TrainingRite programmes are delivered online.

Will I get hands-on practice?

Sessions are instructor-led and use realistic applications. You then apply each concept through guided assignments on a separate practice application.

How can I enquire about fees and the next batch?

Fees and batch schedules are not published on the website. Submit the enquiry form on this page and our counselling team will share them with you by email or phone.

Related courses