PostgreSQL Administration, Optimization and Replication Training Course

Overview

PostgreSQL (or Postgres) is one of the most popular open-source relational databases.

In this instructor-led, live training (onsite or remote), participants will learn the general and advanced aspects of PostgreSQL, including administration, performance turning, advanced development, and replication as they step through a series of hands-on, live-lab exercises involving setup, configuration, testing, deployment and troubleshooting.

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

  • Use advanced administration techniques to configure and manage a PostgreSQL server.
  • Optimize the database and queries for maximum performance.
  • Replicate and scale a PostgreSQL server.

Format of the Course

  • Part lecture, part discussion, exercises and heavy hands-on practice

Note

  • This course is based PostreSQL 9 but can be adapted for a different version.
  • To request a customized training for this course, please contact us to arrange.

Requirements

  • Experience with SQL
  • Experience with the Linux command line
  • Experience with basic UNIX shell programming

Audience

  • Database administrators
  • Systems administrators
  • Developers with database experience

Course Outline

Introduction

PostgresSQL Setup and Configuration

Overview of PostgreSQL Architecture and Advanced Features

PostgreSQL Administration

  • Connecting to the Database
  • Overview of PgAdmin III
  • Configuring the Server
  • Setting up and Managing Users
  • Overview of Database Object Types
  • Setting Permissions & Security
  • Querying the Database
  • Monitoring the Database
  • Ensuring Atomicity and Isolation
  • Backing up and Restoring PostreSQL
  • Migrating Data to Another System
  • Working with PostreSQL Logs

Performance Optimization

  • Reducing I/O with HOT
  • Locking & Concurrency
  • Evaluating the Design of the Database
  • Optimizing SQL Queries with EXPLAIN
  • Inspecting the Workload
  • Benchmarking and Profiling
  • Optimzing Joins
  • Using Indexes Efficiently

PostgreSQL Replication

  • Overview of PostgreSQL Replication
  • High Availability in PostgreSQL
  • Streaming Replication
  • Managing Clusters Using repmgr
  • Running Queries on Database in Hot Standby
  • Synchronous Replication
  • File based Log Shipping
  • Trigger-based Logical Replication with Slony & Londiste
  • Connection Pooling
  • Upgrading PostgreSQL
  • Sharding and Scaling PostgreSQL

Summary and Conclusion

Leave a Reply

Your email address will not be published. Required fields are marked *