PostgreSQL for Administrators - 3 Days

• Basic knowledge in database administration
• Familiarity with SQL language

Audience

• Database administrators
• System administrators

PostgreSQL (also known as Postgres) is an open source relational database management system or RDBMS. It is built to handle large data workloads while maintaining stability, reliability, and optimal performance.

This instructor-led, live training (online or onsite) is aimed at database admins and sysadmins who wish to use PostgreSQL to manage large-scale data while optimizing the database systems for high availability and performance.

By the end of this training, participants will be able to:

• Install and configure PostgreSQL.
• Understand the features and architecture of PostgreSQL.
• Learn how to perform database operations with SQL.
• Perform database backup and recovery to prevent data loss.
• Configure the server for high availability.
• Learn the tools and techniques for high performance tuning.
• Explore the external tools for monitoring and observability.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction

• Overview of PostgreSQL features and architecture
• Basics of database administration

Getting Started

• Installing PostgreSQL
• Configuring a pgAdmin 4 client
• Creating a database user
• Creating a database

Database Operations

• SQL syntax, functions, and operators
• Data definition and data types
• User-defined data types
• Table schemas
• Creating and modifying tables
• Manipulating data in tables
• Querying and filtering data
• Exploring constraints
• Conversion functions
• Joining multiple tables
• Combining queries
• Indexes and full-text search
• Concurrency control
• Regular expressions

Database Server Administration

• Security and encryption
• Access roles and permissions
• Backup and restore
• Upgrading PostgreSQL
• High availability
• Load balancing and replication
• Statistics and monitoring

High Performance Tuning

• Resource management
• Monitoring query performance
• Parameter tuning
• Index optimization
• Query optimization
• Scalability and partitioning

External Tools for Monitoring and Observability

• pgAdmin
• pgBadger
• pgbench
• node-exporter Prometheus, Grafana
• Zabbix

Troubleshooting

Summary and Next Steps