PostgreSQL Server Administration (TTDB7020)

Explore the PostgreSQL Database Server Architecture, Administration, Configuration, Management, Monitoring, Reporting and More

TTDB7020

Introductory

3 Days

Course Overview

Overview

PostgreSQL is a powerful, open-source, object-relational database system known for being reliable, secure, and flexible. For administrators, learning PostgreSQL equips you with the skills needed to handle complex and high-performance databases in our data-driven world. Big-name companies like Apple, Cisco, Fujitsu, and IBM trust PostgreSQL for their critical applications, which highlights its significance and broad industry adoption. By getting the hang of PostgreSQL, administrators can boost their career opportunities and play a key role in the success of data-focused businesses.

PostgreSQL Server Administration is a three-day hands-on course geared for administrators seeking to sharpen their skills and elevate their database management capabilities. Throughout the course you’ll explore a wide range of essential topics, from server architecture and user accounts to monitoring and routine maintenance. Throughout the course, you'll gain invaluable insights and practical techniques that will empower you to optimize, secure, and manage your PostgreSQL environment effectively.

As you navigate through server configuration, database reporting, backup and restore procedures and more, you'll have the opportunity to apply your newfound knowledge to real-world scenarios. By the end of the course, you'll be equipped with the confidence and skillset required to tackle a wide array of administrative challenges and to effectively manage your PostgreSQL databases.

 

Learning Objectives

This course combines engaging instructor-led presentations and useful demonstrations with valuable hands-on labs and engaging group activities.  

Working in a hands-on learning environment led by our expert facilitator you will:

  • Gain expertise in managing and maintaining PostgreSQL server architecture, including optimizing performance parameters, memory and disk management, and implementing security best practices.
  • Build proficiency in creating, configuring, and securing user accounts by managing roles, privileges, and authentication methods tailored to an administrator's perspective.
  • Develop the ability to efficiently monitor database activity, identify performance bottlenecks, and analyze resource usage to ensure optimal database performance.
  • Acquire skills in routine database maintenance, such as index and table management, vacuuming and analyzing, and handling bloat, to keep the PostgreSQL environment healthy and efficient.
  • Master backup and restore procedures, including implementing physical and logical backups, point-in-time recovery, and developing disaster recovery plans to ensure data integrity and availability.

Course Objectives

Learning Objectives

This course combines engaging instructor-led presentations and useful demonstrations with valuable hands-on labs and engaging group activities.  

Working in a hands-on learning environment led by our expert facilitator you will:

  • Gain expertise in managing and maintaining PostgreSQL server architecture, including optimizing performance parameters, memory and disk management, and implementing security best practices.
  • Build proficiency in creating, configuring, and securing user accounts by managing roles, privileges, and authentication methods tailored to an administrator's perspective.
  • Develop the ability to efficiently monitor database activity, identify performance bottlenecks, and analyze resource usage to ensure optimal database performance.
  • Acquire skills in routine database maintenance, such as index and table management, vacuuming and analyzing, and handling bloat, to keep the PostgreSQL environment healthy and efficient.
  • Master backup and restore procedures, including implementing physical and logical backups, point-in-time recovery, and developing disaster recovery plans to ensure data integrity and availability.

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

Course Topics / Agenda

  1. Server Architecture
  • Understanding PostgreSQL layers
  • Process and memory model
  • Storage subsystem overview
  • Transaction and locking
  1. User Accounts
  • Creating user accounts
  • Managing roles and privileges
  • Authentication methods
  • Securing user access
  1. Server Startup and Shutdown
  • PostgreSQL startup process
  • Graceful shutdown techniques
  • Crash recovery mechanisms
  • Identifying common issues
  1. Server Configuration
  • Essential configuration files
  • Tuning performance parameters
  • Memory and disk management
  • Security best practices
  1. Connections and Authorization
  • Connection types and methods
  • Managing connection limits
  • Role-based access control
  • Implementing SSL/TLS
  1. Database Reporting
  • Query performance analysis
  • Identifying slow queries
  • Using EXPLAIN and ANALYZE
  • Index usage statistics
  1. Logs
  • Configuring log output
  • Analyzing log data
  • Log rotation and retention
  • Troubleshooting common errors
  1. Statistics
  • PostgreSQL statistics collector
  • Interpreting statistics views
  • Monitoring performance metrics
  • Detecting anomalies
  1. Managing Databases
  • Creating and dropping databases
  • Managing tablespaces
  • Schema management
  • Handling large objects
  1. Routine Database Maintenance
  • Index and table maintenance
  • Vacuuming and analyzing
  • Managing dead rows
  • Handling bloat
  1. Backup and Restore
  • Physical and logical backups
  • Point-in-time recovery
  • Backup strategies
  • Disaster recovery planning
  1. Monitoring Database Activity
  • Active session monitoring
  • Lock contention analysis
  • Identifying long-running queries
  • Resource usage tracking
  1. Monitoring Disk Usage
  • Disk space allocation
  • Table and index sizing
  • Identifying disk bottlenecks
  • Capacity planning
  1. Information Schema
  • Navigating information schema
  • Querying metadata
  • Understanding system catalogs
  • Exploring object dependencies
  1. 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.

Raise the bar for advancing technology skills

Attend a Class!

Live scheduled classes are listed below or browse our full course catalog anytime

Special Offers

We regulary offer discounts for individuals, groups and corporate teams. Contact us

Custom Team Training

Check out custom training solutions planned around your unique needs and skills.

EveryCourse Extras

Exclusive materials, ongoing support and a free live course refresh with every class.

Attend a Course

Please see the current upcoming available open enrollment course dates posted below. Please feel free to Register Online below, or call 844-475-4559 toll free to connect with our Registrar for assistance. If you need additional date options, please contact us for scheduling.

Course Title Days Date Time Price
PostgreSQL Server Administration (TTDB7020) 3 Days Mar 25 to Mar 27 10:00 AM to 06:00 PM EST $2,295.00 Enroll
PostgreSQL Server Administration (TTDB7020) 3 Days May 20 to May 22 10:00 AM to 06:00 PM EST $2,295.00 Enroll
PostgreSQL Server Administration (TTDB7020) 3 Days Jul 15 to Jul 17 10:00 AM to 06:00 PM EST $2,295.00 Enroll
PostgreSQL Server Administration (TTDB7020) 3 Days Sep 16 to Sep 18 10:00 AM to 06:00 PM EST $2,295.00 Enroll
PostgreSQL Server Administration (TTDB7020) 3 Days Nov 4 to Nov 6 10:00 AM to 06:00 PM EST $2,295.00 Enroll

Mix, Match & Master!
2FOR1: Two Courses, One Price!

Enroll in *any* two public courses (for 2023 *OR* 2024 dates!) by December 31, for one price!  Learn something new, or share the promo!

Click for Details & Additional Offers

Learn. Explore. Advance!

Extend your training investment! Recorded sessions, free re-sits and after course support included with Every Course
Trivera MiniCamps
Gain the skills you need with less time in the classroom with our short course, live-online hands-on events
Trivera QuickSkills: Free Courses and Webinars
Training on us! Keep your skills current with free live events, courses & webinars
Trivera AfterCourse: Coaching and Support
Expert level after-training support to help organizations put new training skills into practice on the job

The voices of our customers speak volumes

Special Offers
Limited Offer for most courses.

SAVE 50%

Learn More