PostgreSQL Administration, Optimization and Replication

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

Audience

• Database administrators
• Systems administrators
• Developers with database experience

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.

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