PostgreSQL Administration & Performance Tuning Training in Kuala Lumpur, Malaysia

This course provides comprehensive knowledge of PostgreSQL database administration, including installation, configuration, optimization, and replication techniques. It is designed for database professionals seeking to manage, fine-tune, and scale PostgreSQL databases for high availability and performance. Participants will gain hands-on experience in real-world scenarios such as backup strategies, performance tuning, and setting up replication for fault tolerance.

Course Agenda:

PostgreSQL Administration Fundamentals

• Introduction to PostgreSQL architecture and components

• Installation on Linux/Windows systems

• Configuration files and server tuning basics

• Role-based authentication and user management

• Creating and managing databases and schemas

• Tablespaces and storage configuration

Optimization and Performance Tuning

• Understanding the PostgreSQL Query Planner (EXPLAIN, ANALYZE)

• Index types (B-tree, GIN, GiST, BRIN) and usage strategies

• Vacuum, Autovacuum, and transaction ID wraparound

• Configuration tuning: memory, connections, and caching

• Query optimization techniques and slow query diagnosis

• Monitoring tools: pg_stat_activity, pg_stat_statements, and others

Backup, Recovery, and Replication

• Backup strategies: logical vs physical

• Using pg_dump, pg_basebackup, and WAL archiving

• PITR (Point-In-Time Recovery)

• Streaming replication setup and management

• Logical replication and publication/subscription model

• Introduction to failover and high availability tools (e.g., repmgr, Patroni)

• Security, logging, and PostgreSQL best practices

Hands-on Exercises