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