PostgreSQL Server Administration (TTDB7020)

Course Objectives

Course Prerequisites

Audience

The ideal audience for this course includes database administrators, system administrators, IT managers, and DevOps professionals who are responsible for managing and maintaining PostgreSQL database systems. The course is also suitable for individuals transitioning into these roles or seeking to deepen their understanding of PostgreSQL administration, ensuring they are equipped with the necessary skills and knowledge to excel in their careers.

Pre-Requisites

To ensure a smooth learning experience and maximize the benefits of attending this course, you should have the following prerequisite skills:

• Basic understanding of relational database management systems (RDBMS) and their core concepts, such as tables, indexes, and transactions.
• Familiarity with SQL, including the ability to write and execute simple queries, such as SELECT, INSERT, UPDATE, and DELETE statements.
• Working knowledge of command-line interfaces and experience navigating through directories, executing commands, and editing configuration files.
• General knowledge of computer systems and networking concepts, including the fundamentals of operating systems, file systems, and TCP/IP networking.

Take Before: We recommend attendees have the skills in the course listed below, or attend this course as a pre-requisite:

• TTSQL003     Introduction to SQL Querying Basics

Related Courses

This is a subset of related courses we offer. Please see the website for the complete catalog.

• TTDB7020    PostgreSQL Server Administration
• TTDB7023    Advanced PostgreSQL Administration and Performance
• TTDB7024    PostgreSQL for Database Developers

• TTDB7026     Working with PostgreSQL: Hands-on Guide to Robust Database Solutions

Course Agenda

1. Server Architecture

• Understanding PostgreSQL layers
• Process and memory model
• Storage subsystem overview
• Transaction and locking

2. User Accounts

• Creating user accounts
• Managing roles and privileges
• Authentication methods
• Securing user access

3. Server Startup and Shutdown

• PostgreSQL startup process
• Graceful shutdown techniques
• Crash recovery mechanisms
• Identifying common issues

4. Server Configuration

• Essential configuration files
• Tuning performance parameters
• Memory and disk management
• Security best practices

5. Connections and Authorization

• Connection types and methods
• Managing connection limits
• Role-based access control
• Implementing SSL/TLS

6. Database Reporting

• Query performance analysis
• Identifying slow queries
• Using EXPLAIN and ANALYZE
• Index usage statistics

7. Logs

• Configuring log output
• Analyzing log data
• Log rotation and retention
• Troubleshooting common errors

8. Statistics

• PostgreSQL statistics collector
• Interpreting statistics views
• Monitoring performance metrics
• Detecting anomalies

9. Managing Databases

• Creating and dropping databases
• Managing tablespaces
• Schema management
• Handling large objects

10. Routine Database Maintenance

• Index and table maintenance
• Vacuuming and analyzing
• Managing dead rows
• Handling bloat

11. Backup and Restore

• Physical and logical backups
• Point-in-time recovery
• Backup strategies
• Disaster recovery planning

12. Monitoring Database Activity

• Active session monitoring
• Lock contention analysis
• Identifying long-running queries
• Resource usage tracking

13. Monitoring Disk Usage

• Disk space allocation
• Table and index sizing
• Identifying disk bottlenecks
• Capacity planning

14. Information Schema

• Navigating information schema
• Querying metadata
• Understanding system catalogs
• Exploring object dependencies

15. Wrap Up & Additional Resources

• Recap of key concepts
• PostgreSQL community resources
• Advanced topics exploration
• Continuous practice and learning opportunities

Course Materials

Student Materials & Lab Environment

All course software (limited versions, for course use only), digital courseware files or course notes, labs / data sets and solutions (as applicable) are provided for you in our “easy access / no install required” high-speed remote lab environment. Our tech team works with every student to ensure everyone is set up with solid access and ready to go prior to every course start date, ensuring smooth delivery and great hands-on experience. Please ask for details.