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.

starstarstarstarstar

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

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.

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

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.

Language

English

Prerequisites

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.

$2,295

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
29
Nov
Wednesday
10:00 AM ET -
6:00 PM ET
Filling Fast
Available
Loading...