PostgreSQL Administration, Optimization and Replication Training Course

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