Postgresql Advanced Administration

Duration

3 days

Objective

An instructor-led training (onsite or remote), participants will learn the general and advanced aspects of PostgreSQL, including administration, performance turning and replication.

Pre-requisite

Basic PostgreSQL Administration

Experience with SQL

Experience with the Linux / Windows command line

Module 1: PostgreSQL Advanced 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

Module 2. 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

Module 3. 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

Connection Pooling

Sharding and Scaling PostgreSQL

Programme scope, delivery arrangements and suitability are confirmed during enquiry.